1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.custom_render;
17
18 import static examples.custom_render.JsonTestDynamicSqlSupport.description;
19 import static examples.custom_render.JsonTestDynamicSqlSupport.id;
20 import static examples.custom_render.JsonTestDynamicSqlSupport.info;
21 import static examples.custom_render.JsonTestDynamicSqlSupport.jsonTest;
22 import static org.assertj.core.api.Assertions.assertThat;
23 import static org.mybatis.dynamic.sql.SqlBuilder.insert;
24 import static org.mybatis.dynamic.sql.SqlBuilder.insertInto;
25 import static org.mybatis.dynamic.sql.SqlBuilder.insertMultiple;
26 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
27 import static org.mybatis.dynamic.sql.SqlBuilder.select;
28 import static org.mybatis.dynamic.sql.SqlBuilder.update;
29
30 import java.sql.JDBCType;
31 import java.util.List;
32 import java.util.Map;
33 import java.util.Optional;
34
35 import config.TestContainersConfiguration;
36 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
37 import org.apache.ibatis.mapping.Environment;
38 import org.apache.ibatis.session.Configuration;
39 import org.apache.ibatis.session.SqlSession;
40 import org.apache.ibatis.session.SqlSessionFactory;
41 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
42 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
43 import org.junit.jupiter.api.BeforeEach;
44 import org.junit.jupiter.api.Test;
45 import org.mybatis.dynamic.sql.SqlColumn;
46 import org.mybatis.dynamic.sql.insert.render.GeneralInsertStatementProvider;
47 import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
48 import org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider;
49 import org.mybatis.dynamic.sql.render.RenderingStrategies;
50 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
51 import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
52 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
53 import org.testcontainers.containers.PostgreSQLContainer;
54 import org.testcontainers.junit.jupiter.Container;
55 import org.testcontainers.junit.jupiter.Testcontainers;
56
57 @Testcontainers
58 class CustomRenderingTest {
59
60 @SuppressWarnings("resource")
61 @Container
62 private static final PostgreSQLContainer<?> postgres =
63 new PostgreSQLContainer<>(TestContainersConfiguration.POSTGRES_LATEST)
64 .withInitScript("examples/custom_render/dbInit.sql");
65
66 private SqlSessionFactory sqlSessionFactory;
67
68 @BeforeEach
69 void setUp() {
70 UnpooledDataSource ds = new UnpooledDataSource(postgres.getDriverClassName(), postgres.getJdbcUrl(),
71 postgres.getUsername(), postgres.getPassword());
72 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
73 Configuration configuration = new Configuration(environment);
74 configuration.addMapper(JsonTestMapper.class);
75 configuration.addMapper(CommonSelectMapper.class);
76 sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
77 }
78
79 @Test
80 void testInsertRecord() {
81 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
82 JsonTestMapper mapper = sqlSession.getMapper(JsonTestMapper.class);
83
84 JsonTestRecord row = new JsonTestRecord(1, "Fred",
85 "{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
86
87 InsertStatementProvider<JsonTestRecord> insertStatement = insert(row).into(jsonTest)
88 .map(id).toProperty("id")
89 .map(description).toProperty("description")
90 .map(info).toProperty("info")
91 .build()
92 .render(RenderingStrategies.MYBATIS3);
93
94 String expected = "insert into JsonTest (id, description, info) "
95 + "values (#{row.id,jdbcType=INTEGER}, #{row.description,jdbcType=VARCHAR}, "
96 + "#{row.info,jdbcType=VARCHAR}::json)";
97
98 assertThat(insertStatement.getInsertStatement()).isEqualTo(expected);
99
100 int rows = mapper.insert(insertStatement);
101 assertThat(rows).isEqualTo(1);
102
103 row = new JsonTestRecord(2, "Wilma",
104 "{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
105
106 insertStatement = insert(row).into(jsonTest)
107 .map(id).toProperty("id")
108 .map(description).toProperty("description")
109 .map(info).toProperty("info")
110 .build()
111 .render(RenderingStrategies.MYBATIS3);
112
113 rows = mapper.insert(insertStatement);
114 assertThat(rows).isEqualTo(1);
115
116 SelectStatementProvider selectStatement = select(id, description, info)
117 .from(jsonTest)
118 .orderBy(id)
119 .build()
120 .render(RenderingStrategies.MYBATIS3);
121
122 List<JsonTestRecord> records = mapper.selectMany(selectStatement);
123 assertThat(records).hasSize(2);
124 assertThat(records.get(0).info()).isEqualTo("{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
125 assertThat(records.get(1).info()).isEqualTo("{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
126 }
127 }
128
129 @Test
130 void testGeneralInsert() {
131 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
132 JsonTestMapper mapper = sqlSession.getMapper(JsonTestMapper.class);
133
134 GeneralInsertStatementProvider insertStatement = insertInto(jsonTest)
135 .set(id).toValue(1)
136 .set(description).toValue("Fred")
137 .set(info).toValue("{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}")
138 .build()
139 .render(RenderingStrategies.MYBATIS3);
140
141 String expected = "insert into JsonTest (id, description, info) "
142 + "values (#{parameters.p1,jdbcType=INTEGER}, #{parameters.p2,jdbcType=VARCHAR}, "
143 + "#{parameters.p3,jdbcType=VARCHAR}::json)";
144
145 assertThat(insertStatement.getInsertStatement()).isEqualTo(expected);
146
147 int rows = mapper.generalInsert(insertStatement);
148 assertThat(rows).isEqualTo(1);
149
150 insertStatement = insertInto(jsonTest)
151 .set(id).toValue(2)
152 .set(description).toValue("Wilma")
153 .set(info).toValue("{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}")
154 .build()
155 .render(RenderingStrategies.MYBATIS3);
156
157 rows = mapper.generalInsert(insertStatement);
158 assertThat(rows).isEqualTo(1);
159
160 SelectStatementProvider selectStatement = select(id, description, info)
161 .from(jsonTest)
162 .orderBy(id)
163 .build()
164 .render(RenderingStrategies.MYBATIS3);
165
166 List<JsonTestRecord> records = mapper.selectMany(selectStatement);
167 assertThat(records).hasSize(2);
168 assertThat(records.get(0).info()).isEqualTo("{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
169 assertThat(records.get(1).info()).isEqualTo("{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
170 }
171 }
172
173 @Test
174 void testInsertMultiple() {
175 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
176 JsonTestMapper mapper = sqlSession.getMapper(JsonTestMapper.class);
177
178 JsonTestRecord record1 = new JsonTestRecord(1, "Fred",
179 "{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
180
181 JsonTestRecord record2 = new JsonTestRecord(2, "Wilma",
182 "{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
183
184 MultiRowInsertStatementProvider<JsonTestRecord> insertStatement = insertMultiple(record1, record2)
185 .into(jsonTest)
186 .map(id).toProperty("id")
187 .map(description).toProperty("description")
188 .map(info).toProperty("info")
189 .build()
190 .render(RenderingStrategies.MYBATIS3);
191
192 String expected = "insert into JsonTest (id, description, info) "
193 + "values (#{records[0].id,jdbcType=INTEGER}, #{records[0].description,jdbcType=VARCHAR}, "
194 + "#{records[0].info,jdbcType=VARCHAR}::json), "
195 + "(#{records[1].id,jdbcType=INTEGER}, #{records[1].description,jdbcType=VARCHAR}, "
196 + "#{records[1].info,jdbcType=VARCHAR}::json)";
197
198 assertThat(insertStatement.getInsertStatement()).isEqualTo(expected);
199
200 int rows = mapper.insertMultiple(insertStatement);
201 assertThat(rows).isEqualTo(2);
202
203 SelectStatementProvider selectStatement = select(id, description, info)
204 .from(jsonTest)
205 .orderBy(id)
206 .build()
207 .render(RenderingStrategies.MYBATIS3);
208
209 List<JsonTestRecord> records = mapper.selectMany(selectStatement);
210 assertThat(records).hasSize(2);
211 assertThat(records.get(0).info()).isEqualTo("{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
212 assertThat(records.get(1).info()).isEqualTo("{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
213 }
214 }
215
216 @Test
217 void testUpdate() {
218 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
219 JsonTestMapper mapper = sqlSession.getMapper(JsonTestMapper.class);
220
221 JsonTestRecord record1 = new JsonTestRecord(1, "Fred",
222 "{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
223
224 JsonTestRecord record2 = new JsonTestRecord(2, "Wilma",
225 "{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
226
227 MultiRowInsertStatementProvider<JsonTestRecord> insertStatement = insertMultiple(record1, record2)
228 .into(jsonTest)
229 .map(id).toProperty("id")
230 .map(description).toProperty("description")
231 .map(info).toProperty("info")
232 .build()
233 .render(RenderingStrategies.MYBATIS3);
234
235 int rows = mapper.insertMultiple(insertStatement);
236 assertThat(rows).isEqualTo(2);
237
238 UpdateStatementProvider updateStatement = update(jsonTest)
239 .set(info).equalTo("{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 26}")
240 .where(id, isEqualTo(2))
241 .build()
242 .render(RenderingStrategies.MYBATIS3);
243
244 String expected = "update JsonTest "
245 + "set info = #{parameters.p1,jdbcType=VARCHAR}::json "
246 + "where id = #{parameters.p2,jdbcType=INTEGER}";
247
248 assertThat(updateStatement.getUpdateStatement()).isEqualTo(expected);
249
250 rows = mapper.update(updateStatement);
251 assertThat(rows).isEqualTo(1);
252
253 SelectStatementProvider selectStatement = select(id, description, info)
254 .from(jsonTest)
255 .orderBy(id)
256 .build()
257 .render(RenderingStrategies.MYBATIS3);
258
259 List<JsonTestRecord> records = mapper.selectMany(selectStatement);
260 assertThat(records).hasSize(2);
261 assertThat(records.get(0).info()).isEqualTo("{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
262 assertThat(records.get(1).info()).isEqualTo("{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 26}");
263 }
264 }
265
266 @Test
267 void testDeReference() {
268 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
269 JsonTestMapper mapper = sqlSession.getMapper(JsonTestMapper.class);
270
271 JsonTestRecord record1 = new JsonTestRecord(1, "Fred",
272 "{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
273
274 JsonTestRecord record2 = new JsonTestRecord(2, "Wilma",
275 "{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
276
277 MultiRowInsertStatementProvider<JsonTestRecord> insertStatement = insertMultiple(record1, record2)
278 .into(jsonTest)
279 .map(id).toProperty("id")
280 .map(description).toProperty("description")
281 .map(info).toProperty("info")
282 .build()
283 .render(RenderingStrategies.MYBATIS3);
284
285 int rows = mapper.insertMultiple(insertStatement);
286 assertThat(rows).isEqualTo(2);
287
288 SelectStatementProvider selectStatement = select(id, description, info)
289 .from(jsonTest)
290 .where(dereference(info, "age"), isEqualTo("25"))
291 .build()
292 .render(RenderingStrategies.MYBATIS3);
293
294 String expected = "select id, description, info "
295 + "from JsonTest "
296 + "where info->>'age' = #{parameters.p1,jdbcType=VARCHAR}";
297
298 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
299
300 Optional<JsonTestRecord> row = mapper.selectOne(selectStatement);
301
302 assertThat(row).hasValueSatisfying( r ->
303 assertThat(r.info())
304 .isEqualTo("{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}")
305 );
306 }
307 }
308
309 @Test
310 void testDereference2() {
311 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
312 JsonTestMapper mapper = sqlSession.getMapper(JsonTestMapper.class);
313
314 JsonTestRecord record1 = new JsonTestRecord(1, "Fred",
315 "{\"firstName\": \"Fred\", \"lastName\": \"Flintstone\", \"age\": 30}");
316
317 JsonTestRecord record2 = new JsonTestRecord(2, "Wilma",
318 "{\"firstName\": \"Wilma\", \"lastName\": \"Flintstone\", \"age\": 25}");
319
320 MultiRowInsertStatementProvider<JsonTestRecord> insertStatement = insertMultiple(record1, record2)
321 .into(jsonTest)
322 .map(id).toProperty("id")
323 .map(description).toProperty("description")
324 .map(info).toProperty("info")
325 .build()
326 .render(RenderingStrategies.MYBATIS3);
327
328 int rows = mapper.insertMultiple(insertStatement);
329 assertThat(rows).isEqualTo(2);
330
331 SelectStatementProvider selectStatement = select(dereference(info, "firstName").as("firstname"))
332 .from(jsonTest)
333 .where(dereference(info, "age"), isEqualTo("25"))
334 .build()
335 .render(RenderingStrategies.MYBATIS3);
336
337 String expected = "select info->>'firstName' as firstname "
338 + "from JsonTest "
339 + "where info->>'age' = #{parameters.p1,jdbcType=VARCHAR}";
340
341 assertThat(selectStatement.getSelectStatement()).isEqualTo(expected);
342
343 List<Map<String, Object>> records = mapper.selectManyMappedRows(selectStatement);
344 assertThat(records).hasSize(1);
345 assertThat(records.get(0)).containsEntry("firstname", "Wilma");
346 }
347 }
348
349 private <T> SqlColumn<String> dereference(SqlColumn<T> column, String attribute) {
350 return SqlColumn.of(column.name() + "->>'" + attribute + "'", column.table(), JDBCType.VARCHAR);
351 }
352 }