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