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.assertThat;
20 import static org.mybatis.dynamic.sql.SqlBuilder.*;
21
22 import java.io.InputStream;
23 import java.io.InputStreamReader;
24 import java.math.BigDecimal;
25 import java.sql.Connection;
26 import java.sql.DriverManager;
27 import java.util.List;
28 import java.util.Map;
29 import java.util.Optional;
30 import java.util.function.Function;
31
32 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
33 import org.apache.ibatis.jdbc.ScriptRunner;
34 import org.apache.ibatis.mapping.Environment;
35 import org.apache.ibatis.session.Configuration;
36 import org.apache.ibatis.session.SqlSession;
37 import org.apache.ibatis.session.SqlSessionFactory;
38 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
39 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
40 import org.assertj.core.data.Percentage;
41 import org.junit.jupiter.api.BeforeEach;
42 import org.junit.jupiter.api.Test;
43 import org.mybatis.dynamic.sql.render.RenderingStrategies;
44 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
45 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
46
47 class CommonSelectMapperTest {
48
49 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
50 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
51
52 private SqlSessionFactory sqlSessionFactory;
53
54 @BeforeEach
55 void setup() throws Exception {
56 Class.forName(JDBC_DRIVER);
57 InputStream is = getClass().getResourceAsStream("/examples/animal/data/CreateAnimalData.sql");
58 assert is != null;
59 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "")) {
60 ScriptRunner sr = new ScriptRunner(connection);
61 sr.setLogWriter(null);
62 sr.runScript(new InputStreamReader(is));
63 }
64
65 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
66 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
67 Configuration config = new Configuration(environment);
68 config.addMapper(CommonSelectMapper.class);
69 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
70 }
71
72 private final Function<Map<String, Object>, AnimalData> rowMapper = map -> new AnimalData(
73 (Integer) map.get("ID"),
74 (String) map.get("ANIMAL_NAME"),
75 (Double) map.get("BRAIN_WEIGHT"),
76 (Double) map.get("BODY_WEIGHT"));
77
78 @Test
79 void testGeneralSelectOne() {
80 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
81 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
82 SelectStatementProvider selectStatement = select(id.asCamelCase(), animalName.asCamelCase())
83 .from(animalData)
84 .where(id, isEqualTo(1))
85 .build()
86 .render(RenderingStrategies.MYBATIS3);
87 Map<String, Object> row = mapper.selectOneMappedRow(selectStatement);
88
89 assertThat(row).containsEntry("id", 1);
90 assertThat(row).containsEntry("animalName", "Lesser short-tailed shrew");
91 }
92 }
93
94 @Test
95 void testGeneralSelectOneWithRowMapper() {
96 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
97 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
98 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
99 .from(animalData)
100 .where(id, isEqualTo(1))
101 .build()
102 .render(RenderingStrategies.MYBATIS3);
103
104 AnimalData animal = mapper.selectOne(selectStatement, rowMapper);
105
106 assertThat(animal).isNotNull();
107 assertThat(animal.id()).isEqualTo(1);
108 assertThat(animal.animalName()).isEqualTo("Lesser short-tailed shrew");
109 assertThat(animal.bodyWeight()).isEqualTo(0.14);
110 assertThat(animal.brainWeight()).isEqualTo(0.005);
111 }
112 }
113
114 @Test
115 void testGeneralSelectOneWithRowMapperAndNullRow() {
116 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
117 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
118 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
119 .from(animalData)
120 .where(id, isEqualTo(-237))
121 .build()
122 .render(RenderingStrategies.MYBATIS3);
123
124 AnimalData animal = mapper.selectOne(selectStatement, rowMapper);
125 assertThat(animal).isNull();
126 }
127 }
128
129 @Test
130 void testGeneralSelectMany() {
131 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
132 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
133 SelectStatementProvider selectStatement = select(id, animalName)
134 .from(animalData)
135 .where(id, isIn(1, 2))
136 .orderBy(id)
137 .build()
138 .render(RenderingStrategies.MYBATIS3);
139 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
140
141 assertThat(rows).hasSize(2);
142
143 assertThat(rows.get(0)).containsEntry("ID", 1);
144 assertThat(rows.get(0)).containsEntry("ANIMAL_NAME", "Lesser short-tailed shrew");
145 assertThat(rows.get(1)).containsEntry("ID", 2);
146 assertThat(rows.get(1)).containsEntry("ANIMAL_NAME", "Little brown bat");
147 }
148 }
149
150 @Test
151 void testGeneralSelectManyWithRowMapper() {
152 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
153 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
154 SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
155 .from(animalData)
156 .where(id, isIn(1, 2))
157 .orderBy(id)
158 .build()
159 .render(RenderingStrategies.MYBATIS3);
160 List<AnimalData> rows = mapper.selectMany(selectStatement, rowMapper);
161
162 assertThat(rows).hasSize(2);
163
164 assertThat(rows.get(0).id()).isEqualTo(1);
165 assertThat(rows.get(0).animalName()).isEqualTo("Lesser short-tailed shrew");
166 assertThat(rows.get(0).bodyWeight()).isEqualTo(0.14);
167 assertThat(rows.get(0).brainWeight()).isEqualTo(0.005);
168 assertThat(rows.get(1).id()).isEqualTo(2);
169 assertThat(rows.get(1).animalName()).isEqualTo("Little brown bat");
170 assertThat(rows.get(1).bodyWeight()).isEqualTo(0.25);
171 assertThat(rows.get(1).brainWeight()).isEqualTo(0.01);
172 }
173 }
174
175 @Test
176 void testSelectOneBigDecimal() {
177 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
178 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
179 SelectStatementProvider selectStatement = select(bodyWeight)
180 .from(animalData)
181 .where(id, isEqualTo(1))
182 .build()
183 .render(RenderingStrategies.MYBATIS3);
184
185 BigDecimal bodyWeight = mapper.selectOneBigDecimal(selectStatement);
186
187 assertThat(bodyWeight).isCloseTo(new BigDecimal("0.14"), Percentage.withPercentage(0.01));
188 }
189 }
190
191 @Test
192 void testSelectOptionalBigDecimalPresent() {
193 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
194 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
195 SelectStatementProvider selectStatement = select(bodyWeight)
196 .from(animalData)
197 .where(id, isEqualTo(1))
198 .build()
199 .render(RenderingStrategies.MYBATIS3);
200
201 Optional<BigDecimal> bodyWeight = mapper.selectOptionalBigDecimal(selectStatement);
202
203 assertThat(bodyWeight).hasValueSatisfying(bw -> assertThat(bw).isCloseTo(new BigDecimal("0.14"), Percentage.withPercentage(0.01)));
204 }
205 }
206
207 @Test
208 void testSelectOptionalBigDecimalMissing() {
209 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
210 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
211 SelectStatementProvider selectStatement = select(bodyWeight)
212 .from(animalData)
213 .where(id, isEqualTo(1000))
214 .build()
215 .render(RenderingStrategies.MYBATIS3);
216
217 Optional<BigDecimal> bodyWeight = mapper.selectOptionalBigDecimal(selectStatement);
218
219 assertThat(bodyWeight).isEmpty();
220 }
221 }
222
223 @Test
224 void testSelectManyBigDecimals() {
225 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
226 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
227 SelectStatementProvider selectStatement = select(bodyWeight)
228 .from(animalData)
229 .where(id, isIn(1, 2))
230 .orderBy(id)
231 .build()
232 .render(RenderingStrategies.MYBATIS3);
233
234 List<BigDecimal> bodyWeights = mapper.selectManyBigDecimals(selectStatement);
235
236 assertThat(bodyWeights).hasSize(2);
237 assertThat(bodyWeights.get(0)).isCloseTo(new BigDecimal("0.14"), Percentage.withPercentage(0.01));
238 assertThat(bodyWeights.get(1)).isCloseTo(new BigDecimal("0.25"), Percentage.withPercentage(0.01));
239 }
240 }
241
242 @Test
243 void testSelectOneDouble() {
244 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
245 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
246 SelectStatementProvider selectStatement = select(bodyWeight)
247 .from(animalData)
248 .where(id, isEqualTo(1))
249 .build()
250 .render(RenderingStrategies.MYBATIS3);
251
252 Double bodyWeight = mapper.selectOneDouble(selectStatement);
253
254 assertThat(bodyWeight).isEqualTo(0.14);
255 }
256 }
257
258 @Test
259 void testSelectOptionalDoublePresent() {
260 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
261 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
262 SelectStatementProvider selectStatement = select(bodyWeight)
263 .from(animalData)
264 .where(id, isEqualTo(1))
265 .build()
266 .render(RenderingStrategies.MYBATIS3);
267
268 Optional<Double> bodyWeight = mapper.selectOptionalDouble(selectStatement);
269
270 assertThat(bodyWeight).hasValueSatisfying(bw -> assertThat(bw).isEqualTo(0.14));
271 }
272 }
273
274 @Test
275 void testSelectOptionalDoubleMissing() {
276 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
277 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
278 SelectStatementProvider selectStatement = select(bodyWeight)
279 .from(animalData)
280 .where(id, isEqualTo(1000))
281 .build()
282 .render(RenderingStrategies.MYBATIS3);
283
284 Optional<Double> bodyWeight = mapper.selectOptionalDouble(selectStatement);
285
286 assertThat(bodyWeight).isEmpty();
287 }
288 }
289
290 @Test
291 void testSelectManyDoubles() {
292 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
293 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
294 SelectStatementProvider selectStatement = select(bodyWeight)
295 .from(animalData)
296 .where(id, isIn(1, 2))
297 .orderBy(id)
298 .build()
299 .render(RenderingStrategies.MYBATIS3);
300
301 List<Double> bodyWeights = mapper.selectManyDoubles(selectStatement);
302
303 assertThat(bodyWeights).hasSize(2);
304 assertThat(bodyWeights.get(0)).isEqualTo(0.14);
305 assertThat(bodyWeights.get(1)).isEqualTo(0.25);
306 }
307 }
308
309 @Test
310 void testSelectOneInteger() {
311 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
312 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
313 SelectStatementProvider selectStatement = select(id)
314 .from(animalData)
315 .where(id, isEqualTo(1))
316 .build()
317 .render(RenderingStrategies.MYBATIS3);
318
319 Integer id = mapper.selectOneInteger(selectStatement);
320
321 assertThat(id).isEqualTo(1);
322 }
323 }
324
325 @Test
326 void testSelectOptionalIntegerPresent() {
327 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
328 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
329 SelectStatementProvider selectStatement = select(id)
330 .from(animalData)
331 .where(id, isEqualTo(1))
332 .build()
333 .render(RenderingStrategies.MYBATIS3);
334
335 Optional<Integer> id = mapper.selectOptionalInteger(selectStatement);
336
337 assertThat(id).hasValueSatisfying(i -> assertThat(i).isEqualTo(1));
338 }
339 }
340
341 @Test
342 void testSelectOptionalIntegerMissing() {
343 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
344 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
345 SelectStatementProvider selectStatement = select(id)
346 .from(animalData)
347 .where(id, isEqualTo(1000))
348 .build()
349 .render(RenderingStrategies.MYBATIS3);
350
351 Optional<Integer> id = mapper.selectOptionalInteger(selectStatement);
352
353 assertThat(id).isEmpty();
354 }
355 }
356
357 @Test
358 void testSelectManyIntegers() {
359 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
360 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
361 SelectStatementProvider selectStatement = select(id)
362 .from(animalData)
363 .where(id, isIn(1, 2))
364 .orderBy(id)
365 .build()
366 .render(RenderingStrategies.MYBATIS3);
367
368 List<Integer> ids = mapper.selectManyIntegers(selectStatement);
369
370 assertThat(ids).hasSize(2);
371 assertThat(ids.get(0)).isEqualTo(1);
372 assertThat(ids.get(1)).isEqualTo(2);
373 }
374 }
375
376 @Test
377 void testSelectOneLong() {
378 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
379 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
380 SelectStatementProvider selectStatement = select(id)
381 .from(animalData)
382 .where(id, isEqualTo(1))
383 .build()
384 .render(RenderingStrategies.MYBATIS3);
385
386 Long id = mapper.selectOneLong(selectStatement);
387
388 assertThat(id).isEqualTo(1);
389 }
390 }
391
392 @Test
393 void testSelectOptionalLongPresent() {
394 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
395 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
396 SelectStatementProvider selectStatement = select(id)
397 .from(animalData)
398 .where(id, isEqualTo(1))
399 .build()
400 .render(RenderingStrategies.MYBATIS3);
401
402 Optional<Long> id = mapper.selectOptionalLong(selectStatement);
403
404 assertThat(id).hasValueSatisfying(i -> assertThat(i).isEqualTo(1));
405 }
406 }
407
408 @Test
409 void testSelectOptionalLongMissing() {
410 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
411 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
412 SelectStatementProvider selectStatement = select(id)
413 .from(animalData)
414 .where(id, isEqualTo(1000))
415 .build()
416 .render(RenderingStrategies.MYBATIS3);
417
418 Optional<Long> id = mapper.selectOptionalLong(selectStatement);
419
420 assertThat(id).isEmpty();
421 }
422 }
423
424 @Test
425 void testSelectManyLongs() {
426 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
427 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
428 SelectStatementProvider selectStatement = select(id)
429 .from(animalData)
430 .where(id, isIn(1, 2))
431 .orderBy(id)
432 .build()
433 .render(RenderingStrategies.MYBATIS3);
434
435 List<Long> ids = mapper.selectManyLongs(selectStatement);
436
437 assertThat(ids).hasSize(2);
438 assertThat(ids.get(0)).isEqualTo(1);
439 assertThat(ids.get(1)).isEqualTo(2);
440 }
441 }
442
443 @Test
444 void testSelectOneString() {
445 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
446 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
447 SelectStatementProvider selectStatement = select(animalName)
448 .from(animalData)
449 .where(id, isEqualTo(1))
450 .build()
451 .render(RenderingStrategies.MYBATIS3);
452
453 String name = mapper.selectOneString(selectStatement);
454
455 assertThat(name).isEqualTo("Lesser short-tailed shrew");
456 }
457 }
458
459 @Test
460 void testSelectOptionalStringPresent() {
461 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
462 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
463 SelectStatementProvider selectStatement = select(animalName)
464 .from(animalData)
465 .where(id, isEqualTo(1))
466 .build()
467 .render(RenderingStrategies.MYBATIS3);
468
469 Optional<String> name = mapper.selectOptionalString(selectStatement);
470
471 assertThat(name).hasValueSatisfying(n -> assertThat(n).isEqualTo("Lesser short-tailed shrew"));
472 }
473 }
474
475 @Test
476 void testSelectOptionalStringMissing() {
477 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
478 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
479 SelectStatementProvider selectStatement = select(animalName)
480 .from(animalData)
481 .where(id, isEqualTo(1000))
482 .build()
483 .render(RenderingStrategies.MYBATIS3);
484
485 Optional<String> name = mapper.selectOptionalString(selectStatement);
486
487 assertThat(name).isEmpty();
488 }
489 }
490
491 @Test
492 void testSelectManyStrings() {
493 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
494 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
495 SelectStatementProvider selectStatement = select(animalName)
496 .from(animalData)
497 .where(id, isIn(1, 2))
498 .orderBy(id)
499 .build()
500 .render(RenderingStrategies.MYBATIS3);
501
502 List<String> names = mapper.selectManyStrings(selectStatement);
503
504 assertThat(names).hasSize(2);
505 assertThat(names.get(0)).isEqualTo("Lesser short-tailed shrew");
506 assertThat(names.get(1)).isEqualTo("Little brown bat");
507 }
508 }
509 }