1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.joins;
17
18 import static examples.joins.ItemMasterDynamicSQLSupport.itemMaster;
19 import static examples.joins.OrderLineDynamicSQLSupport.orderLine;
20 import static org.assertj.core.api.Assertions.assertThat;
21 import static org.mybatis.dynamic.sql.SqlBuilder.*;
22
23 import java.io.InputStream;
24 import java.io.InputStreamReader;
25 import java.sql.Connection;
26 import java.sql.DriverManager;
27 import java.util.List;
28 import java.util.Map;
29
30 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
31 import org.apache.ibatis.jdbc.ScriptRunner;
32 import org.apache.ibatis.mapping.Environment;
33 import org.apache.ibatis.session.Configuration;
34 import org.apache.ibatis.session.SqlSession;
35 import org.apache.ibatis.session.SqlSessionFactory;
36 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
37 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
38 import org.junit.jupiter.api.BeforeEach;
39 import org.junit.jupiter.api.Test;
40 import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
41 import org.mybatis.dynamic.sql.render.RenderingStrategies;
42 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
43 import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
44 import org.mybatis.dynamic.sql.util.mybatis3.CommonDeleteMapper;
45 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
46 import org.mybatis.dynamic.sql.util.mybatis3.CommonUpdateMapper;
47
48 class ExistsTest {
49
50 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
51 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
52
53 private SqlSessionFactory sqlSessionFactory;
54
55 @BeforeEach
56 void setup() throws Exception {
57 Class.forName(JDBC_DRIVER);
58 try (InputStream is = getClass().getResourceAsStream("/examples/joins/CreateJoinDB.sql")) {
59 assert is != null;
60 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "");
61 InputStreamReader isr = new InputStreamReader(is)) {
62 ScriptRunner sr = new ScriptRunner(connection);
63 sr.setLogWriter(null);
64 sr.runScript(isr);
65 }
66 }
67
68 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
69 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
70 Configuration config = new Configuration(environment);
71 config.addMapper(JoinMapper.class);
72 config.addMapper(CommonDeleteMapper.class);
73 config.addMapper(CommonSelectMapper.class);
74 config.addMapper(CommonUpdateMapper.class);
75 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
76 }
77
78 @Test
79 void testExists() {
80 try (SqlSession session = sqlSessionFactory.openSession()) {
81 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
82
83 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
84 .from(itemMaster, "im")
85 .where(exists(
86 select(orderLine.allColumns())
87 .from(orderLine, "ol")
88 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
89 ))
90 .orderBy(itemMaster.itemId)
91 .build()
92 .render(RenderingStrategies.MYBATIS3);
93
94 String expectedStatement = "select im.* from ItemMaster im"
95 + " where exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
96 + " order by item_id";
97 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
98
99 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
100
101 assertThat(rows).hasSize(3);
102 Map<String, Object> row = rows.get(0);
103 assertThat(row).containsEntry("ITEM_ID", 22);
104 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
105
106 row = rows.get(1);
107 assertThat(row).containsEntry("ITEM_ID", 33);
108 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
109
110 row = rows.get(2);
111 assertThat(row).containsEntry("ITEM_ID", 44);
112 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
113 }
114 }
115
116 @Test
117 void testNotExists() {
118 try (SqlSession session = sqlSessionFactory.openSession()) {
119 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
120
121 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
122 .from(itemMaster, "im")
123 .where(notExists(
124 select(orderLine.allColumns())
125 .from(orderLine, "ol")
126 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
127 ))
128 .orderBy(itemMaster.itemId)
129 .build()
130 .render(RenderingStrategies.MYBATIS3);
131
132 String expectedStatement = "select im.* from ItemMaster im"
133 + " where not exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
134 + " order by item_id";
135 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
136
137 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
138
139 assertThat(rows).hasSize(1);
140 Map<String, Object> row = rows.get(0);
141 assertThat(row).containsEntry("ITEM_ID", 55);
142 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
143 }
144 }
145
146 @Test
147 void testNotExistsWithNotCriterion() {
148 try (SqlSession session = sqlSessionFactory.openSession()) {
149 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
150
151 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
152 .from(itemMaster, "im")
153 .where(not(exists(
154 select(orderLine.allColumns())
155 .from(orderLine, "ol")
156 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
157 )))
158 .orderBy(itemMaster.itemId)
159 .build()
160 .render(RenderingStrategies.MYBATIS3);
161
162 String expectedStatement = "select im.* from ItemMaster im"
163 + " where not exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
164 + " order by item_id";
165 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
166
167 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
168
169 assertThat(rows).hasSize(1);
170 Map<String, Object> row = rows.get(0);
171 assertThat(row).containsEntry("ITEM_ID", 55);
172 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
173 }
174 }
175
176 @Test
177 void testAndExists() {
178 try (SqlSession session = sqlSessionFactory.openSession()) {
179 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
180
181 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
182 .from(itemMaster, "im")
183 .where(itemMaster.itemId, isEqualTo(22))
184 .and(exists(
185 select(orderLine.allColumns())
186 .from(orderLine, "ol")
187 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
188 ))
189 .orderBy(itemMaster.itemId)
190 .build()
191 .render(RenderingStrategies.MYBATIS3);
192
193 String expectedStatement = "select im.* from ItemMaster im"
194 + " where im.item_id = #{parameters.p1,jdbcType=INTEGER}"
195 + " and exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
196 + " order by item_id";
197 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
198
199 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
200
201 assertThat(rows).hasSize(1);
202 Map<String, Object> row = rows.get(0);
203 assertThat(row).containsEntry("ITEM_ID", 22);
204 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
205 }
206 }
207
208 @Test
209 void testAndExistsAnd() {
210 try (SqlSession session = sqlSessionFactory.openSession()) {
211 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
212
213 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
214 .from(itemMaster, "im")
215 .where(itemMaster.itemId, isEqualTo(22))
216 .and(exists(
217 select(orderLine.allColumns())
218 .from(orderLine, "ol")
219 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
220 ), and(itemMaster.itemId, isGreaterThan(2)))
221 .orderBy(itemMaster.itemId)
222 .build()
223 .render(RenderingStrategies.MYBATIS3);
224
225 String expectedStatement = "select im.* from ItemMaster im"
226 + " where im.item_id = #{parameters.p1,jdbcType=INTEGER}"
227 + " and (exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
228 + " and im.item_id > #{parameters.p2,jdbcType=INTEGER})"
229 + " order by item_id";
230 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
231
232 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
233
234 assertThat(rows).hasSize(1);
235 Map<String, Object> row = rows.get(0);
236 assertThat(row).containsEntry("ITEM_ID", 22);
237 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
238 }
239 }
240
241 @Test
242 void testOrExists() {
243 try (SqlSession session = sqlSessionFactory.openSession()) {
244 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
245
246 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
247 .from(itemMaster, "im")
248 .where(itemMaster.itemId, isEqualTo(22))
249 .or(exists(
250 select(orderLine.allColumns())
251 .from(orderLine, "ol")
252 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
253 ))
254 .orderBy(itemMaster.itemId)
255 .build()
256 .render(RenderingStrategies.MYBATIS3);
257
258 String expectedStatement = "select im.* from ItemMaster im"
259 + " where im.item_id = #{parameters.p1,jdbcType=INTEGER}"
260 + " or exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
261 + " order by item_id";
262 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
263
264 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
265
266 assertThat(rows).hasSize(3);
267 Map<String, Object> row = rows.get(0);
268 assertThat(row).containsEntry("ITEM_ID", 22);
269 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
270
271 row = rows.get(1);
272 assertThat(row).containsEntry("ITEM_ID", 33);
273 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
274
275 row = rows.get(2);
276 assertThat(row).containsEntry("ITEM_ID", 44);
277 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
278 }
279 }
280
281 @Test
282 void testOrExistsAnd() {
283 try (SqlSession session = sqlSessionFactory.openSession()) {
284 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
285
286 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
287 .from(itemMaster, "im")
288 .where(itemMaster.itemId, isEqualTo(22))
289 .or(exists(
290 select(orderLine.allColumns())
291 .from(orderLine, "ol")
292 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
293 ), and(itemMaster.itemId, isGreaterThan(2)))
294 .orderBy(itemMaster.itemId)
295 .build()
296 .render(RenderingStrategies.MYBATIS3);
297
298 String expectedStatement = "select im.* from ItemMaster im"
299 + " where im.item_id = #{parameters.p1,jdbcType=INTEGER}"
300 + " or (exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
301 + " and im.item_id > #{parameters.p2,jdbcType=INTEGER})"
302 + " order by item_id";
303 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
304
305 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
306
307 assertThat(rows).hasSize(3);
308 Map<String, Object> row = rows.get(0);
309 assertThat(row).containsEntry("ITEM_ID", 22);
310 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
311
312 row = rows.get(1);
313 assertThat(row).containsEntry("ITEM_ID", 33);
314 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
315
316 row = rows.get(2);
317 assertThat(row).containsEntry("ITEM_ID", 44);
318 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
319 }
320 }
321
322 @Test
323 void testWhereExistsOr() {
324 try (SqlSession session = sqlSessionFactory.openSession()) {
325 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
326
327 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
328 .from(itemMaster, "im")
329 .where(exists(
330 select(orderLine.allColumns())
331 .from(orderLine, "ol")
332 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
333 ), or(itemMaster.itemId, isEqualTo(22)))
334 .orderBy(itemMaster.itemId)
335 .build()
336 .render(RenderingStrategies.MYBATIS3);
337
338 String expectedStatement = "select im.* from ItemMaster im"
339 + " where exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
340 + " or im.item_id = #{parameters.p1,jdbcType=INTEGER}"
341 + " order by item_id";
342 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
343
344 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
345
346 assertThat(rows).hasSize(3);
347 Map<String, Object> row = rows.get(0);
348 assertThat(row).containsEntry("ITEM_ID", 22);
349 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
350
351 row = rows.get(1);
352 assertThat(row).containsEntry("ITEM_ID", 33);
353 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
354
355 row = rows.get(2);
356 assertThat(row).containsEntry("ITEM_ID", 44);
357 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
358 }
359 }
360
361 @Test
362 void testWhereExistsAnd() {
363 try (SqlSession session = sqlSessionFactory.openSession()) {
364 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
365
366 SelectStatementProvider selectStatement = select(itemMaster.allColumns())
367 .from(itemMaster, "im")
368 .where(exists(
369 select(orderLine.allColumns())
370 .from(orderLine, "ol")
371 .where(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
372 ), and(itemMaster.itemId, isEqualTo(22)))
373 .orderBy(itemMaster.itemId)
374 .build()
375 .render(RenderingStrategies.MYBATIS3);
376
377 String expectedStatement = "select im.* from ItemMaster im"
378 + " where exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)"
379 + " and im.item_id = #{parameters.p1,jdbcType=INTEGER}"
380 + " order by item_id";
381 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
382
383 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
384
385 assertThat(rows).hasSize(1);
386 Map<String, Object> row = rows.get(0);
387 assertThat(row).containsEntry("ITEM_ID", 22);
388 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
389 }
390 }
391
392 @Test
393 void testDeleteWithHardAlias() {
394 try (SqlSession session = sqlSessionFactory.openSession()) {
395 CommonDeleteMapper mapper = session.getMapper(CommonDeleteMapper.class);
396
397 ItemMasterDynamicSQLSupport.ItemMaster im = itemMaster.withAlias("im");
398
399 DeleteStatementProvider deleteStatement = deleteFrom(im)
400 .where(notExists(select(orderLine.allColumns())
401 .from(orderLine, "ol")
402 .where(orderLine.itemId, isEqualTo(im.itemId)))
403 )
404 .build()
405 .render(RenderingStrategies.MYBATIS3);
406
407 String expectedStatement = "delete from ItemMaster im where not exists "
408 + "(select ol.* from OrderLine ol where ol.item_id = im.item_id)";
409 assertThat(deleteStatement.getDeleteStatement()).isEqualTo(expectedStatement);
410
411 int rows = mapper.delete(deleteStatement);
412 assertThat(rows).isEqualTo(1);
413 }
414 }
415
416 @Test
417 void testDeleteWithSoftAlias() {
418 try (SqlSession session = sqlSessionFactory.openSession()) {
419 CommonDeleteMapper mapper = session.getMapper(CommonDeleteMapper.class);
420
421 DeleteStatementProvider deleteStatement = deleteFrom(itemMaster, "im")
422 .where(notExists(select(orderLine.allColumns())
423 .from(orderLine, "ol")
424 .where(orderLine.itemId, isEqualTo(itemMaster.itemId)))
425 )
426 .build()
427 .render(RenderingStrategies.MYBATIS3);
428
429 String expectedStatement = "delete from ItemMaster im where not exists "
430 + "(select ol.* from OrderLine ol where ol.item_id = im.item_id)";
431 assertThat(deleteStatement.getDeleteStatement()).isEqualTo(expectedStatement);
432
433 int rows = mapper.delete(deleteStatement);
434 assertThat(rows).isEqualTo(1);
435 }
436 }
437
438 @Test
439 void testUpdateWithHardAlias() {
440 try (SqlSession session = sqlSessionFactory.openSession()) {
441 CommonUpdateMapper mapper = session.getMapper(CommonUpdateMapper.class);
442
443 ItemMasterDynamicSQLSupport.ItemMaster im = itemMaster.withAlias("im");
444
445 UpdateStatementProvider updateStatement = update(im)
446 .set(im.description).equalTo("No Orders")
447 .where(notExists(select(orderLine.allColumns())
448 .from(orderLine, "ol")
449 .where(orderLine.itemId, isEqualTo(im.itemId)))
450 )
451 .build()
452 .render(RenderingStrategies.MYBATIS3);
453
454 String expectedStatement = "update ItemMaster im "
455 + "set im.description = #{parameters.p1,jdbcType=VARCHAR} "
456 + "where not exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)";
457 assertThat(updateStatement.getUpdateStatement()).isEqualTo(expectedStatement);
458
459 int rows = mapper.update(updateStatement);
460 assertThat(rows).isEqualTo(1);
461 }
462 }
463
464 @Test
465 void testUpdateWithSoftAlias() {
466 try (SqlSession session = sqlSessionFactory.openSession()) {
467 CommonUpdateMapper mapper = session.getMapper(CommonUpdateMapper.class);
468
469 UpdateStatementProvider updateStatement = update(itemMaster, "im")
470 .set(itemMaster.description).equalTo("No Orders")
471 .where(notExists(select(orderLine.allColumns())
472 .from(orderLine, "ol")
473 .where(orderLine.itemId, isEqualTo(itemMaster.itemId)))
474 )
475 .build()
476 .render(RenderingStrategies.MYBATIS3);
477
478 String expectedStatement = "update ItemMaster im "
479 + "set im.description = #{parameters.p1,jdbcType=VARCHAR} "
480 + "where not exists (select ol.* from OrderLine ol where ol.item_id = im.item_id)";
481 assertThat(updateStatement.getUpdateStatement()).isEqualTo(expectedStatement);
482
483 int rows = mapper.update(updateStatement);
484 assertThat(rows).isEqualTo(1);
485 }
486 }
487 }