1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.simple;
17
18 import static examples.simple.AddressDynamicSqlSupport.address;
19 import static examples.simple.PersonDynamicSqlSupport.addressId;
20 import static examples.simple.PersonDynamicSqlSupport.birthDate;
21 import static examples.simple.PersonDynamicSqlSupport.employed;
22 import static examples.simple.PersonDynamicSqlSupport.firstName;
23 import static examples.simple.PersonDynamicSqlSupport.id;
24 import static examples.simple.PersonDynamicSqlSupport.lastName;
25 import static examples.simple.PersonDynamicSqlSupport.occupation;
26 import static examples.simple.PersonDynamicSqlSupport.person;
27 import static org.assertj.core.api.Assertions.assertThat;
28 import static org.assertj.core.api.Assertions.assertThatExceptionOfType;
29 import static org.mybatis.dynamic.sql.SqlBuilder.*;
30
31 import java.io.InputStream;
32 import java.io.InputStreamReader;
33 import java.sql.Connection;
34 import java.sql.DriverManager;
35 import java.util.Collection;
36 import java.util.Date;
37 import java.util.List;
38 import java.util.Map;
39 import java.util.Optional;
40
41 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
42 import org.apache.ibatis.jdbc.ScriptRunner;
43 import org.apache.ibatis.mapping.Environment;
44 import org.apache.ibatis.session.Configuration;
45 import org.apache.ibatis.session.SqlSession;
46 import org.apache.ibatis.session.SqlSessionFactory;
47 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
48 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
49 import org.junit.jupiter.api.BeforeEach;
50 import org.junit.jupiter.api.Test;
51 import org.mybatis.dynamic.sql.AndOrCriteriaGroup;
52 import org.mybatis.dynamic.sql.ColumnAndConditionCriterion;
53 import org.mybatis.dynamic.sql.NullCriterion;
54 import org.mybatis.dynamic.sql.SortSpecification;
55 import org.mybatis.dynamic.sql.SqlCriterion;
56 import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
57 import org.mybatis.dynamic.sql.dsl.CountDSL;
58 import org.mybatis.dynamic.sql.dsl.CountDSLCompleter;
59 import org.mybatis.dynamic.sql.dsl.DeleteDSL;
60 import org.mybatis.dynamic.sql.dsl.DeleteDSLCompleter;
61 import org.mybatis.dynamic.sql.dsl.SelectDSL;
62 import org.mybatis.dynamic.sql.dsl.SelectDSLCompleter;
63 import org.mybatis.dynamic.sql.dsl.UpdateDSL;
64 import org.mybatis.dynamic.sql.exception.NonRenderingWhereClauseException;
65 import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
66 import org.mybatis.dynamic.sql.render.RenderingStrategies;
67 import org.mybatis.dynamic.sql.select.HavingApplier;
68 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
69 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
70 import org.mybatis.dynamic.sql.where.WhereApplier;
71
72 class PersonMapperV2Test {
73
74 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
75 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
76
77 private SqlSessionFactory sqlSessionFactory;
78
79 @BeforeEach
80 void setup() throws Exception {
81 Class.forName(JDBC_DRIVER);
82 try (InputStream is = getClass().getResourceAsStream("/examples/simple/CreateSimpleDB.sql")) {
83 assert is != null;
84 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "");
85 InputStreamReader isr = new InputStreamReader(is)) {
86 ScriptRunner sr = new ScriptRunner(connection);
87 sr.setLogWriter(null);
88 sr.runScript(isr);
89 }
90 }
91
92 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
93 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
94 Configuration config = new Configuration(environment);
95 config.addMapper(PersonMapperV2.class);
96 config.addMapper(PersonWithAddressMapperV2.class);
97 config.addMapper(CommonSelectMapper.class);
98 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
99 }
100
101 @Test
102 void testSelect() {
103 try (SqlSession session = sqlSessionFactory.openSession()) {
104 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
105
106 List<PersonRecord> rows = mapper.select(c ->
107 c.where(id, isEqualTo(1))
108 .or(occupation, isNull()));
109
110 assertThat(rows).hasSize(3);
111 }
112 }
113
114 @Test
115 void testSelectEmployed() {
116 try (SqlSession session = sqlSessionFactory.openSession()) {
117 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
118
119 List<PersonRecord> rows = mapper.select(c ->
120 c.where(employed, isTrue())
121 .orderBy(id));
122
123 assertThat(rows).hasSize(4);
124 assertThat(rows.get(0).id()).isEqualTo(1);
125 }
126 }
127
128 @Test
129 void testSelectUnemployed() {
130 try (SqlSession session = sqlSessionFactory.openSession()) {
131 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
132
133 List<PersonRecord> rows = mapper.select(c ->
134 c.where(employed, isFalse())
135 .orderBy(id));
136
137 assertThat(rows).hasSize(2);
138 assertThat(rows.get(0).id()).isEqualTo(3);
139 }
140 }
141
142 @Test
143 void testSelectWithTypeConversion() throws NumberFormatException {
144 try (SqlSession session = sqlSessionFactory.openSession()) {
145 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
146
147 List<PersonRecord> rows = mapper.select(c ->
148 c.where(id, isEqualTo("1").map(Integer::parseInt))
149 .or(occupation, isNull()));
150
151 assertThat(rows).hasSize(3);
152 }
153 }
154
155 @Test
156 void testSelectWithTypeConversionAndFilterAndNull() throws NumberFormatException{
157 try (SqlSession session = sqlSessionFactory.openSession()) {
158 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
159
160 List<PersonRecord> rows = mapper.select(c ->
161 c.where(id, isEqualToWhenPresent((String) null).map(Integer::parseInt))
162 .or(occupation, isNull()));
163
164 assertThat(rows).hasSize(2);
165 }
166 }
167
168
169 @Test
170 void testGeneralSelect() {
171 try (SqlSession session = sqlSessionFactory.openSession()) {
172 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
173
174 SelectStatementProvider selectStatement = SelectDSL.select(id.as("A_ID"), firstName, lastName, birthDate, employed,
175 occupation, addressId)
176 .from(person)
177 .where(id, isEqualTo(1))
178 .or(occupation, isNull())
179 .build()
180 .render(RenderingStrategies.MYBATIS3);
181
182 List<PersonRecord> rows = mapper.selectMany(selectStatement);
183 assertThat(rows).hasSize(3);
184 }
185 }
186
187 @Test
188 void testSelectAll() {
189 try (SqlSession session = sqlSessionFactory.openSession()) {
190 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
191
192 List<PersonRecord> rows = mapper.select(SelectDSLCompleter.allRows());
193
194 assertThat(rows).hasSize(6);
195 assertThat(rows.get(0).id()).isEqualTo(1);
196 assertThat(rows.get(5).id()).isEqualTo(6);
197 }
198 }
199
200 @Test
201 void testSelectAllOrdered() {
202 try (SqlSession session = sqlSessionFactory.openSession()) {
203 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
204
205 List<PersonRecord> rows = mapper
206 .select(SelectDSLCompleter.allRowsOrderedBy(lastName.descending(), firstName.descending()));
207
208 assertThat(rows).hasSize(6);
209 assertThat(rows.get(0).id()).isEqualTo(5);
210 assertThat(rows.get(5).id()).isEqualTo(1);
211 }
212 }
213
214 @Test
215 void testSelectDistinct() {
216 try (SqlSession session = sqlSessionFactory.openSession()) {
217 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
218
219 List<PersonRecord> rows = mapper.selectDistinct(c ->
220 c.where(id, isGreaterThan(1))
221 .or(occupation, isNull()));
222
223 assertThat(rows).hasSize(5);
224 }
225 }
226
227 @Test
228 void testSelectWithTypeHandler() {
229 try (SqlSession session = sqlSessionFactory.openSession()) {
230 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
231
232 List<PersonRecord> rows = mapper.select(c ->
233 c.where(employed, isEqualTo(false))
234 .orderBy(id));
235
236 assertThat(rows).hasSize(2);
237 assertThat(rows.get(0).id()).isEqualTo(3);
238 assertThat(rows.get(1).id()).isEqualTo(6);
239 }
240 }
241
242 @Test
243 void testSelectByPrimaryKeyWithMissingRecord() {
244 try (SqlSession session = sqlSessionFactory.openSession()) {
245 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
246
247 Optional<PersonRecord> row = mapper.selectByPrimaryKey(300);
248 assertThat(row).isNotPresent();
249 }
250 }
251
252 @Test
253 void testFirstNameIn() {
254 try (SqlSession session = sqlSessionFactory.openSession()) {
255 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
256
257 List<PersonRecord> rows = mapper.select(c ->
258 c.where(firstName, isIn("Fred", "Barney")));
259
260 assertThat(rows).hasSize(2);
261 assertThat(rows.get(0))
262 .isNotNull()
263 .extracting("lastName").isNotNull()
264 .extracting("name").isEqualTo("Flintstone");
265 assertThat(rows.get(1))
266 .isNotNull()
267 .extracting("lastName").isNotNull()
268 .extracting("name").isEqualTo("Rubble");
269 }
270 }
271
272 @Test
273 void testOrderByCollection() {
274 Collection<SortSpecification> orderByColumns = List.of(firstName);
275
276 try (SqlSession session = sqlSessionFactory.openSession()) {
277 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
278
279 List<PersonRecord> rows = mapper.select(c -> c
280 .where(firstName, isIn("Fred", "Barney"))
281 .orderBy(orderByColumns)
282 );
283
284 assertThat(rows).hasSize(2);
285 assertThat(rows.get(0))
286 .isNotNull()
287 .extracting("lastName").isNotNull()
288 .extracting("name").isEqualTo("Rubble");
289 assertThat(rows.get(1))
290 .isNotNull()
291 .extracting("lastName").isNotNull()
292 .extracting("name").isEqualTo("Flintstone");
293 }
294 }
295
296 @Test
297 void testDelete() {
298 try (SqlSession session = sqlSessionFactory.openSession()) {
299 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
300 int rows = mapper.delete(c ->
301 c.where(occupation, isNull()));
302 assertThat(rows).isEqualTo(2);
303 }
304 }
305
306
307 @Test
308 void testGeneralDelete() {
309 try (SqlSession session = sqlSessionFactory.openSession()) {
310 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
311
312 DeleteStatementProvider deleteStatement = DeleteDSL.deleteFrom(person)
313 .where(occupation, isNull())
314 .build()
315 .render(RenderingStrategies.MYBATIS3);
316
317 int rows = mapper.delete(deleteStatement);
318 assertThat(rows).isEqualTo(2);
319 }
320 }
321
322 @Test
323 void testDeleteAll() {
324 try (SqlSession session = sqlSessionFactory.openSession()) {
325 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
326 int rows = mapper.delete(DeleteDSLCompleter.allRows());
327
328 assertThat(rows).isEqualTo(6);
329 }
330 }
331
332 @Test
333 void testDeleteByPrimaryKey() {
334 try (SqlSession session = sqlSessionFactory.openSession()) {
335 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
336 int rows = mapper.deleteByPrimaryKey(2);
337
338 assertThat(rows).isEqualTo(1);
339 }
340 }
341
342 @Test
343 void testInsert() {
344 try (SqlSession session = sqlSessionFactory.openSession()) {
345 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
346 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
347
348 int rows = mapper.insert(row);
349 assertThat(rows).isEqualTo(1);
350 }
351 }
352
353 @Test
354 void testRawInsert() {
355 try (SqlSession session = sqlSessionFactory.openSession()) {
356 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
357 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
358
359 InsertStatementProvider<PersonRecord> insertStatement = insert(row).into(person)
360 .withMappedColumn(id)
361 .withMappedColumn(firstName)
362 .withMappedColumn(lastName)
363 .withMappedColumn(birthDate)
364 .withMappedColumn(employed)
365 .withMappedColumn(occupation)
366 .withMappedColumn(addressId)
367 .build().render(RenderingStrategies.MYBATIS3);
368
369 int rows = mapper.insert(insertStatement);
370 assertThat(rows).isEqualTo(1);
371 }
372 }
373
374 @Test
375 void testGeneralInsert() {
376 try (SqlSession session = sqlSessionFactory.openSession()) {
377 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
378 int rows = mapper.generalInsert(c ->
379 c.set(id).toValue(100)
380 .set(firstName).toValue("Joe")
381 .set(lastName).toValue(new LastName("Jones"))
382 .set(birthDate).toValue(new Date())
383 .set(employed).toValue(true)
384 .set(occupation).toValue("Developer")
385 .set(addressId).toValue(1)
386 );
387
388 assertThat(rows).isEqualTo(1);
389 }
390 }
391
392 @Test
393 void testInsertMultiple() {
394 try (SqlSession session = sqlSessionFactory.openSession()) {
395 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
396
397 List<PersonRecord> records = List.of(
398 new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1),
399 new PersonRecord(101, "Sarah", new LastName("Smith"), new Date(), true, "Architect", 2)
400 );
401
402 int rows = mapper.insertMultiple(records);
403 assertThat(rows).isEqualTo(2);
404 }
405 }
406
407 @Test
408 void testInsertSelective() {
409 try (SqlSession session = sqlSessionFactory.openSession()) {
410 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
411 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), false, null, 1);
412
413 int rows = mapper.insertSelective(row);
414 assertThat(rows).isEqualTo(1);
415 }
416 }
417
418 @Test
419 void testUpdateByPrimaryKeyNullKeyShouldThrowException() {
420 try (SqlSession session = sqlSessionFactory.openSession()) {
421 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
422 PersonRecord row = new PersonRecord(null, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
423
424 assertThatExceptionOfType(NonRenderingWhereClauseException.class).isThrownBy(() -> mapper.updateByPrimaryKey(row));
425 }
426 }
427
428 @Test
429 void testUpdateByPrimaryKey() {
430 try (SqlSession session = sqlSessionFactory.openSession()) {
431 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
432 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
433
434 int rows = mapper.insert(row);
435 assertThat(rows).isEqualTo(1);
436
437 row = row.withOccupation("Programmer");
438 rows = mapper.updateByPrimaryKey(row);
439 assertThat(rows).isEqualTo(1);
440
441 Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
442 assertThat(newRecord).hasValueSatisfying(r ->
443 assertThat(r.occupation()).isEqualTo("Programmer"));
444 }
445 }
446
447 @Test
448 void testUpdateByPrimaryKeyDelayedWhere() {
449 try (SqlSession session = sqlSessionFactory.openSession()) {
450 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
451 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
452
453 int rows = mapper.insert(row);
454 assertThat(rows).isEqualTo(1);
455
456 row = row.withOccupation("Programmer");
457
458 var start = UpdateDSL.update(person)
459 .set(firstName).equalToOrNull(row::firstName)
460 .set(lastName).equalToOrNull(row::lastName)
461 .set(birthDate).equalToOrNull(row::birthDate)
462 .set(employed).equalToOrNull(row::employed)
463 .set(occupation).equalToOrNull(row::occupation)
464 .set(addressId).equalToOrNull(row::addressId)
465 .where();
466
467 var statement = start.and(id, isEqualToWhenPresent(row::id))
468 .build().render(RenderingStrategies.MYBATIS3);
469
470
471 rows = mapper.update(statement);
472 assertThat(rows).isEqualTo(1);
473
474 Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
475 assertThat(newRecord).hasValueSatisfying(r ->
476 assertThat(r.occupation()).isEqualTo("Programmer"));
477 }
478 }
479
480 @Test
481 void testUpdateByPrimaryKeySelective() {
482 try (SqlSession session = sqlSessionFactory.openSession()) {
483 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
484 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
485
486 int rows = mapper.insert(row);
487 assertThat(rows).isEqualTo(1);
488
489 PersonRecord updateRecord = new PersonRecord(100, null, null, null, null, "Programmer", null);
490 rows = mapper.updateByPrimaryKeySelective(updateRecord);
491 assertThat(rows).isEqualTo(1);
492
493 Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
494 assertThat(newRecord).hasValueSatisfying(r -> {
495 assertThat(r.occupation()).isEqualTo("Programmer");
496 assertThat(r.firstName()).isEqualTo("Joe");
497 });
498 }
499 }
500
501 @Test
502 void testUpdate() {
503 try (SqlSession session = sqlSessionFactory.openSession()) {
504 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
505 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
506
507 int rows = mapper.insert(row);
508 assertThat(rows).isEqualTo(1);
509
510 PersonRecord updateRow = row.withOccupation("Programmer");
511
512 rows = mapper.update(c ->
513 PersonMapperV2.updateAllColumns(updateRow, c)
514 .where(id, isEqualTo(100))
515 .and(firstName, isEqualTo("Joe")));
516
517 assertThat(rows).isEqualTo(1);
518
519 Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
520 assertThat(newRecord).hasValueSatisfying(r ->
521 assertThat(r.occupation()).isEqualTo("Programmer"));
522 }
523 }
524
525 @Test
526 void testUpdateOneField() {
527 try (SqlSession session = sqlSessionFactory.openSession()) {
528 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
529 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
530
531 int rows = mapper.insert(row);
532 assertThat(rows).isEqualTo(1);
533
534 rows = mapper.update(c ->
535 c.set(occupation).equalTo("Programmer")
536 .where(id, isEqualTo(100)));
537
538 assertThat(rows).isEqualTo(1);
539
540 Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
541 assertThat(newRecord).hasValueSatisfying(r ->
542 assertThat(r.occupation()).isEqualTo("Programmer"));
543 }
544 }
545
546 @Test
547 void testUpdateAll() {
548 try (SqlSession session = sqlSessionFactory.openSession()) {
549 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
550 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
551
552 int rows = mapper.insert(row);
553 assertThat(rows).isEqualTo(1);
554
555 PersonRecord updateRecord = new PersonRecord(null, null, null, null, null, "Programmer", null);
556 rows = mapper.update(c ->
557 PersonMapperV2.updateSelectiveColumns(updateRecord, c));
558
559 assertThat(rows).isEqualTo(7);
560
561 Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
562 assertThat(newRecord).hasValueSatisfying(r ->
563 assertThat(r.occupation()).isEqualTo("Programmer"));
564 }
565 }
566
567 @Test
568 void testUpdateSelective() {
569 try (SqlSession session = sqlSessionFactory.openSession()) {
570 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
571 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
572
573 int rows = mapper.insert(row);
574 assertThat(rows).isEqualTo(1);
575
576 PersonRecord updateRecord = new PersonRecord(null, null, null, null, null, "Programmer", null);
577 rows = mapper.update(c ->
578 PersonMapperV2.updateSelectiveColumns(updateRecord, c)
579 .where(id, isEqualTo(100)));
580
581 assertThat(rows).isEqualTo(1);
582
583 Optional<PersonRecord> newRecord = mapper.selectByPrimaryKey(100);
584 assertThat(newRecord).hasValueSatisfying(r ->
585 assertThat(r.occupation()).isEqualTo("Programmer"));
586 }
587 }
588
589 @Test
590 void testCount() {
591 try (SqlSession session = sqlSessionFactory.openSession()) {
592 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
593 long rows = mapper.count(c ->
594 c.where(occupation, isNull()));
595
596 assertThat(rows).isEqualTo(2L);
597 }
598 }
599
600 @Test
601 void testCountWithDelayedWhere() {
602 try (SqlSession session = sqlSessionFactory.openSession()) {
603 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
604 var start = CountDSL.countFrom(person).where();
605 var statement = start.and(occupation, isNull()).build().render(RenderingStrategies.MYBATIS3);
606 long rows = mapper.count(statement);
607
608 assertThat(rows).isEqualTo(2L);
609 }
610 }
611
612 @Test
613 void testCountWithAlias() {
614 try (SqlSession session = sqlSessionFactory.openSession()) {
615 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
616 var statement = CountDSL.countFrom(person, "p")
617 .where(occupation, isNull())
618 .build().render(RenderingStrategies.MYBATIS3);
619 long rows = mapper.count(statement);
620
621 assertThat(rows).isEqualTo(2L);
622 }
623 }
624
625 @Test
626 void testCountAll() {
627 try (SqlSession session = sqlSessionFactory.openSession()) {
628 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
629 long rows = mapper.count(CountDSLCompleter.allRows());
630
631 assertThat(rows).isEqualTo(6L);
632 }
633 }
634
635 @Test
636 void testCountLastName() {
637 try (SqlSession session = sqlSessionFactory.openSession()) {
638 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
639 long rows = mapper.count(lastName, CountDSLCompleter.allRows());
640
641 assertThat(rows).isEqualTo(6L);
642 }
643 }
644
645 @Test
646 void testCountDistinctLastName() {
647 try (SqlSession session = sqlSessionFactory.openSession()) {
648 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
649 long rows = mapper.countDistinct(lastName, CountDSLCompleter.allRows());
650
651 assertThat(rows).isEqualTo(2L);
652 }
653 }
654
655 @Test
656 void testTypeHandledLike() {
657 try (SqlSession session = sqlSessionFactory.openSession()) {
658 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
659
660 List<PersonRecord> rows = mapper.select(c ->
661 c.where(lastName, isLike(new LastName("Fl%")))
662 .orderBy(id));
663
664 assertThat(rows).hasSize(3);
665 assertThat(rows.get(0).firstName()).isEqualTo("Fred");
666 }
667 }
668
669 @Test
670 void testTypeHandledNotLike() {
671 try (SqlSession session = sqlSessionFactory.openSession()) {
672 PersonMapperV2 mapper = session.getMapper(PersonMapperV2.class);
673
674 List<PersonRecord> rows = mapper.select(c ->
675 c.where(lastName, isNotLike(new LastName("Fl%")))
676 .orderBy(id));
677
678 assertThat(rows).hasSize(3);
679 assertThat(rows.get(0).firstName()).isEqualTo("Barney");
680 }
681 }
682
683 @Test
684 void testJoinAllRows() {
685 try (SqlSession session = sqlSessionFactory.openSession()) {
686 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
687 List<PersonWithAddress> records = mapper.select(
688 SelectDSLCompleter.allRowsOrderedBy(id)
689 );
690
691 assertThat(records).hasSize(6);
692 assertThat(records.get(0).getId()).isEqualTo(1);
693 assertThat(records.get(0).getEmployed()).isTrue();
694 assertThat(records.get(0).getFirstName()).isEqualTo("Fred");
695 assertThat(records.get(0).getLastName()).isEqualTo(new LastName("Flintstone"));
696 assertThat(records.get(0).getOccupation()).isEqualTo("Brontosaurus Operator");
697 assertThat(records.get(0).getBirthDate()).isNotNull();
698 assertThat(records.get(0).getAddress()).isNotNull()
699 .extracting("id", "streetAddress", "city", "state", "addressType")
700 .containsExactly(1, "123 Main Street", "Bedrock", "IN", AddressRecord.AddressType.HOME);
701
702 assertThat(records.get(4).getAddress()).isNotNull()
703 .extracting("addressType")
704 .isEqualTo(AddressRecord.AddressType.BUSINESS);
705 }
706 }
707
708 @Test
709 void testJoinOneRow() {
710 try (SqlSession session = sqlSessionFactory.openSession()) {
711 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
712 List<PersonWithAddress> records = mapper.select(c -> c.where(id, isEqualTo(1)));
713
714 assertThat(records).hasSize(1);
715 assertThat(records.get(0).getId()).isEqualTo(1);
716 assertThat(records.get(0).getEmployed()).isTrue();
717 assertThat(records.get(0).getFirstName()).isEqualTo("Fred");
718 assertThat(records.get(0).getLastName()).isEqualTo(new LastName("Flintstone"));
719 assertThat(records.get(0).getOccupation()).isEqualTo("Brontosaurus Operator");
720 assertThat(records.get(0).getBirthDate()).isNotNull();
721 assertThat(records.get(0).getAddress()).isNotNull()
722 .extracting("id", "streetAddress", "city", "state", "addressType")
723 .containsExactly(1, "123 Main Street", "Bedrock", "IN", AddressRecord.AddressType.HOME);
724 }
725 }
726
727 @Test
728 void testJoinPrimaryKey() {
729 try (SqlSession session = sqlSessionFactory.openSession()) {
730 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
731 Optional<PersonWithAddress> row = mapper.selectByPrimaryKey(1);
732
733 assertThat(row).hasValueSatisfying(r -> {
734 assertThat(r.getId()).isEqualTo(1);
735 assertThat(r.getEmployed()).isTrue();
736 assertThat(r.getFirstName()).isEqualTo("Fred");
737 assertThat(r.getLastName()).isEqualTo(new LastName("Flintstone"));
738 assertThat(r.getOccupation()).isEqualTo("Brontosaurus Operator");
739 assertThat(r.getBirthDate()).isNotNull();
740 assertThat(r.getAddress()).isNotNull()
741 .extracting("id", "streetAddress", "city", "state", "addressType")
742 .containsExactly(1, "123 Main Street", "Bedrock", "IN", AddressRecord.AddressType.HOME);
743 });
744 }
745 }
746
747 @Test
748 void testJoinPrimaryKeyInvalidRecord() {
749 try (SqlSession session = sqlSessionFactory.openSession()) {
750 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
751 Optional<PersonWithAddress> row = mapper.selectByPrimaryKey(55);
752
753 assertThat(row).isEmpty();
754 }
755 }
756
757 @Test
758 void testJoinCount() {
759 try (SqlSession session = sqlSessionFactory.openSession()) {
760 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
761 long count = mapper.count(c -> c.where(person.id, isEqualTo(55)));
762
763 assertThat(count).isZero();
764 }
765 }
766
767 @Test
768 void testJoinCountWithSubcriteria() {
769 try (SqlSession session = sqlSessionFactory.openSession()) {
770 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
771 long count = mapper.count(c -> c.where(person.id, isEqualTo(55), or(person.id, isEqualTo(1))));
772
773 assertThat(count).isEqualTo(1);
774 }
775 }
776
777 @Test
778 void testJoinCountWithSubcriteriaInSingleStatement() {
779 try (SqlSession session = sqlSessionFactory.openSession()) {
780 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
781
782 SelectStatementProvider selectStatement = CountDSL.countFrom(person)
783 .join(address).on(person.id, isEqualTo(address.id))
784 .where(person.id, isEqualTo(55), or(person.id, isEqualTo(1)))
785 .build()
786 .render(RenderingStrategies.MYBATIS3);
787
788 String expected = """
789 select count(*)
790 from Person
791 join Address on Person.id = Address.address_id
792 where Person.id = #{parameters.p1,jdbcType=INTEGER} or Person.id = #{parameters.p2,jdbcType=INTEGER}
793 """;
794 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
795
796 long count = mapper.count(selectStatement);
797 assertThat(count).isEqualTo(1);
798 }
799 }
800
801 @Test
802 void testJoinCountInSingleStatement() {
803 try (SqlSession session = sqlSessionFactory.openSession()) {
804 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
805
806 SelectStatementProvider selectStatement = CountDSL.countFrom(person, "p")
807 .join(address, "a").on(person.id, isEqualTo(address.id))
808 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
809 .build()
810 .render(RenderingStrategies.MYBATIS3);
811
812 String expected = """
813 select count(*)
814 from Person p
815 join Address a on p.id = a.address_id
816 """;
817 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
818
819 long count = mapper.count(selectStatement);
820 assertThat(count).isEqualTo(2);
821 }
822 }
823
824 @Test
825 void testJoinCountWhereApplier() {
826 try (SqlSession session = sqlSessionFactory.openSession()) {
827 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
828
829 WhereApplier whereApplier = where(person.id, isLessThan(4)).toWhereApplier();
830
831 SelectStatementProvider selectStatement = CountDSL.countFrom(person, "p")
832 .join(address, "a").on(person.id, isEqualTo(address.id))
833 .applyWhere(whereApplier)
834 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
835 .build()
836 .render(RenderingStrategies.MYBATIS3);
837
838 String expected = """
839 select count(*)
840 from Person p
841 join Address a on p.id = a.address_id
842 where p.id < #{parameters.p1,jdbcType=INTEGER}
843 """;
844 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
845
846 long count = mapper.count(selectStatement);
847 assertThat(count).isEqualTo(2);
848 }
849 }
850
851 @Test
852 void testJoinCountDelayedWhere() {
853 try (SqlSession session = sqlSessionFactory.openSession()) {
854 PersonWithAddressMapperV2 mapper = session.getMapper(PersonWithAddressMapperV2.class);
855
856 var start = CountDSL.countFrom(person, "p")
857 .join(address, "a").on(person.id, isEqualTo(address.id))
858 .where();
859
860 var selectStatement = start
861 .or(person.id, isLessThan(4))
862 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
863 .build()
864 .render(RenderingStrategies.MYBATIS3);
865
866 String expected = """
867 select count(*)
868 from Person p
869 join Address a on p.id = a.address_id
870 where p.id < #{parameters.p1,jdbcType=INTEGER}
871 """;
872 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
873
874 long count = mapper.count(selectStatement);
875 assertThat(count).isEqualTo(2);
876 }
877 }
878
879 @Test
880 void testHaving() {
881 try (SqlSession session = sqlSessionFactory.openSession()) {
882 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
883 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
884 .from(person)
885 .groupBy(lastName)
886 .having(count(), isGreaterThan(1L))
887 .build()
888 .render(RenderingStrategies.MYBATIS3);
889
890 String expected = """
891 select last_name, count(*)
892 from Person
893 group by last_name
894 having count(*) > #{parameters.p1}
895 """;
896 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
897
898 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
899 assertThat(rows).hasSize(2);
900 }
901 }
902
903 @Test
904 void testHavingOrderBy() {
905 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
906 .from(person)
907 .groupBy(lastName)
908 .having(count(), isGreaterThan(1L))
909 .and(count(), isLessThan(3L))
910 .orderBy(lastName)
911 .build()
912 .render(RenderingStrategies.MYBATIS3);
913
914 String expected = """
915 select last_name, count(*)
916 from Person
917 group by last_name
918 having count(*) > #{parameters.p1}
919 and count(*) < #{parameters.p2}
920 order by last_name
921 """;
922 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
923 }
924
925 @Test
926 void testHavingUnion() {
927 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
928 .from(person)
929 .groupBy(lastName)
930 .having(count(), isGreaterThan(1L))
931 .union()
932 .select(lastName, count())
933 .from(person)
934 .groupBy(lastName)
935 .having(count(), isLessThan(5L))
936 .orderBy(lastName)
937 .build()
938 .render(RenderingStrategies.MYBATIS3);
939
940 String expected = """
941 select last_name, count(*)
942 from Person
943 group by last_name
944 having count(*) > #{parameters.p1}
945 union
946 select last_name, count(*)
947 from Person
948 group by last_name
949 having count(*) < #{parameters.p2}
950 order by last_name
951 """;
952 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
953 }
954
955 @Test
956 void testHavingUnionAll() {
957 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
958 .from(person)
959 .groupBy(lastName)
960 .having(count(), isGreaterThan(1L))
961 .unionAll()
962 .select(lastName, count())
963 .from(person)
964 .groupBy(lastName)
965 .having(count(), isLessThan(5L))
966 .orderBy(lastName)
967 .build()
968 .render(RenderingStrategies.MYBATIS3);
969
970 String expected = """
971 select last_name, count(*)
972 from Person
973 group by last_name
974 having count(*) > #{parameters.p1}
975 union all
976 select last_name, count(*)
977 from Person
978 group by last_name
979 having count(*) < #{parameters.p2}
980 order by last_name
981 """;
982 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
983 }
984
985 @Test
986 void testHavingLimit() {
987 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
988 .from(person)
989 .groupBy(lastName)
990 .having(count(), isGreaterThan(1L))
991 .and(count(), isLessThan(3L))
992 .limit(10)
993 .orderBy(lastName)
994 .build()
995 .render(RenderingStrategies.MYBATIS3);
996
997 String expected = """
998 select last_name, count(*)
999 from Person
1000 group by last_name
1001 having count(*) > #{parameters.p1}
1002 and count(*) < #{parameters.p2}
1003 order by last_name
1004 limit #{parameters.p3}
1005 """;
1006 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1007 }
1008
1009 @Test
1010 void testHavingOffset() {
1011 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
1012 .from(person)
1013 .groupBy(lastName)
1014 .having(count(), isGreaterThan(1L))
1015 .and(count(), isLessThan(3L))
1016 .offset(10)
1017 .orderBy(lastName)
1018 .build()
1019 .render(RenderingStrategies.MYBATIS3);
1020
1021 String expected = """
1022 select last_name, count(*)
1023 from Person
1024 group by last_name
1025 having count(*) > #{parameters.p1}
1026 and count(*) < #{parameters.p2}
1027 order by last_name
1028 offset #{parameters.p3} rows
1029 """;
1030 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1031 }
1032
1033 @Test
1034 void testHavingFetchFirst() {
1035 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
1036 .from(person)
1037 .groupBy(lastName)
1038 .having(count(), isGreaterThan(1L))
1039 .and(count(), isLessThan(3L))
1040 .fetchFirst(10).rowsOnly()
1041 .orderBy(lastName)
1042 .build()
1043 .render(RenderingStrategies.MYBATIS3);
1044
1045 String expected = """
1046 select last_name, count(*)
1047 from Person
1048 group by last_name
1049 having count(*) > #{parameters.p1}
1050 and count(*) < #{parameters.p2}
1051 order by last_name
1052 fetch first #{parameters.p3} rows only
1053 """;
1054 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1055 }
1056
1057 @Test
1058 void testHavingNoWait() {
1059 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
1060 .from(person)
1061 .groupBy(lastName)
1062 .having(count(), isGreaterThan(1L))
1063 .and(count(), isLessThan(3L))
1064 .nowait()
1065 .build()
1066 .render(RenderingStrategies.MYBATIS3);
1067
1068 String expected = """
1069 select last_name, count(*)
1070 from Person
1071 group by last_name
1072 having count(*) > #{parameters.p1}
1073 and count(*) < #{parameters.p2}
1074 nowait
1075 """;
1076 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1077 }
1078
1079 @Test
1080 void testHavingForUpdate() {
1081 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
1082 .from(person)
1083 .groupBy(lastName)
1084 .having(count(), isGreaterThan(1L))
1085 .and(count(), isLessThan(3L))
1086 .forUpdate()
1087 .build()
1088 .render(RenderingStrategies.MYBATIS3);
1089
1090 String expected = """
1091 select last_name, count(*)
1092 from Person
1093 group by last_name
1094 having count(*) > #{parameters.p1}
1095 and count(*) < #{parameters.p2}
1096 for update
1097 """;
1098 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1099 }
1100
1101 @Test
1102 void testComplexHaving() {
1103 try (SqlSession session = sqlSessionFactory.openSession()) {
1104 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1105 SelectStatementProvider selectStatement = SelectDSL.select(id, lastName, count())
1106 .from(person)
1107 .groupBy(id, lastName)
1108 .having(
1109 not(id, isGreaterThan(25)),
1110 and(count(), isGreaterThanOrEqualTo(0L))
1111 )
1112 .build()
1113 .render(RenderingStrategies.MYBATIS3);
1114
1115 String expected = """
1116 select id, last_name, count(*)
1117 from Person
1118 group by id, last_name
1119 having not id > #{parameters.p1,jdbcType=INTEGER} and count(*) >= #{parameters.p2}
1120 """;
1121 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1122
1123 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1124 assertThat(rows).hasSize(6);
1125 }
1126 }
1127
1128 @Test
1129 void testHavingApplier() {
1130 try (SqlSession session = sqlSessionFactory.openSession()) {
1131 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1132
1133 HavingApplier havingApplier = having(count(), isGreaterThan(1L)).toHavingApplier();
1134
1135 HavingApplier composedHaving = havingApplier
1136 .andThen(c -> c.and(lastName, isEqualTo(new LastName("Flintstone"))));
1137
1138 SelectStatementProvider selectStatement = SelectDSL.select(lastName, count())
1139 .from(person)
1140 .groupBy(lastName)
1141 .applyHaving(composedHaving)
1142 .build()
1143 .render(RenderingStrategies.MYBATIS3);
1144
1145 String expected = """
1146 select last_name, count(*)
1147 from Person
1148 group by last_name
1149 having count(*) > #{parameters.p1}
1150 and last_name = #{parameters.p2,jdbcType=VARCHAR,typeHandler=examples.simple.LastNameTypeHandler}
1151 """;
1152 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1153
1154 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1155 assertThat(rows).hasSize(1);
1156 }
1157 }
1158
1159 @Test
1160 void testWherePaging1() {
1161 SelectStatementProvider selectStatement = SelectDSL.select(id, lastName)
1162 .from(person)
1163 .where(id, isGreaterThan(25))
1164 .limit(10)
1165 .offset(10)
1166 .nowait()
1167 .forUpdate()
1168 .build()
1169 .render(RenderingStrategies.MYBATIS3);
1170
1171 String expected = """
1172 select id, last_name
1173 from Person
1174 where id > #{parameters.p1,jdbcType=INTEGER}
1175 limit #{parameters.p2}
1176 offset #{parameters.p3}
1177 for update
1178 nowait
1179 """;
1180 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1181 }
1182
1183 @Test
1184 void testWherePaging2() {
1185 SelectStatementProvider selectStatement = SelectDSL.select(id, lastName)
1186 .from(person)
1187 .where(id, isGreaterThan(25))
1188 .fetchFirst(10)
1189 .rowsOnly()
1190 .nowait()
1191 .forUpdate()
1192 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
1193 .build()
1194 .render(RenderingStrategies.MYBATIS3);
1195
1196 String expected = """
1197 select id, last_name
1198 from Person
1199 where id > #{parameters.p1,jdbcType=INTEGER}
1200 fetch first #{parameters.p2} rows only
1201 for update
1202 nowait
1203 """;
1204 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1205 }
1206
1207 @Test
1208 void testWherePaging3() {
1209 try (SqlSession session = sqlSessionFactory.openSession()) {
1210 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1211 SelectStatementProvider selectStatement = SelectDSL.select(id, lastName)
1212 .from(person)
1213 .where(id, isGreaterThan(25))
1214 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
1215 .offset(10)
1216 .fetchFirst(10)
1217 .rowsOnly()
1218 .forUpdate()
1219 .build()
1220 .render(RenderingStrategies.MYBATIS3);
1221
1222 String expected = """
1223 select id, last_name
1224 from Person
1225 where id > #{parameters.p1,jdbcType=INTEGER}
1226 offset #{parameters.p2} rows
1227 fetch first #{parameters.p3} rows only
1228 for update
1229 """;
1230 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1231
1232 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1233 assertThat(rows).isEmpty();
1234 }
1235 }
1236
1237 @Test
1238 void testWhereUnion() {
1239 try (SqlSession session = sqlSessionFactory.openSession()) {
1240 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1241 SelectStatementProvider selectStatement = SelectDSL.select(id, lastName)
1242 .from(person)
1243 .where(id, isGreaterThan(25))
1244 .union()
1245 .select(id, lastName)
1246 .from(person)
1247 .where(id, isLessThan(10))
1248 .build()
1249 .render(RenderingStrategies.MYBATIS3);
1250
1251 String expected = """
1252 select id, last_name
1253 from Person
1254 where id > #{parameters.p1,jdbcType=INTEGER}
1255 union
1256 select id, last_name
1257 from Person
1258 where id < #{parameters.p2,jdbcType=INTEGER}
1259 """;
1260 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1261
1262 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1263 assertThat(rows).hasSize(6);
1264 }
1265 }
1266
1267 @Test
1268 void testWhereUnionAll() {
1269 try (SqlSession session = sqlSessionFactory.openSession()) {
1270 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1271 SelectStatementProvider selectStatement = SelectDSL.select(id, lastName)
1272 .from(person)
1273 .where(id, isGreaterThan(25))
1274 .unionAll()
1275 .selectDistinct(id, lastName)
1276 .from(person)
1277 .where(id, isLessThan(10))
1278 .build()
1279 .render(RenderingStrategies.MYBATIS3);
1280
1281 String expected = """
1282 select id, last_name
1283 from Person
1284 where id > #{parameters.p1,jdbcType=INTEGER}
1285 union all
1286 select distinct id, last_name
1287 from Person
1288 where id < #{parameters.p2,jdbcType=INTEGER}
1289 """;
1290 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1291
1292 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1293 assertThat(rows).hasSize(6);
1294 }
1295 }
1296
1297 @Test
1298 void testWhereApplier() {
1299 var whereApplier = where(id, isGreaterThan(25)).toWhereApplier();
1300 var composedWhere = whereApplier.andThen(c -> c.and(lastName, isEqualTo(new LastName("Flintstone"))));
1301
1302 var selectStatement = SelectDSL.select(id, lastName)
1303 .from(person)
1304 .applyWhere(composedWhere)
1305 .build()
1306 .render(RenderingStrategies.MYBATIS3);
1307
1308 String expected = """
1309 select id, last_name
1310 from Person
1311 where id > #{parameters.p1,jdbcType=INTEGER}
1312 and last_name = #{parameters.p2,jdbcType=VARCHAR,typeHandler=examples.simple.LastNameTypeHandler}
1313 """;
1314 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1315 }
1316
1317 @Test
1318 void testWhereGroupBy() {
1319 var selectStatement = SelectDSL.select(id, lastName, count())
1320 .from(person)
1321 .where(id, isGreaterThan(25))
1322 .groupBy(id, lastName)
1323 .build()
1324 .render(RenderingStrategies.MYBATIS3);
1325
1326 String expected = """
1327 select id, last_name, count(*)
1328 from Person
1329 where id > #{parameters.p1,jdbcType=INTEGER}
1330 group by id, last_name
1331 """;
1332 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1333 }
1334
1335 @Test
1336 void testWhereNoWait() {
1337 var selectStatement = SelectDSL.select(id, lastName)
1338 .from(person)
1339 .where(id, isGreaterThan(25))
1340 .nowait()
1341 .build()
1342 .render(RenderingStrategies.MYBATIS3);
1343
1344 String expected = """
1345 select id, last_name
1346 from Person
1347 where id > #{parameters.p1,jdbcType=INTEGER}
1348 nowait
1349 """;
1350 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1351 }
1352
1353 @Test
1354 void testWhereLimitNoWait() {
1355 var selectStatement = SelectDSL.select(id, lastName)
1356 .from(person)
1357 .where(id, isGreaterThan(25))
1358 .limit(10)
1359 .nowait()
1360 .build()
1361 .render(RenderingStrategies.MYBATIS3);
1362
1363 String expected = """
1364 select id, last_name
1365 from Person
1366 where id > #{parameters.p1,jdbcType=INTEGER}
1367 limit #{parameters.p2}
1368 nowait
1369 """;
1370 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1371 }
1372
1373 @Test
1374 void testWhereOffsetNoWait() {
1375 var selectStatement = SelectDSL.select(id, lastName)
1376 .from(person)
1377 .where(id, isGreaterThan(25))
1378 .offset(10)
1379 .nowait()
1380 .build()
1381 .render(RenderingStrategies.MYBATIS3);
1382
1383 String expected = """
1384 select id, last_name
1385 from Person
1386 where id > #{parameters.p1,jdbcType=INTEGER}
1387 offset #{parameters.p2} rows
1388 nowait
1389 """;
1390 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1391 }
1392
1393 @Test
1394 void testWhereNoForUpdate() {
1395 var selectStatement = SelectDSL.select(id, lastName)
1396 .from(person)
1397 .where(id, isGreaterThan(25))
1398 .forUpdate()
1399 .build()
1400 .render(RenderingStrategies.MYBATIS3);
1401
1402 String expected = """
1403 select id, last_name
1404 from Person
1405 where id > #{parameters.p1,jdbcType=INTEGER}
1406 for update
1407 """;
1408 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1409 }
1410
1411 @Test
1412 void testWhereMultipleCriteria() {
1413 SqlCriterion initialCriterion = new NullCriterion();
1414 List<AndOrCriteriaGroup> groups = List.of(
1415 new AndOrCriteriaGroup.Builder()
1416 .withConnector("and")
1417 .withInitialCriterion(new ColumnAndConditionCriterion.Builder<Integer>()
1418 .withColumn(id)
1419 .withCondition(isGreaterThan(25))
1420 .build()
1421 ).build()
1422 );
1423
1424 var selectStatement = SelectDSL.select(id, lastName)
1425 .from(person)
1426 .where(initialCriterion, groups)
1427 .forUpdate()
1428 .build()
1429 .render(RenderingStrategies.MYBATIS3);
1430
1431 String expected = """
1432 select id, last_name
1433 from Person
1434 where id > #{parameters.p1,jdbcType=INTEGER}
1435 for update
1436 """;
1437 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1438 }
1439
1440 @Test
1441 void testSelectJoinConfigure() {
1442 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1443 .from(person, "p")
1444 .join(address, "a").on(person.id, isEqualTo(address.id))
1445 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
1446 .build()
1447 .render(RenderingStrategies.MYBATIS3);
1448
1449 String expected = """
1450 select p.id, a.city
1451 from Person p
1452 join Address a on p.id = a.address_id
1453 """;
1454 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1455 }
1456
1457 @Test
1458 void testSelectJoinWhere() {
1459 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1460 .from(person, "p")
1461 .join(address, "a").on(person.id, isEqualTo(address.id))
1462 .where(person.id, isGreaterThan(25))
1463 .build()
1464 .render(RenderingStrategies.MYBATIS3);
1465
1466 String expected = """
1467 select p.id, a.city
1468 from Person p
1469 join Address a on p.id = a.address_id
1470 where p.id > #{parameters.p1,jdbcType=INTEGER}
1471 """;
1472 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1473 }
1474
1475 @Test
1476 void testSelectJoinDelayedWhere() {
1477 var start = SelectDSL.select(person.id, address.city)
1478 .from(person, "p")
1479 .join(address, "a").on(person.id, isEqualTo(address.id))
1480 .where();
1481
1482 var selectStatement = start
1483 .and(person.id, isGreaterThan(25))
1484 .build()
1485 .render(RenderingStrategies.MYBATIS3);
1486
1487 String expected = """
1488 select p.id, a.city
1489 from Person p
1490 join Address a on p.id = a.address_id
1491 where p.id > #{parameters.p1,jdbcType=INTEGER}
1492 """;
1493 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1494 }
1495
1496 @Test
1497 void testSelectJoinWhereApplier() {
1498 var whereApplier = where(person.id, isGreaterThan(25)).toWhereApplier();
1499
1500 var selectStatement = SelectDSL.select(person.id, address.city)
1501 .from(person, "p")
1502 .join(address, "a").on(person.id, isEqualTo(address.id))
1503 .applyWhere(whereApplier)
1504 .build()
1505 .render(RenderingStrategies.MYBATIS3);
1506
1507 String expected = """
1508 select p.id, a.city
1509 from Person p
1510 join Address a on p.id = a.address_id
1511 where p.id > #{parameters.p1,jdbcType=INTEGER}
1512 """;
1513 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1514 }
1515
1516 @Test
1517 void testSelectJoinGroupBy() {
1518 var selectStatement = SelectDSL.select(address.city, count())
1519 .from(person, "p")
1520 .join(address, "a").on(person.id, isEqualTo(address.id))
1521 .groupBy(address.city)
1522 .build()
1523 .render(RenderingStrategies.MYBATIS3);
1524
1525 String expected = """
1526 select a.city, count(*)
1527 from Person p
1528 join Address a on p.id = a.address_id
1529 group by a.city
1530 """;
1531 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1532 }
1533
1534 @Test
1535 void testSelectJoinOrderBy() {
1536 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1537 .from(person, "p")
1538 .join(address, "a").on(person.id, isEqualTo(address.id))
1539 .orderBy(person.id, address.city)
1540 .build()
1541 .render(RenderingStrategies.MYBATIS3);
1542
1543 String expected = """
1544 select p.id, a.city
1545 from Person p
1546 join Address a on p.id = a.address_id
1547 order by id, city
1548 """;
1549 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1550 }
1551
1552 @Test
1553 void testSelectJoinLimit() {
1554 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1555 .from(person, "p")
1556 .join(address, "a").on(person.id, isEqualTo(address.id))
1557 .limit(10)
1558 .build()
1559 .render(RenderingStrategies.MYBATIS3);
1560
1561 String expected = """
1562 select p.id, a.city
1563 from Person p
1564 join Address a on p.id = a.address_id
1565 limit #{parameters.p1}
1566 """;
1567 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1568 }
1569
1570 @Test
1571 void testSelectJoinOffset() {
1572 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1573 .from(person, "p")
1574 .join(address, "a").on(person.id, isEqualTo(address.id))
1575 .offset(10)
1576 .build()
1577 .render(RenderingStrategies.MYBATIS3);
1578
1579 String expected = """
1580 select p.id, a.city
1581 from Person p
1582 join Address a on p.id = a.address_id
1583 offset #{parameters.p1} rows
1584 """;
1585 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1586 }
1587
1588 @Test
1589 void testSelectJoinFetchFirst() {
1590 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1591 .from(person, "p")
1592 .join(address, "a").on(person.id, isEqualTo(address.id))
1593 .fetchFirst(10).rowsOnly()
1594 .build()
1595 .render(RenderingStrategies.MYBATIS3);
1596
1597 String expected = """
1598 select p.id, a.city
1599 from Person p
1600 join Address a on p.id = a.address_id
1601 fetch first #{parameters.p1} rows only
1602 """;
1603 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1604 }
1605
1606 @Test
1607 void testSelectJoinNoWait() {
1608 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1609 .from(person, "p")
1610 .join(address, "a").on(person.id, isEqualTo(address.id))
1611 .nowait()
1612 .build()
1613 .render(RenderingStrategies.MYBATIS3);
1614
1615 String expected = """
1616 select p.id, a.city
1617 from Person p
1618 join Address a on p.id = a.address_id
1619 nowait
1620 """;
1621 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1622 }
1623
1624 @Test
1625 void testSelectJoinForUpdate() {
1626 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1627 .from(person, "p")
1628 .join(address, "a").on(person.id, isEqualTo(address.id))
1629 .forUpdate()
1630 .build()
1631 .render(RenderingStrategies.MYBATIS3);
1632
1633 String expected = """
1634 select p.id, a.city
1635 from Person p
1636 join Address a on p.id = a.address_id
1637 for update
1638 """;
1639 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1640 }
1641
1642 @Test
1643 void testSelectJoinUnion() {
1644 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1645 .from(person, "p")
1646 .join(address, "a").on(person.id, isEqualTo(address.id))
1647 .union()
1648 .select(person.id, address.city)
1649 .from(person, "p")
1650 .join(address, "a").on(person.id, isEqualTo(address.id))
1651 .build()
1652 .render(RenderingStrategies.MYBATIS3);
1653
1654 String expected = """
1655 select p.id, a.city
1656 from Person p
1657 join Address a on p.id = a.address_id
1658 union
1659 select p.id, a.city
1660 from Person p
1661 join Address a on p.id = a.address_id
1662 """;
1663 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1664 }
1665
1666 @Test
1667 void testSelectJoinUnionAll() {
1668 SelectStatementProvider selectStatement = SelectDSL.select(person.id, address.city)
1669 .from(person, "p")
1670 .join(address, "a").on(person.id, isEqualTo(address.id))
1671 .unionAll()
1672 .select(person.id, address.city)
1673 .from(person, "p")
1674 .join(address, "a").on(person.id, isEqualTo(address.id))
1675 .build()
1676 .render(RenderingStrategies.MYBATIS3);
1677
1678 String expected = """
1679 select p.id, a.city
1680 from Person p
1681 join Address a on p.id = a.address_id
1682 union all
1683 select p.id, a.city
1684 from Person p
1685 join Address a on p.id = a.address_id
1686 """;
1687 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
1688 }
1689 }