1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.complexquery;
17
18 import static org.assertj.core.api.Assertions.assertThat;
19 import static org.mybatis.dynamic.sql.SqlBuilder.and;
20 import static org.mybatis.dynamic.sql.SqlBuilder.exists;
21 import static org.mybatis.dynamic.sql.SqlBuilder.group;
22 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
23 import static org.mybatis.dynamic.sql.SqlBuilder.isGreaterThan;
24 import static org.mybatis.dynamic.sql.SqlBuilder.isLessThan;
25 import static org.mybatis.dynamic.sql.SqlBuilder.or;
26 import static org.mybatis.dynamic.sql.SqlBuilder.select;
27
28 import org.junit.jupiter.api.Test;
29 import org.mybatis.dynamic.sql.SqlColumn;
30 import org.mybatis.dynamic.sql.SqlTable;
31 import org.mybatis.dynamic.sql.render.RenderingStrategies;
32 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
33
34 class GroupingTest {
35 private static class Foo extends SqlTable {
36 public final SqlColumn<Integer> columnA = column("A");
37 public final SqlColumn<Integer> columnB = column("B");
38 public final SqlColumn<Integer> columnC = column("C");
39
40 public Foo() {
41 super("Foo");
42 }
43 }
44
45 private static final Foo foo = new Foo();
46 private static final SqlColumn<Integer> columnA = foo.columnA;
47 private static final SqlColumn<Integer> columnB = foo.columnB;
48 private static final SqlColumn<Integer> columnC = foo.columnC;
49
50 @Test
51 void testSimpleGrouping() {
52 SelectStatementProvider selectStatement = select(columnA, columnB, columnC)
53 .from(foo)
54 .where(columnA, isEqualTo(1), or(columnA, isEqualTo(2)))
55 .and(columnB, isEqualTo(3))
56 .build()
57 .render(RenderingStrategies.MYBATIS3);
58
59 String expected = "select A, B, C"
60 + " from Foo"
61 + " where (A = #{parameters.p1} or A = #{parameters.p2}) and B = #{parameters.p3}";
62
63 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
64 assertThat(selectStatement.getParameters()).containsEntry("p1", 1);
65 assertThat(selectStatement.getParameters()).containsEntry("p2", 2);
66 assertThat(selectStatement.getParameters()).containsEntry("p3", 3);
67 }
68
69 @Test
70 void testComplexGrouping() {
71 SelectStatementProvider selectStatement = select(columnA, columnB, columnC)
72 .from(foo)
73 .where(
74 group(columnA, isEqualTo(1), or(columnA, isGreaterThan(5))),
75 and(columnB, isEqualTo(1)),
76 or(columnA, isLessThan(0), and(columnB, isEqualTo(2)))
77 )
78 .and(columnC, isEqualTo(1))
79 .build()
80 .render(RenderingStrategies.MYBATIS3);
81
82 String expected = "select A, B, C"
83 + " from Foo"
84 + " where ((A = #{parameters.p1} or A > #{parameters.p2}) and B = #{parameters.p3} or (A < #{parameters.p4} and B = #{parameters.p5})) and C = #{parameters.p6}";
85
86 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
87 assertThat(selectStatement.getParameters()).containsEntry("p1", 1);
88 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
89 assertThat(selectStatement.getParameters()).containsEntry("p3", 1);
90 assertThat(selectStatement.getParameters()).containsEntry("p4", 0);
91 assertThat(selectStatement.getParameters()).containsEntry("p5", 2);
92 assertThat(selectStatement.getParameters()).containsEntry("p6", 1);
93 }
94
95 @Test
96 void testGroupAndExists() {
97 SelectStatementProvider selectStatement = select(columnA, columnB, columnC)
98 .from(foo)
99 .where(
100 group(exists(select(foo.allColumns()).from(foo).where(columnA, isEqualTo(3))), and (columnA, isEqualTo(1)), or(columnA, isGreaterThan(5))),
101 and(columnB, isEqualTo(1)),
102 or(columnA, isLessThan(0), and(columnB, isEqualTo(2)))
103 )
104 .and(columnC, isEqualTo(1))
105 .build()
106 .render(RenderingStrategies.MYBATIS3);
107
108 String expected = "select A, B, C"
109 + " from Foo"
110 + " where ((exists (select * from Foo where A = #{parameters.p1}) and A = #{parameters.p2} or A > #{parameters.p3}) and B = #{parameters.p4} or (A < #{parameters.p5} and B = #{parameters.p6})) and C = #{parameters.p7}";
111
112 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
113 assertThat(selectStatement.getParameters()).containsEntry("p1", 3);
114 assertThat(selectStatement.getParameters()).containsEntry("p2", 1);
115 assertThat(selectStatement.getParameters()).containsEntry("p3", 5);
116 assertThat(selectStatement.getParameters()).containsEntry("p4", 1);
117 assertThat(selectStatement.getParameters()).containsEntry("p5", 0);
118 assertThat(selectStatement.getParameters()).containsEntry("p6", 2);
119 assertThat(selectStatement.getParameters()).containsEntry("p7", 1);
120 }
121
122 @Test
123 void testNestedGrouping() {
124 SelectStatementProvider selectStatement = select(columnA, columnB, columnC)
125 .from(foo)
126 .where(
127 group(group(columnA, isEqualTo(1), or(columnA, isGreaterThan(5))), and(columnA, isGreaterThan(5))),
128 and(group(columnA, isEqualTo(1), or(columnA, isGreaterThan(5))), or(columnB, isEqualTo(1))),
129 or(group(columnA, isEqualTo(1), or(columnA, isGreaterThan(5))), and(columnA, isLessThan(0), and(columnB, isEqualTo(2))))
130 )
131 .and(columnC, isEqualTo(1))
132 .build()
133 .render(RenderingStrategies.MYBATIS3);
134
135 String expected = "select A, B, C"
136 + " from Foo"
137 + " where (((A = #{parameters.p1} or A > #{parameters.p2}) and A > #{parameters.p3}) and ((A = #{parameters.p4} or A > #{parameters.p5}) or B = #{parameters.p6}) or ((A = #{parameters.p7} or A > #{parameters.p8}) and (A < #{parameters.p9} and B = #{parameters.p10}))) and C = #{parameters.p11}";
138
139 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
140 assertThat(selectStatement.getParameters()).containsEntry("p1", 1);
141 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
142 assertThat(selectStatement.getParameters()).containsEntry("p3", 5);
143 assertThat(selectStatement.getParameters()).containsEntry("p4", 1);
144 assertThat(selectStatement.getParameters()).containsEntry("p5", 5);
145 assertThat(selectStatement.getParameters()).containsEntry("p6", 1);
146 assertThat(selectStatement.getParameters()).containsEntry("p7", 1);
147 assertThat(selectStatement.getParameters()).containsEntry("p8", 5);
148 assertThat(selectStatement.getParameters()).containsEntry("p9", 0);
149 assertThat(selectStatement.getParameters()).containsEntry("p10", 2);
150 assertThat(selectStatement.getParameters()).containsEntry("p11", 1);
151 }
152
153 @Test
154 void testAndOrCriteriaGroups() {
155 SelectStatementProvider selectStatement = select(columnA, columnB, columnC)
156 .from(foo)
157 .where(columnA, isEqualTo(6))
158 .and(columnC, isEqualTo(1))
159 .and(group(columnA, isEqualTo(1), or(columnA, isGreaterThan(5))), or(columnB, isEqualTo(1)))
160 .or(group(columnA, isEqualTo(1), or(columnA, isGreaterThan(5))), and(columnA, isLessThan(0), and(columnB, isEqualTo(2))))
161 .build()
162 .render(RenderingStrategies.MYBATIS3);
163
164 String expected = "select A, B, C"
165 + " from Foo"
166 + " where A = #{parameters.p1}"
167 + " and C = #{parameters.p2}"
168 + " and ((A = #{parameters.p3} or A > #{parameters.p4}) or B = #{parameters.p5})"
169 + " or ((A = #{parameters.p6} or A > #{parameters.p7}) and (A < #{parameters.p8} and B = #{parameters.p9}))";
170
171 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
172 assertThat(selectStatement.getParameters()).containsEntry("p1", 6);
173 assertThat(selectStatement.getParameters()).containsEntry("p2", 1);
174 assertThat(selectStatement.getParameters()).containsEntry("p3", 1);
175 assertThat(selectStatement.getParameters()).containsEntry("p4", 5);
176 assertThat(selectStatement.getParameters()).containsEntry("p5", 1);
177 assertThat(selectStatement.getParameters()).containsEntry("p6", 1);
178 assertThat(selectStatement.getParameters()).containsEntry("p7", 5);
179 assertThat(selectStatement.getParameters()).containsEntry("p8", 0);
180 assertThat(selectStatement.getParameters()).containsEntry("p9", 2);
181 }
182 }