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.assertj.core.api.Assertions.entry;
20 import static org.mybatis.dynamic.sql.SqlBuilder.*;
21
22 import java.util.Optional;
23
24 import org.junit.jupiter.api.Test;
25 import org.mybatis.dynamic.sql.SqlColumn;
26 import org.mybatis.dynamic.sql.SqlTable;
27 import org.mybatis.dynamic.sql.render.RenderingStrategies;
28
29 class OptionalCriterionRenderTest {
30 private static final SqlTable person = SqlTable.of("person");
31 private static final SqlColumn<Integer> id = person.column("id");
32 private static final SqlColumn<String> firstName = person.column("first_name");
33 private static final SqlColumn<String> lastName = person.column("last_name");
34
35 @Test
36 void testNoRenderableCriteria() {
37 Optional<WhereClauseProvider> whereClause = where(id, isEqualToWhenPresent((Integer) null))
38 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
39 .build()
40 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
41
42 assertThat(whereClause).isEmpty();
43 }
44
45 @Test
46 void testDisabledIsNull() {
47 Optional<WhereClauseProvider> whereClause = where(id, isNull().filter(() -> false))
48 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
49 .build()
50 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
51
52 assertThat(whereClause).isEmpty();
53 }
54
55 @Test
56 void testEnabledIsNull() {
57 Optional<WhereClauseProvider> whereClause = where(id, isNull().filter(() -> true))
58 .build()
59 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
60
61 assertThat(whereClause).hasValueSatisfying(wc -> {
62 assertThat(wc.getWhereClause()).isEqualTo("where id is null");
63 assertThat(wc.getParameters()).isEmpty();
64 });
65 }
66
67 @Test
68 void testDisabledIsNotNull() {
69 Optional<WhereClauseProvider> whereClause = where(id, isNotNull().filter(() -> false))
70 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
71 .build()
72 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
73
74 assertThat(whereClause).isEmpty();
75 }
76
77 @Test
78 void testEnabledIsNotNull() {
79 Optional<WhereClauseProvider> whereClause = where(id, isNotNull().filter(() -> true))
80 .build()
81 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
82
83 assertThat(whereClause).hasValueSatisfying(wc -> {
84 assertThat(wc.getWhereClause()).isEqualTo("where id is not null");
85 assertThat(wc.getParameters()).isEmpty();
86 });
87 }
88
89 @Test
90 void testOneRenderableCriteriaBeforeNull() {
91 Optional<WhereClauseProvider> whereClause = where(id, isEqualToWhenPresent(22))
92 .and(firstName, isEqualToWhenPresent((String) null))
93 .build()
94 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
95
96 assertThat(whereClause).hasValueSatisfying(wc -> {
97 assertThat(wc.getParameters()).containsExactly(entry("p1", 22));
98 assertThat(wc.getWhereClause()).isEqualTo("where id = :p1");
99 });
100 }
101
102 @Test
103 void testOneRenderableCriteriaBeforeNull2() {
104 Optional<WhereClauseProvider> whereClause = where(id, isEqualToWhenPresent(22), and(firstName, isEqualToWhenPresent((String) null)))
105 .build()
106 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
107
108 assertThat(whereClause).hasValueSatisfying(wc -> {
109 assertThat(wc.getParameters()).containsExactly(entry("p1", 22));
110 assertThat(wc.getWhereClause()).isEqualTo("where id = :p1");
111 });
112 }
113
114 @Test
115 void testOneRenderableCriteriaAfterNull() {
116 Optional<WhereClauseProvider> whereClause = where(id, isEqualToWhenPresent((Integer) null))
117 .and(firstName, isEqualToWhenPresent("fred"))
118 .build()
119 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
120
121 assertThat(whereClause).hasValueSatisfying(wc -> {
122 assertThat(wc.getParameters()).containsExactly(entry("p1", "fred"));
123 assertThat(wc.getWhereClause()).isEqualTo("where first_name = :p1");
124 });
125 }
126
127 @Test
128 void testOneRenderableCriteriaAfterNull2() {
129 Optional<WhereClauseProvider> whereClause = where(id, isEqualToWhenPresent((Integer) null), and(firstName, isEqualToWhenPresent("fred")))
130 .build()
131 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
132
133 assertThat(whereClause).hasValueSatisfying(wc -> {
134 assertThat(wc.getParameters()).containsExactly(entry("p1", "fred"));
135 assertThat(wc.getWhereClause()).isEqualTo("where first_name = :p1");
136 });
137 }
138
139 @Test
140 void testOverrideFirstConnector() {
141 Optional<WhereClauseProvider> whereClause = where(id, isEqualToWhenPresent((Integer) null), and(firstName, isEqualToWhenPresent("fred")), or(lastName, isEqualTo("flintstone")))
142 .build()
143 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
144
145 assertThat(whereClause).hasValueSatisfying(wc -> {
146 assertThat(wc.getParameters()).containsExactly(entry("p1", "fred"), entry("p2", "flintstone"));
147 assertThat(wc.getWhereClause()).isEqualTo("where first_name = :p1 or last_name = :p2");
148 });
149 }
150
151 @Test
152 void testWhereExists() {
153 Optional<WhereClauseProvider> whereClause = where(
154 exists(
155 select(person.allColumns())
156 .from(person)
157 .where(id, isEqualTo(3))
158 ))
159 .build()
160 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
161
162 assertThat(whereClause).hasValueSatisfying(wc -> {
163 assertThat(wc.getParameters()).containsExactly(entry("p1", 3));
164 assertThat(wc.getWhereClause()).isEqualTo("where exists (select * from person where id = :p1)");
165 });
166 }
167
168 @Test
169 void testWhereExistsOr() {
170 Optional<WhereClauseProvider> whereClause = where(
171 exists(
172 select(person.allColumns())
173 .from(person)
174 .where(id, isEqualTo(3))
175 ),
176 or(exists(
177 select(person.allColumns())
178 .from(person)
179 .where(id, isEqualTo(4))
180 )))
181 .build()
182 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
183
184 String expected = "where exists (select * from person where id = :p1) " +
185 "or exists (select * from person where id = :p2)";
186
187 assertThat(whereClause).hasValueSatisfying(wc -> {
188 assertThat(wc.getParameters()).containsExactly(entry("p1", 3), entry("p2", 4));
189 assertThat(wc.getWhereClause()).isEqualTo(expected);
190 });
191 }
192
193 @Test
194 void testWhereExistsOrOr() {
195 Optional<WhereClauseProvider> whereClause = where(
196 exists(
197 select(person.allColumns())
198 .from(person)
199 .where(id, isEqualTo(3))
200 ),
201 or(exists(
202 select(person.allColumns())
203 .from(person)
204 .where(id, isEqualTo(4))
205 ), or(exists(
206 select(person.allColumns())
207 .from(person)
208 .where(id, isEqualTo(5))
209
210 )
211 )))
212 .build()
213 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
214
215 String expected = "where exists (select * from person where id = :p1) " +
216 "or (exists (select * from person where id = :p2) " +
217 "or exists (select * from person where id = :p3))";
218
219 assertThat(whereClause).hasValueSatisfying(wc -> {
220 assertThat(wc.getParameters()).containsExactly(entry("p1", 3), entry("p2", 4), entry("p3", 5));
221 assertThat(wc.getWhereClause()).isEqualTo(expected);
222 });
223 }
224
225 @Test
226 void testWhereExistsAnd() {
227 Optional<WhereClauseProvider> whereClause = where(
228 exists(
229 select(person.allColumns())
230 .from(person)
231 .where(id, isEqualTo(3))
232 ),
233 and(exists(
234 select(person.allColumns())
235 .from(person)
236 .where(id, isEqualTo(4))
237 )))
238 .build()
239 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
240
241 String expected = "where exists (select * from person where id = :p1) " +
242 "and exists (select * from person where id = :p2)";
243
244 assertThat(whereClause).hasValueSatisfying(wc -> {
245 assertThat(wc.getParameters()).containsExactly(entry("p1", 3), entry("p2", 4));
246 assertThat(wc.getWhereClause()).isEqualTo(expected);
247 });
248 }
249
250 @Test
251 void testWhereExistsAndAnd() {
252 Optional<WhereClauseProvider> whereClause = where(
253 exists(
254 select(person.allColumns())
255 .from(person)
256 .where(id, isEqualTo(3))
257 ),
258 and(exists(
259 select(person.allColumns())
260 .from(person)
261 .where(id, isEqualTo(4))
262 ), and(exists(
263 select(person.allColumns())
264 .from(person)
265 .where(id, isEqualTo(5))
266
267 )
268 )))
269 .build()
270 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
271
272 String expected = "where exists (select * from person where id = :p1) " +
273 "and (exists (select * from person where id = :p2) " +
274 "and exists (select * from person where id = :p3))";
275
276 assertThat(whereClause).hasValueSatisfying(wc -> {
277 assertThat(wc.getParameters()).containsExactly(entry("p1", 3), entry("p2", 4), entry("p3", 5));
278 assertThat(wc.getWhereClause()).isEqualTo(expected);
279 });
280 }
281
282 @Test
283 void testCollapsingCriteriaGroup1() {
284 Optional<WhereClauseProvider> whereClause = where(
285 group(firstName, isEqualToWhenPresent((String) null)), or(lastName, isEqualToWhenPresent((String) null)))
286 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
287 .build()
288 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
289
290 assertThat(whereClause).isEmpty();
291 }
292
293 @Test
294 void testCollapsingCriteriaGroup2() {
295 Optional<WhereClauseProvider> whereClause = where(
296 group(firstName, isEqualTo("Fred")), or(lastName, isEqualToWhenPresent((String) null)))
297 .build()
298 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
299
300 String expected = "where first_name = :p1";
301
302 assertThat(whereClause).hasValueSatisfying(wc -> {
303 assertThat(wc.getParameters()).containsExactly(entry("p1", "Fred"));
304 assertThat(wc.getWhereClause()).isEqualTo(expected);
305 });
306 }
307
308 @Test
309 void testCollapsingCriteriaGroup3() {
310 Optional<WhereClauseProvider> whereClause = where(
311 group(firstName, isEqualTo("Fred")), or(lastName, isEqualToWhenPresent((String) null)), or(firstName, isEqualTo("Betty")))
312 .build()
313 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
314
315 String expected = "where first_name = :p1 or first_name = :p2";
316
317 assertThat(whereClause).hasValueSatisfying(wc -> {
318 assertThat(wc.getParameters()).containsExactly(entry("p1", "Fred"), entry("p2", "Betty"));
319 assertThat(wc.getWhereClause()).isEqualTo(expected);
320 });
321 }
322 }