1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.generated.always.spring;
17
18 import static examples.generated.always.spring.GeneratedAlwaysDynamicSqlSupport.*;
19 import static org.assertj.core.api.Assertions.assertThat;
20 import static org.assertj.core.api.Assertions.entry;
21 import static org.mybatis.dynamic.sql.SqlBuilder.*;
22
23 import java.util.ArrayList;
24 import java.util.List;
25
26 import org.junit.jupiter.api.BeforeEach;
27 import org.junit.jupiter.api.Test;
28 import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
29 import org.mybatis.dynamic.sql.insert.BatchInsertModel;
30 import org.mybatis.dynamic.sql.insert.GeneralInsertModel;
31 import org.mybatis.dynamic.sql.insert.InsertModel;
32 import org.mybatis.dynamic.sql.insert.MultiRowInsertModel;
33 import org.mybatis.dynamic.sql.insert.render.BatchInsert;
34 import org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider;
35 import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
36 import org.mybatis.dynamic.sql.render.RenderingStrategies;
37 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
38 import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
39 import org.mybatis.dynamic.sql.util.Buildable;
40 import org.mybatis.dynamic.sql.util.spring.BatchInsertUtility;
41 import org.mybatis.dynamic.sql.util.spring.NamedParameterJdbcTemplateExtensions;
42 import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
43 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
44 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
45 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
46 import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
47 import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
48 import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
49 import org.springframework.jdbc.support.GeneratedKeyHolder;
50 import org.springframework.jdbc.support.KeyHolder;
51
52 import examples.generated.always.GeneratedAlwaysRecord;
53
54 class SpringTest {
55 private NamedParameterJdbcTemplate template;
56
57 @BeforeEach
58 void setup() {
59 EmbeddedDatabase db = new EmbeddedDatabaseBuilder()
60 .setType(EmbeddedDatabaseType.HSQL)
61 .generateUniqueName(true)
62 .addScript("classpath:/examples/generated/always/CreateGeneratedAlwaysDB.sql")
63 .build();
64 template = new NamedParameterJdbcTemplate(db);
65 }
66
67 @Test
68 void testRender() {
69 SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, fullName)
70 .from(generatedAlways, "a")
71 .where(id, isGreaterThan(3))
72 .orderBy(id.descending())
73 .build()
74 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
75
76 String expected = "select a.id as A_ID, a.first_name, a.last_name, a.full_name "
77 + "from GeneratedAlways a "
78 + "where a.id > :p1 "
79 + "order by id DESC";
80
81 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
82 }
83
84 @Test
85 void testSelect() {
86 SelectStatementProvider selectStatement = select(id, firstName, lastName, fullName)
87 .from(generatedAlways)
88 .where(id, isGreaterThan(3))
89 .orderBy(id.descending())
90 .build()
91 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
92
93 SqlParameterSource namedParameters = new MapSqlParameterSource(selectStatement.getParameters());
94
95 List<GeneratedAlwaysRecord> records = template.query(selectStatement.getSelectStatement(), namedParameters,
96 (rs, rowNum) -> {
97 GeneratedAlwaysRecord row = new GeneratedAlwaysRecord();
98 row.setId(rs.getInt(1));
99 row.setFirstName(rs.getString(2));
100 row.setLastName(rs.getString(3));
101 row.setFullName(rs.getString(4));
102 return row;
103 });
104
105 assertThat(records).hasSize(3);
106 assertThat(records.get(0).getId()).isEqualTo(6);
107 assertThat(records.get(0).getFirstName()).isEqualTo("Bamm Bamm");
108 assertThat(records.get(0).getLastName()).isEqualTo("Rubble");
109 assertThat(records.get(0).getFullName()).isEqualTo("Bamm Bamm Rubble");
110
111 assertThat(records.get(1).getId()).isEqualTo(5);
112 assertThat(records.get(2).getId()).isEqualTo(4);
113
114 }
115
116 @Test
117 void testDelete() {
118 DeleteStatementProvider deleteStatement = deleteFrom(generatedAlways)
119 .where(id, isLessThan(3))
120 .build()
121 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
122
123 SqlParameterSource parameterSource = new MapSqlParameterSource(deleteStatement.getParameters());
124
125 int rows = template.update(deleteStatement.getDeleteStatement(), parameterSource);
126
127 assertThat(rows).isEqualTo(2);
128 }
129
130 @Test
131 void testInsert() {
132 GeneratedAlwaysRecord row = new GeneratedAlwaysRecord();
133 row.setId(100);
134 row.setFirstName("Bob");
135 row.setLastName("Jones");
136
137 InsertStatementProvider<GeneratedAlwaysRecord> insertStatement = insert(row)
138 .into(generatedAlways)
139 .map(id).toProperty("id")
140 .map(firstName).toProperty("firstName")
141 .map(lastName).toProperty("lastName")
142 .build()
143 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
144
145 SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(insertStatement);
146 KeyHolder keyHolder = new GeneratedKeyHolder();
147
148 int rows = template.update(insertStatement.getInsertStatement(), parameterSource, keyHolder);
149
150 assertThat(rows).isEqualTo(1);
151 assertThat(keyHolder).extracting(KeyHolder::getKeys).extracting("FULL_NAME").isEqualTo("Bob Jones");
152 }
153
154 @Test
155 void testInsertWithExtensions() {
156 GeneratedAlwaysRecord row = new GeneratedAlwaysRecord();
157 row.setId(100);
158 row.setFirstName("Bob");
159 row.setLastName("Jones");
160
161 Buildable<InsertModel<GeneratedAlwaysRecord>> insertStatement = insert(row)
162 .into(generatedAlways)
163 .map(id).toProperty("id")
164 .map(firstName).toProperty("firstName")
165 .map(lastName).toProperty("lastName");
166
167 NamedParameterJdbcTemplateExtensions extensions = new NamedParameterJdbcTemplateExtensions(template);
168
169 KeyHolder keyHolder = new GeneratedKeyHolder();
170
171 int rows = extensions.insert(insertStatement, keyHolder);
172
173 assertThat(rows).isEqualTo(1);
174 assertThat(keyHolder).extracting(KeyHolder::getKeys).extracting("FULL_NAME").isEqualTo("Bob Jones");
175 }
176
177 @Test
178 void testGeneralInsert() {
179 GeneralInsertStatementProvider insertStatement = insertInto(generatedAlways)
180 .set(id).toValue(100)
181 .set(firstName).toValue("Bob")
182 .set(lastName).toValue("Jones")
183 .build()
184 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
185
186 int rows = template.update(insertStatement.getInsertStatement(), insertStatement.getParameters());
187
188 assertThat(rows).isEqualTo(1);
189 }
190
191 @Test
192 void testGeneralInsertWithGeneratedKey() {
193 GeneralInsertStatementProvider insertStatement = insertInto(generatedAlways)
194 .set(id).toValue(100)
195 .set(firstName).toValue("Bob")
196 .set(lastName).toValue("Jones")
197 .build()
198 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
199
200 MapSqlParameterSource parameterSource = new MapSqlParameterSource(insertStatement.getParameters());
201 KeyHolder keyHolder = new GeneratedKeyHolder();
202
203 int rows = template.update(insertStatement.getInsertStatement(), parameterSource, keyHolder);
204
205 assertThat(rows).isEqualTo(1);
206 assertThat(keyHolder).extracting(KeyHolder::getKeys).extracting("FULL_NAME").isEqualTo("Bob Jones");
207 }
208
209 @Test
210 void testGeneralInsertWithGeneratedKeyAndExtensions() {
211 Buildable<GeneralInsertModel> insertStatement = insertInto(generatedAlways)
212 .set(id).toValue(100)
213 .set(firstName).toValue("Bob")
214 .set(lastName).toValue("Jones");
215
216 NamedParameterJdbcTemplateExtensions extensions = new NamedParameterJdbcTemplateExtensions(template);
217 KeyHolder keyHolder = new GeneratedKeyHolder();
218
219 int rows = extensions.generalInsert(insertStatement, keyHolder);
220
221 assertThat(rows).isEqualTo(1);
222 assertThat(keyHolder).extracting(KeyHolder::getKeys).extracting("FULL_NAME").isEqualTo("Bob Jones");
223 }
224
225 @Test
226 void testInsertBatch() {
227 List<GeneratedAlwaysRecord> records = new ArrayList<>();
228 GeneratedAlwaysRecord row = new GeneratedAlwaysRecord();
229 row.setId(100);
230 row.setFirstName("Bob");
231 row.setLastName("Jones");
232 records.add(row);
233
234 row = new GeneratedAlwaysRecord();
235 row.setId(101);
236 row.setFirstName("Jim");
237 row.setLastName("Smith");
238 records.add(row);
239
240 SqlParameterSource[] batch = BatchInsertUtility.createBatch(records);
241
242 BatchInsert<GeneratedAlwaysRecord> batchInsert = insertBatch(records)
243 .into(generatedAlways)
244 .map(id).toProperty("id")
245 .map(firstName).toProperty("firstName")
246 .map(lastName).toProperty("lastName")
247 .build()
248 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
249
250 int[] updateCounts = template.batchUpdate(batchInsert.getInsertStatementSQL(), batch);
251
252 assertThat(updateCounts).hasSize(2);
253 assertThat(updateCounts[0]).isEqualTo(1);
254 assertThat(updateCounts[1]).isEqualTo(1);
255 }
256
257 @Test
258 void testInsertBatchWithExtensions() {
259 NamedParameterJdbcTemplateExtensions extensions = new NamedParameterJdbcTemplateExtensions(template);
260
261 List<GeneratedAlwaysRecord> records = new ArrayList<>();
262 GeneratedAlwaysRecord row = new GeneratedAlwaysRecord();
263 row.setId(100);
264 row.setFirstName("Bob");
265 row.setLastName("Jones");
266 records.add(row);
267
268 row = new GeneratedAlwaysRecord();
269 row.setId(101);
270 row.setFirstName("Jim");
271 row.setLastName("Smith");
272 records.add(row);
273
274 Buildable<BatchInsertModel<GeneratedAlwaysRecord>> insertStatement = insertBatch(records)
275 .into(generatedAlways)
276 .map(id).toProperty("id")
277 .map(firstName).toProperty("firstName")
278 .map(lastName).toProperty("lastName");
279
280 int[] updateCounts = extensions.insertBatch(insertStatement);
281
282 assertThat(updateCounts).hasSize(2);
283 assertThat(updateCounts[0]).isEqualTo(1);
284 assertThat(updateCounts[1]).isEqualTo(1);
285 }
286
287 @Test
288 void testMultiRowInsert() {
289 NamedParameterJdbcTemplateExtensions extensions = new NamedParameterJdbcTemplateExtensions(template);
290 KeyHolder keyHolder = new GeneratedKeyHolder();
291
292 List<GeneratedAlwaysRecord> records = new ArrayList<>();
293 GeneratedAlwaysRecord row = new GeneratedAlwaysRecord();
294 row.setId(100);
295 row.setFirstName("Bob");
296 row.setLastName("Jones");
297 records.add(row);
298
299 row = new GeneratedAlwaysRecord();
300 row.setId(101);
301 row.setFirstName("Jim");
302 row.setLastName("Smith");
303 records.add(row);
304
305 Buildable<MultiRowInsertModel<GeneratedAlwaysRecord>> insertStatement = insertMultiple(records).into(generatedAlways)
306 .map(id).toProperty("id")
307 .map(firstName).toProperty("firstName")
308 .map(lastName).toProperty("lastName");
309
310 int rows = extensions.insertMultiple(insertStatement, keyHolder);
311
312 assertThat(rows).isEqualTo(2);
313 assertThat(keyHolder.getKeyList().get(0)).contains(entry("FULL_NAME", "Bob Jones"));
314 assertThat(keyHolder.getKeyList().get(1)).contains(entry("FULL_NAME", "Jim Smith"));
315 }
316
317 @Test
318 void testUpdate() {
319 UpdateStatementProvider updateStatement = update(generatedAlways)
320 .set(firstName).equalToStringConstant("Rob")
321 .where(id, isIn(1, 5, 22))
322 .build()
323 .render(RenderingStrategies.SPRING_NAMED_PARAMETER);
324
325 SqlParameterSource parameterSource = new MapSqlParameterSource(updateStatement.getParameters());
326
327 int rows = template.update(updateStatement.getUpdateStatement(), parameterSource);
328
329 assertThat(rows).isEqualTo(2);
330 }
331 }