1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.mariadb;
17
18 import static examples.mariadb.NumbersDynamicSQLSupport.description;
19 import static examples.mariadb.NumbersDynamicSQLSupport.id;
20 import static examples.mariadb.NumbersDynamicSQLSupport.numbers;
21 import static org.assertj.core.api.Assertions.assertThat;
22 import static org.mybatis.dynamic.sql.SqlBuilder.case_;
23 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
24 import static org.mybatis.dynamic.sql.SqlBuilder.select;
25
26 import java.util.List;
27 import java.util.Map;
28
29 import config.TestContainersConfiguration;
30 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
31 import org.apache.ibatis.mapping.Environment;
32 import org.apache.ibatis.session.Configuration;
33 import org.apache.ibatis.session.SqlSession;
34 import org.apache.ibatis.session.SqlSessionFactory;
35 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
36 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
37 import org.junit.jupiter.api.BeforeAll;
38 import org.junit.jupiter.api.Test;
39 import org.mybatis.dynamic.sql.render.RenderingStrategies;
40 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
41 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
42 import org.testcontainers.containers.MariaDBContainer;
43 import org.testcontainers.junit.jupiter.Container;
44 import org.testcontainers.junit.jupiter.Testcontainers;
45
46 @Testcontainers
47 class OrderByCaseTest {
48
49 @SuppressWarnings("resource")
50 @Container
51 private static final MariaDBContainer<?> mariadb =
52 new MariaDBContainer<>(TestContainersConfiguration.MARIADB_LATEST)
53 .withInitScript("examples/mariadb/CreateDB.sql");
54
55 private static SqlSessionFactory sqlSessionFactory;
56
57 @BeforeAll
58 static void setup() {
59 UnpooledDataSource ds = new UnpooledDataSource(mariadb.getDriverClassName(), mariadb.getJdbcUrl(),
60 mariadb.getUsername(), mariadb.getPassword());
61 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
62 Configuration config = new Configuration(environment);
63 config.addMapper(CommonSelectMapper.class);
64 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
65 }
66
67 @Test
68 void testOrderBySimpleCase() {
69 try (SqlSession session = sqlSessionFactory.openSession()) {
70 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
71
72 SelectStatementProvider selectStatement = select(id, description).from(numbers)
73 .orderBy(case_(description)
74 .when("One").then(3)
75 .when("Two").then(5)
76 .when("Three").then(4)
77 .when("Four").then(2)
78 .when("Five").then(1)
79 .else_(99)
80 .end())
81 .build()
82 .render(RenderingStrategies.MYBATIS3);
83
84 String expected = "select id, description from numbers order by case description "
85 + "when #{parameters.p1,jdbcType=VARCHAR} then 3 "
86 + "when #{parameters.p2,jdbcType=VARCHAR} then 5 "
87 + "when #{parameters.p3,jdbcType=VARCHAR} then 4 "
88 + "when #{parameters.p4,jdbcType=VARCHAR} then 2 "
89 + "when #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end";
90
91 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
92
93 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
94 assertThat(rows).hasSize(5);
95 assertThat(rows.get(0)).extracting("id", "description").containsExactly(5, "Five");
96 assertThat(rows.get(1)).extracting("id", "description").containsExactly(4, "Four");
97 assertThat(rows.get(2)).extracting("id", "description").containsExactly(1, "One");
98 assertThat(rows.get(3)).extracting("id", "description").containsExactly(3, "Three");
99 assertThat(rows.get(4)).extracting("id", "description").containsExactly(2, "Two");
100 }
101 }
102
103 @Test
104 void testOrderBySimpleCaseWithTableAlias() {
105
106 try (SqlSession session = sqlSessionFactory.openSession()) {
107 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
108
109 SelectStatementProvider selectStatement = select(id, description).from(numbers, "n")
110 .orderBy(case_(description)
111 .when("One").then(3)
112 .when("Two").then(5)
113 .when("Three").then(4)
114 .when("Four").then(2)
115 .when("Five").then(1)
116 .else_(99)
117 .end())
118 .build()
119 .render(RenderingStrategies.MYBATIS3);
120
121 String expected = "select n.id, n.description from numbers n order by case description "
122 + "when #{parameters.p1,jdbcType=VARCHAR} then 3 "
123 + "when #{parameters.p2,jdbcType=VARCHAR} then 5 "
124 + "when #{parameters.p3,jdbcType=VARCHAR} then 4 "
125 + "when #{parameters.p4,jdbcType=VARCHAR} then 2 "
126 + "when #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end";
127
128 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
129
130 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
131 assertThat(rows).hasSize(5);
132 assertThat(rows.get(0)).extracting("id", "description").containsExactly(5, "Five");
133 assertThat(rows.get(1)).extracting("id", "description").containsExactly(4, "Four");
134 assertThat(rows.get(2)).extracting("id", "description").containsExactly(1, "One");
135 assertThat(rows.get(3)).extracting("id", "description").containsExactly(3, "Three");
136 assertThat(rows.get(4)).extracting("id", "description").containsExactly(2, "Two");
137 }
138 }
139
140 @Test
141 void testOrderBySimpleCaseWithColumnAlias() {
142
143 try (SqlSession session = sqlSessionFactory.openSession()) {
144 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
145
146 SelectStatementProvider selectStatement = select(id, description.as("descr")).from(numbers)
147 .orderBy(case_(description.as("descr"))
148 .when("One").then(3)
149 .when("Two").then(5)
150 .when("Three").then(4)
151 .when("Four").then(2)
152 .when("Five").then(1)
153 .else_(99)
154 .end())
155 .build()
156 .render(RenderingStrategies.MYBATIS3);
157
158 String expected = "select id, description as descr from numbers order by case descr "
159 + "when #{parameters.p1,jdbcType=VARCHAR} then 3 "
160 + "when #{parameters.p2,jdbcType=VARCHAR} then 5 "
161 + "when #{parameters.p3,jdbcType=VARCHAR} then 4 "
162 + "when #{parameters.p4,jdbcType=VARCHAR} then 2 "
163 + "when #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end";
164
165 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
166
167 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
168 assertThat(rows).hasSize(5);
169 assertThat(rows.get(0)).extracting("id", "descr").containsExactly(5, "Five");
170 assertThat(rows.get(1)).extracting("id", "descr").containsExactly(4, "Four");
171 assertThat(rows.get(2)).extracting("id", "descr").containsExactly(1, "One");
172 assertThat(rows.get(3)).extracting("id", "descr").containsExactly(3, "Three");
173 assertThat(rows.get(4)).extracting("id", "descr").containsExactly(2, "Two");
174 }
175 }
176
177 @Test
178 void testOrderBySimpleCaseDescending() {
179 try (SqlSession session = sqlSessionFactory.openSession()) {
180 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
181
182 SelectStatementProvider selectStatement = select(id, description).from(numbers)
183 .orderBy(case_(description)
184 .when("One").then(3)
185 .when("Two").then(5)
186 .when("Three").then(4)
187 .when("Four").then(2)
188 .when("Five").then(1)
189 .else_(99)
190 .end().descending())
191 .build()
192 .render(RenderingStrategies.MYBATIS3);
193
194 String expected = "select id, description from numbers order by case description "
195 + "when #{parameters.p1,jdbcType=VARCHAR} then 3 "
196 + "when #{parameters.p2,jdbcType=VARCHAR} then 5 "
197 + "when #{parameters.p3,jdbcType=VARCHAR} then 4 "
198 + "when #{parameters.p4,jdbcType=VARCHAR} then 2 "
199 + "when #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end DESC";
200
201 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
202
203 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
204 assertThat(rows).hasSize(5);
205 assertThat(rows.get(4)).extracting("id", "description").containsExactly(5, "Five");
206 assertThat(rows.get(3)).extracting("id", "description").containsExactly(4, "Four");
207 assertThat(rows.get(2)).extracting("id", "description").containsExactly(1, "One");
208 assertThat(rows.get(1)).extracting("id", "description").containsExactly(3, "Three");
209 assertThat(rows.get(0)).extracting("id", "description").containsExactly(2, "Two");
210 }
211 }
212
213 @Test
214 void testOrderBySearchedCase() {
215 try (SqlSession session = sqlSessionFactory.openSession()) {
216 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
217
218 SelectStatementProvider selectStatement = select(id, description).from(numbers)
219 .orderBy(case_()
220 .when(description, isEqualTo("One")).then(3)
221 .when(description, isEqualTo("Two")).then(5)
222 .when(description, isEqualTo("Three")).then(4)
223 .when(description, isEqualTo("Four")).then(2)
224 .when(description, isEqualTo("Five")).then(1)
225 .else_(99)
226 .end())
227 .build()
228 .render(RenderingStrategies.MYBATIS3);
229
230 String expected = "select id, description from numbers order by case "
231 + "when description = #{parameters.p1,jdbcType=VARCHAR} then 3 "
232 + "when description = #{parameters.p2,jdbcType=VARCHAR} then 5 "
233 + "when description = #{parameters.p3,jdbcType=VARCHAR} then 4 "
234 + "when description = #{parameters.p4,jdbcType=VARCHAR} then 2 "
235 + "when description = #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end";
236
237 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
238
239 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
240 assertThat(rows).hasSize(5);
241 assertThat(rows.get(0)).extracting("id", "description").containsExactly(5, "Five");
242 assertThat(rows.get(1)).extracting("id", "description").containsExactly(4, "Four");
243 assertThat(rows.get(2)).extracting("id", "description").containsExactly(1, "One");
244 assertThat(rows.get(3)).extracting("id", "description").containsExactly(3, "Three");
245 assertThat(rows.get(4)).extracting("id", "description").containsExactly(2, "Two");
246 }
247 }
248
249 @Test
250 void testOrderBySearchedCaseWithTableAlias() {
251
252 try (SqlSession session = sqlSessionFactory.openSession()) {
253 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
254
255 SelectStatementProvider selectStatement = select(id, description).from(numbers, "n")
256 .orderBy(case_()
257 .when(description, isEqualTo("One")).then(3)
258 .when(description, isEqualTo("Two")).then(5)
259 .when(description, isEqualTo("Three")).then(4)
260 .when(description, isEqualTo("Four")).then(2)
261 .when(description, isEqualTo("Five")).then(1)
262 .else_(99)
263 .end())
264 .build()
265 .render(RenderingStrategies.MYBATIS3);
266
267 String expected = "select n.id, n.description from numbers n order by case "
268 + "when description = #{parameters.p1,jdbcType=VARCHAR} then 3 "
269 + "when description = #{parameters.p2,jdbcType=VARCHAR} then 5 "
270 + "when description = #{parameters.p3,jdbcType=VARCHAR} then 4 "
271 + "when description = #{parameters.p4,jdbcType=VARCHAR} then 2 "
272 + "when description = #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end";
273
274 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
275
276 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
277 assertThat(rows).hasSize(5);
278 assertThat(rows.get(0)).extracting("id", "description").containsExactly(5, "Five");
279 assertThat(rows.get(1)).extracting("id", "description").containsExactly(4, "Four");
280 assertThat(rows.get(2)).extracting("id", "description").containsExactly(1, "One");
281 assertThat(rows.get(3)).extracting("id", "description").containsExactly(3, "Three");
282 assertThat(rows.get(4)).extracting("id", "description").containsExactly(2, "Two");
283 }
284 }
285
286 @Test
287 void testOrderBySearchedCaseWithColumnAlias() {
288
289 try (SqlSession session = sqlSessionFactory.openSession()) {
290 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
291
292 SelectStatementProvider selectStatement = select(id, description.as("descr")).from(numbers)
293 .orderBy(case_()
294 .when(description.as("descr"), isEqualTo("One")).then(3)
295 .when(description.as("descr"), isEqualTo("Two")).then(5)
296 .when(description.as("descr"), isEqualTo("Three")).then(4)
297 .when(description.as("descr"), isEqualTo("Four")).then(2)
298 .when(description.as("descr"), isEqualTo("Five")).then(1)
299 .else_(99)
300 .end())
301 .build()
302 .render(RenderingStrategies.MYBATIS3);
303
304 String expected = "select id, description as descr from numbers order by case "
305 + "when descr = #{parameters.p1,jdbcType=VARCHAR} then 3 "
306 + "when descr = #{parameters.p2,jdbcType=VARCHAR} then 5 "
307 + "when descr = #{parameters.p3,jdbcType=VARCHAR} then 4 "
308 + "when descr = #{parameters.p4,jdbcType=VARCHAR} then 2 "
309 + "when descr = #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end";
310
311 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
312
313 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
314 assertThat(rows).hasSize(5);
315 assertThat(rows.get(0)).extracting("id", "descr").containsExactly(5, "Five");
316 assertThat(rows.get(1)).extracting("id", "descr").containsExactly(4, "Four");
317 assertThat(rows.get(2)).extracting("id", "descr").containsExactly(1, "One");
318 assertThat(rows.get(3)).extracting("id", "descr").containsExactly(3, "Three");
319 assertThat(rows.get(4)).extracting("id", "descr").containsExactly(2, "Two");
320 }
321 }
322
323 @Test
324 void testOrderBySearchedCaseDescending() {
325 try (SqlSession session = sqlSessionFactory.openSession()) {
326 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
327
328 SelectStatementProvider selectStatement = select(id, description).from(numbers)
329 .orderBy(case_()
330 .when(description, isEqualTo("One")).then(3)
331 .when(description, isEqualTo("Two")).then(5)
332 .when(description, isEqualTo("Three")).then(4)
333 .when(description, isEqualTo("Four")).then(2)
334 .when(description, isEqualTo("Five")).then(1)
335 .else_(99)
336 .end().descending())
337 .build()
338 .render(RenderingStrategies.MYBATIS3);
339
340 String expected = "select id, description from numbers order by case "
341 + "when description = #{parameters.p1,jdbcType=VARCHAR} then 3 "
342 + "when description = #{parameters.p2,jdbcType=VARCHAR} then 5 "
343 + "when description = #{parameters.p3,jdbcType=VARCHAR} then 4 "
344 + "when description = #{parameters.p4,jdbcType=VARCHAR} then 2 "
345 + "when description = #{parameters.p5,jdbcType=VARCHAR} then 1 else 99 end DESC";
346
347 assertThat(selectStatement.getSelectStatement()).isEqualTo( expected);
348
349 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
350 assertThat(rows).hasSize(5);
351 assertThat(rows.get(4)).extracting("id", "description").containsExactly(5, "Five");
352 assertThat(rows.get(3)).extracting("id", "description").containsExactly(4, "Four");
353 assertThat(rows.get(2)).extracting("id", "description").containsExactly(1, "One");
354 assertThat(rows.get(1)).extracting("id", "description").containsExactly(3, "Three");
355 assertThat(rows.get(0)).extracting("id", "description").containsExactly(2, "Two");
356 }
357 }
358 }