1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.animal.data;
17
18 import static examples.animal.data.AnimalDataDynamicSqlSupport.*;
19 import static org.assertj.core.api.Assertions.as;
20 import static org.assertj.core.api.Assertions.assertThat;
21 import static org.assertj.core.api.Assertions.assertThatExceptionOfType;
22 import static org.assertj.core.api.Assertions.within;
23 import static org.assertj.core.api.InstanceOfAssertFactories.DOUBLE;
24 import static org.assertj.core.api.InstanceOfAssertFactories.MAP;
25 import static org.junit.jupiter.api.Assertions.assertAll;
26 import static org.mybatis.dynamic.sql.SqlBuilder.*;
27
28 import java.io.InputStream;
29 import java.io.InputStreamReader;
30 import java.sql.Connection;
31 import java.sql.DriverManager;
32 import java.util.ArrayList;
33 import java.util.Collections;
34 import java.util.List;
35 import java.util.Map;
36
37 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
38 import org.apache.ibatis.jdbc.ScriptRunner;
39 import org.apache.ibatis.mapping.Environment;
40 import org.apache.ibatis.session.Configuration;
41 import org.apache.ibatis.session.ExecutorType;
42 import org.apache.ibatis.session.RowBounds;
43 import org.apache.ibatis.session.SqlSession;
44 import org.apache.ibatis.session.SqlSessionFactory;
45 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
46 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
47 import org.jspecify.annotations.Nullable;
48 import org.junit.jupiter.api.BeforeEach;
49 import org.junit.jupiter.api.Test;
50 import org.mybatis.dynamic.sql.BasicColumn;
51 import org.mybatis.dynamic.sql.SqlTable;
52 import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
53 import org.mybatis.dynamic.sql.exception.NonRenderingWhereClauseException;
54 import org.mybatis.dynamic.sql.insert.render.BatchInsert;
55 import org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider;
56 import org.mybatis.dynamic.sql.insert.render.InsertSelectStatementProvider;
57 import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
58 import org.mybatis.dynamic.sql.render.RenderingStrategies;
59 import org.mybatis.dynamic.sql.select.SelectDSLCompleter;
60 import org.mybatis.dynamic.sql.select.SelectModel;
61 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
62 import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
63 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
64 import org.mybatis.dynamic.sql.util.mybatis3.MyBatis3Utils;
65
66 class AnimalDataTest {
67
68 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
69 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
70
71 private SqlSessionFactory sqlSessionFactory;
72
73 @BeforeEach
74 void setup() throws Exception {
75 Class.forName(JDBC_DRIVER);
76 try (InputStream is = getClass().getResourceAsStream("/examples/animal/data/CreateAnimalData.sql")) {
77 assert is != null;
78 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "");
79 InputStreamReader isr = new InputStreamReader(is)) {
80 ScriptRunner sr = new ScriptRunner(connection);
81 sr.setLogWriter(null);
82 sr.runScript(isr);
83 }
84 }
85
86 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
87 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
88 Configuration config = new Configuration(environment);
89 config.addMapper(AnimalDataMapper.class);
90 config.addMapper(CommonSelectMapper.class);
91 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
92 }
93
94 @Test
95 void testSelectAllRows() {
96 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
97 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
98 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
99 .from(animalData)
100 .build()
101 .render(RenderingStrategies.MYBATIS3);
102 List<AnimalData> animals = mapper.selectMany(selectStatement);
103
104 assertAll(
105 () -> assertThat(animals).hasSize(65),
106 () -> assertThat(animals).first().isNotNull().extracting(AnimalData::id).isEqualTo(1)
107 );
108 }
109 }
110
111 @Test
112 void testSelectAllRowsWithNullLimit() {
113 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
114 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
115 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
116 .from(animalData)
117 .limitWhenPresent(null)
118 .build()
119 .render(RenderingStrategies.MYBATIS3);
120 List<AnimalData> animals = mapper.selectMany(selectStatement);
121
122 assertAll(
123 () -> assertThat(animals).hasSize(65),
124 () -> assertThat(animals).first().isNotNull().extracting(AnimalData::id).isEqualTo(1)
125 );
126 }
127 }
128
129 @Test
130 void testSelectAllRowsWithRowBounds() {
131 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
132 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
133 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
134 .from(animalData)
135 .build()
136 .render(RenderingStrategies.MYBATIS3);
137
138 RowBounds rowBounds = new RowBounds(4, 6);
139
140 List<AnimalData> animals = mapper.selectManyWithRowBounds(selectStatement, rowBounds);
141 assertAll(
142 () -> assertThat(animals).hasSize(6),
143 () -> assertThat(animals).first().isNotNull().extracting(AnimalData::id).isEqualTo(5)
144 );
145 }
146 }
147
148 @Test
149 void testSelectAllRowsWithOrder() {
150 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
151 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
152 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
153 .from(animalData)
154 .orderBy(id.descending())
155 .build()
156 .render(RenderingStrategies.MYBATIS3);
157 List<AnimalData> animals = mapper.selectMany(selectStatement);
158 assertAll(
159 () -> assertThat(animals).hasSize(65),
160 () -> assertThat(animals).first().isNotNull().extracting(AnimalData::id).isEqualTo(65)
161 );
162 }
163 }
164
165 @Test
166 void testSelectAllRowsAllColumns() {
167 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
168 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
169 SelectStatementProvider selectStatement = select(animalData.allColumns())
170 .from(animalData)
171 .orderBy(id.descending())
172 .build()
173 .render(RenderingStrategies.MYBATIS3);
174 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
175 assertAll(
176 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select * from AnimalData order by id DESC"),
177 () -> assertThat(animals).hasSize(65),
178 () -> assertThat(animals).first().isNotNull()
179 .extracting("ID", "ANIMAL_NAME")
180 .containsExactly(65, "Brachiosaurus")
181 );
182 }
183 }
184
185 @Test
186 void testSelectAllRowsAllColumnsWithOrder() {
187 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
188 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
189 SelectStatementProvider selectStatement = select(animalData.allColumns())
190 .from(animalData)
191 .orderBy(id.descending())
192 .build()
193 .render(RenderingStrategies.MYBATIS3);
194 List<AnimalData> animals = mapper.selectMany(selectStatement);
195 assertAll(
196 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select * from AnimalData order by id DESC"),
197 () -> assertThat(animals).hasSize(65),
198 () -> assertThat(animals).first().isNotNull()
199 .extracting(AnimalData::id).isEqualTo(65)
200 );
201 }
202 }
203
204 @Test
205 void testSelectAllRowsAllColumnsWithOrderAndAlias() {
206 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
207 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
208 SelectStatementProvider selectStatement = select(animalData.allColumns())
209 .from(animalData, "ad")
210 .orderBy(id.descending())
211 .build()
212 .render(RenderingStrategies.MYBATIS3);
213 List<AnimalData> animals = mapper.selectMany(selectStatement);
214 assertAll(
215 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select ad.* from AnimalData ad order by id DESC"),
216 () -> assertThat(animals).hasSize(65),
217 () -> assertThat(animals).first().isNotNull().extracting(AnimalData::id).isEqualTo(65)
218 );
219 }
220 }
221
222 @Test
223 void testSelectRowsLessThan20() {
224 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
225 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
226
227 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
228 .from(animalData)
229 .where(id, isLessThan(20))
230 .build()
231 .render(RenderingStrategies.MYBATIS3);
232
233 List<AnimalData> animals = mapper.selectMany(selectStatement);
234 assertThat(animals).hasSize(19);
235 }
236 }
237
238 @Test
239 void testSelectSkipLocked() {
240 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
241 .from(animalData)
242 .where(id, isLessThan(20))
243 .skipLocked()
244 .build()
245 .render(RenderingStrategies.MYBATIS3);
246
247 String expected = """
248 select id, animal_name, body_weight, brain_weight
249 from AnimalData
250 where id < #{parameters.p1,jdbcType=INTEGER}
251 skip locked
252 """;
253
254 assertThat(selectStatement.getSelectStatement()).isEqualToNormalizingWhitespace(expected);
255 }
256
257 @Test
258 void testSelectRowsBetween30And40() {
259 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
260 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
261
262 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
263 .from(animalData)
264 .where(id, isBetween(30).and(40))
265 .build()
266 .render(RenderingStrategies.MYBATIS3);
267
268 List<AnimalData> animals = mapper.selectMany(selectStatement);
269 assertThat(animals).hasSize(11);
270 }
271 }
272
273 @Test
274 void testSelectRowsNotBetween() {
275 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
276 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
277
278 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
279 .from(animalData)
280 .where(id, isNotBetween(10).and(60))
281 .build()
282 .render(RenderingStrategies.MYBATIS3);
283
284 List<AnimalData> animals = mapper.selectMany(selectStatement);
285 assertThat(animals).hasSize(14);
286 }
287 }
288
289 @Test
290 void testSelectRowsNotBetweenWithNewNot() {
291 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
292 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
293
294 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
295 .from(animalData)
296 .where(not(id, isBetween(10).and(60)))
297 .build()
298 .render(RenderingStrategies.MYBATIS3);
299
300 String expected = "select id, animal_name, body_weight, brain_weight"
301 + " from AnimalData"
302 + " where not id between #{parameters.p1,jdbcType=INTEGER} and #{parameters.p2,jdbcType=INTEGER}";
303
304 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
305 List<AnimalData> animals = mapper.selectMany(selectStatement);
306 assertThat(animals).hasSize(14);
307 }
308 }
309
310 @Test
311 void testSelectRowsNotBetweenWithNotGroup() {
312 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
313 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
314
315 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
316 .from(animalData)
317 .where(not(id, isBetween(10).and(60),
318 or(animalName, isEqualTo("Little brown bat"))))
319 .build()
320 .render(RenderingStrategies.MYBATIS3);
321
322 String expected = "select id, animal_name, body_weight, brain_weight"
323 + " from AnimalData"
324 + " where not (id between #{parameters.p1,jdbcType=INTEGER} and #{parameters.p2,jdbcType=INTEGER}"
325 + " or animal_name = #{parameters.p3,jdbcType=VARCHAR})";
326
327 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
328 List<AnimalData> animals = mapper.selectMany(selectStatement);
329 assertThat(animals).hasSize(13);
330 }
331 }
332
333 @Test
334 void testSelectRowsNotBetweenWithNotAndGroup() {
335 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
336 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
337
338 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
339 .from(animalData)
340 .where(not(group(id, isBetween(10).and(60),
341 or(animalName, isEqualTo("Little brown bat")))))
342 .build()
343 .render(RenderingStrategies.MYBATIS3);
344
345 String expected = "select id, animal_name, body_weight, brain_weight"
346 + " from AnimalData"
347 + " where not (id between #{parameters.p1,jdbcType=INTEGER} and #{parameters.p2,jdbcType=INTEGER}"
348 + " or animal_name = #{parameters.p3,jdbcType=VARCHAR})";
349
350 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
351 List<AnimalData> animals = mapper.selectMany(selectStatement);
352 assertThat(animals).hasSize(13);
353 }
354 }
355
356 @Test
357 void testUnionSelectWithWhere() {
358 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
359 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
360
361 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
362 .from(animalData)
363 .where(id, isLessThan(20))
364 .union()
365 .select(id, animalName, bodyWeight, brainWeight)
366 .from(animalData)
367 .where(id, isGreaterThan(40))
368 .build()
369 .render(RenderingStrategies.MYBATIS3);
370
371 String expected = "select id, animal_name, body_weight, brain_weight "
372 + "from AnimalData "
373 + "where id < #{parameters.p1,jdbcType=INTEGER} "
374 + "union "
375 + "select id, animal_name, body_weight, brain_weight "
376 + "from AnimalData "
377 + "where id > #{parameters.p2,jdbcType=INTEGER}";
378
379 List<AnimalData> animals = mapper.selectMany(selectStatement);
380 assertAll(
381 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
382 () -> assertThat(animals).hasSize(44),
383 () -> assertThat(selectStatement.getParameters()).hasSize(2),
384 () -> assertThat(selectStatement.getParameters()).containsEntry("p1", 20),
385 () -> assertThat(selectStatement.getParameters()).containsEntry("p2", 40)
386 );
387 }
388 }
389
390 @Test
391 void testUnionSelectWithoutWhere() {
392 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
393 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
394
395 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
396 .from(animalData)
397 .union()
398 .selectDistinct(id, animalName, bodyWeight, brainWeight)
399 .from(animalData)
400 .orderBy(id)
401 .build()
402 .render(RenderingStrategies.MYBATIS3);
403
404 String expected = "select id, animal_name, body_weight, brain_weight "
405 + "from AnimalData "
406 + "union "
407 + "select distinct id, animal_name, body_weight, brain_weight "
408 + "from AnimalData "
409 + "order by id";
410
411 List<AnimalData> animals = mapper.selectMany(selectStatement);
412 assertAll(
413 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
414 () -> assertThat(animals).hasSize(65),
415 () -> assertThat(selectStatement.getParameters()).isEmpty()
416 );
417 }
418 }
419
420 @Test
421 void testUnionAllSelectWithoutWhere() {
422 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
423 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
424
425 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
426 .from(animalData)
427 .unionAll()
428 .selectDistinct(id, animalName, bodyWeight, brainWeight)
429 .from(animalData)
430 .orderBy(id)
431 .build()
432 .render(RenderingStrategies.MYBATIS3);
433
434 String expected = "select id, animal_name, body_weight, brain_weight "
435 + "from AnimalData "
436 + "union all "
437 + "select distinct id, animal_name, body_weight, brain_weight "
438 + "from AnimalData "
439 + "order by id";
440
441 List<AnimalData> animals = mapper.selectMany(selectStatement);
442 assertAll(
443 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
444 () -> assertThat(animals).hasSize(130),
445 () -> assertThat(selectStatement.getParameters()).isEmpty()
446 );
447 }
448 }
449
450 @Test
451 void testUnionSelectWithTableAliases() {
452 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
453 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
454
455 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
456 .from(animalData, "a")
457 .where(id, isLessThan(20))
458 .union()
459 .select(id, animalName, bodyWeight, brainWeight)
460 .from(animalData, "b")
461 .where(id, isGreaterThan(40))
462 .orderBy(id)
463 .build()
464 .render(RenderingStrategies.MYBATIS3);
465
466 String expected = "select a.id, a.animal_name, a.body_weight, a.brain_weight "
467 + "from AnimalData a "
468 + "where a.id < #{parameters.p1,jdbcType=INTEGER} "
469 + "union "
470 + "select b.id, b.animal_name, b.body_weight, b.brain_weight "
471 + "from AnimalData b "
472 + "where b.id > #{parameters.p2,jdbcType=INTEGER} "
473 + "order by id";
474
475 List<AnimalData> animals = mapper.selectMany(selectStatement);
476
477 assertAll(
478 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
479 () -> assertThat(animals).hasSize(44),
480 () -> assertThat(selectStatement.getParameters()).hasSize(2),
481 () -> assertThat(selectStatement.getParameters()).containsEntry("p1", 20),
482 () -> assertThat(selectStatement.getParameters()).containsEntry("p2", 40)
483 );
484 }
485 }
486
487 @Test
488 void testUnionAllSelectWithTableAliases() {
489 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
490 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
491
492 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
493 .from(animalData, "a")
494 .where(id, isLessThan(20))
495 .unionAll()
496 .select(id, animalName, bodyWeight, brainWeight)
497 .from(animalData, "b")
498 .where(id, isGreaterThan(40))
499 .orderBy(id)
500 .build()
501 .render(RenderingStrategies.MYBATIS3);
502
503 String expected = "select a.id, a.animal_name, a.body_weight, a.brain_weight "
504 + "from AnimalData a "
505 + "where a.id < #{parameters.p1,jdbcType=INTEGER} "
506 + "union all "
507 + "select b.id, b.animal_name, b.body_weight, b.brain_weight "
508 + "from AnimalData b "
509 + "where b.id > #{parameters.p2,jdbcType=INTEGER} "
510 + "order by id";
511
512 List<AnimalData> animals = mapper.selectMany(selectStatement);
513
514 assertAll(
515 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
516 () -> assertThat(animals).hasSize(44),
517 () -> assertThat(selectStatement.getParameters()).hasSize(2),
518 () -> assertThat(selectStatement.getParameters()).containsEntry("p1", 20),
519 () -> assertThat(selectStatement.getParameters()).containsEntry("p2", 40)
520 );
521 }
522 }
523
524 @Test
525 void testUnionSelectWithTableAndColumnAliases() {
526 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
527 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
528
529 SelectStatementProvider selectStatement = select(id.as("animalId"), animalName, bodyWeight, brainWeight)
530 .from(animalData, "a")
531 .where(id, isLessThan(20))
532 .union()
533 .select(id.as("animalId"), animalName, bodyWeight, brainWeight)
534 .from(animalData, "b")
535 .where(id, isGreaterThan(40))
536 .orderBy(sortColumn("animalId"))
537 .build()
538 .render(RenderingStrategies.MYBATIS3);
539
540 String expected = "select a.id as animalId, a.animal_name, a.body_weight, a.brain_weight "
541 + "from AnimalData a "
542 + "where a.id < #{parameters.p1,jdbcType=INTEGER} "
543 + "union "
544 + "select b.id as animalId, b.animal_name, b.body_weight, b.brain_weight "
545 + "from AnimalData b "
546 + "where b.id > #{parameters.p2,jdbcType=INTEGER} "
547 + "order by animalId";
548
549 List<AnimalData> animals = mapper.selectMany(selectStatement);
550
551 assertAll(
552 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
553 () -> assertThat(animals).hasSize(44),
554 () -> assertThat(selectStatement.getParameters()).hasSize(2),
555 () -> assertThat(selectStatement.getParameters()).containsEntry("p1", 20),
556 () -> assertThat(selectStatement.getParameters()).containsEntry("p2", 40)
557 );
558 }
559 }
560
561 @Test
562 void testIsEqualCondition() {
563 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
564 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
565
566 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
567 .from(animalData)
568 .where(id, isEqualTo(5))
569 .build()
570 .render(RenderingStrategies.MYBATIS3);
571
572 List<AnimalData> animals = mapper.selectMany(selectStatement);
573 assertThat(animals).hasSize(1);
574 }
575 }
576
577 @Test
578 void testIsNotEqualCondition() {
579 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
580 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
581
582 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
583 .from(animalData)
584 .where(id, isNotEqualTo(5))
585 .build()
586 .render(RenderingStrategies.MYBATIS3);
587
588 List<AnimalData> animals = mapper.selectMany(selectStatement);
589 assertThat(animals).hasSize(64);
590 }
591 }
592
593 @Test
594 void testIsGreaterThanOrEqualToCondition() {
595 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
596 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
597
598 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
599 .from(animalData)
600 .where(id, isGreaterThanOrEqualTo(60))
601 .build()
602 .render(RenderingStrategies.MYBATIS3);
603
604 List<AnimalData> animals = mapper.selectMany(selectStatement);
605 assertThat(animals).hasSize(6);
606 }
607 }
608
609 @Test
610 void testIsLessThanOrEqualToCondition() {
611 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
612 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
613
614 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
615 .from(animalData)
616 .where(id, isLessThanOrEqualTo(10))
617 .build()
618 .render(RenderingStrategies.MYBATIS3);
619
620 List<AnimalData> animals = mapper.selectMany(selectStatement);
621 assertThat(animals).hasSize(10);
622 }
623 }
624
625 @Test
626 void testInCondition() {
627 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
628 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
629
630 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
631 .from(animalData)
632 .where(id, isIn(5, 8, 10))
633 .build()
634 .render(RenderingStrategies.MYBATIS3);
635
636 List<AnimalData> animals = mapper.selectMany(selectStatement);
637 assertThat(animals).hasSize(3);
638 }
639 }
640
641 @Test
642 void testInConditionWithEventuallyEmptyList() {
643 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
644 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
645
646 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
647 .from(animalData)
648 .where(id, isInWhenPresent(null, 22, null).filter(i -> i != 22))
649 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
650 .build()
651 .render(RenderingStrategies.MYBATIS3);
652
653 assertThat(selectStatement.getSelectStatement())
654 .isEqualTo("select id, animal_name, body_weight, brain_weight from AnimalData");
655 List<AnimalData> animals = mapper.selectMany(selectStatement);
656 assertThat(animals).hasSize(65);
657 }
658 }
659
660 @Test
661 void testInConditionWithEventuallyEmptyListForceRendering() {
662 List<@Nullable Integer> inValues = new ArrayList<>();
663 inValues.add(null);
664 inValues.add(22);
665 inValues.add(null);
666
667 SelectModel selectModel = select(id, animalName, bodyWeight, brainWeight)
668 .from(animalData)
669 .where(id, isInWhenPresent(inValues).filter(i -> i != 22))
670 .build();
671
672 assertThatExceptionOfType(NonRenderingWhereClauseException.class).isThrownBy(() ->
673 selectModel.render(RenderingStrategies.MYBATIS3)
674 );
675 }
676
677 @Test
678 void testInConditionWithEmptyList() {
679 SelectModel selectModel = select(id, animalName, bodyWeight, brainWeight)
680 .from(animalData)
681 .where(id, isInWhenPresent(Collections.emptyList()))
682 .build();
683
684 assertThatExceptionOfType(NonRenderingWhereClauseException.class).isThrownBy(() ->
685 selectModel.render(RenderingStrategies.MYBATIS3)
686 );
687 }
688
689 @Test
690 void testInCaseSensitiveCondition() {
691 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
692 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
693
694 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
695 .from(animalData)
696 .where(animalName, isInCaseInsensitiveWhenPresent("yellow-bellied marmot", "verbet", null))
697 .build()
698 .render(RenderingStrategies.MYBATIS3);
699
700 List<AnimalData> animals = mapper.selectMany(selectStatement);
701 assertThat(animals).hasSize(2);
702 }
703 }
704
705 @Test
706 void testNotInCondition() {
707 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
708 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
709
710 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
711 .from(animalData)
712 .where(id, isNotIn(5, 8, 10))
713 .build()
714 .render(RenderingStrategies.MYBATIS3);
715
716 List<AnimalData> animals = mapper.selectMany(selectStatement);
717 assertThat(animals).hasSize(62);
718 }
719 }
720
721 @Test
722 void testNotInCaseSensitiveCondition() {
723 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
724 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
725
726 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
727 .from(animalData)
728 .where(animalName, isNotInCaseInsensitive("yellow-bellied marmot", "verbet"))
729 .build()
730 .render(RenderingStrategies.MYBATIS3);
731
732 List<AnimalData> animals = mapper.selectMany(selectStatement);
733 assertThat(animals).hasSize(63);
734 }
735 }
736
737 @Test
738 void testNotInCaseSensitiveConditionWithNull() {
739 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
740 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
741
742 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
743 .from(animalData)
744 .where(animalName, isNotInCaseInsensitiveWhenPresent((String) null))
745 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true)) .build()
746 .render(RenderingStrategies.MYBATIS3);
747
748 List<AnimalData> animals = mapper.selectMany(selectStatement);
749 assertThat(animals).hasSize(65);
750 }
751 }
752
753 @Test
754 void testNotInConditionWithEventuallyEmptyList() {
755 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
756 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
757
758 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
759 .from(animalData)
760 .where(id, isNotInWhenPresent(null, 22, null).filter(i -> i != 22))
761 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
762 .build()
763 .render(RenderingStrategies.MYBATIS3);
764
765 assertThat(selectStatement.getSelectStatement())
766 .isEqualTo("select id, animal_name, body_weight, brain_weight from AnimalData");
767 List<AnimalData> animals = mapper.selectMany(selectStatement);
768 assertThat(animals).hasSize(65);
769 }
770 }
771
772 @Test
773 void testNotInConditionWithEventuallyEmptyListForceRendering() {
774 SelectModel selectModel = select(id, animalName, bodyWeight, brainWeight)
775 .from(animalData)
776 .where(id, isNotInWhenPresent(null, 22, null)
777 .filter(i -> i != 22))
778 .build();
779
780 assertThatExceptionOfType(NonRenderingWhereClauseException.class).isThrownBy(() ->
781 selectModel.render(RenderingStrategies.MYBATIS3)
782 );
783 }
784
785 @Test
786 void testLikeCondition() {
787 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
788 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
789
790 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
791 .from(animalData)
792 .where(animalName, isLike("%squirrel"))
793 .build()
794 .render(RenderingStrategies.MYBATIS3);
795
796 List<AnimalData> animals = mapper.selectMany(selectStatement);
797 assertThat(animals).hasSize(2);
798 }
799 }
800
801 @Test
802 void testLikeCaseInsensitive() {
803 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
804 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
805
806 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
807 .from(animalData)
808 .where(animalName, isLikeCaseInsensitive("%squirrel"))
809 .build()
810 .render(RenderingStrategies.MYBATIS3);
811
812 List<AnimalData> animals = mapper.selectMany(selectStatement);
813
814 assertAll(
815 () -> assertThat(animals).hasSize(2),
816 () -> assertThat(animals).element(0).isNotNull().extracting(AnimalData::animalName).isEqualTo("Ground squirrel"),
817 () -> assertThat(animals).element(1).isNotNull().extracting(AnimalData::animalName).isEqualTo("Artic ground squirrel")
818 );
819 }
820 }
821
822 @Test
823 void testLikeLowerCase() {
824 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
825 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
826
827 SelectStatementProvider selectStatement = select(id, lower(animalName).as("AnimalName"), bodyWeight, brainWeight)
828 .from(animalData)
829 .where(lower(animalName), isLike("%squirrel"))
830 .build()
831 .render(RenderingStrategies.MYBATIS3);
832
833 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
834
835 assertAll(
836 () -> assertThat(animals).hasSize(2),
837 () -> assertThat(animals).element(0).isNotNull()
838 .extracting("ANIMALNAME").isEqualTo("ground squirrel"),
839 () -> assertThat(animals).element(1).isNotNull()
840 .extracting("ANIMALNAME").isEqualTo("artic ground squirrel")
841 );
842 }
843 }
844
845 @Test
846 void testLikeUpperCase() {
847 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
848 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
849
850 SelectStatementProvider selectStatement = select(id, upper(animalName).as("animalname"), bodyWeight, brainWeight)
851 .from(animalData)
852 .where(upper(animalName), isLike("%SQUIRREL"))
853 .build()
854 .render(RenderingStrategies.MYBATIS3);
855
856 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
857
858 assertAll(
859 () -> assertThat(animals).hasSize(2),
860 () -> assertThat(animals).element(0).isNotNull().extracting("ANIMALNAME")
861 .isEqualTo("GROUND SQUIRREL"),
862 () -> assertThat(animals).element(1).isNotNull().extracting("ANIMALNAME")
863 .isEqualTo("ARTIC GROUND SQUIRREL")
864 );
865 }
866 }
867
868 @Test
869 void testLength() {
870 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
871 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
872
873 SelectStatementProvider selectStatement = select(id, upper(animalName).as("animalname"), bodyWeight, brainWeight)
874 .from(animalData)
875 .where(Length.length(animalName), isGreaterThan(22))
876 .orderBy(id)
877 .build()
878 .render(RenderingStrategies.MYBATIS3);
879
880 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
881
882 assertAll(
883 () -> assertThat(animals).hasSize(2),
884 () -> assertThat(animals).element(0).isNotNull()
885 .extracting("ANIMALNAME").isEqualTo("LESSER SHORT-TAILED SHREW"),
886 () -> assertThat(animals).element(1).isNotNull()
887 .extracting("ANIMALNAME").isEqualTo("AFRICAN GIANT POUCHED RAT")
888 );
889 }
890 }
891
892 @Test
893 void testNumericConstant() {
894 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
895 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
896
897 SelectStatementProvider selectStatement = select(id, animalName, constant("3").as("some_number"))
898 .from(animalData, "a")
899 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
900 .build()
901 .render(RenderingStrategies.MYBATIS3);
902
903 String expected = "select a.id, a.animal_name, 3 as some_number "
904 + "from AnimalData a "
905 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
906
907 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
908
909 assertAll(
910 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
911 () -> assertThat(animals).hasSize(3),
912 () -> assertThat(animals).element(0).isNotNull()
913 .extracting("ANIMAL_NAME", "SOME_NUMBER")
914 .containsExactly("African elephant", 3),
915 () -> assertThat(animals).element(1).isNotNull()
916 .extracting("ANIMAL_NAME", "SOME_NUMBER")
917 .containsExactly("Dipliodocus", 3),
918 () -> assertThat(animals).element(2).isNotNull()
919 .extracting("ANIMAL_NAME", "SOME_NUMBER")
920 .containsExactly("Brachiosaurus", 3)
921 );
922 }
923 }
924
925 @Test
926 void testStringConstant() {
927 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
928 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
929
930 SelectStatementProvider selectStatement = select(id, animalName, stringConstant("fred").as("some_string"))
931 .from(animalData, "a")
932 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
933 .build()
934 .render(RenderingStrategies.MYBATIS3);
935
936 String expected = "select a.id, a.animal_name, 'fred' as some_string "
937 + "from AnimalData a "
938 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
939
940 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
941
942 assertAll(
943 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
944 () -> assertThat(animals).hasSize(3),
945 () -> assertThat(animals).element(0).isNotNull()
946 .extracting("ANIMAL_NAME", "SOME_STRING")
947 .containsExactly("African elephant", "fred"),
948 () -> assertThat(animals).element(1).isNotNull()
949 .extracting("ANIMAL_NAME", "SOME_STRING")
950 .containsExactly("Dipliodocus", "fred"),
951 () -> assertThat(animals).element(2).isNotNull()
952 .extracting("ANIMAL_NAME", "SOME_STRING")
953 .containsExactly("Brachiosaurus", "fred")
954 );
955 }
956 }
957
958 @Test
959 void testAdd() {
960 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
961 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
962
963 SelectStatementProvider selectStatement = select(id, animalName, add(bodyWeight, brainWeight).as("calculated_weight"))
964 .from(animalData, "a")
965 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
966 .build()
967 .render(RenderingStrategies.MYBATIS3);
968
969 String expected = "select a.id, a.animal_name, (a.body_weight + a.brain_weight) as calculated_weight "
970 + "from AnimalData a "
971 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
972
973 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
974
975 assertAll(
976 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
977 () -> assertThat(animals).hasSize(3),
978 () -> assertThat(animals).element(0).isNotNull()
979 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
980 .containsExactly("African elephant", 12366.0),
981 () -> assertThat(animals).element(1).isNotNull()
982 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
983 .containsExactly("Dipliodocus", 11750.0),
984 () -> assertThat(animals).element(2).isNotNull()
985 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
986 .containsExactly("Brachiosaurus", 87154.5)
987 );
988 }
989 }
990
991 @Test
992 void testAddConstant() {
993 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
994 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
995
996 SelectStatementProvider selectStatement = select(id, animalName, add(bodyWeight, constant("22"), constant("33")).as("calculated_weight"))
997 .from(animalData, "a")
998 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
999 .build()
1000 .render(RenderingStrategies.MYBATIS3);
1001
1002 String expected = "select a.id, a.animal_name, (a.body_weight + 22 + 33) as calculated_weight "
1003 + "from AnimalData a "
1004 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1005
1006 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1007
1008 assertAll(
1009 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1010 () -> assertThat(animals).hasSize(3),
1011 () -> assertThat(animals).element(0).isNotNull()
1012 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1013 .containsExactly("African elephant", 5767.0),
1014 () -> assertThat(animals).element(1).isNotNull()
1015 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1016 .containsExactly("Dipliodocus", 105.0),
1017 () -> assertThat(animals).element(2).isNotNull()
1018 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1019 .containsExactly("Brachiosaurus", 209.5)
1020 );
1021 }
1022 }
1023
1024 @Test
1025 void testAddConstantWithConstantFirst() {
1026 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1027 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1028
1029 SelectStatementProvider selectStatement = select(id, animalName, add(constant("22"), bodyWeight, constant("33")).as("calculated_weight"))
1030 .from(animalData, "a")
1031 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1032 .build()
1033 .render(RenderingStrategies.MYBATIS3);
1034
1035 String expected = "select a.id, a.animal_name, (22 + a.body_weight + 33) as calculated_weight "
1036 + "from AnimalData a "
1037 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1038
1039 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1040
1041 assertAll(
1042 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1043 () -> assertThat(animals).hasSize(3),
1044 () -> assertThat(animals).element(0).isNotNull()
1045 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1046 .containsExactly("African elephant", 5767.0),
1047 () -> assertThat(animals).element(1).isNotNull()
1048 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1049 .containsExactly("Dipliodocus", 105.0),
1050 () -> assertThat(animals).element(2).isNotNull()
1051 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1052 .containsExactly("Brachiosaurus", 209.5)
1053 );
1054 }
1055 }
1056
1057 @Test
1058 void testConcat() {
1059 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1060 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1061
1062 SelectStatementProvider selectStatement = select(id, concat(animalName, stringConstant(" - The Legend")).as("display_name"))
1063 .from(animalData, "a")
1064 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1065 .build()
1066 .render(RenderingStrategies.MYBATIS3);
1067
1068 String expected = "select a.id, concat(a.animal_name, ' - The Legend') as display_name "
1069 + "from AnimalData a "
1070 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1071
1072 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1073
1074 assertAll(
1075 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1076 () -> assertThat(animals).hasSize(3),
1077 () -> assertThat(animals).element(0).isNotNull()
1078 .extracting("DISPLAY_NAME")
1079 .isEqualTo("African elephant - The Legend"),
1080 () -> assertThat(animals).element(1).isNotNull()
1081 .extracting("DISPLAY_NAME")
1082 .isEqualTo("Dipliodocus - The Legend"),
1083 () -> assertThat(animals).element(2).isNotNull()
1084 .extracting("DISPLAY_NAME")
1085 .isEqualTo("Brachiosaurus - The Legend")
1086 );
1087 }
1088 }
1089
1090 @Test
1091 void testConcatenate() {
1092 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1093 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1094
1095 SelectStatementProvider selectStatement = select(id, concatenate(animalName, stringConstant(" - The Legend")).as("display_name"))
1096 .from(animalData, "a")
1097 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1098 .build()
1099 .render(RenderingStrategies.MYBATIS3);
1100
1101 String expected = "select a.id, (a.animal_name || ' - The Legend') as display_name "
1102 + "from AnimalData a "
1103 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1104
1105 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1106
1107 assertAll(
1108 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1109 () -> assertThat(animals).hasSize(3),
1110 () -> assertThat(animals).element(0).isNotNull()
1111 .extracting("DISPLAY_NAME")
1112 .isEqualTo("African elephant - The Legend"),
1113 () -> assertThat(animals).element(1).isNotNull()
1114 .extracting("DISPLAY_NAME")
1115 .isEqualTo("Dipliodocus - The Legend"),
1116 () -> assertThat(animals).element(2).isNotNull()
1117 .extracting("DISPLAY_NAME")
1118 .isEqualTo("Brachiosaurus - The Legend")
1119 );
1120 }
1121 }
1122
1123 @Test
1124 void testConcatenateConstantFirst() {
1125 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1126 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1127
1128 SelectStatementProvider selectStatement = select(id, concatenate(stringConstant("Name: "), animalName).as("display_name"))
1129 .from(animalData, "a")
1130 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1131 .build()
1132 .render(RenderingStrategies.MYBATIS3);
1133
1134 String expected = "select a.id, ('Name: ' || a.animal_name) as display_name "
1135 + "from AnimalData a "
1136 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1137
1138 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1139
1140 assertAll(
1141 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1142 () -> assertThat(animals).hasSize(3),
1143 () -> assertThat(animals).element(0).isNotNull()
1144 .extracting("DISPLAY_NAME")
1145 .isEqualTo("Name: African elephant"),
1146 () -> assertThat(animals).element(1).isNotNull()
1147 .extracting("DISPLAY_NAME")
1148 .isEqualTo("Name: Dipliodocus"),
1149 () -> assertThat(animals).element(2).isNotNull()
1150 .extracting("DISPLAY_NAME")
1151 .isEqualTo("Name: Brachiosaurus")
1152 );
1153 }
1154 }
1155
1156 @Test
1157 void testDivide() {
1158 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1159 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1160
1161 SelectStatementProvider selectStatement = select(id, animalName, divide(bodyWeight, brainWeight).as("calculated_weight"))
1162 .from(animalData, "a")
1163 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1164 .build()
1165 .render(RenderingStrategies.MYBATIS3);
1166
1167 String expected = "select a.id, a.animal_name, (a.body_weight / a.brain_weight) as calculated_weight "
1168 + "from AnimalData a "
1169 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1170
1171 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1172
1173 assertAll(
1174 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1175 () -> assertThat(animals).hasSize(3),
1176 () -> assertThat(animals).element(0, as(MAP)).isNotNull()
1177 .containsEntry("ANIMAL_NAME", "African elephant")
1178 .extracting("CALCULATED_WEIGHT", as(DOUBLE)).isEqualTo(0.858, within(0.001)),
1179 () -> assertThat(animals).element(1, as(MAP)).isNotNull()
1180 .containsEntry("ANIMAL_NAME", "Dipliodocus")
1181 .extracting("CALCULATED_WEIGHT", as(DOUBLE)).isEqualTo(0.004, within(0.001)),
1182 () -> assertThat(animals).element(2, as(MAP)).isNotNull()
1183 .containsEntry("ANIMAL_NAME", "Brachiosaurus")
1184 .extracting("CALCULATED_WEIGHT", as(DOUBLE)).isEqualTo(0.001, within(0.001))
1185 );
1186 }
1187 }
1188
1189 @Test
1190 void testDivideConstant() {
1191 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1192 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1193
1194 SelectStatementProvider selectStatement = select(id, animalName, divide(bodyWeight, constant("10.0")).as("calculated_weight"))
1195 .from(animalData, "a")
1196 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1197 .build()
1198 .render(RenderingStrategies.MYBATIS3);
1199
1200 String expected = "select a.id, a.animal_name, (a.body_weight / 10.0) as calculated_weight "
1201 + "from AnimalData a "
1202 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1203
1204 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1205
1206 assertAll(
1207 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1208 () -> assertThat(animals).hasSize(3),
1209 () -> assertThat(animals).element(0).isNotNull()
1210 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1211 .containsExactly("African elephant", 571.2),
1212 () -> assertThat(animals).element(1).isNotNull()
1213 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1214 .containsExactly("Dipliodocus", 5.0),
1215 () -> assertThat(animals).element(2).isNotNull()
1216 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1217 .containsExactly("Brachiosaurus", 15.45)
1218 );
1219 }
1220 }
1221
1222 @Test
1223 void testMultiply() {
1224 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1225 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1226
1227 SelectStatementProvider selectStatement = select(id, animalName, multiply(bodyWeight, brainWeight).as("calculated_weight"))
1228 .from(animalData, "a")
1229 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1230 .build()
1231 .render(RenderingStrategies.MYBATIS3);
1232
1233 String expected = "select a.id, a.animal_name, (a.body_weight * a.brain_weight) as calculated_weight "
1234 + "from AnimalData a "
1235 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1236
1237 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1238
1239 assertAll(
1240 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1241 () -> assertThat(animals).hasSize(3),
1242 () -> assertThat(animals).element(0).isNotNull()
1243 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1244 .containsExactly("African elephant", 38007648.0),
1245 () -> assertThat(animals).element(1).isNotNull()
1246 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1247 .containsExactly("Dipliodocus", 585000.0),
1248 () -> assertThat(animals).element(2).isNotNull()
1249 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1250 .containsExactly("Brachiosaurus", 13441500.0)
1251 );
1252 }
1253 }
1254
1255 @Test
1256 void testMultiplyConstant() {
1257 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1258 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1259
1260 SelectStatementProvider selectStatement = select(id, animalName, multiply(bodyWeight, constant("2.0")).as("calculated_weight"))
1261 .from(animalData, "a")
1262 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1263 .build()
1264 .render(RenderingStrategies.MYBATIS3);
1265
1266 String expected = "select a.id, a.animal_name, (a.body_weight * 2.0) as calculated_weight "
1267 + "from AnimalData a "
1268 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1269
1270 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1271
1272 assertAll(
1273 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1274 () -> assertThat(animals).hasSize(3),
1275 () -> assertThat(animals).element(0).isNotNull()
1276 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1277 .containsExactly("African elephant", 11424.0),
1278 () -> assertThat(animals).element(1).isNotNull()
1279 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1280 .containsExactly("Dipliodocus", 100.0),
1281 () -> assertThat(animals).element(2).isNotNull()
1282 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1283 .containsExactly("Brachiosaurus", 309.0)
1284 );
1285 }
1286 }
1287
1288 @Test
1289 void testSubtract() {
1290 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1291 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1292
1293 SelectStatementProvider selectStatement = select(id, animalName, subtract(bodyWeight, brainWeight).as("calculated_weight"))
1294 .from(animalData, "a")
1295 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1296 .build()
1297 .render(RenderingStrategies.MYBATIS3);
1298
1299 String expected = "select a.id, a.animal_name, (a.body_weight - a.brain_weight) as calculated_weight "
1300 + "from AnimalData a "
1301 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1302
1303 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1304
1305 assertAll(
1306 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1307 () -> assertThat(animals).hasSize(3),
1308 () -> assertThat(animals).element(0).isNotNull()
1309 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1310 .containsExactly("African elephant", -942.0),
1311 () -> assertThat(animals).element(1).isNotNull()
1312 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1313 .containsExactly("Dipliodocus", -11650.0),
1314 () -> assertThat(animals).element(2).isNotNull()
1315 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1316 .containsExactly("Brachiosaurus", -86845.5)
1317 );
1318 }
1319 }
1320
1321 @Test
1322 void testSubtractConstant() {
1323 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1324 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1325
1326 SelectStatementProvider selectStatement = select(id, animalName, subtract(bodyWeight, constant("5.5")).as("calculated_weight"))
1327 .from(animalData, "a")
1328 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1329 .build()
1330 .render(RenderingStrategies.MYBATIS3);
1331
1332 String expected = "select a.id, a.animal_name, (a.body_weight - 5.5) as calculated_weight "
1333 + "from AnimalData a "
1334 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1335
1336 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1337
1338 assertAll(
1339 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1340 () -> assertThat(animals).hasSize(3),
1341 () -> assertThat(animals).element(0).isNotNull()
1342 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1343 .containsExactly("African elephant", 5706.5),
1344 () -> assertThat(animals).element(1).isNotNull()
1345 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1346 .containsExactly("Dipliodocus", 44.5),
1347 () -> assertThat(animals).element(2).isNotNull()
1348 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1349 .containsExactly("Brachiosaurus", 149.0)
1350 );
1351 }
1352 }
1353
1354 @Test
1355 void testGeneralOperator() {
1356 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1357 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1358
1359 SelectStatementProvider selectStatement = select(id, animalName, applyOperator("-", bodyWeight, brainWeight).as("calculated_weight"))
1360 .from(animalData, "a")
1361 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1362 .build()
1363 .render(RenderingStrategies.MYBATIS3);
1364
1365 String expected = "select a.id, a.animal_name, (a.body_weight - a.brain_weight) as calculated_weight "
1366 + "from AnimalData a "
1367 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1368
1369 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1370
1371 assertAll(
1372 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1373 () -> assertThat(animals).hasSize(3),
1374 () -> assertThat(animals).element(0).isNotNull()
1375 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1376 .containsExactly("African elephant", -942.0),
1377 () -> assertThat(animals).element(1).isNotNull()
1378 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1379 .containsExactly("Dipliodocus", -11650.0),
1380 () -> assertThat(animals).element(2).isNotNull()
1381 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1382 .containsExactly("Brachiosaurus", -86845.5)
1383 );
1384 }
1385 }
1386
1387 @Test
1388 void testComplexExpression() {
1389 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1390 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1391
1392 SelectStatementProvider selectStatement = select(id, animalName, add(multiply(bodyWeight, constant("5.5")), subtract(brainWeight, constant("2"))).as("calculated_weight"))
1393 .from(animalData, "a")
1394 .where(add(bodyWeight, brainWeight), isGreaterThan(10000.0))
1395 .build()
1396 .render(RenderingStrategies.MYBATIS3);
1397
1398 String expected = "select a.id, a.animal_name, ((a.body_weight * 5.5) + (a.brain_weight - 2)) as calculated_weight "
1399 + "from AnimalData a "
1400 + "where (a.body_weight + a.brain_weight) > #{parameters.p1,jdbcType=DOUBLE}";
1401
1402 List<Map<String, Object>> animals = mapper.selectManyMappedRows(selectStatement);
1403
1404 assertAll(
1405 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo(expected),
1406 () -> assertThat(animals).hasSize(3),
1407 () -> assertThat(animals).element(0).isNotNull()
1408 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1409 .containsExactly("African elephant", 38068.0),
1410 () -> assertThat(animals).element(1).isNotNull()
1411 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1412 .containsExactly("Dipliodocus", 11973.0),
1413 () -> assertThat(animals).element(2).isNotNull()
1414 .extracting("ANIMAL_NAME", "CALCULATED_WEIGHT")
1415 .containsExactly("Brachiosaurus", 87847.75)
1416 );
1417 }
1418 }
1419
1420 @Test
1421 void testNotLikeCondition() {
1422 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1423 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1424
1425 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1426 .from(animalData)
1427 .where(animalName, isNotLike("%squirrel"))
1428 .build()
1429 .render(RenderingStrategies.MYBATIS3);
1430
1431 List<AnimalData> animals = mapper.selectMany(selectStatement);
1432 assertThat(animals).hasSize(63);
1433 }
1434 }
1435
1436 @Test
1437 void testNotLikeCaseInsensistveCondition() {
1438 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1439 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1440
1441 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1442 .from(animalData)
1443 .where(animalName, isNotLikeCaseInsensitive("%squirrel"))
1444 .build()
1445 .render(RenderingStrategies.MYBATIS3);
1446
1447 List<AnimalData> animals = mapper.selectMany(selectStatement);
1448 assertThat(animals).hasSize(63);
1449 }
1450 }
1451
1452 @Test
1453 void testDeleteThreeRows() {
1454 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1455 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1456
1457 DeleteStatementProvider deleteStatement = deleteFrom(animalData)
1458 .where(id, isIn(5, 8, 10))
1459 .build()
1460 .render(RenderingStrategies.MYBATIS3);
1461
1462 int rowCount = mapper.delete(deleteStatement);
1463 assertThat(rowCount).isEqualTo(3);
1464 }
1465 }
1466
1467 @Test
1468 void testComplexDelete() {
1469 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1470 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1471
1472 DeleteStatementProvider deleteStatement = deleteFrom(animalData)
1473 .where(id, isLessThan(10))
1474 .or(id, isGreaterThan(60))
1475 .build()
1476 .render(RenderingStrategies.MYBATIS3);
1477
1478 int rowCount = mapper.delete(deleteStatement);
1479 assertThat(rowCount).isEqualTo(14);
1480 }
1481 }
1482
1483 @Test
1484 void testIsNullCondition() {
1485 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1486 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1487
1488 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1489 .from(animalData)
1490 .where(id, isNull())
1491 .build()
1492 .render(RenderingStrategies.MYBATIS3);
1493
1494 List<AnimalData> animals = mapper.selectMany(selectStatement);
1495 assertThat(animals).isEmpty();
1496 }
1497 }
1498
1499 @Test
1500 void testIsNotNullCondition() {
1501 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1502 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1503
1504 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1505 .from(animalData)
1506 .where(id, isNotNull())
1507 .build()
1508 .render(RenderingStrategies.MYBATIS3);
1509
1510 List<AnimalData> animals = mapper.selectMany(selectStatement);
1511 assertThat(animals).hasSize(65);
1512 }
1513 }
1514
1515 @Test
1516 void testComplexCondition() {
1517 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1518 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1519
1520 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1521 .from(animalData)
1522 .where(id, isIn(1, 5, 7))
1523 .or(id, isIn(2, 6, 8), and(animalName, isLike("%bat")))
1524 .or(id, isGreaterThan(60))
1525 .and(bodyWeight, isBetween(1.0).and(3.0))
1526 .orderBy(id.descending(), bodyWeight)
1527 .build()
1528 .render(RenderingStrategies.MYBATIS3);
1529
1530 List<AnimalData> animals = mapper.selectMany(selectStatement);
1531 assertThat(animals).hasSize(4);
1532 }
1533 }
1534
1535 @Test
1536 void testUpdate() {
1537 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1538 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1539
1540 UpdateStatementProvider updateStatement = update(animalData)
1541 .set(bodyWeight).equalTo(2.6)
1542 .set(animalName).equalToNull()
1543 .where(id, isIn(1, 5, 7))
1544 .or(id, isIn(2, 6, 8), and(animalName, isLike("%bat")))
1545 .or(id, isGreaterThan(60))
1546 .and(bodyWeight, isBetween(1.0).and(3.0))
1547 .build()
1548 .render(RenderingStrategies.MYBATIS3);
1549
1550 int rows = mapper.update(updateStatement);
1551 assertThat(rows).isEqualTo(4);
1552 }
1553 }
1554
1555 @Test
1556 void testUpdateValueOrNullWithValue() {
1557 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1558 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1559
1560 UpdateStatementProvider updateStatement = update(animalData)
1561 .set(animalName).equalToOrNull("fred")
1562 .where(id, isEqualTo(1))
1563 .build()
1564 .render(RenderingStrategies.MYBATIS3);
1565
1566 assertThat(updateStatement.getUpdateStatement()).isEqualTo(
1567 "update AnimalData set animal_name = #{parameters.p1,jdbcType=VARCHAR} where id = #{parameters.p2,jdbcType=INTEGER}");
1568 int rows = mapper.update(updateStatement);
1569 assertThat(rows).isEqualTo(1);
1570 }
1571 }
1572
1573 @Test
1574 void testUpdateValueOrNullWithNull() {
1575 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1576 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1577
1578 UpdateStatementProvider updateStatement = update(animalData)
1579 .set(animalName).equalToOrNull((String) null)
1580 .where(id, isEqualTo(1))
1581 .build()
1582 .render(RenderingStrategies.MYBATIS3);
1583
1584 assertThat(updateStatement.getUpdateStatement()).isEqualTo(
1585 "update AnimalData set animal_name = null where id = #{parameters.p1,jdbcType=INTEGER}");
1586 int rows = mapper.update(updateStatement);
1587 assertThat(rows).isEqualTo(1);
1588 }
1589 }
1590
1591 @Test
1592 void testInsert() {
1593 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1594 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1595 AnimalData row = new AnimalData(100, "Old Shep", 22.5, 1.2);
1596
1597 InsertStatementProvider<AnimalData> insertStatement = insert(row)
1598 .into(animalData)
1599 .map(id).toProperty("id")
1600 .map(animalName).toProperty("animalName")
1601 .map(bodyWeight).toProperty("bodyWeight")
1602 .map(brainWeight).toProperty("brainWeight")
1603 .build()
1604 .render(RenderingStrategies.MYBATIS3);
1605
1606 int rows = mapper.insert(insertStatement);
1607 assertThat(rows).isEqualTo(1);
1608 }
1609 }
1610
1611 @Test
1612 void testInsertNull() {
1613 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1614 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1615 AnimalData row = new AnimalData(100, "Old Shep", 22.5, 1.2);
1616
1617 InsertStatementProvider<AnimalData> insertStatement = insert(row)
1618 .into(animalData)
1619 .map(id).toProperty("id")
1620 .map(animalName).toNull()
1621 .map(bodyWeight).toProperty("bodyWeight")
1622 .map(brainWeight).toProperty("brainWeight")
1623 .build()
1624 .render(RenderingStrategies.MYBATIS3);
1625
1626 int rows = mapper.insert(insertStatement);
1627 assertThat(rows).isEqualTo(1);
1628 }
1629 }
1630
1631 @Test
1632 void testBulkInsert() {
1633 try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
1634 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1635 List<AnimalData> records = List.of(
1636 new AnimalData(100, "Old Shep", 0.0, 22.5),
1637 new AnimalData(101, "Old Dan", 0.0, 22.5)
1638 );
1639
1640 BatchInsert<AnimalData> batchInsert = insertBatch(records)
1641 .into(animalData)
1642 .map(id).toProperty("id")
1643 .map(animalName).toNull()
1644 .map(bodyWeight).toProperty("bodyWeight")
1645 .map(brainWeight).toConstant("1.2")
1646 .build()
1647 .render(RenderingStrategies.MYBATIS3);
1648
1649 batchInsert.insertStatements().forEach(mapper::insert);
1650 sqlSession.commit();
1651
1652 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1653 .from(animalData)
1654 .where(id, isIn(100, 101))
1655 .orderBy(id)
1656 .build()
1657 .render(RenderingStrategies.MYBATIS3);
1658
1659 List<AnimalData> animals = mapper.selectMany(selectStatement);
1660
1661 assertAll(
1662 () -> assertThat(animals).hasSize(2),
1663 () -> assertThat(animals).element(0).isNotNull()
1664 .extracting(AnimalData::id, AnimalData::brainWeight, AnimalData::animalName)
1665 .containsExactly(100, 1.2, null),
1666 () -> assertThat(animals).element(1).isNotNull()
1667 .extracting(AnimalData::id, AnimalData::brainWeight, AnimalData::animalName)
1668 .containsExactly(101, 1.2, null)
1669 );
1670 }
1671 }
1672
1673 @Test
1674 void testBulkInsert2() {
1675 try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
1676 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1677 List<AnimalData> records = List.of(
1678 new AnimalData(100, "Old Shep", 0.0, 22.5),
1679 new AnimalData(101, "Old Dan", 0.0, 22.5)
1680 );
1681
1682 BatchInsert<AnimalData> batchInsert = insertBatch(records)
1683 .into(animalData)
1684 .map(id).toProperty("id")
1685 .map(animalName).toStringConstant("Old Fred")
1686 .map(bodyWeight).toProperty("bodyWeight")
1687 .map(brainWeight).toConstant("1.2")
1688 .build()
1689 .render(RenderingStrategies.MYBATIS3);
1690
1691 batchInsert.insertStatements().forEach(mapper::insert);
1692 mapper.flush();
1693
1694 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1695 .from(animalData)
1696 .where(id, isIn(100, 101))
1697 .orderBy(id)
1698 .build()
1699 .render(RenderingStrategies.MYBATIS3);
1700
1701 List<AnimalData> animals = mapper.selectMany(selectStatement);
1702
1703 assertAll(
1704 () -> assertThat(animals).hasSize(2),
1705 () -> assertThat(animals).element(0).isNotNull()
1706 .extracting(AnimalData::id, AnimalData::brainWeight, AnimalData::animalName)
1707 .containsExactly(100, 1.2, "Old Fred"),
1708 () -> assertThat(animals).element(1).isNotNull()
1709 .extracting(AnimalData::id, AnimalData::brainWeight, AnimalData::animalName)
1710 .containsExactly(101, 1.2, "Old Fred")
1711 );
1712 }
1713 }
1714
1715 @Test
1716 void testOrderByAndDistinct() {
1717 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1718 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1719
1720 SelectStatementProvider selectStatement = selectDistinct(id, animalName, bodyWeight, brainWeight)
1721 .from(animalData)
1722 .where(id, isLessThan(10))
1723 .or(id, isGreaterThan(60))
1724 .orderBy(id.descending(), animalName)
1725 .build()
1726 .render(RenderingStrategies.MYBATIS3);
1727
1728 List<AnimalData> rows = mapper.selectMany(selectStatement);
1729
1730 assertAll(
1731 () -> assertThat(rows).hasSize(14),
1732 () -> assertThat(rows).first().isNotNull().extracting(AnimalData::id).isEqualTo(65)
1733 );
1734 }
1735 }
1736
1737 @Test
1738 void testOrderByWithFullClause() {
1739 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1740 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1741
1742 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1743 .from(animalData)
1744 .where(id, isLessThan(10))
1745 .or(id, isGreaterThan(60))
1746 .orderBy(id.descending())
1747 .build()
1748 .render(RenderingStrategies.MYBATIS3);
1749
1750 List<AnimalData> rows = mapper.selectMany(selectStatement);
1751
1752 assertAll(
1753 () -> assertThat(rows).hasSize(14),
1754 () -> assertThat(rows).first().isNotNull().extracting(AnimalData::id).isEqualTo(65)
1755 );
1756 }
1757 }
1758
1759 @Test
1760 void testCount() {
1761 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1762 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1763
1764 SelectStatementProvider selectStatement = select(count().as("total"))
1765 .from(animalData, "a")
1766 .build()
1767 .render(RenderingStrategies.MYBATIS3);
1768
1769 Long count = mapper.selectOneLong(selectStatement);
1770
1771 assertAll(
1772 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select count(*) as total from AnimalData a"),
1773 () -> assertThat(count).isEqualTo(65)
1774 );
1775 }
1776 }
1777
1778 @Test
1779 void testCountField() {
1780 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1781 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1782
1783 SelectStatementProvider selectStatement = select(count(brainWeight).as("total"))
1784 .from(animalData, "a")
1785 .build()
1786 .render(RenderingStrategies.MYBATIS3);
1787
1788 Long count = mapper.selectOneLong(selectStatement);
1789
1790 assertAll(
1791 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select count(a.brain_weight) as total from AnimalData a"),
1792 () -> assertThat(count).isEqualTo(65)
1793 );
1794 }
1795 }
1796
1797 @Test
1798 void testCountNoAlias() {
1799 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1800 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1801
1802 SelectStatementProvider selectStatement = select(count())
1803 .from(animalData)
1804 .build()
1805 .render(RenderingStrategies.MYBATIS3);
1806
1807 Long count = mapper.selectOneLong(selectStatement);
1808
1809 assertAll(
1810 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select count(*) from AnimalData"),
1811 () -> assertThat(count).isEqualTo(65)
1812 );
1813 }
1814 }
1815
1816 @Test
1817 void testMax() {
1818 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1819 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1820
1821 SelectStatementProvider selectStatement = select(max(brainWeight).as("total"))
1822 .from(animalData, "a")
1823 .build()
1824 .render(RenderingStrategies.MYBATIS3);
1825
1826 Double max = mapper.selectOneDouble(selectStatement);
1827
1828 assertAll(
1829 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select max(a.brain_weight) as total from AnimalData a"),
1830 () -> assertThat(max).isEqualTo(87000.0)
1831 );
1832 }
1833 }
1834
1835 @Test
1836 void testMaxNoAlias() {
1837 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1838 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1839
1840 SelectStatementProvider selectStatement = select(max(brainWeight))
1841 .from(animalData)
1842 .build()
1843 .render(RenderingStrategies.MYBATIS3);
1844
1845 Double max = mapper.selectOneDouble(selectStatement);
1846
1847 assertAll(
1848 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select max(brain_weight) from AnimalData"),
1849 () -> assertThat(max).isEqualTo(87000.0)
1850 );
1851 }
1852 }
1853
1854 @Test
1855 void testMaxSubselect() {
1856 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1857 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1858
1859 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1860 .from(animalData, "a")
1861 .where(brainWeight, isEqualTo(select(max(brainWeight)).from(animalData, "b")))
1862 .build()
1863 .render(RenderingStrategies.MYBATIS3);
1864
1865 List<AnimalData> records = mapper.selectMany(selectStatement);
1866
1867 assertAll(
1868 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select a.id, a.animal_name, a.body_weight, a.brain_weight from AnimalData a where a.brain_weight = (select max(b.brain_weight) from AnimalData b)"),
1869 () -> assertThat(records).hasSize(1),
1870 () -> assertThat(records).first().isNotNull().extracting(AnimalData::animalName).isEqualTo("Brachiosaurus")
1871 );
1872 }
1873 }
1874
1875 @Test
1876 void testMin() {
1877 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1878 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1879
1880 SelectStatementProvider selectStatement = select(min(brainWeight).as("total"))
1881 .from(animalData, "a")
1882 .build()
1883 .render(RenderingStrategies.MYBATIS3);
1884
1885 Double min = mapper.selectOneDouble(selectStatement);
1886
1887 assertAll(
1888 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select min(a.brain_weight) as total from AnimalData a"),
1889 () -> assertThat(min).isEqualTo(0.005)
1890 );
1891 }
1892 }
1893
1894 @Test
1895 void testMinNoAlias() {
1896 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1897 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1898
1899 SelectStatementProvider selectStatement = select(min(brainWeight))
1900 .from(animalData)
1901 .build()
1902 .render(RenderingStrategies.MYBATIS3);
1903
1904 Double min = mapper.selectOneDouble(selectStatement);
1905
1906 assertAll(
1907 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select min(brain_weight) from AnimalData"),
1908 () -> assertThat(min).isEqualTo(0.005)
1909 );
1910 }
1911 }
1912
1913 @Test
1914 void testMinSubselect() {
1915 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1916 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1917
1918 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1919 .from(animalData, "a")
1920 .where(brainWeight, isNotEqualTo(select(min(brainWeight)).from(animalData, "b")))
1921 .orderBy(animalName)
1922 .build()
1923 .render(RenderingStrategies.MYBATIS3);
1924
1925 List<AnimalData> records = mapper.selectMany(selectStatement);
1926
1927 assertAll(
1928 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select a.id, a.animal_name, a.body_weight, a.brain_weight from AnimalData a where a.brain_weight <> (select min(b.brain_weight) from AnimalData b) order by animal_name"),
1929 () -> assertThat(records).hasSize(64),
1930 () -> assertThat(records).first().isNotNull().extracting(AnimalData::animalName).isEqualTo("African elephant")
1931 );
1932 }
1933 }
1934
1935 @Test
1936 void testMinSubselectNoAlias() {
1937 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1938 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1939
1940 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
1941 .from(animalData)
1942 .where(brainWeight, isNotEqualTo(select(min(brainWeight)).from(animalData)))
1943 .orderBy(animalName)
1944 .build()
1945 .render(RenderingStrategies.MYBATIS3);
1946
1947 List<AnimalData> records = mapper.selectMany(selectStatement);
1948
1949 assertAll(
1950 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select id, animal_name, body_weight, brain_weight from AnimalData where brain_weight <> (select min(brain_weight) from AnimalData) order by animal_name"),
1951 () -> assertThat(records).hasSize(64),
1952 () -> assertThat(records).first().isNotNull().extracting(AnimalData::animalName).isEqualTo("African elephant")
1953 );
1954 }
1955 }
1956
1957 @Test
1958 void testAvg() {
1959 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1960 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1961
1962 SelectStatementProvider selectStatement = select(avg(brainWeight).as("average"))
1963 .from(animalData, "a")
1964 .build()
1965 .render(RenderingStrategies.MYBATIS3);
1966
1967 Double average = mapper.selectOneDouble(selectStatement);
1968
1969 assertAll(
1970 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select avg(a.brain_weight) as average from AnimalData a"),
1971 () -> assertThat(average).isEqualTo(1852.69, within(.01))
1972 );
1973 }
1974 }
1975
1976 @Test
1977 void testSum() {
1978 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1979 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
1980
1981 SelectStatementProvider selectStatement = select(sum(brainWeight).as("total"))
1982 .from(animalData)
1983 .build()
1984 .render(RenderingStrategies.MYBATIS3);
1985
1986 Double total = mapper.selectOneDouble(selectStatement);
1987
1988 assertAll(
1989 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select sum(brain_weight) as total from AnimalData"),
1990 () -> assertThat(total).isEqualTo(120424.97, within(.01))
1991 );
1992 }
1993 }
1994
1995 @Test
1996 void testLessThanSubselect() {
1997 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
1998 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
1999
2000 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
2001 .from(animalData, "a")
2002 .where(brainWeight, isLessThan(select(max(brainWeight)).from(animalData, "b")))
2003 .build()
2004 .render(RenderingStrategies.MYBATIS3);
2005
2006 List<AnimalData> records = mapper.selectMany(selectStatement);
2007
2008 assertAll(
2009 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select a.id, a.animal_name, a.body_weight, a.brain_weight from AnimalData a where a.brain_weight < (select max(b.brain_weight) from AnimalData b)"),
2010 () -> assertThat(records).hasSize(64)
2011 );
2012 }
2013 }
2014
2015 @Test
2016 void testLessThanOrEqualToSubselect() {
2017 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2018 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2019
2020 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
2021 .from(animalData, "a")
2022 .where(brainWeight, isLessThanOrEqualTo(select(max(brainWeight)).from(animalData, "b")))
2023 .build()
2024 .render(RenderingStrategies.MYBATIS3);
2025
2026 List<AnimalData> records = mapper.selectMany(selectStatement);
2027
2028 assertAll(
2029 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select a.id, a.animal_name, a.body_weight, a.brain_weight from AnimalData a where a.brain_weight <= (select max(b.brain_weight) from AnimalData b)"),
2030 () -> assertThat(records).hasSize(65)
2031 );
2032 }
2033 }
2034
2035 @Test
2036 void testGreaterThanSubselect() {
2037 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2038 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2039
2040 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
2041 .from(animalData, "a")
2042 .where(brainWeight, isGreaterThan(select(min(brainWeight)).from(animalData, "b")))
2043 .build()
2044 .render(RenderingStrategies.MYBATIS3);
2045
2046 List<AnimalData> records = mapper.selectMany(selectStatement);
2047
2048 assertAll(
2049 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select a.id, a.animal_name, a.body_weight, a.brain_weight from AnimalData a where a.brain_weight > (select min(b.brain_weight) from AnimalData b)"),
2050 () -> assertThat(records).hasSize(64)
2051 );
2052 }
2053 }
2054
2055 @Test
2056 void testGreaterThanOrEqualToSubselect() {
2057 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2058 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2059
2060 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
2061 .from(animalData, "a")
2062 .where(brainWeight, isGreaterThanOrEqualTo(select(min(brainWeight)).from(animalData, "b")))
2063 .build()
2064 .render(RenderingStrategies.MYBATIS3);
2065
2066 List<AnimalData> records = mapper.selectMany(selectStatement);
2067
2068 assertAll(
2069 () -> assertThat(selectStatement.getSelectStatement()).isEqualTo("select a.id, a.animal_name, a.body_weight, a.brain_weight from AnimalData a where a.brain_weight >= (select min(b.brain_weight) from AnimalData b)"),
2070 () -> assertThat(records).hasSize(65)
2071 );
2072 }
2073 }
2074
2075 @Test
2076 void testInsertSelectWithColumnList() {
2077 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2078 SqlTable animalDataCopy = SqlTable.of("AnimalDataCopy");
2079 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2080
2081 InsertSelectStatementProvider insertSelectStatement = insertInto(animalDataCopy)
2082 .withColumnList(id, animalName, bodyWeight, brainWeight)
2083 .withSelectStatement(select(id, animalName, bodyWeight, brainWeight).from(animalData).where(id, isLessThan(22)))
2084 .build()
2085 .render(RenderingStrategies.MYBATIS3);
2086
2087 String expected = "insert into AnimalDataCopy (id, animal_name, body_weight, brain_weight) "
2088 + "select id, animal_name, body_weight, brain_weight "
2089 + "from AnimalData "
2090 + "where id < #{parameters.p1,jdbcType=INTEGER}";
2091
2092 int rows = mapper.insertSelect(insertSelectStatement);
2093
2094 assertAll(
2095 () -> assertThat(insertSelectStatement.getInsertStatement()).isEqualTo(expected),
2096 () -> assertThat(insertSelectStatement.getParameters()).hasSize(1),
2097 () -> assertThat(insertSelectStatement.getParameters()).containsEntry("p1", 22),
2098 () -> assertThat(rows).isEqualTo(21)
2099 );
2100 }
2101 }
2102
2103 @Test
2104 void testInsertSelectWithoutColumnList() {
2105 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2106 SqlTable animalDataCopy = SqlTable.of("AnimalDataCopy");
2107 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2108
2109 InsertSelectStatementProvider insertSelectStatement = insertInto(animalDataCopy)
2110 .withSelectStatement(select(id, animalName, bodyWeight, brainWeight).from(animalData).where(id, isLessThan(33)))
2111 .build()
2112 .render(RenderingStrategies.MYBATIS3);
2113
2114 String expected = "insert into AnimalDataCopy "
2115 + "select id, animal_name, body_weight, brain_weight "
2116 + "from AnimalData "
2117 + "where id < #{parameters.p1,jdbcType=INTEGER}";
2118 int rows = mapper.insertSelect(insertSelectStatement);
2119
2120 assertAll(
2121 () -> assertThat(insertSelectStatement.getInsertStatement()).isEqualTo(expected),
2122 () -> assertThat(insertSelectStatement.getParameters()).hasSize(1),
2123 () -> assertThat(insertSelectStatement.getParameters()).containsEntry("p1", 33),
2124 () -> assertThat(rows).isEqualTo(32)
2125 );
2126 }
2127 }
2128
2129 @Test
2130 void testGeneralInsert() {
2131 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2132 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2133
2134 GeneralInsertStatementProvider insertStatement = insertInto(animalData)
2135 .set(id).toValue(101)
2136 .set(animalName).toStringConstant("Fred")
2137 .set(brainWeight).toConstant("2.2")
2138 .set(bodyWeight).toValue(4.5)
2139 .build()
2140 .render(RenderingStrategies.MYBATIS3);
2141
2142 String expected = "insert into AnimalData (id, animal_name, brain_weight, body_weight) "
2143 + "values (#{parameters.p1,jdbcType=INTEGER}, 'Fred', 2.2, #{parameters.p2,jdbcType=DOUBLE})";
2144
2145 assertThat(insertStatement.getInsertStatement()).isEqualTo(expected);
2146 assertThat(insertStatement.getParameters()).hasSize(2);
2147 assertThat(insertStatement.getParameters()).containsEntry("p1", 101);
2148 assertThat(insertStatement.getParameters()).containsEntry("p2", 4.5);
2149
2150 int rows = mapper.generalInsert(insertStatement);
2151 assertThat(rows).isEqualTo(1);
2152 }
2153 }
2154
2155 @Test
2156 void testGeneralInsertValueOrNullWithValue() {
2157 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2158 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2159
2160 GeneralInsertStatementProvider insertStatement = insertInto(animalData)
2161 .set(id).toValue(101)
2162 .set(animalName).toValueOrNull("Fred")
2163 .set(brainWeight).toConstant("2.2")
2164 .set(bodyWeight).toValue(4.5)
2165 .build()
2166 .render(RenderingStrategies.MYBATIS3);
2167
2168 String expected = "insert into AnimalData (id, animal_name, brain_weight, body_weight) "
2169 + "values (#{parameters.p1,jdbcType=INTEGER}, #{parameters.p2,jdbcType=VARCHAR}, 2.2, "
2170 + "#{parameters.p3,jdbcType=DOUBLE})";
2171
2172 assertThat(insertStatement.getInsertStatement()).isEqualTo(expected);
2173 assertThat(insertStatement.getParameters()).hasSize(3);
2174 assertThat(insertStatement.getParameters()).containsEntry("p1", 101);
2175 assertThat(insertStatement.getParameters()).containsEntry("p2", "Fred");
2176 assertThat(insertStatement.getParameters()).containsEntry("p3", 4.5);
2177
2178 int rows = mapper.generalInsert(insertStatement);
2179 assertThat(rows).isEqualTo(1);
2180 }
2181 }
2182
2183 @Test
2184 void testGeneralInsertValueOrNullWithNull() {
2185 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2186 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2187
2188 GeneralInsertStatementProvider insertStatement = insertInto(animalData)
2189 .set(id).toValue(101)
2190 .set(animalName).toValueOrNull((String) null)
2191 .set(brainWeight).toConstant("2.2")
2192 .set(bodyWeight).toValue(4.5)
2193 .build()
2194 .render(RenderingStrategies.MYBATIS3);
2195
2196 String expected = "insert into AnimalData (id, animal_name, brain_weight, body_weight) "
2197 + "values (#{parameters.p1,jdbcType=INTEGER}, null, 2.2, "
2198 + "#{parameters.p2,jdbcType=DOUBLE})";
2199
2200 assertThat(insertStatement.getInsertStatement()).isEqualTo(expected);
2201 assertThat(insertStatement.getParameters()).hasSize(2);
2202 assertThat(insertStatement.getParameters()).containsEntry("p1", 101);
2203 assertThat(insertStatement.getParameters()).containsEntry("p2", 4.5);
2204
2205 int rows = mapper.generalInsert(insertStatement);
2206 assertThat(rows).isEqualTo(1);
2207 }
2208 }
2209
2210 @Test
2211 void testUpdateWithSelect() {
2212 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2213 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2214
2215 UpdateStatementProvider updateStatement = update(animalData)
2216 .set(brainWeight).equalTo(select(avg(brainWeight)).from(animalData).where(brainWeight, isGreaterThan(22.0)))
2217 .where(brainWeight, isLessThan(1.0))
2218 .build()
2219 .render(RenderingStrategies.MYBATIS3);
2220
2221 String expected = "update AnimalData "
2222 + "set brain_weight = (select avg(brain_weight) from AnimalData where brain_weight > #{parameters.p1,jdbcType=DOUBLE}) "
2223 + "where brain_weight < #{parameters.p2,jdbcType=DOUBLE}";
2224 int rows = mapper.update(updateStatement);
2225
2226 assertAll(
2227 () -> assertThat(updateStatement.getUpdateStatement()).isEqualTo(expected),
2228 () -> assertThat(updateStatement.getParameters()).hasSize(2),
2229 () -> assertThat(updateStatement.getParameters()).containsEntry("p1", 22.0),
2230 () -> assertThat(updateStatement.getParameters()).containsEntry("p2", 1.0),
2231 () -> assertThat(rows).isEqualTo(20)
2232 );
2233 }
2234 }
2235
2236 @Test
2237 void testUpdateWithAddAndSubtract() {
2238 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2239 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2240
2241 UpdateStatementProvider updateStatement = update(animalData)
2242 .set(brainWeight).equalTo(add(brainWeight, constant("2")))
2243 .set(bodyWeight).equalTo(subtract(bodyWeight, constant("3")))
2244 .set(animalName).equalToWhenPresent((String) null)
2245 .where(id, isEqualTo(1))
2246 .build()
2247 .render(RenderingStrategies.MYBATIS3);
2248
2249 String expected = "update AnimalData "
2250 + "set brain_weight = (brain_weight + 2), body_weight = (body_weight - 3) "
2251 + "where id = #{parameters.p1,jdbcType=INTEGER}";
2252
2253 assertThat(updateStatement.getUpdateStatement()).isEqualTo(expected);
2254 assertThat(updateStatement.getParameters()).hasSize(1);
2255 assertThat(updateStatement.getParameters()).containsEntry("p1", 1);
2256
2257 int rows = mapper.update(updateStatement);
2258 assertThat(rows).isEqualTo(1);
2259
2260 AnimalData row = MyBatis3Utils.selectOne(mapper::selectOne,
2261 BasicColumn.columnList(id, animalName, bodyWeight, brainWeight),
2262 animalData,
2263 (SelectDSLCompleter) c -> c.where(id, isEqualTo(1))
2264 );
2265
2266 assertThat(row.bodyWeight()).isEqualTo(-2.86);
2267 assertThat(row.brainWeight()).isEqualTo(2.005);
2268 }
2269 }
2270
2271 @Test
2272 void testUpdateWithMultiplyAndDivide() {
2273 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
2274 AnimalDataMapper mapper = sqlSession.getMapper(AnimalDataMapper.class);
2275
2276 UpdateStatementProvider updateStatement = update(animalData)
2277 .set(brainWeight).equalTo(divide(brainWeight, constant("2")))
2278 .set(bodyWeight).equalTo(multiply(bodyWeight, constant("3")))
2279 .where(id, isEqualTo(1))
2280 .build()
2281 .render(RenderingStrategies.MYBATIS3);
2282
2283 String expected = "update AnimalData "
2284 + "set brain_weight = (brain_weight / 2), body_weight = (body_weight * 3) "
2285 + "where id = #{parameters.p1,jdbcType=INTEGER}";
2286 assertThat(updateStatement.getUpdateStatement()).isEqualTo(expected);
2287 assertThat(updateStatement.getParameters()).hasSize(1);
2288 assertThat(updateStatement.getParameters()).containsEntry("p1", 1);
2289
2290 int rows = mapper.update(updateStatement);
2291 assertThat(rows).isEqualTo(1);
2292
2293 AnimalData row = MyBatis3Utils.selectOne(mapper::selectOne,
2294 BasicColumn.columnList(id, animalName, bodyWeight, brainWeight),
2295 animalData,
2296 (SelectDSLCompleter) c -> c.where(id, isEqualTo(1))
2297 );
2298
2299 assertThat(row.bodyWeight()).isEqualTo(0.42, within(.001));
2300 assertThat(row.brainWeight()).isEqualTo(.0025);
2301 }
2302 }
2303 }