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