1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.groupby;
17
18 import static examples.groupby.AddressDynamicSqlSupport.*;
19 import static examples.groupby.Person2DynamicSqlSupport.person2;
20 import static examples.groupby.PersonDynamicSqlSupport.*;
21 import static org.assertj.core.api.Assertions.assertThat;
22 import static org.mybatis.dynamic.sql.SqlBuilder.*;
23
24 import java.io.InputStream;
25 import java.io.InputStreamReader;
26 import java.sql.Connection;
27 import java.sql.DriverManager;
28 import java.util.List;
29 import java.util.Map;
30
31 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
32 import org.apache.ibatis.jdbc.ScriptRunner;
33 import org.apache.ibatis.mapping.Environment;
34 import org.apache.ibatis.session.Configuration;
35 import org.apache.ibatis.session.SqlSession;
36 import org.apache.ibatis.session.SqlSessionFactory;
37 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
38 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
39 import org.junit.jupiter.api.BeforeEach;
40 import org.junit.jupiter.api.Test;
41 import org.mybatis.dynamic.sql.render.RenderingStrategies;
42 import org.mybatis.dynamic.sql.select.HavingApplier;
43 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
44 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
45
46 class GroupByTest {
47
48 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
49 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
50
51 private SqlSessionFactory sqlSessionFactory;
52
53 @BeforeEach
54 void setup() throws Exception {
55 Class.forName(JDBC_DRIVER);
56 InputStream is = getClass().getResourceAsStream("/examples/groupby/CreateGroupByDB.sql");
57 assert is != null;
58 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "")) {
59 ScriptRunner sr = new ScriptRunner(connection);
60 sr.setLogWriter(null);
61 sr.runScript(new InputStreamReader(is));
62 }
63
64 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
65 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
66 Configuration config = new Configuration(environment);
67 config.addMapper(CommonSelectMapper.class);
68 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
69 }
70
71 @Test
72 void testBasicGroupBy() {
73 try (SqlSession session = sqlSessionFactory.openSession()) {
74 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
75
76 SelectStatementProvider selectStatement = select(gender, count())
77 .from(person)
78 .groupBy(gender)
79 .build()
80 .render(RenderingStrategies.MYBATIS3);
81
82 String expected = "select gender, count(*) from Person group by gender";
83 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
84
85 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
86 assertThat(rows).hasSize(2);
87 Map<String, Object> row = rows.get(0);
88 assertThat(row).containsEntry("GENDER", "Male");
89 assertThat(row).containsEntry("C2", 4L);
90
91 row = rows.get(1);
92 assertThat(row).containsEntry("GENDER", "Female");
93 assertThat(row).containsEntry("C2", 3L);
94 }
95 }
96
97 @Test
98 void testBasicGroupByWithAggregateAlias() {
99 try (SqlSession session = sqlSessionFactory.openSession()) {
100 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
101
102 SelectStatementProvider selectStatement = select(gender, count().as("count"))
103 .from(person)
104 .groupBy(gender)
105 .build()
106 .render(RenderingStrategies.MYBATIS3);
107
108 String expected = "select gender, count(*) as count from Person group by gender";
109 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
110
111 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
112 assertThat(rows).hasSize(2);
113 Map<String, Object> row = rows.get(0);
114 assertThat(row).containsEntry("GENDER", "Male");
115 assertThat(row).containsEntry("COUNT", 4L);
116
117 row = rows.get(1);
118 assertThat(row).containsEntry("GENDER", "Female");
119 assertThat(row).containsEntry("COUNT", 3L);
120 }
121 }
122
123 @Test
124 void testGroupByAfterJoin() {
125 try (SqlSession session = sqlSessionFactory.openSession()) {
126 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
127
128 SelectStatementProvider selectStatement = select(lastName, streetAddress, count().as("count"))
129 .from(person, "p").join(address, "a").on(person.addressId, isEqualTo(address.id))
130 .groupBy(lastName, streetAddress)
131 .build()
132 .render(RenderingStrategies.MYBATIS3);
133
134 String expected = "select p.last_name, a.street_address, count(*) as count" +
135 " from Person p join Address a on p.address_id = a.address_id" +
136 " group by p.last_name, a.street_address";
137 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
138
139 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
140 assertThat(rows).hasSize(2);
141 Map<String, Object> row = rows.get(0);
142 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
143 assertThat(row).containsEntry("STREET_ADDRESS", "123 Main Street");
144 assertThat(row).containsEntry("COUNT", 4L);
145
146 row = rows.get(1);
147 assertThat(row).containsEntry("LAST_NAME", "Rubble");
148 assertThat(row).containsEntry("STREET_ADDRESS", "456 Main Street");
149 assertThat(row).containsEntry("COUNT", 3L);
150 }
151 }
152
153 @Test
154 void testUnionAfterJoin() {
155 try (SqlSession session = sqlSessionFactory.openSession()) {
156 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
157
158 SelectStatementProvider selectStatement = select(lastName, firstName, streetAddress)
159 .from(person, "p").join(address, "a").on(person.addressId, isEqualTo(address.id))
160 .union()
161 .select(person2.lastName, person2.firstName, streetAddress)
162 .from(person2, "p").join(address, "a").on(person2.addressId, isEqualTo(address.id))
163 .orderBy(lastName, firstName)
164 .build()
165 .render(RenderingStrategies.MYBATIS3);
166
167 String expected = "select p.last_name, p.first_name, a.street_address" +
168 " from Person p join Address a on p.address_id = a.address_id" +
169 " union" +
170 " select p.last_name, p.first_name, a.street_address" +
171 " from Person2 p join Address a on p.address_id = a.address_id" +
172 " order by last_name, first_name";
173 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
174
175 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
176 assertThat(rows).hasSize(10);
177 Map<String, Object> row = rows.get(0);
178 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
179 assertThat(row).containsEntry("FIRST_NAME", "Dino");
180 assertThat(row).containsEntry("STREET_ADDRESS", "123 Main Street");
181
182 row = rows.get(9);
183 assertThat(row).containsEntry("LAST_NAME", "Smith");
184 assertThat(row).containsEntry("FIRST_NAME", "Suzy");
185 assertThat(row).containsEntry("STREET_ADDRESS", "123 Main Street");
186 }
187 }
188
189 @Test
190 void testUnionAllAfterJoin() {
191 try (SqlSession session = sqlSessionFactory.openSession()) {
192 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
193
194 SelectStatementProvider selectStatement = select(lastName, firstName, streetAddress)
195 .from(person, "p").join(address, "a").on(person.addressId, isEqualTo(address.id))
196 .unionAll()
197 .select(person2.lastName, person2.firstName, streetAddress)
198 .from(person2, "p").join(address, "a").on(person2.addressId, isEqualTo(address.id))
199 .orderBy(lastName, firstName)
200 .build()
201 .render(RenderingStrategies.MYBATIS3);
202
203 String expected = "select p.last_name, p.first_name, a.street_address" +
204 " from Person p join Address a on p.address_id = a.address_id" +
205 " union all" +
206 " select p.last_name, p.first_name, a.street_address" +
207 " from Person2 p join Address a on p.address_id = a.address_id" +
208 " order by last_name, first_name";
209 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
210
211 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
212 assertThat(rows).hasSize(10);
213 Map<String, Object> row = rows.get(0);
214 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
215 assertThat(row).containsEntry("FIRST_NAME", "Dino");
216 assertThat(row).containsEntry("STREET_ADDRESS", "123 Main Street");
217
218 row = rows.get(9);
219 assertThat(row).containsEntry("LAST_NAME", "Smith");
220 assertThat(row).containsEntry("FIRST_NAME", "Suzy");
221 assertThat(row).containsEntry("STREET_ADDRESS", "123 Main Street");
222 }
223 }
224
225 @Test
226 void testUnionAfterGroupBy() {
227 try (SqlSession session = sqlSessionFactory.openSession()) {
228 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
229
230 SelectStatementProvider selectStatement = select(stringConstant("Gender"), gender.as("value"), count().as("count"))
231 .from(person)
232 .groupBy(gender)
233 .union()
234 .select(stringConstant("Last Name"), lastName.as("value"), count().as("count"))
235 .from(person)
236 .groupBy(lastName)
237 .build()
238 .render(RenderingStrategies.MYBATIS3);
239
240 String expected = "select 'Gender', gender as value, count(*) as count from Person group by gender" +
241 " union" +
242 " select 'Last Name', last_name as value, count(*) as count from Person group by last_name";
243 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
244
245 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
246 assertThat(rows).hasSize(4);
247 Map<String, Object> row = rows.get(0);
248 assertThat(row).containsEntry("C1", "Gender ");
249 assertThat(row).containsEntry("VALUE", "Female");
250 assertThat(row).containsEntry("COUNT", 3L);
251
252 row = rows.get(1);
253 assertThat(row).containsEntry("C1", "Gender ");
254 assertThat(row).containsEntry("VALUE", "Male");
255 assertThat(row).containsEntry("COUNT", 4L);
256
257 row = rows.get(2);
258 assertThat(row).containsEntry("C1", "Last Name");
259 assertThat(row).containsEntry("VALUE", "Flintstone");
260 assertThat(row).containsEntry("COUNT", 4L);
261
262 row = rows.get(3);
263 assertThat(row).containsEntry("C1", "Last Name");
264 assertThat(row).containsEntry("VALUE", "Rubble");
265 assertThat(row).containsEntry("COUNT", 3L);
266 }
267 }
268
269 @Test
270 void testUnionAllAfterGroupBy() {
271 try (SqlSession session = sqlSessionFactory.openSession()) {
272 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
273
274 SelectStatementProvider selectStatement = select(stringConstant("Gender"), gender.as("value"), count().as("count"))
275 .from(person)
276 .groupBy(gender)
277 .unionAll()
278 .select(stringConstant("Last Name"), lastName.as("value"), count().as("count"))
279 .from(person)
280 .groupBy(lastName)
281 .build()
282 .render(RenderingStrategies.MYBATIS3);
283
284 String expected = "select 'Gender', gender as value, count(*) as count from Person group by gender" +
285 " union all" +
286 " select 'Last Name', last_name as value, count(*) as count from Person group by last_name";
287 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
288
289 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
290 assertThat(rows).hasSize(4);
291 Map<String, Object> row = rows.get(0);
292 assertThat(row).containsEntry("C1", "Gender ");
293 assertThat(row).containsEntry("VALUE", "Male");
294 assertThat(row).containsEntry("COUNT", 4L);
295
296 row = rows.get(1);
297 assertThat(row).containsEntry("C1", "Gender ");
298 assertThat(row).containsEntry("VALUE", "Female");
299 assertThat(row).containsEntry("COUNT", 3L);
300
301 row = rows.get(2);
302 assertThat(row).containsEntry("C1", "Last Name");
303 assertThat(row).containsEntry("VALUE", "Flintstone");
304 assertThat(row).containsEntry("COUNT", 4L);
305
306 row = rows.get(3);
307 assertThat(row).containsEntry("C1", "Last Name");
308 assertThat(row).containsEntry("VALUE", "Rubble");
309 assertThat(row).containsEntry("COUNT", 3L);
310 }
311 }
312
313 @Test
314 void testBasicGroupByOrderByWithAggregateAlias() {
315 try (SqlSession session = sqlSessionFactory.openSession()) {
316 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
317
318 SelectStatementProvider selectStatement = select(gender, count().as("count"))
319 .from(person)
320 .groupBy(gender)
321 .orderBy(gender)
322 .build()
323 .render(RenderingStrategies.MYBATIS3);
324
325 String expected = "select gender, count(*) as count from Person group by gender order by gender";
326 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
327
328 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
329 assertThat(rows).hasSize(2);
330 Map<String, Object> row = rows.get(0);
331 assertThat(row).containsEntry("GENDER", "Female");
332 assertThat(row).containsEntry("COUNT", 3L);
333
334 row = rows.get(1);
335 assertThat(row).containsEntry("GENDER", "Male");
336 assertThat(row).containsEntry("COUNT", 4L);
337 }
338 }
339
340 @Test
341 void testBasicGroupByOrderByWithCalculatedColumnAndTableAlias() {
342 try (SqlSession session = sqlSessionFactory.openSession()) {
343 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
344
345 SelectStatementProvider selectStatement = select(substring(gender, 1, 1).as("ShortGender"), avg(age).as("AverageAge"))
346 .from(person, "a")
347 .groupBy(substring(gender, 1, 1))
348 .orderBy(sortColumn("ShortGender").descending())
349 .build()
350 .render(RenderingStrategies.MYBATIS3);
351
352 String expected = "select substring(a.gender, 1, 1) as ShortGender, avg(a.age) as AverageAge from Person a group by substring(a.gender, 1, 1) order by ShortGender DESC";
353 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
354
355 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
356 assertThat(rows).hasSize(2);
357 Map<String, Object> row = rows.get(0);
358 assertThat(row).containsEntry("SHORTGENDER", "M");
359 assertThat(row).containsEntry("AVERAGEAGE", 25);
360
361 row = rows.get(1);
362 assertThat(row).containsEntry("SHORTGENDER", "F");
363 assertThat(row).containsEntry("AVERAGEAGE", 27);
364 }
365 }
366
367 @Test
368 void testGroupByAfterWhere() {
369 try (SqlSession session = sqlSessionFactory.openSession()) {
370 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
371
372 SelectStatementProvider selectStatement = select(lastName, count().as("count"))
373 .from(person, "a")
374 .where(gender, isEqualTo("Male"))
375 .groupBy(lastName)
376 .build()
377 .render(RenderingStrategies.MYBATIS3);
378
379 String expected = "select a.last_name, count(*) as count from Person a where a.gender = #{parameters.p1,jdbcType=VARCHAR} group by a.last_name";
380 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
381
382 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
383 assertThat(rows).hasSize(2);
384 Map<String, Object> row = rows.get(0);
385 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
386 assertThat(row).containsEntry("COUNT", 2L);
387
388 row = rows.get(1);
389 assertThat(row).containsEntry("LAST_NAME", "Rubble");
390 assertThat(row).containsEntry("COUNT", 2L);
391 }
392 }
393
394 @Test
395 void testLimitAndOffsetAfterGroupBy() {
396 try (SqlSession session = sqlSessionFactory.openSession()) {
397 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
398
399 SelectStatementProvider selectStatement = select(lastName, count().as("count"))
400 .from(person)
401 .groupBy(lastName)
402 .limit(1)
403 .offset(1)
404 .build()
405 .render(RenderingStrategies.MYBATIS3);
406
407 String expected = "select last_name, count(*) as count from Person group by last_name limit #{parameters.p1} offset #{parameters.p2}";
408 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
409
410 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
411 assertThat(rows).hasSize(1);
412 Map<String, Object> row = rows.get(0);
413 assertThat(row).containsEntry("LAST_NAME", "Rubble");
414 assertThat(row).containsEntry("COUNT", 3L);
415 }
416 }
417
418 @Test
419 void testLimitOnlyAfterGroupBy() {
420 try (SqlSession session = sqlSessionFactory.openSession()) {
421 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
422
423 SelectStatementProvider selectStatement = select(lastName, count().as("count"))
424 .from(person)
425 .groupBy(lastName)
426 .limit(1)
427 .build()
428 .render(RenderingStrategies.MYBATIS3);
429
430 String expected = "select last_name, count(*) as count from Person group by last_name limit #{parameters.p1}";
431 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
432
433 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
434 assertThat(rows).hasSize(1);
435 Map<String, Object> row = rows.get(0);
436 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
437 assertThat(row).containsEntry("COUNT", 4L);
438 }
439 }
440
441 @Test
442 void testOffsetOnlyAfterGroupBy() {
443 try (SqlSession session = sqlSessionFactory.openSession()) {
444 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
445
446 SelectStatementProvider selectStatement = select(lastName, count().as("count"))
447 .from(person)
448 .groupBy(lastName)
449 .offset(1)
450 .build()
451 .render(RenderingStrategies.MYBATIS3);
452
453 String expected = "select last_name, count(*) as count from Person group by last_name offset #{parameters.p1} rows";
454 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
455
456 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
457 assertThat(rows).hasSize(1);
458 Map<String, Object> row = rows.get(0);
459 assertThat(row).containsEntry("LAST_NAME", "Rubble");
460 assertThat(row).containsEntry("COUNT", 3L);
461 }
462 }
463
464 @Test
465 void testOffsetAndFetchFirstAfterGroupBy() {
466 try (SqlSession session = sqlSessionFactory.openSession()) {
467 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
468
469 SelectStatementProvider selectStatement = select(lastName, count().as("count"))
470 .from(person)
471 .groupBy(lastName)
472 .offset(1)
473 .fetchFirst(1).rowsOnly()
474 .build()
475 .render(RenderingStrategies.MYBATIS3);
476
477 String expected = "select last_name, count(*) as count from Person group by last_name offset #{parameters.p1} rows fetch first #{parameters.p2} rows only";
478 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
479
480 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
481 assertThat(rows).hasSize(1);
482 Map<String, Object> row = rows.get(0);
483 assertThat(row).containsEntry("LAST_NAME", "Rubble");
484 assertThat(row).containsEntry("COUNT", 3L);
485 }
486 }
487
488 @Test
489 void testFetchFirstOnlyAfterGroupBy() {
490 try (SqlSession session = sqlSessionFactory.openSession()) {
491 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
492
493 SelectStatementProvider selectStatement = select(lastName, count().as("count"))
494 .from(person)
495 .groupBy(lastName)
496 .fetchFirst(1).rowsOnly()
497 .build()
498 .render(RenderingStrategies.MYBATIS3);
499
500 String expected = "select last_name, count(*) as count from Person group by last_name fetch first #{parameters.p1} rows only";
501 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
502
503 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
504 assertThat(rows).hasSize(1);
505 Map<String, Object> row = rows.get(0);
506 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
507 assertThat(row).containsEntry("COUNT", 4L);
508 }
509 }
510
511 @Test
512 void testCountDistinct() {
513 try (SqlSession session = sqlSessionFactory.openSession()) {
514 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
515
516 SelectStatementProvider selectStatement = select(countDistinct(lastName).as("count"))
517 .from(person)
518 .build()
519 .render(RenderingStrategies.MYBATIS3);
520
521 String expected = "select count(distinct last_name) as count from Person";
522 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
523
524 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
525 assertThat(rows).hasSize(1);
526 Map<String, Object> row = rows.get(0);
527 assertThat(row).containsEntry("COUNT", 2L);
528 }
529 }
530
531 @Test
532 void testHaving() {
533 try (SqlSession session = sqlSessionFactory.openSession()) {
534 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
535
536 SelectStatementProvider selectStatement = select(lastName, count())
537 .from(person)
538 .groupBy(lastName)
539 .having(count(), isEqualTo(3L))
540 .and(lastName, isEqualTo("Rubble"))
541 .build()
542 .render(RenderingStrategies.MYBATIS3);
543
544 String expected = "select last_name, count(*) from Person group by last_name " +
545 "having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR}";
546 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
547
548 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
549 assertThat(rows).hasSize(1);
550 Map<String, Object> row = rows.get(0);
551 assertThat(row).containsEntry("LAST_NAME", "Rubble");
552 }
553 }
554
555 @Test
556 void testHavingAndOrderBy() {
557 try (SqlSession session = sqlSessionFactory.openSession()) {
558 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
559
560 SelectStatementProvider selectStatement = select(lastName, count())
561 .from(person)
562 .groupBy(lastName)
563 .having(count(), isEqualTo(3L))
564 .orderBy(lastName)
565 .build()
566 .render(RenderingStrategies.MYBATIS3);
567
568 String expected = "select last_name, count(*) from Person group by last_name having count(*) = #{parameters.p1} order by last_name";
569 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
570
571 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
572 assertThat(rows).hasSize(1);
573 Map<String, Object> row = rows.get(0);
574 assertThat(row).containsEntry("LAST_NAME", "Rubble");
575 }
576 }
577
578 @Test
579 void testHavingWithGroup() {
580 try (SqlSession session = sqlSessionFactory.openSession()) {
581 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
582
583 SelectStatementProvider selectStatement = select(lastName, count())
584 .from(person)
585 .groupBy(lastName)
586 .having(group(count(), isEqualTo(3L), and(lastName, isEqualTo("Rubble"))))
587 .limit(1)
588 .build()
589 .render(RenderingStrategies.MYBATIS3);
590
591 String expected = "select last_name, count(*) from Person group by last_name " +
592 "having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR} " +
593 "limit #{parameters.p3}";
594 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
595
596 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
597 assertThat(rows).hasSize(1);
598 Map<String, Object> row = rows.get(0);
599 assertThat(row).containsEntry("LAST_NAME", "Rubble");
600 }
601 }
602
603 @Test
604 void testHavingWithUnion() {
605 try (SqlSession session = sqlSessionFactory.openSession()) {
606 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
607
608 SelectStatementProvider selectStatement = select(lastName, count())
609 .from(person)
610 .groupBy(lastName)
611 .having(group(count(), isEqualTo(3L), and(lastName, isEqualTo("Rubble"))))
612 .union()
613 .select(lastName, count())
614 .from(person)
615 .groupBy(lastName)
616 .having(group(count(), isGreaterThan(1L), and(lastName, isEqualTo("Flintstone"))))
617 .fetchFirst(5).rowsOnly()
618 .build()
619 .render(RenderingStrategies.MYBATIS3);
620
621 String expected = "select last_name, count(*) from Person group by last_name " +
622 "having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR} " +
623 "union select last_name, count(*) from Person group by last_name " +
624 "having count(*) > #{parameters.p3} and last_name = #{parameters.p4,jdbcType=VARCHAR} " +
625 "fetch first #{parameters.p5} rows only";
626 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
627
628 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
629 assertThat(rows).hasSize(2);
630 Map<String, Object> row = rows.get(0);
631 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
632 }
633 }
634
635 @Test
636 void testHavingWithUnionAll() {
637 try (SqlSession session = sqlSessionFactory.openSession()) {
638 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
639
640 SelectStatementProvider selectStatement = select(lastName, count())
641 .from(person)
642 .groupBy(lastName)
643 .having(group(count(), isEqualTo(3L), and(lastName, isEqualTo("Rubble"))))
644 .unionAll()
645 .select(lastName, count())
646 .from(person)
647 .groupBy(lastName)
648 .having(group(count(), isGreaterThan(1L), and(lastName, isEqualTo("Flintstone"))))
649 .offset(1)
650 .build()
651 .render(RenderingStrategies.MYBATIS3);
652
653 String expected = "select last_name, count(*) from Person group by last_name " +
654 "having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR} " +
655 "union all select last_name, count(*) from Person group by last_name " +
656 "having count(*) > #{parameters.p3} and last_name = #{parameters.p4,jdbcType=VARCHAR} " +
657 "offset #{parameters.p5} rows";
658 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
659
660 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
661 assertThat(rows).hasSize(1);
662 Map<String, Object> row = rows.get(0);
663 assertThat(row).containsEntry("LAST_NAME", "Flintstone");
664 }
665 }
666
667 @Test
668 void testStandaloneHaving() {
669 try (SqlSession session = sqlSessionFactory.openSession()) {
670 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
671
672 SelectStatementProvider selectStatement = select(lastName, count())
673 .from(person)
674 .groupBy(lastName)
675 .applyHaving(commonHaving)
676 .build()
677 .render(RenderingStrategies.MYBATIS3);
678
679 String expected = "select last_name, count(*) from Person group by last_name " +
680 "having count(*) = #{parameters.p1}";
681 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
682
683 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
684 assertThat(rows).hasSize(1);
685 Map<String, Object> row = rows.get(0);
686 assertThat(row).containsEntry("LAST_NAME", "Rubble");
687 }
688 }
689
690 @Test
691 void testComposedHaving() {
692 try (SqlSession session = sqlSessionFactory.openSession()) {
693 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
694
695 HavingApplier composedHaving = commonHaving.andThen(d -> d.and(lastName, isEqualTo("Rubble")));
696
697 SelectStatementProvider selectStatement = select(lastName, count())
698 .from(person)
699 .groupBy(lastName)
700 .applyHaving(composedHaving)
701 .build()
702 .render(RenderingStrategies.MYBATIS3);
703
704 String expected = "select last_name, count(*) from Person group by last_name " +
705 "having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR}";
706 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
707
708 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
709 assertThat(rows).hasSize(1);
710 Map<String, Object> row = rows.get(0);
711 assertThat(row).containsEntry("LAST_NAME", "Rubble");
712 }
713 }
714
715 private final HavingApplier commonHaving = having(count(), isEqualTo(3L)).toHavingApplier();
716 }