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