1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.emptywhere;
17
18 import static examples.emptywhere.OrderDynamicSqlSupport.*;
19 import static examples.emptywhere.PersonDynamicSqlSupport.*;
20 import static org.assertj.core.api.Assertions.assertThat;
21 import static org.mybatis.dynamic.sql.SqlBuilder.*;
22
23 import java.util.Optional;
24 import java.util.stream.Stream;
25
26 import org.jspecify.annotations.Nullable;
27 import org.junit.jupiter.api.Test;
28 import org.junit.jupiter.params.ParameterizedTest;
29 import org.junit.jupiter.params.provider.MethodSource;
30 import org.mybatis.dynamic.sql.delete.DeleteDSL;
31 import org.mybatis.dynamic.sql.delete.DeleteModel;
32 import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
33 import org.mybatis.dynamic.sql.render.RenderingStrategies;
34 import org.mybatis.dynamic.sql.select.QueryExpressionDSL;
35 import org.mybatis.dynamic.sql.select.SelectModel;
36 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
37 import org.mybatis.dynamic.sql.update.UpdateDSL;
38 import org.mybatis.dynamic.sql.update.UpdateModel;
39 import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
40 import org.mybatis.dynamic.sql.where.WhereDSL;
41 import org.mybatis.dynamic.sql.where.render.WhereClauseProvider;
42
43 class EmptyWhereTest {
44 private static final String FIRST_NAME = "Fred";
45 private static final String LAST_NAME = "Flintstone";
46
47 static Stream<Variation> whereVariations() {
48 Variation v1 = new Variation(FIRST_NAME, LAST_NAME,
49 "where first_name = #{parameters.p1} or last_name = #{parameters.p2}");
50
51 Variation v2 = new Variation(null, LAST_NAME,
52 "where last_name = #{parameters.p1}");
53
54 Variation v3 = new Variation(FIRST_NAME, null,
55 "where first_name = #{parameters.p1}");
56
57 Variation v4 = new Variation(null, null, "");
58
59 return Stream.of(v1, v2, v3, v4);
60 }
61
62 static Stream<Variation> joinWhereVariations() {
63 Variation v1 = new Variation(FIRST_NAME, LAST_NAME,
64 "where person.first_name = #{parameters.p1} or person.last_name = #{parameters.p2}");
65
66 Variation v2 = new Variation(null, LAST_NAME,
67 "where person.last_name = #{parameters.p1}");
68
69 Variation v3 = new Variation(FIRST_NAME, null,
70 "where person.first_name = #{parameters.p1}");
71
72 Variation v4 = new Variation(null, null, "");
73
74 return Stream.of(v1, v2, v3, v4);
75 }
76
77 static Stream<Variation> updateWhereVariations() {
78 Variation v1 = new Variation(FIRST_NAME, LAST_NAME,
79 "where first_name = #{parameters.p2} or last_name = #{parameters.p3}");
80
81 Variation v2 = new Variation(null, LAST_NAME,
82 "where last_name = #{parameters.p2}");
83
84 Variation v3 = new Variation(FIRST_NAME, null,
85 "where first_name = #{parameters.p2}");
86
87 Variation v4 = new Variation(null, null, "");
88
89 return Stream.of(v1, v2, v3, v4);
90 }
91
92 @Test
93 void testDeleteThreeConditions() {
94 DeleteDSL<DeleteModel>.DeleteWhereBuilder builder = deleteFrom(person)
95 .where(id, isEqualTo(3));
96
97 builder.and(firstName, isEqualTo(FIRST_NAME));
98 builder.and(PersonDynamicSqlSupport.lastName, isEqualTo(LAST_NAME));
99
100 DeleteStatementProvider deleteStatement = builder.build().render(RenderingStrategies.MYBATIS3);
101
102 String expected = "delete from person"
103 + " where id = #{parameters.p1}"
104 + " and first_name = #{parameters.p2}"
105 + " and last_name = #{parameters.p3}";
106
107 assertThat(deleteStatement.getDeleteStatement()).isEqualTo(expected);
108 }
109
110 @ParameterizedTest
111 @MethodSource("whereVariations")
112 void testDeleteVariations(Variation variation) {
113 DeleteDSL<DeleteModel>.DeleteWhereBuilder builder = deleteFrom(person)
114 .where();
115
116 builder.and(firstName, isEqualToWhenPresent(variation.firstName));
117 builder.or(PersonDynamicSqlSupport.lastName, isEqualToWhenPresent(variation.lastName));
118 builder.configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true));
119
120 DeleteStatementProvider deleteStatement = builder.build().render(RenderingStrategies.MYBATIS3);
121
122 String expected = "delete from person " + variation.whereClause;
123
124 assertThat(deleteStatement.getDeleteStatement()).isEqualTo(expected.trim());
125 }
126
127 @Test
128 void testSelectThreeConditions() {
129 QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder builder = select(id, firstName, PersonDynamicSqlSupport.lastName)
130 .from(person)
131 .where(id, isEqualTo(3));
132
133 builder.and(firstName, isEqualTo(FIRST_NAME));
134 builder.and(PersonDynamicSqlSupport.lastName, isEqualTo(LAST_NAME));
135
136 SelectStatementProvider selectStatement = builder.build().render(RenderingStrategies.MYBATIS3);
137
138 String expected = "select id, first_name, last_name"
139 + " from person"
140 + " where id = #{parameters.p1}"
141 + " and first_name = #{parameters.p2}"
142 + " and last_name = #{parameters.p3}";
143
144 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
145 }
146
147 @ParameterizedTest
148 @MethodSource("whereVariations")
149 void testSelectVariations(Variation variation) {
150 QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder builder = select(person.allColumns())
151 .from(person)
152 .where();
153
154 builder.and(firstName, isEqualToWhenPresent(variation.firstName));
155 builder.or(PersonDynamicSqlSupport.lastName, isEqualToWhenPresent(variation.lastName));
156 builder.configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true));
157
158 SelectStatementProvider selectStatement = builder.build().render(RenderingStrategies.MYBATIS3);
159
160 String expected = "select * from person " + variation.whereClause;
161
162 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected.trim());
163 }
164
165 @Test
166 void testJoinThreeConditions() {
167 QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder builder = select(id, firstName, PersonDynamicSqlSupport.lastName, orderDate)
168 .from(person).join(order).on(person.id, isEqualTo(order.personId))
169 .where(id, isEqualTo(3));
170
171 builder.and(firstName, isEqualTo(FIRST_NAME));
172 builder.and(PersonDynamicSqlSupport.lastName, isEqualTo(LAST_NAME));
173
174 SelectStatementProvider selectStatement = builder.build().render(RenderingStrategies.MYBATIS3);
175
176 String expected = "select person.id, person.first_name, person.last_name, order.order_date"
177 + " from person"
178 + " join order on person.id = order.person_id"
179 + " where person.id = #{parameters.p1}"
180 + " and person.first_name = #{parameters.p2}"
181 + " and person.last_name = #{parameters.p3}";
182
183 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
184 }
185
186 @ParameterizedTest
187 @MethodSource("joinWhereVariations")
188 void testJoinVariations(Variation variation) {
189 QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder builder = select(id, firstName, PersonDynamicSqlSupport.lastName, orderDate)
190 .from(person).join(order).on(person.id, isEqualTo(order.personId))
191 .where();
192
193 builder.and(firstName, isEqualToWhenPresent(variation.firstName));
194 builder.or(PersonDynamicSqlSupport.lastName, isEqualToWhenPresent(variation.lastName));
195 builder.configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true));
196
197 SelectStatementProvider selectStatement = builder.build().render(RenderingStrategies.MYBATIS3);
198
199 String expected = "select person.id, person.first_name, person.last_name, order.order_date"
200 + " from person"
201 + " join order on person.id = order.person_id "
202 + variation.whereClause;
203
204 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected.trim());
205 }
206
207 @Test
208 void testUpdateThreeConditions() {
209 UpdateDSL<UpdateModel>.UpdateWhereBuilder builder = update(person)
210 .set(id).equalTo(3)
211 .where(id, isEqualTo(3));
212
213 builder.and(firstName, isEqualTo(FIRST_NAME));
214 builder.and(PersonDynamicSqlSupport.lastName, isEqualTo(LAST_NAME));
215
216 UpdateStatementProvider updateStatement = builder.build().render(RenderingStrategies.MYBATIS3);
217
218 String expected = "update person"
219 + " set id = #{parameters.p1}"
220 + " where id = #{parameters.p2}"
221 + " and first_name = #{parameters.p3}"
222 + " and last_name = #{parameters.p4}";
223
224 assertThat(updateStatement.getUpdateStatement()).isEqualTo(expected);
225 }
226
227 @ParameterizedTest
228 @MethodSource("updateWhereVariations")
229 void testUpdateVariations(Variation variation) {
230 UpdateDSL<UpdateModel>.UpdateWhereBuilder builder = update(person)
231 .set(id).equalTo(3)
232 .where();
233
234 builder.and(firstName, isEqualToWhenPresent(variation.firstName));
235 builder.or(PersonDynamicSqlSupport.lastName, isEqualToWhenPresent(variation.lastName));
236 builder.configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true));
237
238 UpdateStatementProvider updateStatement = builder.build().render(RenderingStrategies.MYBATIS3);
239
240 String expected = "update person"
241 + " set id = #{parameters.p1} "
242 + variation.whereClause;
243
244 assertThat(updateStatement.getUpdateStatement()).isEqualTo(expected.trim());
245 }
246
247 @Test
248 void testWhereThreeConditions() {
249 WhereDSL.StandaloneWhereFinisher builder = where(id, isEqualTo(3));
250
251 builder.and(firstName, isEqualTo(FIRST_NAME));
252 builder.and(PersonDynamicSqlSupport.lastName, isEqualTo(LAST_NAME));
253
254 Optional<WhereClauseProvider> whereClause = builder.build().render(RenderingStrategies.MYBATIS3);
255
256 String expected = "where id = #{parameters.p1}"
257 + " and first_name = #{parameters.p2}"
258 + " and last_name = #{parameters.p3}";
259
260 assertThat(whereClause.map(WhereClauseProvider::getWhereClause)).hasValueSatisfying(wc ->
261 assertThat(wc).isEqualTo(expected)
262 );
263 }
264
265 @ParameterizedTest
266 @MethodSource("whereVariations")
267 void testWhereVariations(Variation variation) {
268 WhereDSL.StandaloneWhereFinisher builder = where();
269
270 builder.and(firstName, isEqualToWhenPresent(variation.firstName));
271 builder.or(PersonDynamicSqlSupport.lastName, isEqualToWhenPresent(variation.lastName));
272 builder.configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true));
273
274 Optional<WhereClauseProvider> whereClause = builder.build().render(RenderingStrategies.MYBATIS3);
275
276 if (variation.firstName == null && variation.lastName == null) {
277 assertThat(whereClause).isEmpty();
278 } else {
279 assertThat(whereClause.map(WhereClauseProvider::getWhereClause)).hasValueSatisfying(wc ->
280 assertThat(wc).isEqualTo(variation.whereClause)
281 );
282 }
283 }
284
285 private record Variation (@Nullable String firstName, @Nullable String lastName, String whereClause) {}
286 }