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.animalData;
19 import static examples.animal.data.AnimalDataDynamicSqlSupport.animalName;
20 import static examples.animal.data.AnimalDataDynamicSqlSupport.brainWeight;
21 import static examples.animal.data.AnimalDataDynamicSqlSupport.id;
22 import static org.assertj.core.api.Assertions.assertThat;
23 import static org.assertj.core.api.Assertions.assertThatExceptionOfType;
24 import static org.assertj.core.api.Assertions.entry;
25 import static org.mybatis.dynamic.sql.SqlBuilder.add;
26 import static org.mybatis.dynamic.sql.SqlBuilder.and;
27 import static org.mybatis.dynamic.sql.SqlBuilder.cast;
28 import static org.mybatis.dynamic.sql.SqlBuilder.constant;
29 import static org.mybatis.dynamic.sql.SqlBuilder.group;
30 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
31 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualToWhenPresent;
32 import static org.mybatis.dynamic.sql.SqlBuilder.isIn;
33 import static org.mybatis.dynamic.sql.SqlBuilder.isLessThan;
34 import static org.mybatis.dynamic.sql.SqlBuilder.or;
35 import static org.mybatis.dynamic.sql.SqlBuilder.case_;
36 import static org.mybatis.dynamic.sql.SqlBuilder.select;
37 import static org.mybatis.dynamic.sql.SqlBuilder.value;
38
39 import java.io.InputStream;
40 import java.io.InputStreamReader;
41 import java.math.BigDecimal;
42 import java.sql.Connection;
43 import java.sql.DriverManager;
44 import java.util.List;
45 import java.util.Map;
46
47 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
48 import org.apache.ibatis.jdbc.ScriptRunner;
49 import org.apache.ibatis.mapping.Environment;
50 import org.apache.ibatis.session.Configuration;
51 import org.apache.ibatis.session.SqlSession;
52 import org.apache.ibatis.session.SqlSessionFactory;
53 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
54 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
55 import org.junit.jupiter.api.BeforeEach;
56 import org.junit.jupiter.api.Test;
57 import org.mybatis.dynamic.sql.exception.InvalidSqlException;
58 import org.mybatis.dynamic.sql.render.RenderingStrategies;
59 import org.mybatis.dynamic.sql.select.caseexpression.SearchedCaseDSL;
60 import org.mybatis.dynamic.sql.select.SelectModel;
61 import org.mybatis.dynamic.sql.select.caseexpression.SimpleCaseDSL;
62 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
63 import org.mybatis.dynamic.sql.util.Messages;
64 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
65
66 class CaseExpressionTest {
67 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
68 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
69
70 private SqlSessionFactory sqlSessionFactory;
71
72 @BeforeEach
73 void setup() throws Exception {
74 Class.forName(JDBC_DRIVER);
75 try (InputStream is = getClass().getResourceAsStream("/examples/animal/data/CreateAnimalData.sql")) {
76 assert is != null;
77 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "");
78 InputStreamReader isr = new InputStreamReader(is)) {
79 ScriptRunner sr = new ScriptRunner(connection);
80 sr.setLogWriter(null);
81 sr.runScript(isr);
82 }
83 }
84
85 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
86 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
87 Configuration config = new Configuration(environment);
88 config.addMapper(CommonSelectMapper.class);
89 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
90 }
91
92 @Test
93 void testSearchedCaseWithStrings() {
94 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
95 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
96
97 SelectStatementProvider selectStatement = select(animalName, case_()
98 .when(animalName, isEqualTo("Artic fox")).or(animalName, isEqualTo("Red fox")).then("Fox")
99 .when(animalName, isEqualTo("Little brown bat")).or(animalName, isEqualTo("Big brown bat")).then("Bat")
100 .else_("Not a Fox or a bat").end().as("AnimalType"))
101 .from(animalData, "a")
102 .where(id, isIn(2, 3, 31, 32, 38, 39))
103 .orderBy(id)
104 .build()
105 .render(RenderingStrategies.MYBATIS3);
106
107 String expected = "select a.animal_name, case " +
108 "when a.animal_name = #{parameters.p1,jdbcType=VARCHAR} or a.animal_name = #{parameters.p2,jdbcType=VARCHAR} then 'Fox' " +
109 "when a.animal_name = #{parameters.p3,jdbcType=VARCHAR} or a.animal_name = #{parameters.p4,jdbcType=VARCHAR} then 'Bat' " +
110 "else 'Not a Fox or a bat' end as AnimalType " +
111 "from AnimalData a where a.id in (" +
112 "#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}," +
113 "#{parameters.p7,jdbcType=INTEGER},#{parameters.p8,jdbcType=INTEGER},#{parameters.p9,jdbcType=INTEGER}," +
114 "#{parameters.p10,jdbcType=INTEGER}) " +
115 "order by id";
116 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
117 assertThat(selectStatement.getParameters()).containsOnly(
118 entry("p1", "Artic fox"),
119 entry("p2", "Red fox"),
120 entry("p3", "Little brown bat"),
121 entry("p4", "Big brown bat"),
122 entry("p5", 2),
123 entry("p6", 3),
124 entry("p7", 31),
125 entry("p8", 32),
126 entry("p9", 38),
127 entry("p10", 39)
128 );
129
130 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
131 assertThat(records).hasSize(6);
132 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Little brown bat"), entry("ANIMALTYPE", "Bat "));
133 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Big brown bat"), entry("ANIMALTYPE", "Bat "));
134 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ANIMALTYPE", "Not a Fox or a bat"));
135 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ANIMALTYPE", "Fox "));
136 assertThat(records.get(4)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ANIMALTYPE", "Fox "));
137 assertThat(records.get(5)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ANIMALTYPE", "Not a Fox or a bat"));
138 }
139 }
140
141 @Test
142 void testSearchedCaseWithIntegers() {
143 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
144 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
145
146 SelectStatementProvider selectStatement = select(animalName, case_()
147 .when(animalName, isEqualTo("Artic fox")).or(animalName, isEqualTo("Red fox")).then(1)
148 .when(animalName, isEqualTo("Little brown bat")).or(animalName, isEqualTo("Big brown bat")).then(2)
149 .else_(3).end().as("AnimalType"))
150 .from(animalData, "a")
151 .where(id, isIn(2, 3, 31, 32, 38, 39))
152 .orderBy(id)
153 .build()
154 .render(RenderingStrategies.MYBATIS3);
155
156 String expected = "select a.animal_name, case " +
157 "when a.animal_name = #{parameters.p1,jdbcType=VARCHAR} or a.animal_name = #{parameters.p2,jdbcType=VARCHAR} then 1 " +
158 "when a.animal_name = #{parameters.p3,jdbcType=VARCHAR} or a.animal_name = #{parameters.p4,jdbcType=VARCHAR} then 2 " +
159 "else 3 end as AnimalType " +
160 "from AnimalData a where a.id in (" +
161 "#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}," +
162 "#{parameters.p7,jdbcType=INTEGER},#{parameters.p8,jdbcType=INTEGER},#{parameters.p9,jdbcType=INTEGER}," +
163 "#{parameters.p10,jdbcType=INTEGER}) " +
164 "order by id";
165 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
166 assertThat(selectStatement.getParameters()).containsOnly(
167 entry("p1", "Artic fox"),
168 entry("p2", "Red fox"),
169 entry("p3", "Little brown bat"),
170 entry("p4", "Big brown bat"),
171 entry("p5", 2),
172 entry("p6", 3),
173 entry("p7", 31),
174 entry("p8", 32),
175 entry("p9", 38),
176 entry("p10", 39)
177 );
178
179 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
180 assertThat(records).hasSize(6);
181 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Little brown bat"), entry("ANIMALTYPE", 2));
182 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Big brown bat"), entry("ANIMALTYPE", 2));
183 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ANIMALTYPE", 3));
184 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ANIMALTYPE", 1));
185 assertThat(records.get(4)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ANIMALTYPE", 1));
186 assertThat(records.get(5)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ANIMALTYPE", 3));
187 }
188 }
189
190 @Test
191 void testSearchedCaseWithBoundValues() {
192 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
193 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
194
195 SelectStatementProvider selectStatement = select(animalName, case_()
196 .when(animalName, isEqualTo("Artic fox")).or(animalName, isEqualTo("Red fox")).then(value("Fox"))
197 .when(animalName, isEqualTo("Little brown bat")).or(animalName, isEqualTo("Big brown bat")).then(value("Bat"))
198 .else_(cast(value("Not a Fox or a bat")).as("VARCHAR(30)")).end().as("AnimalType"))
199 .from(animalData, "a")
200 .where(id, isIn(2, 3, 31, 32, 38, 39))
201 .orderBy(id)
202 .build()
203 .render(RenderingStrategies.MYBATIS3);
204
205 String expected = "select a.animal_name, case " +
206 "when a.animal_name = #{parameters.p1,jdbcType=VARCHAR} or a.animal_name = #{parameters.p2,jdbcType=VARCHAR} then #{parameters.p3} " +
207 "when a.animal_name = #{parameters.p4,jdbcType=VARCHAR} or a.animal_name = #{parameters.p5,jdbcType=VARCHAR} then #{parameters.p6} " +
208 "else cast(#{parameters.p7} as VARCHAR(30)) end as AnimalType " +
209 "from AnimalData a where a.id in (" +
210 "#{parameters.p8,jdbcType=INTEGER},#{parameters.p9,jdbcType=INTEGER}," +
211 "#{parameters.p10,jdbcType=INTEGER},#{parameters.p11,jdbcType=INTEGER},#{parameters.p12,jdbcType=INTEGER}," +
212 "#{parameters.p13,jdbcType=INTEGER}) " +
213 "order by id";
214 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
215 assertThat(selectStatement.getParameters()).containsOnly(
216 entry("p1", "Artic fox"),
217 entry("p2", "Red fox"),
218 entry("p3", "Fox"),
219 entry("p4", "Little brown bat"),
220 entry("p5", "Big brown bat"),
221 entry("p6", "Bat"),
222 entry("p7", "Not a Fox or a bat"),
223 entry("p8", 2),
224 entry("p9", 3),
225 entry("p10", 31),
226 entry("p11", 32),
227 entry("p12", 38),
228 entry("p13", 39)
229 );
230
231 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
232 assertThat(records).hasSize(6);
233 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Little brown bat"), entry("ANIMALTYPE", "Bat"));
234 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Big brown bat"), entry("ANIMALTYPE", "Bat"));
235 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ANIMALTYPE", "Not a Fox or a bat"));
236 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ANIMALTYPE", "Fox"));
237 assertThat(records.get(4)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ANIMALTYPE", "Fox"));
238 assertThat(records.get(5)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ANIMALTYPE", "Not a Fox or a bat"));
239 }
240 }
241
242 @Test
243 void testSearchedCaseNoElse() {
244 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
245 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
246
247 SelectStatementProvider selectStatement = select(animalName, case_()
248 .when(animalName, isEqualTo("Artic fox")).or(animalName, isEqualTo("Red fox")).then("Fox")
249 .when(animalName, isEqualTo("Little brown bat")).or(animalName, isEqualTo("Big brown bat")).then("Bat")
250 .end().as("AnimalType"))
251 .from(animalData, "a")
252 .where(id, isIn(2, 3, 31, 32, 38, 39))
253 .orderBy(id)
254 .build()
255 .render(RenderingStrategies.MYBATIS3);
256
257 String expected = "select a.animal_name, case " +
258 "when a.animal_name = #{parameters.p1,jdbcType=VARCHAR} or a.animal_name = #{parameters.p2,jdbcType=VARCHAR} then 'Fox' " +
259 "when a.animal_name = #{parameters.p3,jdbcType=VARCHAR} or a.animal_name = #{parameters.p4,jdbcType=VARCHAR} then 'Bat' " +
260 "end as AnimalType " +
261 "from AnimalData a where a.id in (" +
262 "#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}," +
263 "#{parameters.p7,jdbcType=INTEGER},#{parameters.p8,jdbcType=INTEGER},#{parameters.p9,jdbcType=INTEGER}," +
264 "#{parameters.p10,jdbcType=INTEGER}) " +
265 "order by id";
266 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
267 assertThat(selectStatement.getParameters()).containsOnly(
268 entry("p1", "Artic fox"),
269 entry("p2", "Red fox"),
270 entry("p3", "Little brown bat"),
271 entry("p4", "Big brown bat"),
272 entry("p5", 2),
273 entry("p6", 3),
274 entry("p7", 31),
275 entry("p8", 32),
276 entry("p9", 38),
277 entry("p10", 39)
278 );
279
280 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
281 assertThat(records).hasSize(6);
282 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Little brown bat"), entry("ANIMALTYPE", "Bat"));
283 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Big brown bat"), entry("ANIMALTYPE", "Bat"));
284 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Cat"));
285 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ANIMALTYPE", "Fox"));
286 assertThat(records.get(4)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ANIMALTYPE", "Fox"));
287 assertThat(records.get(5)).containsOnly(entry("ANIMAL_NAME", "Raccoon"));
288 }
289 }
290
291 @Test
292 void testSearchedCaseWithGroup() {
293 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
294 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
295
296 SelectStatementProvider selectStatement = select(animalName, case_()
297 .when(animalName, isEqualTo("Artic fox")).or(animalName, isEqualTo("Red fox")).then("Fox")
298 .when(animalName, isEqualTo("Little brown bat")).or(animalName, isEqualTo("Big brown bat")).then("Bat")
299 .when(group(animalName, isEqualTo("Cat"), and(id, isEqualTo(31))), or(id, isEqualTo(39))).then("Fred")
300 .else_("Not a Fox or a bat").end().as("AnimalType"))
301 .from(animalData, "a")
302 .where(id, isIn(2, 3, 4, 31, 32, 38, 39))
303 .orderBy(id)
304 .build()
305 .render(RenderingStrategies.MYBATIS3);
306
307 String expected = "select a.animal_name, case " +
308 "when a.animal_name = #{parameters.p1,jdbcType=VARCHAR} or a.animal_name = #{parameters.p2,jdbcType=VARCHAR} then 'Fox' " +
309 "when a.animal_name = #{parameters.p3,jdbcType=VARCHAR} or a.animal_name = #{parameters.p4,jdbcType=VARCHAR} then 'Bat' " +
310 "when (a.animal_name = #{parameters.p5,jdbcType=VARCHAR} and a.id = #{parameters.p6,jdbcType=INTEGER}) or a.id = #{parameters.p7,jdbcType=INTEGER} then 'Fred' " +
311 "else 'Not a Fox or a bat' end as AnimalType " +
312 "from AnimalData a where a.id in (" +
313 "#{parameters.p8,jdbcType=INTEGER},#{parameters.p9,jdbcType=INTEGER}," +
314 "#{parameters.p10,jdbcType=INTEGER},#{parameters.p11,jdbcType=INTEGER},#{parameters.p12,jdbcType=INTEGER}," +
315 "#{parameters.p13,jdbcType=INTEGER},#{parameters.p14,jdbcType=INTEGER}) " +
316 "order by id";
317 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
318 assertThat(selectStatement.getParameters()).containsOnly(
319 entry("p1", "Artic fox"),
320 entry("p2", "Red fox"),
321 entry("p3", "Little brown bat"),
322 entry("p4", "Big brown bat"),
323 entry("p5", "Cat"),
324 entry("p6", 31),
325 entry("p7", 39),
326 entry("p8", 2),
327 entry("p9", 3),
328 entry("p10", 4),
329 entry("p11", 31),
330 entry("p12", 32),
331 entry("p13", 38),
332 entry("p14", 39)
333 );
334
335 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
336 assertThat(records).hasSize(7);
337 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Little brown bat"), entry("ANIMALTYPE", "Bat "));
338 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Big brown bat"), entry("ANIMALTYPE", "Bat "));
339 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Mouse"), entry("ANIMALTYPE", "Not a Fox or a bat"));
340 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ANIMALTYPE", "Fred "));
341 assertThat(records.get(4)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ANIMALTYPE", "Fox "));
342 assertThat(records.get(5)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ANIMALTYPE", "Fox "));
343 assertThat(records.get(6)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ANIMALTYPE", "Fred "));
344 }
345 }
346
347 @Test
348 void testSimpleCassLessThan() {
349 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
350 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
351
352 SelectStatementProvider selectStatement = select(animalName, case_(brainWeight)
353 .when(isLessThan(4.0)).then("small brain")
354 .else_("large brain").end().as("brain_size"))
355 .from(animalData)
356 .where(id, isIn(31, 32, 38, 39))
357 .orderBy(id)
358 .build()
359 .render(RenderingStrategies.MYBATIS3);
360
361 String expected = "select animal_name, case brain_weight " +
362 "when < #{parameters.p1,jdbcType=DOUBLE} then 'small brain' " +
363 "else 'large brain' end as brain_size " +
364 "from AnimalData where id in (" +
365 "#{parameters.p2,jdbcType=INTEGER},#{parameters.p3,jdbcType=INTEGER}," +
366 "#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER}) " +
367 "order by id";
368 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
369 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
370 assertThat(records).hasSize(4);
371 }
372 }
373
374 @Test
375 void testSimpleCaseWithStrings() {
376 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
377 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
378
379 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
380 .when(isEqualTo("Artic fox"), isEqualTo("Red fox")).then("yes")
381 .else_("no").end().as("IsAFox"))
382 .from(animalData)
383 .where(id, isIn(31, 32, 38, 39))
384 .orderBy(id)
385 .build()
386 .render(RenderingStrategies.MYBATIS3);
387
388 String expected = "select animal_name, " +
389 "case animal_name when = #{parameters.p1,jdbcType=VARCHAR}, = #{parameters.p2,jdbcType=VARCHAR} then 'yes' else 'no' end " +
390 "as IsAFox from AnimalData where id in " +
391 "(#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}) " +
392 "order by id";
393 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
394 assertThat(selectStatement.getParameters()).containsOnly(
395 entry("p1", "Artic fox"),
396 entry("p2", "Red fox"),
397 entry("p3", 31),
398 entry("p4", 32),
399 entry("p5", 38),
400 entry("p6", 39)
401 );
402
403 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
404 assertThat(records).hasSize(4);
405 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ISAFOX", "no "));
406 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", "yes"));
407 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", "yes"));
408 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ISAFOX", "no "));
409 }
410 }
411
412 @Test
413 void testSimpleCaseBasicWithStrings() {
414 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
415 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
416
417 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
418 .when("Artic fox", "Red fox").then("yes")
419 .else_("no").end().as("IsAFox"))
420 .from(animalData)
421 .where(id, isIn(31, 32, 38, 39))
422 .orderBy(id)
423 .build()
424 .render(RenderingStrategies.MYBATIS3);
425
426 String expected = "select animal_name, " +
427 "case animal_name when #{parameters.p1,jdbcType=VARCHAR}, #{parameters.p2,jdbcType=VARCHAR} then 'yes' else 'no' end " +
428 "as IsAFox from AnimalData where id in " +
429 "(#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}) " +
430 "order by id";
431 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
432 assertThat(selectStatement.getParameters()).containsOnly(
433 entry("p1", "Artic fox"),
434 entry("p2", "Red fox"),
435 entry("p3", 31),
436 entry("p4", 32),
437 entry("p5", 38),
438 entry("p6", 39)
439 );
440
441 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
442 assertThat(records).hasSize(4);
443 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ISAFOX", "no "));
444 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", "yes"));
445 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", "yes"));
446 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ISAFOX", "no "));
447 }
448 }
449
450 @Test
451 void testSimpleCaseWithBooleans() {
452 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
453 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
454
455 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
456 .when(isEqualTo("Artic fox"), isEqualTo("Red fox")).then(true)
457 .else_(false).end().as("IsAFox"))
458 .from(animalData)
459 .where(id, isIn(31, 32, 38, 39))
460 .orderBy(id)
461 .build()
462 .render(RenderingStrategies.MYBATIS3);
463
464 String expected = "select animal_name, " +
465 "case animal_name when = #{parameters.p1,jdbcType=VARCHAR}, = #{parameters.p2,jdbcType=VARCHAR} then true else false end " +
466 "as IsAFox from AnimalData where id in " +
467 "(#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}) " +
468 "order by id";
469 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
470 assertThat(selectStatement.getParameters()).containsOnly(
471 entry("p1", "Artic fox"),
472 entry("p2", "Red fox"),
473 entry("p3", 31),
474 entry("p4", 32),
475 entry("p5", 38),
476 entry("p6", 39)
477 );
478
479 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
480 assertThat(records).hasSize(4);
481 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ISAFOX", false));
482 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", true));
483 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", true));
484 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ISAFOX", false));
485 }
486 }
487
488 @Test
489 void testSimpleCaseWithBoundValues() {
490 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
491 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
492
493 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
494 .when(isEqualTo("Artic fox"), isEqualTo("Red fox")).then(value("yes"))
495 .else_(cast(value("no")).as("VARCHAR(30)")).end().as("IsAFox"))
496 .from(animalData)
497 .where(id, isIn(31, 32, 38, 39))
498 .orderBy(id)
499 .build()
500 .render(RenderingStrategies.MYBATIS3);
501
502 String expected = "select animal_name, " +
503 "case animal_name when = #{parameters.p1,jdbcType=VARCHAR}, = #{parameters.p2,jdbcType=VARCHAR} then #{parameters.p3} " +
504 "else cast(#{parameters.p4} as VARCHAR(30)) end " +
505 "as IsAFox from AnimalData where id in " +
506 "(#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER},#{parameters.p7,jdbcType=INTEGER},#{parameters.p8,jdbcType=INTEGER}) " +
507 "order by id";
508 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
509 assertThat(selectStatement.getParameters()).containsOnly(
510 entry("p1", "Artic fox"),
511 entry("p2", "Red fox"),
512 entry("p3", "yes"),
513 entry("p4", "no"),
514 entry("p5", 31),
515 entry("p6", 32),
516 entry("p7", 38),
517 entry("p8", 39)
518 );
519
520 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
521 assertThat(records).hasSize(4);
522 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ISAFOX", "no"));
523 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", "yes"));
524 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", "yes"));
525 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ISAFOX", "no"));
526 }
527 }
528
529 @Test
530 void testSimpleCaseBasicWithBooleans() {
531 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
532 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
533
534 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
535 .when("Artic fox", "Red fox").then(true)
536 .else_(false).end().as("IsAFox"))
537 .from(animalData)
538 .where(id, isIn(31, 32, 38, 39))
539 .orderBy(id)
540 .build()
541 .render(RenderingStrategies.MYBATIS3);
542
543 String expected = "select animal_name, " +
544 "case animal_name when #{parameters.p1,jdbcType=VARCHAR}, #{parameters.p2,jdbcType=VARCHAR} then true else false end " +
545 "as IsAFox from AnimalData where id in " +
546 "(#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}) " +
547 "order by id";
548 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
549 assertThat(selectStatement.getParameters()).containsOnly(
550 entry("p1", "Artic fox"),
551 entry("p2", "Red fox"),
552 entry("p3", 31),
553 entry("p4", 32),
554 entry("p5", 38),
555 entry("p6", 39)
556 );
557
558 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
559 assertThat(records).hasSize(4);
560 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ISAFOX", false));
561 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", true));
562 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", true));
563 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ISAFOX", false));
564 }
565 }
566
567 @Test
568 void testSimpleCaseNoElse() {
569 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
570 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
571
572 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
573 .when(isEqualTo("Artic fox"), isEqualTo("Red fox")).then("yes")
574 .end().as("IsAFox"))
575 .from(animalData)
576 .where(id, isIn(31, 32, 38, 39))
577 .orderBy(id)
578 .build()
579 .render(RenderingStrategies.MYBATIS3);
580
581 String expected = "select animal_name, " +
582 "case animal_name when = #{parameters.p1,jdbcType=VARCHAR}, = #{parameters.p2,jdbcType=VARCHAR} then 'yes' end " +
583 "as IsAFox from AnimalData where id in " +
584 "(#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}) " +
585 "order by id";
586 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
587 assertThat(selectStatement.getParameters()).containsOnly(
588 entry("p1", "Artic fox"),
589 entry("p2", "Red fox"),
590 entry("p3", 31),
591 entry("p4", 32),
592 entry("p5", 38),
593 entry("p6", 39)
594 );
595
596 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
597 assertThat(records).hasSize(4);
598 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"));
599 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", "yes"));
600 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", "yes"));
601 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"));
602 }
603 }
604
605 @Test
606 void testSimpleCaseLongs() {
607 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
608 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
609
610 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
611 .when(isEqualTo("Artic fox"), isEqualTo("Red fox")).then(1L)
612 .else_(2L)
613 .end().as("IsAFox"))
614 .from(animalData)
615 .where(id, isIn(31, 32, 38, 39))
616 .orderBy(id)
617 .build()
618 .render(RenderingStrategies.MYBATIS3);
619
620 String expected = "select animal_name, " +
621 "case animal_name when = #{parameters.p1,jdbcType=VARCHAR}, = #{parameters.p2,jdbcType=VARCHAR} then 1 else 2 end " +
622 "as IsAFox from AnimalData where id in " +
623 "(#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}) " +
624 "order by id";
625 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
626 assertThat(selectStatement.getParameters()).containsOnly(
627 entry("p1", "Artic fox"),
628 entry("p2", "Red fox"),
629 entry("p3", 31),
630 entry("p4", 32),
631 entry("p5", 38),
632 entry("p6", 39)
633 );
634
635 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
636 assertThat(records).hasSize(4);
637 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ISAFOX", 2));
638 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", 1));
639 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", 1));
640 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ISAFOX", 2));
641 }
642 }
643
644 @Test
645 void testSimpleCaseDoubles() {
646 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
647 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
648
649 SelectStatementProvider selectStatement = select(animalName, case_(animalName)
650 .when(isEqualTo("Artic fox"), isEqualTo("Red fox")).then(1.1)
651 .else_(2.2)
652 .end().as("IsAFox"))
653 .from(animalData)
654 .where(id, isIn(31, 32, 38, 39))
655 .orderBy(id)
656 .build()
657 .render(RenderingStrategies.MYBATIS3);
658
659 String expected = "select animal_name, " +
660 "case animal_name when = #{parameters.p1,jdbcType=VARCHAR}, = #{parameters.p2,jdbcType=VARCHAR} then 1.1 else 2.2 end " +
661 "as IsAFox from AnimalData where id in " +
662 "(#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER},#{parameters.p5,jdbcType=INTEGER},#{parameters.p6,jdbcType=INTEGER}) " +
663 "order by id";
664 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
665 assertThat(selectStatement.getParameters()).containsOnly(
666 entry("p1", "Artic fox"),
667 entry("p2", "Red fox"),
668 entry("p3", 31),
669 entry("p4", 32),
670 entry("p5", 38),
671 entry("p6", 39)
672 );
673
674 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
675 assertThat(records).hasSize(4);
676 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("ISAFOX", new BigDecimal("2.2")));
677 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("ISAFOX", new BigDecimal("1.1")));
678 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("ISAFOX", new BigDecimal("1.1")));
679 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("ISAFOX", new BigDecimal("2.2")));
680 }
681 }
682
683 @Test
684 void testAliasCast() {
685 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
686 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
687
688 SelectStatementProvider selectStatement = select(animalName, cast(add(id, constant("20"))).as("INTEGER").as("BIG_ID"))
689 .from(animalData)
690 .where(id, isIn(31, 32, 38, 39))
691 .orderBy(id)
692 .build()
693 .render(RenderingStrategies.MYBATIS3);
694
695 String expected = "select animal_name, cast((id + 20) as INTEGER) as BIG_ID " +
696 "from AnimalData where id in " +
697 "(#{parameters.p1,jdbcType=INTEGER},#{parameters.p2,jdbcType=INTEGER},#{parameters.p3,jdbcType=INTEGER},#{parameters.p4,jdbcType=INTEGER}) " +
698 "order by id";
699 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
700 assertThat(selectStatement.getParameters()).containsOnly(
701 entry("p1", 31),
702 entry("p2", 32),
703 entry("p3", 38),
704 entry("p4", 39)
705 );
706
707 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
708 assertThat(records).hasSize(4);
709 assertThat(records.get(0)).containsOnly(entry("ANIMAL_NAME", "Cat"), entry("BIG_ID", 51));
710 assertThat(records.get(1)).containsOnly(entry("ANIMAL_NAME", "Artic fox"), entry("BIG_ID", 52));
711 assertThat(records.get(2)).containsOnly(entry("ANIMAL_NAME", "Red fox"), entry("BIG_ID", 58));
712 assertThat(records.get(3)).containsOnly(entry("ANIMAL_NAME", "Raccoon"), entry("BIG_ID", 59));
713 }
714 }
715
716 @Test
717 void testInvalidSearchedCaseNoConditionsRender() {
718 SelectModel model = select(animalName, case_()
719 .when(animalName, isEqualToWhenPresent((String) null)).then("Fred").end())
720 .from(animalData)
721 .build();
722
723 assertThatExceptionOfType(InvalidSqlException.class)
724 .isThrownBy(() -> model.render(RenderingStrategies.MYBATIS3))
725 .withMessage(Messages.getString("ERROR.39"));
726 }
727
728 @Test
729 void testInvalidSimpleCaseNoConditionsRender() {
730 SelectModel model = select(case_(animalName)
731 .when(isEqualToWhenPresent((String) null)).then("Fred").end())
732 .from(animalData)
733 .build();
734
735 assertThatExceptionOfType(InvalidSqlException.class)
736 .isThrownBy(() -> model.render(RenderingStrategies.MYBATIS3))
737 .withMessage(Messages.getString("ERROR.39"));
738 }
739
740 @Test
741 void testInvalidSearchedCaseNoWhenConditions() {
742 SearchedCaseDSL dsl = case_();
743
744 assertThatExceptionOfType(InvalidSqlException.class).isThrownBy(dsl::end)
745 .withMessage(Messages.getString("ERROR.40"));
746 }
747
748 @Test
749 void testInvalidSimpleCaseNoWhenConditions() {
750 SimpleCaseDSL<Integer> dsl = case_(id);
751 assertThatExceptionOfType(InvalidSqlException.class).isThrownBy(dsl::end)
752 .withMessage(Messages.getString("ERROR.40"));
753 }
754 }