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
206 assertThat(rows).satisfiesExactly(
207 person1 -> assertThat(person1).isNotNull()
208 .extracting("lastName").isNotNull()
209 .extracting("name").isEqualTo("Flintstone"),
210 person2 -> assertThat(person2).isNotNull()
211 .extracting("lastName").isNotNull()
212 .extracting("name").isEqualTo("Rubble")
213 );
214 }
215
216 @Test
217 void testDelete() {
218 Buildable<DeleteModel> deleteStatement = deleteFrom(person)
219 .where(occupation, isNull());
220
221 int rows = template.delete(deleteStatement);
222
223 assertThat(rows).isEqualTo(2);
224 }
225
226 @Test
227 void testDeleteAll() {
228 Buildable<DeleteModel> deleteStatement = deleteFrom(person);
229
230 int rows = template.delete(deleteStatement);
231
232 assertThat(rows).isEqualTo(6);
233 }
234
235 @Test
236 void testDeleteByPrimaryKey() {
237 Buildable<DeleteModel> deleteStatement = deleteFrom(person)
238 .where(id, isEqualTo(2));
239
240 int rows = template.delete(deleteStatement);
241
242 assertThat(rows).isEqualTo(1);
243 }
244
245 @Test
246 void testInsert() {
247 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
248
249 Buildable<InsertModel<PersonRecord>> insertStatement = insert(row).into(person)
250 .map(id).toProperty("id")
251 .map(firstName).toProperty("firstName")
252 .map(lastName).toProperty("lastNameAsString")
253 .map(birthDate).toProperty("birthDate")
254 .map(employed).toProperty("employedAsString")
255 .map(occupation).toProperty("occupation")
256 .map(addressId).toProperty("addressId");
257
258 int rows = template.insert(insertStatement);
259
260 assertThat(rows).isEqualTo(1);
261 }
262
263 @Test
264 void testGeneralInsert() {
265 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
266 .set(id).toValue(100)
267 .set(firstName).toValue("Joe")
268 .set(lastName).toValue(new LastName("Jones"))
269 .set(birthDate).toValue(new Date())
270 .set(employed).toValue(true)
271 .set(occupation).toValue("Developer")
272 .set(addressId).toValue(1);
273
274 int rows = template.generalInsert(insertStatement);
275
276 assertThat(rows).isEqualTo(1);
277 }
278
279 @Test
280 void testInsertMultiple() {
281
282 List<PersonRecord> records = List.of(
283 new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1),
284 new PersonRecord(101, "Sarah", new LastName("Smith"), new Date(), true, "Architect", 2));
285
286 Buildable<MultiRowInsertModel<PersonRecord>> insertStatement = insertMultiple(records).into(person)
287 .map(id).toProperty("id")
288 .map(firstName).toProperty("firstName")
289 .map(lastName).toProperty("lastNameAsString")
290 .map(birthDate).toProperty("birthDate")
291 .map(employed).toProperty("employedAsString")
292 .map(occupation).toProperty("occupation")
293 .map(addressId).toProperty("addressId");
294
295 int rows = template.insertMultiple(insertStatement);
296
297 assertThat(rows).isEqualTo(2);
298 }
299
300 @Test
301 void testInsertBatch() {
302
303 List<PersonRecord> records = List.of(
304 new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1),
305 new PersonRecord(101, "Sarah", new LastName("Smith"), new Date(), true, "Architect", 2));
306
307 Buildable<BatchInsertModel<PersonRecord>> insertStatement = insertBatch(records).into(person)
308 .map(id).toProperty("id")
309 .map(firstName).toProperty("firstName")
310 .map(lastName).toProperty("lastNameAsString")
311 .map(birthDate).toProperty("birthDate")
312 .map(employed).toProperty("employedAsString")
313 .map(occupation).toProperty("occupation")
314 .map(addressId).toProperty("addressId");
315
316 int[] rows = template.insertBatch(insertStatement);
317
318 assertThat(rows).hasSize(2);
319 assertThat(rows[0]).isEqualTo(1);
320 assertThat(rows[1]).isEqualTo(1);
321 }
322
323 @Test
324 void testInsertSelective() {
325 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), false, null, 1);
326
327 Buildable<InsertModel<PersonRecord>> insertStatement = insert(row).into(person)
328 .map(id).toPropertyWhenPresent("id", row::id)
329 .map(firstName).toPropertyWhenPresent("firstName", row::firstName)
330 .map(lastName).toPropertyWhenPresent("lastNameAsString", row::getLastNameAsString)
331 .map(birthDate).toPropertyWhenPresent("birthDate", row::birthDate)
332 .map(employed).toPropertyWhenPresent("employedAsString", row::getEmployedAsString)
333 .map(occupation).toPropertyWhenPresent("occupation", row::occupation)
334 .map(addressId).toPropertyWhenPresent("addressId", row::addressId);
335
336 int rows = template.insert(insertStatement);
337
338 assertThat(rows).isEqualTo(1);
339 }
340
341 @Test
342 void testGeneralInsertWhenTypeConverterReturnsNull() {
343
344 GeneralInsertStatementProvider insertStatement = insertInto(person)
345 .set(id).toValue(100)
346 .set(firstName).toValue("Joe")
347 .set(lastName).toValueWhenPresent(new LastName("Slate"))
348 .set(birthDate).toValue(new Date())
349 .set(employed).toValue(true)
350 .set(occupation).toValue("Quarry Owner")
351 .set(addressId).toValue(1)
352 .build()
353 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
354
355 assertThat(insertStatement.getInsertStatement())
356 .isEqualTo("insert into Person (id, first_name, birth_date, employed, occupation, address_id) values (:p1, :p2, :p3, :p4, :p5, :p6)");
357 int rows = template.generalInsert(insertStatement);
358 assertThat(rows).isEqualTo(1);
359
360 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
361 .from(person)
362 .where(id, isEqualTo(100));
363 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
364 assertThat(newRecord).hasValueSatisfying(
365 r -> assertThat(r).isNotNull().extracting("lastName").isNotNull().extracting("name").isNull()
366 );
367 }
368
369 @Test
370 void testUpdateByPrimaryKey() {
371
372 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
373 .set(id).toValue(100)
374 .set(firstName).toValue("Joe")
375 .set(lastName).toValue(new LastName("Jones"))
376 .set(birthDate).toValue(new Date())
377 .set(employed).toValue(true)
378 .set(occupation).toValue("Developer")
379 .set(addressId).toValue(1);
380
381 int rows = template.generalInsert(insertStatement);
382 assertThat(rows).isEqualTo(1);
383
384 Buildable<UpdateModel> updateStatement = update(person)
385 .set(occupation).equalTo("Programmer")
386 .where(id, isEqualTo(100));
387
388 rows = template.update(updateStatement);
389 assertThat(rows).isEqualTo(1);
390
391 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
392 .from(person)
393 .where(id, isEqualTo(100));
394 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
395 assertThat(newRecord).hasValueSatisfying(r -> assertThat(r.occupation()).isEqualTo("Programmer"));
396 }
397
398 @Test
399 void testUpdateByPrimaryKeyWithTypeHandler() {
400
401 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
402 .set(id).toValue(100)
403 .set(firstName).toValue("Joe")
404 .set(lastName).toValue(new LastName("Jones"))
405 .set(birthDate).toValue(new Date())
406 .set(employed).toValue(true)
407 .set(occupation).toValue("Developer")
408 .set(addressId).toValue(1);
409
410 int rows = template.generalInsert(insertStatement);
411 assertThat(rows).isEqualTo(1);
412
413 Buildable<UpdateModel> updateStatement = update(person)
414 .set(lastName).equalTo(new LastName("Smith"))
415 .where(id, isEqualTo(100));
416
417 rows = template.update(updateStatement);
418 assertThat(rows).isEqualTo(1);
419
420 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
421 .from(person)
422 .where(id, isEqualTo(100));
423 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
424 assertThat(newRecord).hasValueSatisfying(r -> assertThat(r).isNotNull()
425 .extracting("lastName").isNotNull()
426 .extracting("name").isEqualTo("Smith")
427 );
428 }
429
430 @Test
431 void testUpdateByPrimaryKeySelective() {
432 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
433 .set(id).toValue(100)
434 .set(firstName).toValue("Joe")
435 .set(lastName).toValue(new LastName("Jones"))
436 .set(birthDate).toValue(new Date())
437 .set(employed).toValue(true)
438 .set(occupation).toValue("Developer")
439 .set(addressId).toValue(1);
440
441 int rows = template.generalInsert(insertStatement);
442 assertThat(rows).isEqualTo(1);
443
444 PersonRecord updateRecord = new PersonRecord(100, null, null, null, null, "Programmer", null);
445
446 Buildable<UpdateModel> updateStatement = update(person)
447 .set(firstName).equalToWhenPresent(updateRecord::firstName)
448 .set(lastName).equalToWhenPresent(updateRecord::lastName)
449 .set(birthDate).equalToWhenPresent(updateRecord::birthDate)
450 .set(employed).equalToWhenPresent(updateRecord::employed)
451 .set(occupation).equalToWhenPresent(updateRecord::occupation)
452 .set(addressId).equalToWhenPresent(updateRecord::addressId)
453 .where(id, isEqualTo(updateRecord::id));
454
455 rows = template.update(updateStatement);
456 assertThat(rows).isEqualTo(1);
457
458 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
459 .from(person)
460 .where(id, isEqualTo(100));
461 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
462 assertThat(newRecord).hasValueSatisfying(r -> {
463 assertThat(r.occupation()).isEqualTo("Programmer");
464 assertThat(r.firstName()).isEqualTo("Joe");
465 });
466 }
467
468 @Test
469 void testUpdate() {
470 PersonRecord row = new PersonRecord(100, "Joe", new LastName("Jones"), new Date(), true, "Developer", 1);
471
472 Buildable<InsertModel<PersonRecord>> insertStatement = insert(row).into(person)
473 .map(id).toProperty("id")
474 .map(firstName).toProperty("firstName")
475 .map(lastName).toProperty("lastNameAsString")
476 .map(birthDate).toProperty("birthDate")
477 .map(employed).toProperty("employedAsString")
478 .map(occupation).toProperty("occupation")
479 .map(addressId).toProperty("addressId");
480
481 int rows = template.insert(insertStatement);
482 assertThat(rows).isEqualTo(1);
483
484 row = row.withOccupation("Programmer");
485
486 Buildable<UpdateModel> updateStatement = update(person)
487 .set(firstName).equalToWhenPresent(row::firstName)
488 .set(lastName).equalToWhenPresent(row::lastName)
489 .set(birthDate).equalToWhenPresent(row::birthDate)
490 .set(employed).equalToWhenPresent(row::employed)
491 .set(occupation).equalToWhenPresent(row::occupation)
492 .set(addressId).equalToWhenPresent(row::addressId)
493 .where(id, isEqualTo(row::id));
494
495 rows = template.update(updateStatement);
496 assertThat(rows).isEqualTo(1);
497
498 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
499 .from(person)
500 .where(id, isEqualTo(100));
501 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
502 assertThat(newRecord).hasValueSatisfying(r -> {
503 assertThat(r.occupation()).isEqualTo("Programmer");
504 assertThat(r.firstName()).isEqualTo("Joe");
505 });
506 }
507
508 @Test
509 void testUpdateAll() {
510 Buildable<GeneralInsertModel> insertStatement = insertInto(person)
511 .set(id).toValue(100)
512 .set(firstName).toValue("Joe")
513 .set(lastName).toValue(new LastName("Jones"))
514 .set(birthDate).toValue(new Date())
515 .set(employed).toValue(true)
516 .set(occupation).toValue("Developer")
517 .set(addressId).toValue(1);
518
519 int rows = template.generalInsert(insertStatement);
520 assertThat(rows).isEqualTo(1);
521
522 Buildable<UpdateModel> updateStatement = update(person)
523 .set(occupation).equalTo("Programmer");
524
525 rows = template.update(updateStatement);
526 assertThat(rows).isEqualTo(7);
527
528 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
529 .from(person)
530 .where(id, isEqualTo(100));
531
532 Optional<PersonRecord> newRecord = template.selectOne(selectStatement, personRowMapper);
533 assertThat(newRecord).hasValueSatisfying(r -> assertThat(r.occupation()).isEqualTo("Programmer"));
534 }
535
536 @Test
537 void testCount() {
538 Buildable<SelectModel> countStatement = countFrom(person)
539 .where(occupation, isNull());
540
541 long rows = template.count(countStatement);
542 assertThat(rows).isEqualTo(2L);
543 }
544
545 @Test
546 void testCountAll() {
547 Buildable<SelectModel> countStatement = countFrom(person);
548
549 long rows = template.count(countStatement);
550 assertThat(rows).isEqualTo(6L);
551 }
552
553 @Test
554 void testCountLastName() {
555 Buildable<SelectModel> countStatement = countColumn(lastName).from(person);
556
557 long rows = template.count(countStatement);
558 assertThat(rows).isEqualTo(6L);
559 }
560
561 @Test
562 void testCountDistinctLastName() {
563 Buildable<SelectModel> countStatement = countDistinctColumn(lastName).from(person);
564
565 long rows = template.count(countStatement);
566 assertThat(rows).isEqualTo(2L);
567 }
568
569 @Test
570 void testTypeHandledLike() {
571 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
572 .from(person)
573 .where(lastName, isLike(new LastName("Fl%")))
574 .orderBy(id);
575
576 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
577 assertThat(rows).hasSize(3);
578 assertThat(rows.get(0).firstName()).isEqualTo("Fred");
579 }
580
581 @Test
582 void testTypeHandledNotLike() {
583 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation, addressId)
584 .from(person)
585 .where(lastName, isNotLike(new LastName("Fl%")))
586 .orderBy(id);
587
588 List<PersonRecord> rows = template.selectList(selectStatement, personRowMapper);
589
590 assertThat(rows).hasSize(3);
591 assertThat(rows.get(0).firstName()).isEqualTo("Barney");
592 }
593
594 @Test
595 void testAutoMapping() {
596 Buildable<SelectModel> selectStatement = select(address.id.as("id"), address.streetAddress,
597 address.city, address.state)
598 .from(address)
599 .orderBy(address.id);
600
601
602 List<AddressRecord> records = template.selectList(selectStatement,
603 DataClassRowMapper.newInstance(AddressRecord.class));
604
605 assertThat(records).hasSize(2);
606 assertThat(records.get(0).id()).isEqualTo(1);
607 assertThat(records.get(0).streetAddress()).isEqualTo("123 Main Street");
608 assertThat(records.get(0).city()).isEqualTo("Bedrock");
609 assertThat(records.get(0).state()).isEqualTo("IN");
610 }
611
612 @Test
613 void testJoinAllRows() {
614 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
615 address.id, address.streetAddress, address.city, address.state)
616 .from(person)
617 .join(address, on(person.addressId, isEqualTo(address.id)))
618 .orderBy(id);
619
620 List<PersonWithAddress> records = template.selectList(selectStatement, personWithAddressRowMapper);
621
622 assertThat(records).hasSize(6);
623 assertThat(records.get(0).id()).isEqualTo(1);
624 assertThat(records.get(0).employed()).isTrue();
625 assertThat(records.get(0).firstName()).isEqualTo("Fred");
626 assertThat(records.get(0).lastName()).isEqualTo(new LastName("Flintstone"));
627 assertThat(records.get(0).occupation()).isEqualTo("Brontosaurus Operator");
628 assertThat(records.get(0).birthDate()).isNotNull();
629 assertThat(records.get(0).address().id()).isEqualTo(1);
630 assertThat(records.get(0).address().streetAddress()).isEqualTo("123 Main Street");
631 assertThat(records.get(0).address().city()).isEqualTo("Bedrock");
632 assertThat(records.get(0).address().state()).isEqualTo("IN");
633 }
634
635 @Test
636 void testJoinOneRow() {
637 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
638 address.id, address.streetAddress, address.city, address.state)
639 .from(person)
640 .join(address, on(person.addressId, isEqualTo(address.id)))
641 .where(id, isEqualTo(1));
642
643 List<PersonWithAddress> records = template.selectList(selectStatement, personWithAddressRowMapper);
644
645 assertThat(records).hasSize(1);
646 assertThat(records.get(0).id()).isEqualTo(1);
647 assertThat(records.get(0).employed()).isTrue();
648 assertThat(records.get(0).firstName()).isEqualTo("Fred");
649 assertThat(records.get(0).lastName()).isEqualTo(new LastName("Flintstone"));
650 assertThat(records.get(0).occupation()).isEqualTo("Brontosaurus Operator");
651 assertThat(records.get(0).birthDate()).isNotNull();
652 assertThat(records.get(0).address().id()).isEqualTo(1);
653 assertThat(records.get(0).address().streetAddress()).isEqualTo("123 Main Street");
654 assertThat(records.get(0).address().city()).isEqualTo("Bedrock");
655 assertThat(records.get(0).address().state()).isEqualTo("IN");
656 }
657
658 @Test
659 void testJoinPrimaryKey() {
660 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
661 address.id, address.streetAddress, address.city, address.state)
662 .from(person)
663 .join(address, on(person.addressId, isEqualTo(address.id)))
664 .where(id, isEqualTo(1));
665
666 Optional<PersonWithAddress> row = template.selectOne(selectStatement, personWithAddressRowMapper);
667
668 assertThat(row).hasValueSatisfying(r -> {
669 assertThat(r.id()).isEqualTo(1);
670 assertThat(r.employed()).isTrue();
671 assertThat(r.firstName()).isEqualTo("Fred");
672 assertThat(r.lastName()).isEqualTo(new LastName("Flintstone"));
673 assertThat(r.occupation()).isEqualTo("Brontosaurus Operator");
674 assertThat(r.birthDate()).isNotNull();
675 assertThat(r.address().id()).isEqualTo(1);
676 assertThat(r.address().streetAddress()).isEqualTo("123 Main Street");
677 assertThat(r.address().city()).isEqualTo("Bedrock");
678 assertThat(r.address().state()).isEqualTo("IN");
679 });
680 }
681
682 @Test
683 void testJoinPrimaryKeyInvalidRecord() {
684 Buildable<SelectModel> selectStatement = select(id, firstName, lastName, birthDate, employed, occupation,
685 address.id, address.streetAddress, address.city, address.state)
686 .from(person)
687 .join(address, on(person.addressId, isEqualTo(address.id)))
688 .where(id, isEqualTo(55));
689
690 Optional<PersonWithAddress> row = template.selectOne(selectStatement, personWithAddressRowMapper);
691 assertThat(row).isEmpty();
692 }
693
694 @Test
695 void testJoinCount() {
696 Buildable<SelectModel> countStatement = countFrom(person)
697 .join(address, on(person.addressId, isEqualTo(address.id)))
698 .where(id, isEqualTo(55));
699
700 long count = template.count(countStatement);
701 assertThat(count).isZero();
702 }
703
704 @Test
705 void testJoinCountWithSubCriteria() {
706 Buildable<SelectModel> countStatement = countFrom(person)
707 .join(address, on(person.addressId, isEqualTo(address.id)))
708 .where(person.id, isEqualTo(55), or(person.id, isEqualTo(1)));
709
710 long count = template.count(countStatement);
711 assertThat(count).isEqualTo(1);
712 }
713
714 private final RowMapper<PersonWithAddress> personWithAddressRowMapper =
715 (rs, i) -> new PersonWithAddress(rs.getInt(1),
716 rs.getString(2),
717 new LastName(rs.getString(3)),
718 rs.getTimestamp(4),
719 "Yes".equals(rs.getString(5)),
720 rs.getString(6),
721 new AddressRecord(rs.getInt(7),
722 rs.getString(8),
723 rs.getString(9),
724 rs.getString(10)));
725
726
727 static final RowMapper<PersonRecord> personRowMapper =
728 (rs, i) -> new PersonRecord(rs.getInt(1),
729 rs.getString(2),
730 new LastName(rs.getString(3)),
731 rs.getTimestamp(4),
732 "Yes".equals(rs.getString(5)),
733 rs.getString(6),
734 rs.getInt(7));
735 }