1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.spring;
17
18 import static examples.spring.AddressDynamicSqlSupport.address;
19 import static examples.spring.PersonDynamicSqlSupport.*;
20 import static org.assertj.core.api.Assertions.assertThat;
21 import static org.mybatis.dynamic.sql.SqlBuilder.*;
22
23 import java.util.Date;
24 import java.util.List;
25 import java.util.Optional;
26
27 import org.junit.jupiter.api.Test;
28 import org.mybatis.dynamic.sql.delete.DeleteModel;
29 import org.mybatis.dynamic.sql.insert.BatchInsertModel;
30 import org.mybatis.dynamic.sql.insert.GeneralInsertModel;
31 import org.mybatis.dynamic.sql.insert.InsertModel;
32 import org.mybatis.dynamic.sql.insert.MultiRowInsertModel;
33 import org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider;
34 import org.mybatis.dynamic.sql.render.RenderingStrategies;
35 import org.mybatis.dynamic.sql.select.SelectModel;
36 import org.mybatis.dynamic.sql.update.UpdateModel;
37 import org.mybatis.dynamic.sql.util.Buildable;
38 import org.mybatis.dynamic.sql.util.spring.NamedParameterJdbcTemplateExtensions;
39 import org.springframework.beans.factory.annotation.Autowired;
40 import org.springframework.jdbc.core.DataClassRowMapper;
41 import org.springframework.jdbc.core.RowMapper;
42 import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;
43 import org.springframework.transaction.annotation.Transactional;
44
45 @SpringJUnitConfig(classes = SpringConfiguration.class)
46 @Transactional
47 class PersonTemplateTest {
48
49 @Autowired
50 private NamedParameterJdbcTemplateExtensions template;
51
52 @Test
53 void testSelect() {
54 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
55 .from(person)
56 .where(id, isEqualTo(1))
57 .or(occupation, isNull());
58
59 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
60
61 assertThat(rows).hasSize(3);
62 }
63
64 @Test
65 void testSelectAll() {
66 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
67 .from(person);
68
69 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
70
71 assertThat(rows).hasSize(6);
72 assertThat(rows.get(0).id()).isEqualTo(1);
73 assertThat(rows.get(5).id()).isEqualTo(6);
74
75 }
76
77 @Test
78 void testSelectAllOrdered() {
79 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
80 .from(person)
81 .orderBy(lastName.descending(), firstName.descending());
82
83 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
84
85 assertThat(rows).hasSize(6);
86 assertThat(rows.get(0).id()).isEqualTo(5);
87 assertThat(rows.get(5).id()).isEqualTo(1);
88
89 }
90
91 @Test
92 void testSelectDistinct() {
93 Buildable<SelectModel> selectStatement = selectDistinct(id, firstName, lastName, birthDate, employed, occupation, addressId)
94 .from(person)
95 .where(id, isGreaterThan(1))
96 .or(occupation, isNull());
97
98 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
99
100 assertThat(rows).hasSize(5);
101 }
102
103 @Test
104 void testSelectWithUnion() {
105 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
106 .from(person)
107 .where(id, isEqualTo(1))
108 .union()
109 .select(id, firstName, lastName, birthDate, employed, occupation, addressId)
110 .from(person)
111 .where(id, isEqualTo(2))
112 .union()
113 .select(id, firstName, lastName, birthDate, employed, occupation, addressId)
114 .from(person)
115 .where(id, isEqualTo(2));
116
117
118 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
119
120 assertThat(rows).hasSize(2);
121 }
122
123 @Test
124 void testSelectWithUnionAll() {
125 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
126 .from(person)
127 .where(id, isEqualTo(1))
128 .union()
129 .select(id, firstName, lastName, birthDate, employed, occupation, addressId)
130 .from(person)
131 .where(id, isEqualTo(2))
132 .unionAll()
133 .select(id, firstName, lastName, birthDate, employed, occupation, addressId)
134 .from(person)
135 .where(id, isEqualTo(2));
136
137
138 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
139
140 assertThat(rows).hasSize(3);
141 }
142
143 @Test
144 void testSelectWithTypeHandler() {
145 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
146 .from(person)
147 .where(employed, isEqualTo(false))
148 .orderBy(id);
149
150 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
151
152 assertThat(rows).hasSize(2);
153 assertThat(rows.get(0).id()).isEqualTo(3);
154 assertThat(rows.get(1).id()).isEqualTo(6);
155 }
156
157 @Test
158 void testSelectBetweenWithTypeHandler() {
159 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
160 .from(person)
161 .where(lastName, isBetween(new LastName("Adams")).and(new LastName("Jones")))
162 .orderBy(id);
163
164 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
165
166 assertThat(rows).hasSize(3);
167 assertThat(rows.get(0).id()).isEqualTo(1);
168 assertThat(rows.get(1).id()).isEqualTo(2);
169 }
170
171 @Test
172 void testSelectListWithTypeHandler() {
173 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
174 .from(person)
175 .where(lastName, isIn(new LastName("Flintstone"), new LastName("Rubble")))
176 .orderBy(id);
177
178 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
179
180 assertThat(rows).hasSize(6);
181 assertThat(rows.get(0).id()).isEqualTo(1);
182 assertThat(rows.get(1).id()).isEqualTo(2);
183 }
184
185 @Test
186 void testSelectByPrimaryKeyWithMissingRecord() {
187 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
188 .from(person)
189 .where(id, isEqualTo(300));
190
191 Optional<PersonRecord> row = template.selectOne(selectStatement, personRowMapper);
192
193 assertThat(row).isNotPresent();
194 }
195
196 @Test
197 void testFirstNameIn() {
198 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
199 .from(person)
200 .where(firstName, isIn("Fred", "Barney"));
201
202 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
203
204 assertThat(rows).hasSize(2)
205 .satisfiesExactly(
206 person1 -> assertThat(person1).isNotNull()
207 .extracting("lastName").isNotNull()
208 .extracting("name").isEqualTo("Flintstone"),
209 person2 -> assertThat(person2).isNotNull()
210 .extracting("lastName").isNotNull()
211 .extracting("name").isEqualTo("Rubble")
212 );
213 }
214
215 @Test
216 void testDelete() {
217 Buildable<DeleteModel> deleteStatement = deleteFrom(person)
218 .where(occupation, isNull());
219
220 int rows = template.delete(deleteStatement);
221
222 assertThat(rows).isEqualTo(2);
223 }
224
225 @Test
226 void testDeleteAll() {
227 Buildable<DeleteModel> deleteStatement = deleteFrom(person);
228
229 int rows = template.delete(deleteStatement);
230
231 assertThat(rows).isEqualTo(6);
232 }
233
234 @Test
235 void testDeleteByPrimaryKey() {
236 Buildable<DeleteModel> deleteStatement = deleteFrom(person)
237 .where(id, isEqualTo(2));
238
239 int rows = template.delete(deleteStatement);
240
241 assertThat(rows).isEqualTo(1);
242 }
243
244 @Test
245 void testInsert() {
246 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
247
248 Buildable<InsertModel<PersonRecord>> insertStatement = insert(row).into(person)
249 .map(id).toProperty("id")
250 .map(firstName).toProperty("firstName")
251 .map(lastName).toProperty("lastNameAsString")
252 .map(birthDate).toProperty("birthDate")
253 .map(employed).toProperty("employedAsString")
254 .map(occupation).toProperty("occupation")
255 .map(addressId).toProperty("addressId");
256
257 int rows = template.insert(insertStatement);
258
259 assertThat(rows).isEqualTo(1);
260 }
261
262 @Test
263 void testGeneralInsert() {
264 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
265 .set(id).toValue(100)
266 .set(firstName).toValue("Joe")
267 .set(lastName).toValue(new LastName("Jones"))
268 .set(birthDate).toValue(new Date())
269 .set(employed).toValue(true)
270 .set(occupation).toValue("Developer")
271 .set(addressId).toValue(1);
272
273 int rows = template.generalInsert(insertStatement);
274
275 assertThat(rows).isEqualTo(1);
276 }
277
278 @Test
279 void testInsertMultiple() {
280
281 List<PersonRecord> records = List.of(
282 new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1),
283 new PersonRecord(101, "Sarah", new LastName("Smith"), new Date(), true, "Architect", 2));
284
285 Buildable<MultiRowInsertModel<PersonRecord>> insertStatement = insertMultiple(records).into(person)
286 .map(id).toProperty("id")
287 .map(firstName).toProperty("firstName")
288 .map(lastName).toProperty("lastNameAsString")
289 .map(birthDate).toProperty("birthDate")
290 .map(employed).toProperty("employedAsString")
291 .map(occupation).toProperty("occupation")
292 .map(addressId).toProperty("addressId");
293
294 int rows = template.insertMultiple(insertStatement);
295
296 assertThat(rows).isEqualTo(2);
297 }
298
299 @Test
300 void testInsertBatch() {
301
302 List<PersonRecord> records = List.of(
303 new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1),
304 new PersonRecord(101, "Sarah", new LastName("Smith"), new Date(), true, "Architect", 2));
305
306 Buildable<BatchInsertModel<PersonRecord>> insertStatement = insertBatch(records).into(person)
307 .map(id).toProperty("id")
308 .map(firstName).toProperty("firstName")
309 .map(lastName).toProperty("lastNameAsString")
310 .map(birthDate).toProperty("birthDate")
311 .map(employed).toProperty("employedAsString")
312 .map(occupation).toProperty("occupation")
313 .map(addressId).toProperty("addressId");
314
315 int[] rows = template.insertBatch(insertStatement);
316
317 assertThat(rows).hasSize(2);
318 assertThat(rows[0]).isEqualTo(1);
319 assertThat(rows[1]).isEqualTo(1);
320 }
321
322 @Test
323 void testInsertSelective() {
324 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), false, null, 1);
325
326 Buildable<InsertModel<PersonRecord>> insertStatement = insert(row).into(person)
327 .map(id).toPropertyWhenPresent("id", row::id)
328 .map(firstName).toPropertyWhenPresent("firstName", row::firstName)
329 .map(lastName).toPropertyWhenPresent("lastNameAsString", row::getLastNameAsString)
330 .map(birthDate).toPropertyWhenPresent("birthDate", row::birthDate)
331 .map(employed).toPropertyWhenPresent("employedAsString", row::getEmployedAsString)
332 .map(occupation).toPropertyWhenPresent("occupation", row::occupation)
333 .map(addressId).toPropertyWhenPresent("addressId", row::addressId);
334
335 int rows = template.insert(insertStatement);
336
337 assertThat(rows).isEqualTo(1);
338 }
339
340 @Test
341 void testGeneralInsertWhenTypeConverterReturnsNull() {
342
343 GeneralInsertStatementProvider insertStatement = insertInto(person)
344 .set(id).toValue(100)
345 .set(firstName).toValue("Joe")
346 .set(lastName).toValueWhenPresent(new LastName("Slate"))
347 .set(birthDate).toValue(new Date())
348 .set(employed).toValue(true)
349 .set(occupation).toValue("Quarry Owner")
350 .set(addressId).toValue(1)
351 .build()
352 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
353
354 assertThat(insertStatement.getInsertStatement())
355 .isEqualTo("insert into Person (id, first_name, birth_date, employed, occupation, address_id) values (:p1, :p2, :p3, :p4, :p5, :p6)");
356 int rows = template.generalInsert(insertStatement);
357 assertThat(rows).isEqualTo(1);
358
359 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
360 .from(person)
361 .where(id, isEqualTo(100));
362 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
363 assertThat(newRecord).hasValueSatisfying(
364 r -> assertThat(r).isNotNull().extracting("lastName").isNotNull().extracting("name").isNull()
365 );
366 }
367
368 @Test
369 void testUpdateByPrimaryKey() {
370
371 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
372 .set(id).toValue(100)
373 .set(firstName).toValue("Joe")
374 .set(lastName).toValue(new LastName("Jones"))
375 .set(birthDate).toValue(new Date())
376 .set(employed).toValue(true)
377 .set(occupation).toValue("Developer")
378 .set(addressId).toValue(1);
379
380 int rows = template.generalInsert(insertStatement);
381 assertThat(rows).isEqualTo(1);
382
383 Buildable<UpdateModel> updateStatement = update(person)
384 .set(occupation).equalTo("Programmer")
385 .where(id, isEqualTo(100));
386
387 rows = template.update(updateStatement);
388 assertThat(rows).isEqualTo(1);
389
390 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
391 .from(person)
392 .where(id, isEqualTo(100));
393 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
394 assertThat(newRecord).hasValueSatisfying(r -> assertThat(r.occupation()).isEqualTo("Programmer"));
395 }
396
397 @Test
398 void testUpdateByPrimaryKeyWithTypeHandler() {
399
400 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
401 .set(id).toValue(100)
402 .set(firstName).toValue("Joe")
403 .set(lastName).toValue(new LastName("Jones"))
404 .set(birthDate).toValue(new Date())
405 .set(employed).toValue(true)
406 .set(occupation).toValue("Developer")
407 .set(addressId).toValue(1);
408
409 int rows = template.generalInsert(insertStatement);
410 assertThat(rows).isEqualTo(1);
411
412 Buildable<UpdateModel> updateStatement = update(person)
413 .set(lastName).equalTo(new LastName("Smith"))
414 .where(id, isEqualTo(100));
415
416 rows = template.update(updateStatement);
417 assertThat(rows).isEqualTo(1);
418
419 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
420 .from(person)
421 .where(id, isEqualTo(100));
422 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
423 assertThat(newRecord).hasValueSatisfying(r -> assertThat(r).isNotNull()
424 .extracting("lastName").isNotNull()
425 .extracting("name").isEqualTo("Smith")
426 );
427 }
428
429 @Test
430 void testUpdateByPrimaryKeySelective() {
431 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
432 .set(id).toValue(100)
433 .set(firstName).toValue("Joe")
434 .set(lastName).toValue(new LastName("Jones"))
435 .set(birthDate).toValue(new Date())
436 .set(employed).toValue(true)
437 .set(occupation).toValue("Developer")
438 .set(addressId).toValue(1);
439
440 int rows = template.generalInsert(insertStatement);
441 assertThat(rows).isEqualTo(1);
442
443 PersonRecord updateRecord = new PersonRecord(100, null, null, null, null, "Programmer", null);
444
445 Buildable<UpdateModel> updateStatement = update(person)
446 .set(firstName).equalToWhenPresent(updateRecord::firstName)
447 .set(lastName).equalToWhenPresent(updateRecord::lastName)
448 .set(birthDate).equalToWhenPresent(updateRecord::birthDate)
449 .set(employed).equalToWhenPresent(updateRecord::employed)
450 .set(occupation).equalToWhenPresent(updateRecord::occupation)
451 .set(addressId).equalToWhenPresent(updateRecord::addressId)
452 .where(id, isEqualTo(updateRecord::id));
453
454 rows = template.update(updateStatement);
455 assertThat(rows).isEqualTo(1);
456
457 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
458 .from(person)
459 .where(id, isEqualTo(100));
460 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
461 assertThat(newRecord).hasValueSatisfying(r -> {
462 assertThat(r.occupation()).isEqualTo("Programmer");
463 assertThat(r.firstName()).isEqualTo("Joe");
464 });
465 }
466
467 @Test
468 void testUpdate() {
469 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
470
471 Buildable<InsertModel<PersonRecord>> insertStatement = insert(row).into(person)
472 .map(id).toProperty("id")
473 .map(firstName).toProperty("firstName")
474 .map(lastName).toProperty("lastNameAsString")
475 .map(birthDate).toProperty("birthDate")
476 .map(employed).toProperty("employedAsString")
477 .map(occupation).toProperty("occupation")
478 .map(addressId).toProperty("addressId");
479
480 int rows = template.insert(insertStatement);
481 assertThat(rows).isEqualTo(1);
482
483 row = row.withOccupation("Programmer");
484
485 Buildable<UpdateModel> updateStatement = update(person)
486 .set(firstName).equalToWhenPresent(row::firstName)
487 .set(lastName).equalToWhenPresent(row::lastName)
488 .set(birthDate).equalToWhenPresent(row::birthDate)
489 .set(employed).equalToWhenPresent(row::employed)
490 .set(occupation).equalToWhenPresent(row::occupation)
491 .set(addressId).equalToWhenPresent(row::addressId)
492 .where(id, isEqualTo(row::id));
493
494 rows = template.update(updateStatement);
495 assertThat(rows).isEqualTo(1);
496
497 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
498 .from(person)
499 .where(id, isEqualTo(100));
500 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
501 assertThat(newRecord).hasValueSatisfying(r -> {
502 assertThat(r.occupation()).isEqualTo("Programmer");
503 assertThat(r.firstName()).isEqualTo("Joe");
504 });
505 }
506
507 @Test
508 void testUpdateAll() {
509 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
510 .set(id).toValue(100)
511 .set(firstName).toValue("Joe")
512 .set(lastName).toValue(new LastName("Jones"))
513 .set(birthDate).toValue(new Date())
514 .set(employed).toValue(true)
515 .set(occupation).toValue("Developer")
516 .set(addressId).toValue(1);
517
518 int rows = template.generalInsert(insertStatement);
519 assertThat(rows).isEqualTo(1);
520
521 Buildable<UpdateModel> updateStatement = update(person)
522 .set(occupation).equalTo("Programmer");
523
524 rows = template.update(updateStatement);
525 assertThat(rows).isEqualTo(7);
526
527 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
528 .from(person)
529 .where(id, isEqualTo(100));
530
531 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
532 assertThat(newRecord).hasValueSatisfying(r -> assertThat(r.occupation()).isEqualTo("Programmer"));
533 }
534
535 @Test
536 void testCount() {
537 Buildable<SelectModel> countStatement = countFrom(person)
538 .where(occupation, isNull());
539
540 long rows = template.count(countStatement);
541 assertThat(rows).isEqualTo(2L);
542 }
543
544 @Test
545 void testCountAll() {
546 Buildable<SelectModel> countStatement = countFrom(person);
547
548 long rows = template.count(countStatement);
549 assertThat(rows).isEqualTo(6L);
550 }
551
552 @Test
553 void testCountLastName() {
554 Buildable<SelectModel> countStatement = countColumn(lastName).from(person);
555
556 long rows = template.count(countStatement);
557 assertThat(rows).isEqualTo(6L);
558 }
559
560 @Test
561 void testCountDistinctLastName() {
562 Buildable<SelectModel> countStatement = countDistinctColumn(lastName).from(person);
563
564 long rows = template.count(countStatement);
565 assertThat(rows).isEqualTo(2L);
566 }
567
568 @Test
569 void testTypeHandledLike() {
570 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
571 .from(person)
572 .where(lastName, isLike(new LastName("Fl%")))
573 .orderBy(id);
574
575 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
576 assertThat(rows).hasSize(3);
577 assertThat(rows.get(0).firstName()).isEqualTo("Fred");
578 }
579
580 @Test
581 void testTypeHandledNotLike() {
582 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
583 .from(person)
584 .where(lastName, isNotLike(new LastName("Fl%")))
585 .orderBy(id);
586
587 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
588
589 assertThat(rows).hasSize(3);
590 assertThat(rows.get(0).firstName()).isEqualTo("Barney");
591 }
592
593 @Test
594 void testAutoMapping() {
595 Buildable<SelectModel> selectStatement = select(address.id.as("id"), address.streetAddress,
596 address.city, address.state)
597 .from(address)
598 .orderBy(address.id);
599
600
601 List<AddressRecord> records = template.selectList(selectStatement,
602 DataClassRowMapper.newInstance(AddressRecord.class));
603
604 assertThat(records).hasSize(2);
605 assertThat(records.get(0).id()).isEqualTo(1);
606 assertThat(records.get(0).streetAddress()).isEqualTo("123 Main Street");
607 assertThat(records.get(0).city()).isEqualTo("Bedrock");
608 assertThat(records.get(0).state()).isEqualTo("IN");
609 }
610
611 @Test
612 void testJoinAllRows() {
613 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
614 address.id, address.streetAddress, address.city, address.state)
615 .from(person)
616 .join(address, on(person.addressId, isEqualTo(address.id)))
617 .orderBy(id);
618
619 List<PersonWithAddress> records = template.selectList(selectStatement, personWithAddressRowMapper);
620
621 assertThat(records).hasSize(6);
622 assertThat(records.get(0).id()).isEqualTo(1);
623 assertThat(records.get(0).employed()).isTrue();
624 assertThat(records.get(0).firstName()).isEqualTo("Fred");
625 assertThat(records.get(0).lastName()).isEqualTo(new LastName("Flintstone"));
626 assertThat(records.get(0).occupation()).isEqualTo("Brontosaurus Operator");
627 assertThat(records.get(0).birthDate()).isNotNull();
628 assertThat(records.get(0).address().id()).isEqualTo(1);
629 assertThat(records.get(0).address().streetAddress()).isEqualTo("123 Main Street");
630 assertThat(records.get(0).address().city()).isEqualTo("Bedrock");
631 assertThat(records.get(0).address().state()).isEqualTo("IN");
632 }
633
634 @Test
635 void testJoinOneRow() {
636 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
637 address.id, address.streetAddress, address.city, address.state)
638 .from(person)
639 .join(address, on(person.addressId, isEqualTo(address.id)))
640 .where(id, isEqualTo(1));
641
642 List<PersonWithAddress> records = template.selectList(selectStatement, personWithAddressRowMapper);
643
644 assertThat(records).hasSize(1);
645 assertThat(records.get(0).id()).isEqualTo(1);
646 assertThat(records.get(0).employed()).isTrue();
647 assertThat(records.get(0).firstName()).isEqualTo("Fred");
648 assertThat(records.get(0).lastName()).isEqualTo(new LastName("Flintstone"));
649 assertThat(records.get(0).occupation()).isEqualTo("Brontosaurus Operator");
650 assertThat(records.get(0).birthDate()).isNotNull();
651 assertThat(records.get(0).address().id()).isEqualTo(1);
652 assertThat(records.get(0).address().streetAddress()).isEqualTo("123 Main Street");
653 assertThat(records.get(0).address().city()).isEqualTo("Bedrock");
654 assertThat(records.get(0).address().state()).isEqualTo("IN");
655 }
656
657 @Test
658 void testJoinPrimaryKey() {
659 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
660 address.id, address.streetAddress, address.city, address.state)
661 .from(person)
662 .join(address, on(person.addressId, isEqualTo(address.id)))
663 .where(id, isEqualTo(1));
664
665 Optional<PersonWithAddress> row = template.selectOne(selectStatement, personWithAddressRowMapper);
666
667 assertThat(row).hasValueSatisfying(r -> {
668 assertThat(r.id()).isEqualTo(1);
669 assertThat(r.employed()).isTrue();
670 assertThat(r.firstName()).isEqualTo("Fred");
671 assertThat(r.lastName()).isEqualTo(new LastName("Flintstone"));
672 assertThat(r.occupation()).isEqualTo("Brontosaurus Operator");
673 assertThat(r.birthDate()).isNotNull();
674 assertThat(r.address().id()).isEqualTo(1);
675 assertThat(r.address().streetAddress()).isEqualTo("123 Main Street");
676 assertThat(r.address().city()).isEqualTo("Bedrock");
677 assertThat(r.address().state()).isEqualTo("IN");
678 });
679 }
680
681 @Test
682 void testJoinPrimaryKeyInvalidRecord() {
683 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
684 address.id, address.streetAddress, address.city, address.state)
685 .from(person)
686 .join(address, on(person.addressId, isEqualTo(address.id)))
687 .where(id, isEqualTo(55));
688
689 Optional<PersonWithAddress> row = template.selectOne(selectStatement, personWithAddressRowMapper);
690 assertThat(row).isEmpty();
691 }
692
693 @Test
694 void testJoinCount() {
695 Buildable<SelectModel> countStatement = countFrom(person)
696 .join(address, on(person.addressId, isEqualTo(address.id)))
697 .where(id, isEqualTo(55));
698
699 long count = template.count(countStatement);
700 assertThat(count).isZero();
701 }
702
703 @Test
704 void testJoinCountWithSubCriteria() {
705 Buildable<SelectModel> countStatement = countFrom(person)
706 .join(address, on(person.addressId, isEqualTo(address.id)))
707 .where(person.id, isEqualTo(55), or(person.id, isEqualTo(1)));
708
709 long count = template.count(countStatement);
710 assertThat(count).isEqualTo(1);
711 }
712
713 private final RowMapper<PersonWithAddress> personWithAddressRowMapper =
714 (rs, i) -> new PersonWithAddress(rs.getInt(1),
715 rs.getString(2),
716 new LastName(rs.getString(3)),
717 rs.getTimestamp(4),
718 "Yes".equals(rs.getString(5)),
719 rs.getString(6),
720 new AddressRecord(rs.getInt(7),
721 rs.getString(8),
722 rs.getString(9),
723 rs.getString(10)));
724
725
726 static final RowMapper<PersonRecord> personRowMapper =
727 (rs, i) -> new PersonRecord(rs.getInt(1),
728 rs.getString(2),
729 new LastName(rs.getString(3)),
730 rs.getTimestamp(4),
731 "Yes".equals(rs.getString(5)),
732 rs.getString(6),
733 rs.getInt(7));
734 }