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