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