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