1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.mybatis.dynamic.sql.where.render;
17
18 import static org.assertj.core.api.Assertions.assertThat;
19 import static org.mybatis.dynamic.sql.SqlBuilder.*;
20
21 import java.util.stream.Stream;
22
23 import org.junit.jupiter.params.ParameterizedTest;
24 import org.junit.jupiter.params.provider.Arguments;
25 import org.junit.jupiter.params.provider.MethodSource;
26 import org.mybatis.dynamic.sql.SqlColumn;
27 import org.mybatis.dynamic.sql.SqlTable;
28 import org.mybatis.dynamic.sql.render.RenderingStrategies;
29 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
30 import org.mybatis.dynamic.sql.dsl.WhereDSL;
31 import org.mybatis.dynamic.sql.where.WhereApplier;
32
33 class OptionalCriterionRenderTest {
34 private static final SqlTable person = SqlTable.of("person");
35 private static final SqlColumn<Integer> id = person.column("id");
36 private static final SqlColumn<String> firstName = person.column("first_name");
37 private static final SqlColumn<String> lastName = person.column("last_name");
38
39 @ParameterizedTest
40 @MethodSource("testVariations")
41 void testVariations(Variation variation) {
42 SelectStatementProvider selectStatement = select(person.allColumns())
43 .from(person)
44 .applyWhere(variation.whereApplier)
45 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
46 .build()
47 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
48
49 String expected = "select * from person " + variation.expected;
50 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected.trim());
51 }
52
53 private record Variation(WhereApplier whereApplier, String expected) {}
54
55 static Stream<Arguments> testVariations() {
56 return Stream.of(
57 testNoRenderableCriteria(),
58 testDisabledIsNull(),
59 testEnabledIsNull(),
60 testDisabledIsNotNull(),
61 testEnabledIsNotNull(),
62 testOneRenderableCriteriaBeforeNull(),
63 testOneRenderableCriteriaBeforeNull2(),
64 testOneRenderableCriteriaAfterNull(),
65 testOneRenderableCriteriaAfterNull2(),
66 testOverrideFirstConnector(),
67 testWhereExists(),
68 testWhereExistsOr(),
69 testWhereExistsOrOr(),
70 testWhereExistsAnd(),
71 testWhereExistsAndAnd(),
72 testCollapsingCriteriaGroup1(),
73 testCollapsingCriteriaGroup2(),
74 testCollapsingCriteriaGroup3(),
75 testCollapsingCriteriaGroup4()
76 );
77 }
78
79 static Arguments testNoRenderableCriteria() {
80 WhereApplier whereClause = where(id, isEqualToWhenPresent((Integer) null))
81 .toWhereApplier();
82 return Arguments.argumentSet("No Renderable Criteria", new Variation(whereClause, ""));
83 }
84
85 static Arguments testDisabledIsNull() {
86 WhereApplier whereClause = where(id, isNull().filter(() -> false))
87 .toWhereApplier();
88 return Arguments.argumentSet("Disabled Is Null", new Variation(whereClause, ""));
89 }
90
91 static Arguments testEnabledIsNull() {
92 WhereApplier whereClause = where(id, isNull().filter(() -> true))
93 .toWhereApplier();
94 return Arguments.argumentSet("Enabled Is Null", new Variation(whereClause, "where id is null"));
95 }
96
97 static Arguments testDisabledIsNotNull() {
98 WhereApplier whereClause = where(id, isNotNull().filter(() -> false))
99 .toWhereApplier();
100 return Arguments.argumentSet("Disabled Is Not Null", new Variation(whereClause, ""));
101 }
102
103 static Arguments testEnabledIsNotNull() {
104 WhereApplier whereClause = where(id, isNotNull().filter(() -> true))
105 .toWhereApplier();
106 return Arguments.argumentSet("Enabled Is Not Null", new Variation(whereClause, "where id is not null"));
107 }
108
109 static Arguments testOneRenderableCriteriaBeforeNull() {
110 WhereApplier whereClause = where(id, isEqualToWhenPresent(22))
111 .and(firstName, isEqualToWhenPresent((String) null))
112 .toWhereApplier();
113 return Arguments.argumentSet("One Renderable Criteria Before Null", new Variation(whereClause, "where id = :p1"));
114 }
115
116 static Arguments testOneRenderableCriteriaBeforeNull2() {
117 WhereApplier whereClause = where(id, isEqualToWhenPresent(22), and(firstName, isEqualToWhenPresent((String) null)))
118 .toWhereApplier();
119 return Arguments.argumentSet("One Renderable Criteria Before Null 2", new Variation(whereClause, "where id = :p1"));
120 }
121
122 static Arguments testOneRenderableCriteriaAfterNull() {
123 WhereApplier whereClause = where(id, isEqualToWhenPresent((Integer) null))
124 .and(firstName, isEqualToWhenPresent("Fred"))
125 .toWhereApplier();
126 return Arguments.argumentSet("One Renderable Criteria After Null", new Variation(whereClause, "where first_name = :p1"));
127 }
128
129 static Arguments testOneRenderableCriteriaAfterNull2() {
130 WhereApplier whereClause = where(id, isEqualToWhenPresent((Integer) null), and(firstName, isEqualToWhenPresent("Fred")))
131 .toWhereApplier();
132 return Arguments.argumentSet("One Renderable Criteria After Null 2", new Variation(whereClause, "where first_name = :p1"));
133 }
134
135 static Arguments testOverrideFirstConnector() {
136 WhereApplier whereClause = where(id, isEqualToWhenPresent((Integer) null), and(firstName, isEqualToWhenPresent("Fred")), or(lastName, isEqualTo("flintstone")))
137 .toWhereApplier();
138 return Arguments.argumentSet("Override First Connector", new Variation(whereClause, "where first_name = :p1 or last_name = :p2"));
139 }
140
141 static Arguments testWhereExists() {
142 WhereApplier whereClause = where(
143 exists(
144 select(person.allColumns())
145 .from(person)
146 .where(id, isEqualTo(3))
147 ))
148 .toWhereApplier();
149 return Arguments.argumentSet("Where Exists", new Variation(whereClause, "where exists (select * from person where id = :p1)"));
150 }
151
152 static Arguments testWhereExistsOr() {
153 WhereApplier whereClause = where(
154 exists(
155 select(person.allColumns())
156 .from(person)
157 .where(id, isEqualTo(3))
158 ),
159 or(exists(
160 select(person.allColumns())
161 .from(person)
162 .where(id, isEqualTo(4))
163 )))
164 .toWhereApplier();
165
166 String expected = "where exists (select * from person where id = :p1) " +
167 "or exists (select * from person where id = :p2)";
168 return Arguments.argumentSet("Where Exists Or", new Variation(whereClause, expected));
169 }
170
171 static Arguments testWhereExistsOrOr() {
172 WhereApplier whereClause = where(
173 exists(
174 select(person.allColumns())
175 .from(person)
176 .where(id, isEqualTo(3))
177 ),
178 or(exists(
179 select(person.allColumns())
180 .from(person)
181 .where(id, isEqualTo(4))
182 ), or(exists(
183 select(person.allColumns())
184 .from(person)
185 .where(id, isEqualTo(5))
186
187 )
188 )))
189 .toWhereApplier();
190
191 String expected = "where exists (select * from person where id = :p1) " +
192 "or (exists (select * from person where id = :p2) " +
193 "or exists (select * from person where id = :p3))";
194
195 return Arguments.argumentSet("Where Exists Or Or", new Variation(whereClause, expected));
196 }
197
198 static Arguments testWhereExistsAnd() {
199 WhereApplier whereClause = where(
200 exists(
201 select(person.allColumns())
202 .from(person)
203 .where(id, isEqualTo(3))
204 ),
205 and(exists(
206 select(person.allColumns())
207 .from(person)
208 .where(id, isEqualTo(4))
209 )))
210 .toWhereApplier();
211
212 String expected = "where exists (select * from person where id = :p1) " +
213 "and exists (select * from person where id = :p2)";
214
215 return Arguments.argumentSet("Where Exists And", new Variation(whereClause, expected));
216 }
217
218 static Arguments testWhereExistsAndAnd() {
219 WhereApplier whereClause = where(
220 exists(
221 select(person.allColumns())
222 .from(person)
223 .where(id, isEqualTo(3))
224 ),
225 and(exists(
226 select(person.allColumns())
227 .from(person)
228 .where(id, isEqualTo(4))
229 ), and(exists(
230 select(person.allColumns())
231 .from(person)
232 .where(id, isEqualTo(5))
233
234 )
235 )))
236 .toWhereApplier();
237
238 String expected = "where exists (select * from person where id = :p1) " +
239 "and (exists (select * from person where id = :p2) " +
240 "and exists (select * from person where id = :p3))";
241
242 return Arguments.argumentSet("Where Exists And And", new Variation(whereClause, expected));
243 }
244
245 static Arguments testCollapsingCriteriaGroup1() {
246 WhereApplier whereClause = where(
247 group(firstName, isEqualToWhenPresent((String) null)), or(lastName, isEqualToWhenPresent((String) null)))
248 .toWhereApplier();
249 return Arguments.argumentSet("Collapsing Criteria Group", new Variation(whereClause, ""));
250 }
251
252 static Arguments testCollapsingCriteriaGroup2() {
253 WhereApplier whereClause = where(
254 group(firstName, isEqualTo("Fred")), or(lastName, isEqualToWhenPresent((String) null)))
255 .toWhereApplier();
256
257 String expected = "where first_name = :p1";
258
259 return Arguments.argumentSet("Collapsing Criteria Group 2", new Variation(whereClause, expected));
260 }
261
262 static Arguments testCollapsingCriteriaGroup3() {
263 WhereApplier whereClause = where(
264 group(firstName, isEqualTo("Fred")), or(lastName, isEqualToWhenPresent((String) null)), or(firstName, isEqualTo("Betty")))
265 .toWhereApplier();
266
267 String expected = "where first_name = :p1 or first_name = :p2";
268
269 return Arguments.argumentSet("Collapsing Criteria Group 3", new Variation(whereClause, expected));
270 }
271
272 static Arguments testCollapsingCriteriaGroup4() {
273 WhereDSL whereBuilder = where();
274
275 WhereApplier whereClause = whereBuilder.and(group(firstName, isEqualTo("Fred")), or(lastName, isEqualToWhenPresent((String) null)), or(firstName, isEqualTo("Betty")))
276 .toWhereApplier();
277
278 String expected = "where first_name = :p1 or first_name = :p2";
279
280 return Arguments.argumentSet("Collapsing Criteria Group 4", new Variation(whereClause, expected));
281 }
282 }