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