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