1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.joins;
17
18 import static examples.joins.ItemMasterDynamicSQLSupport.itemMaster;
19 import static examples.joins.OrderLineDynamicSQLSupport.orderLine;
20 import static examples.joins.OrderMasterDynamicSQLSupport.orderDate;
21 import static examples.joins.OrderMasterDynamicSQLSupport.orderMaster;
22 import static org.assertj.core.api.Assertions.assertThat;
23 import static org.mybatis.dynamic.sql.SqlBuilder.and;
24 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
25 import static org.mybatis.dynamic.sql.SqlBuilder.on;
26 import static org.mybatis.dynamic.sql.SqlBuilder.sortColumn;
27 import static org.mybatis.dynamic.sql.SqlBuilder.where;
28
29 import java.io.InputStream;
30 import java.io.InputStreamReader;
31 import java.sql.Connection;
32 import java.sql.DriverManager;
33 import java.util.List;
34 import java.util.Map;
35
36 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
37 import org.apache.ibatis.jdbc.ScriptRunner;
38 import org.apache.ibatis.mapping.Environment;
39 import org.apache.ibatis.session.Configuration;
40 import org.apache.ibatis.session.SqlSession;
41 import org.apache.ibatis.session.SqlSessionFactory;
42 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
43 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
44 import org.junit.jupiter.api.BeforeEach;
45 import org.junit.jupiter.api.Test;
46 import org.mybatis.dynamic.sql.dsl.SelectDSL;
47 import org.mybatis.dynamic.sql.render.RenderingStrategies;
48 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
49 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
50
51 class JoinMapperV2Test {
52
53 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
54 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
55
56 private SqlSessionFactory sqlSessionFactory;
57
58 @BeforeEach
59 void setup() throws Exception {
60 Class.forName(JDBC_DRIVER);
61 try (InputStream is = getClass().getResourceAsStream("/examples/joins/CreateJoinDB.sql")) {
62 assert is != null;
63 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "");
64 InputStreamReader isr = new InputStreamReader(is)) {
65 ScriptRunner sr = new ScriptRunner(connection);
66 sr.setLogWriter(null);
67 sr.runScript(isr);
68 }
69 }
70
71 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
72 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
73 Configuration config = new Configuration(environment);
74 config.addMapper(JoinMapper.class);
75 config.addMapper(CommonSelectMapper.class);
76 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
77 }
78
79 @Test
80 void testMultipleTableJoinWithWhereClause() {
81 try (SqlSession session = sqlSessionFactory.openSession()) {
82 JoinMapper mapper = session.getMapper(JoinMapper.class);
83
84 SelectStatementProvider selectStatement = SelectDSL.select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
85 .from(orderMaster, "om")
86 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
87 .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
88 .where(orderMaster.orderId, isEqualTo(2))
89 .build()
90 .render(RenderingStrategies.MYBATIS3);
91
92 String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
93 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
94 + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}";
95 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
96
97 List<OrderMaster> rows = mapper.selectMany(selectStatement);
98
99 assertThat(rows).hasSize(1);
100 OrderMaster orderMaster = rows.get(0);
101 assertThat(orderMaster.getId()).isEqualTo(2);
102 assertThat(orderMaster.getDetails()).hasSize(2);
103 OrderDetail orderDetail = orderMaster.getDetails().get(0);
104 assertThat(orderDetail.lineNumber()).isEqualTo(1);
105 orderDetail = orderMaster.getDetails().get(1);
106 assertThat(orderDetail.lineNumber()).isEqualTo(2);
107 }
108 }
109
110 @Test
111 void testMultipleTableJoinWithApplyWhere() {
112 try (SqlSession session = sqlSessionFactory.openSession()) {
113 JoinMapper mapper = session.getMapper(JoinMapper.class);
114
115 SelectStatementProvider selectStatement = SelectDSL.select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
116 .from(orderMaster, "om")
117 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
118 .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
119 .applyWhere(where(orderMaster.orderId, isEqualTo(2)).toWhereApplier())
120 .build()
121 .render(RenderingStrategies.MYBATIS3);
122
123 String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
124 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
125 + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}";
126 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
127
128 List<OrderMaster> rows = mapper.selectMany(selectStatement);
129
130 assertThat(rows).hasSize(1);
131 OrderMaster orderMaster = rows.get(0);
132 assertThat(orderMaster.getId()).isEqualTo(2);
133 assertThat(orderMaster.getDetails()).hasSize(2);
134 OrderDetail orderDetail = orderMaster.getDetails().get(0);
135 assertThat(orderDetail.lineNumber()).isEqualTo(1);
136 orderDetail = orderMaster.getDetails().get(1);
137 assertThat(orderDetail.lineNumber()).isEqualTo(2);
138 }
139 }
140
141 @Test
142 void testMultipleTableJoinWithComplexWhereClause() {
143 try (SqlSession session = sqlSessionFactory.openSession()) {
144 JoinMapper mapper = session.getMapper(JoinMapper.class);
145
146 SelectStatementProvider selectStatement = SelectDSL.select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
147 .from(orderMaster, "om")
148 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
149 .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
150 .where(orderMaster.orderId, isEqualTo(2), and(orderLine.lineNumber, isEqualTo(2)))
151 .build()
152 .render(RenderingStrategies.MYBATIS3);
153
154 String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
155 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
156 + " where om.order_id = #{parameters.p1,jdbcType=INTEGER} and ol.line_number = #{parameters.p2,jdbcType=INTEGER}";
157 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
158
159 List<OrderMaster> rows = mapper.selectMany(selectStatement);
160
161 assertThat(rows).hasSize(1);
162 OrderMaster orderMaster = rows.get(0);
163 assertThat(orderMaster.getId()).isEqualTo(2);
164 assertThat(orderMaster.getDetails()).hasSize(1);
165 OrderDetail orderDetail = orderMaster.getDetails().get(0);
166 assertThat(orderDetail.lineNumber()).isEqualTo(2);
167 }
168 }
169
170 @Test
171 void testMultipleTableJoinWithOrderBy() {
172 try (SqlSession session = sqlSessionFactory.openSession()) {
173 JoinMapper mapper = session.getMapper(JoinMapper.class);
174
175 SelectStatementProvider selectStatement = SelectDSL.select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
176 .from(orderMaster, "om")
177 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
178 .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
179 .orderBy(orderMaster.orderId)
180 .build()
181 .render(RenderingStrategies.MYBATIS3);
182
183 String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
184 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
185 + " order by order_id";
186 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
187
188 List<OrderMaster> rows = mapper.selectMany(selectStatement);
189
190 assertThat(rows).hasSize(2);
191 OrderMaster orderMaster = rows.get(0);
192 assertThat(orderMaster.getId()).isEqualTo(1);
193 assertThat(orderMaster.getDetails()).hasSize(1);
194 OrderDetail orderDetail = orderMaster.getDetails().get(0);
195 assertThat(orderDetail.lineNumber()).isEqualTo(1);
196
197 orderMaster = rows.get(1);
198 assertThat(orderMaster.getId()).isEqualTo(2);
199 assertThat(orderMaster.getDetails()).hasSize(2);
200 orderDetail = orderMaster.getDetails().get(0);
201 assertThat(orderDetail.lineNumber()).isEqualTo(1);
202 orderDetail = orderMaster.getDetails().get(1);
203 assertThat(orderDetail.lineNumber()).isEqualTo(2);
204 }
205 }
206
207 @Test
208 void testMultipleTableJoinNoAliasWithOrderBy() {
209 try (SqlSession session = sqlSessionFactory.openSession()) {
210 JoinMapper mapper = session.getMapper(JoinMapper.class);
211
212 SelectStatementProvider selectStatement = SelectDSL.select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
213 .from(orderMaster)
214 .join(orderLine).on(orderMaster.orderId, isEqualTo(orderLine.orderId))
215 .join(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
216 .where(orderMaster.orderId, isEqualTo(2))
217 .orderBy(orderMaster.orderId)
218 .build()
219 .render(RenderingStrategies.MYBATIS3);
220
221 String expectedStatement = "select OrderMaster.order_id, OrderMaster.order_date, OrderLine.line_number, ItemMaster.description, OrderLine.quantity"
222 + " from OrderMaster join OrderLine on OrderMaster.order_id = OrderLine.order_id join ItemMaster on OrderLine.item_id = ItemMaster.item_id"
223 + " where OrderMaster.order_id = #{parameters.p1,jdbcType=INTEGER}"
224 + " order by order_id";
225 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
226
227 List<OrderMaster> rows = mapper.selectMany(selectStatement);
228
229 assertThat(rows).hasSize(1);
230 OrderMaster orderMaster = rows.get(0);
231 assertThat(orderMaster.getId()).isEqualTo(2);
232 assertThat(orderMaster.getDetails()).hasSize(2);
233 OrderDetail orderDetail = orderMaster.getDetails().get(0);
234 assertThat(orderDetail.lineNumber()).isEqualTo(1);
235 orderDetail = orderMaster.getDetails().get(1);
236 assertThat(orderDetail.lineNumber()).isEqualTo(2);
237 }
238 }
239
240 @Test
241 void testRightJoin2() {
242 try (SqlSession session = sqlSessionFactory.openSession()) {
243 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
244
245 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
246 .from(orderMaster, "om")
247 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
248 .rightJoin(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
249 .orderBy(orderLine.orderId, itemMaster.itemId)
250 .build()
251 .render(RenderingStrategies.MYBATIS3);
252
253 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
254 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
255 + " right join ItemMaster im on ol.item_id = im.item_id"
256 + " order by order_id, item_id";
257 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
258
259 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
260
261 assertThat(rows).hasSize(5);
262 Map<String, Object> row = rows.get(0);
263 assertThat(row).doesNotContainKey("ORDER_ID");
264 assertThat(row).doesNotContainKey("QUANTITY");
265 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
266 assertThat(row).containsEntry("ITEM_ID", 55);
267
268 row = rows.get(4);
269 assertThat(row).containsEntry("ORDER_ID", 2);
270 assertThat(row).containsEntry("QUANTITY", 1);
271 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
272 assertThat(row).containsEntry("ITEM_ID", 44);
273 }
274 }
275
276 @Test
277 void testRightJoin3() {
278 try (SqlSession session = sqlSessionFactory.openSession()) {
279 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
280
281 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
282 .from(orderMaster, "om")
283 .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
284 .rightJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
285 .orderBy(orderLine.orderId, itemMaster.itemId)
286 .build()
287 .render(RenderingStrategies.MYBATIS3);
288
289 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
290 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
291 + " right join ItemMaster im on ol.item_id = im.item_id"
292 + " order by order_id, item_id";
293 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
294
295 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
296
297 assertThat(rows).hasSize(5);
298 Map<String, Object> row = rows.get(0);
299 assertThat(row).doesNotContainKey("ORDER_ID");
300 assertThat(row).doesNotContainKey("QUANTITY");
301 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
302 assertThat(row).containsEntry("ITEM_ID", 55);
303
304 row = rows.get(4);
305 assertThat(row).containsEntry("ORDER_ID", 2);
306 assertThat(row).containsEntry("QUANTITY", 1);
307 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
308 assertThat(row).containsEntry("ITEM_ID", 44);
309 }
310 }
311
312 @Test
313 void testRightJoinNoAliases() {
314 try (SqlSession session = sqlSessionFactory.openSession()) {
315 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
316
317 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
318 .from(orderMaster)
319 .join(orderLine).on(orderMaster.orderId, isEqualTo(orderLine.orderId))
320 .rightJoin(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
321 .orderBy(orderLine.orderId, itemMaster.itemId)
322 .build()
323 .render(RenderingStrategies.MYBATIS3);
324
325 String expectedStatement = "select OrderLine.order_id, OrderLine.quantity, ItemMaster.item_id, ItemMaster.description"
326 + " from OrderMaster join OrderLine on OrderMaster.order_id = OrderLine.order_id"
327 + " right join ItemMaster on OrderLine.item_id = ItemMaster.item_id"
328 + " order by order_id, item_id";
329 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
330
331 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
332
333 assertThat(rows).hasSize(5);
334 Map<String, Object> row = rows.get(0);
335 assertThat(row).doesNotContainKey("ORDER_ID");
336 assertThat(row).doesNotContainKey("QUANTITY");
337 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
338 assertThat(row).containsEntry("ITEM_ID", 55);
339
340 row = rows.get(4);
341 assertThat(row).containsEntry("ORDER_ID", 2);
342 assertThat(row).containsEntry("QUANTITY", 1);
343 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
344 assertThat(row).containsEntry("ITEM_ID", 44);
345 }
346 }
347
348 @Test
349 void testLeftJoin() {
350 try (SqlSession session = sqlSessionFactory.openSession()) {
351 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
352
353 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
354 .from(itemMaster, "im")
355 .leftJoin(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
356 .orderBy(itemMaster.itemId)
357 .build()
358 .render(RenderingStrategies.MYBATIS3);
359
360 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
361 + " from ItemMaster im left join OrderLine ol on ol.item_id = im.item_id"
362 + " order by item_id";
363 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
364
365 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
366
367 assertThat(rows).hasSize(5);
368 Map<String, Object> row = rows.get(2);
369 assertThat(row).containsEntry("ORDER_ID", 1);
370 assertThat(row).containsEntry("QUANTITY", 1);
371 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
372 assertThat(row).containsEntry("ITEM_ID", 33);
373
374 row = rows.get(4);
375 assertThat(row).doesNotContainKey("ORDER_ID");
376 assertThat(row).doesNotContainKey("QUANTITY");
377 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
378 assertThat(row).containsEntry("ITEM_ID", 55);
379 }
380 }
381
382 @Test
383 void testLeftJoin2() {
384 try (SqlSession session = sqlSessionFactory.openSession()) {
385 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
386
387 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
388 .from(orderMaster, "om")
389 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
390 .leftJoin(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
391 .orderBy(orderLine.orderId, itemMaster.itemId)
392 .build()
393 .render(RenderingStrategies.MYBATIS3);
394
395 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
396 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
397 + " left join ItemMaster im on ol.item_id = im.item_id"
398 + " order by order_id, item_id";
399 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
400
401 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
402
403 assertThat(rows).hasSize(5);
404 Map<String, Object> row = rows.get(2);
405 assertThat(row).containsEntry("ORDER_ID", 2);
406 assertThat(row).containsEntry("QUANTITY", 6);
407 assertThat(row).doesNotContainKey("DESCRIPTION");
408 assertThat(row).doesNotContainKey("ITEM_ID");
409
410 row = rows.get(4);
411 assertThat(row).containsEntry("ORDER_ID", 2);
412 assertThat(row).containsEntry("QUANTITY", 1);
413 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
414 assertThat(row).containsEntry("ITEM_ID", 44);
415 }
416 }
417
418 @Test
419 void testLeftJoin3() {
420 try (SqlSession session = sqlSessionFactory.openSession()) {
421 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
422
423 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
424 .from(orderMaster, "om")
425 .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
426 .leftJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
427 .orderBy(orderLine.orderId, itemMaster.itemId)
428 .build()
429 .render(RenderingStrategies.MYBATIS3);
430
431 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
432 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
433 + " left join ItemMaster im on ol.item_id = im.item_id"
434 + " order by order_id, item_id";
435 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
436
437 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
438
439 assertThat(rows).hasSize(5);
440 Map<String, Object> row = rows.get(2);
441 assertThat(row).containsEntry("ORDER_ID", 2);
442 assertThat(row).containsEntry("QUANTITY", 6);
443 assertThat(row).doesNotContainKey("DESCRIPTION");
444 assertThat(row).doesNotContainKey("ITEM_ID");
445
446 row = rows.get(4);
447 assertThat(row).containsEntry("ORDER_ID", 2);
448 assertThat(row).containsEntry("QUANTITY", 1);
449 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
450 assertThat(row).containsEntry("ITEM_ID", 44);
451 }
452 }
453
454 @Test
455 void testLeftJoinNoAliases() {
456 try (SqlSession session = sqlSessionFactory.openSession()) {
457 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
458
459 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
460 .from(orderMaster)
461 .join(orderLine).on(orderMaster.orderId, isEqualTo(orderLine.orderId))
462 .leftJoin(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
463 .orderBy(orderLine.orderId, itemMaster.itemId)
464 .build()
465 .render(RenderingStrategies.MYBATIS3);
466
467 String expectedStatement = "select OrderLine.order_id, OrderLine.quantity, ItemMaster.item_id, ItemMaster.description"
468 + " from OrderMaster join OrderLine on OrderMaster.order_id = OrderLine.order_id"
469 + " left join ItemMaster on OrderLine.item_id = ItemMaster.item_id"
470 + " order by order_id, item_id";
471 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
472
473 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
474
475 assertThat(rows).hasSize(5);
476 Map<String, Object> row = rows.get(2);
477 assertThat(row).containsEntry("ORDER_ID", 2);
478 assertThat(row).containsEntry("QUANTITY", 6);
479 assertThat(row).doesNotContainKey("DESCRIPTION");
480 assertThat(row).doesNotContainKey("ITEM_ID");
481
482 row = rows.get(4);
483 assertThat(row).containsEntry("ORDER_ID", 2);
484 assertThat(row).containsEntry("QUANTITY", 1);
485 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
486 assertThat(row).containsEntry("ITEM_ID", 44);
487 }
488 }
489
490 @Test
491 void testFullJoin() {
492 try (SqlSession session = sqlSessionFactory.openSession()) {
493 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
494
495 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, orderLine.itemId.as("ol_itemid"), itemMaster.itemId.as("im_itemid"), itemMaster.description)
496 .from(itemMaster, "im")
497 .fullJoin(orderLine, "ol").on(itemMaster.itemId, isEqualTo(orderLine.itemId))
498 .orderBy(orderLine.orderId, sortColumn("im_itemid"))
499 .build()
500 .render(RenderingStrategies.MYBATIS3);
501
502 String expectedStatement = "select ol.order_id, ol.quantity, ol.item_id as ol_itemid, im.item_id as im_itemid, im.description"
503 + " from ItemMaster im full join OrderLine ol on im.item_id = ol.item_id"
504 + " order by order_id, im_itemid";
505 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
506
507 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
508
509 assertThat(rows).hasSize(6);
510 Map<String, Object> row = rows.get(0);
511 assertThat(row).doesNotContainKey("ORDER_ID");
512 assertThat(row).doesNotContainKey("QUANTITY");
513 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
514 assertThat(row).containsEntry("IM_ITEMID", 55);
515
516 row = rows.get(2);
517 assertThat(row).containsEntry("ORDER_ID", 1);
518 assertThat(row).containsEntry("QUANTITY", 1);
519 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
520 assertThat(row).containsEntry("IM_ITEMID", 33);
521
522 row = rows.get(3);
523 assertThat(row).containsEntry("ORDER_ID", 2);
524 assertThat(row).containsEntry("QUANTITY", 6);
525 assertThat(row).containsEntry("OL_ITEMID", 66);
526 assertThat(row).doesNotContainKey("DESCRIPTION");
527 assertThat(row).doesNotContainKey("IM_ITEMID");
528 }
529 }
530
531 @Test
532 void testFullJoin2() {
533 try (SqlSession session = sqlSessionFactory.openSession()) {
534 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
535
536 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
537 .from(orderMaster, "om")
538 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
539 .fullJoin(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
540 .orderBy(orderLine.orderId, itemMaster.itemId)
541 .build()
542 .render(RenderingStrategies.MYBATIS3);
543
544 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
545 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
546 + " full join ItemMaster im on ol.item_id = im.item_id"
547 + " order by order_id, item_id";
548 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
549
550 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
551
552 assertThat(rows).hasSize(6);
553 Map<String, Object> row = rows.get(0);
554 assertThat(row).doesNotContainKey("ORDER_ID");
555 assertThat(row).doesNotContainKey("QUANTITY");
556 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
557 assertThat(row).containsEntry("ITEM_ID", 55);
558
559 row = rows.get(3);
560 assertThat(row).containsEntry("ORDER_ID", 2);
561 assertThat(row).containsEntry("QUANTITY", 6);
562 assertThat(row).doesNotContainKey("DESCRIPTION");
563 assertThat(row).doesNotContainKey("ITEM_ID");
564
565 row = rows.get(5);
566 assertThat(row).containsEntry("ORDER_ID", 2);
567 assertThat(row).containsEntry("QUANTITY", 1);
568 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
569 assertThat(row).containsEntry("ITEM_ID", 44);
570 }
571 }
572
573 @Test
574 void testFullJoin3() {
575 try (SqlSession session = sqlSessionFactory.openSession()) {
576 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
577
578 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
579 .from(orderMaster, "om")
580 .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
581 .fullJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
582 .orderBy(orderLine.orderId, itemMaster.itemId)
583 .build()
584 .render(RenderingStrategies.MYBATIS3);
585
586 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
587 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
588 + " full join ItemMaster im on ol.item_id = im.item_id"
589 + " order by order_id, item_id";
590 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
591
592 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
593
594 assertThat(rows).hasSize(6);
595 Map<String, Object> row = rows.get(0);
596 assertThat(row).doesNotContainKey("ORDER_ID");
597 assertThat(row).doesNotContainKey("QUANTITY");
598 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
599 assertThat(row).containsEntry("ITEM_ID", 55);
600
601 row = rows.get(3);
602 assertThat(row).containsEntry("ORDER_ID", 2);
603 assertThat(row).containsEntry("QUANTITY", 6);
604 assertThat(row).doesNotContainKey("DESCRIPTION");
605 assertThat(row).doesNotContainKey("ITEM_ID");
606
607 row = rows.get(5);
608 assertThat(row).containsEntry("ORDER_ID", 2);
609 assertThat(row).containsEntry("QUANTITY", 1);
610 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
611 assertThat(row).containsEntry("ITEM_ID", 44);
612 }
613 }
614
615 @Test
616 void testFullJoin4() {
617 try (SqlSession session = sqlSessionFactory.openSession()) {
618 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
619
620 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.description)
621 .from(orderMaster, "om")
622 .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
623 .fullJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
624 .orderBy(orderLine.orderId, sortColumn("im", itemMaster.itemId))
625 .build()
626 .render(RenderingStrategies.MYBATIS3);
627
628 String expectedStatement = "select ol.order_id, ol.quantity, im.description"
629 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
630 + " full join ItemMaster im on ol.item_id = im.item_id"
631 + " order by order_id, im.item_id";
632 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
633
634 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
635
636 assertThat(rows).hasSize(6);
637 Map<String, Object> row = rows.get(0);
638 assertThat(row).doesNotContainKey("ORDER_ID");
639 assertThat(row).doesNotContainKey("QUANTITY");
640 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
641
642 row = rows.get(3);
643 assertThat(row).containsEntry("ORDER_ID", 2);
644 assertThat(row).containsEntry("QUANTITY", 6);
645 assertThat(row).doesNotContainKey("DESCRIPTION");
646
647 row = rows.get(5);
648 assertThat(row).containsEntry("ORDER_ID", 2);
649 assertThat(row).containsEntry("QUANTITY", 1);
650 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
651 }
652 }
653
654 @Test
655 void testFullJoin5() {
656 try (SqlSession session = sqlSessionFactory.openSession()) {
657 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
658
659 SelectStatementProvider selectStatement = SelectDSL.select(orderLine.orderId, orderLine.quantity, itemMaster.description)
660 .from(orderMaster, "om")
661 .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
662 .fullJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
663 .orderBy(orderLine.orderId, sortColumn("im", itemMaster.itemId).descending())
664 .build()
665 .render(RenderingStrategies.MYBATIS3);
666
667 String expectedStatement = "select ol.order_id, ol.quantity, im.description"
668 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
669 + " full join ItemMaster im on ol.item_id = im.item_id"
670 + " order by order_id, im.item_id DESC";
671 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
672
673 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
674
675 assertThat(rows).hasSize(6);
676 Map<String, Object> row = rows.get(0);
677 assertThat(row).doesNotContainKey("ORDER_ID");
678 assertThat(row).doesNotContainKey("QUANTITY");
679 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
680
681 row = rows.get(3);
682 assertThat(row).containsEntry("ORDER_ID", 2);
683 assertThat(row).containsEntry("QUANTITY", 6);
684 assertThat(row).doesNotContainKey("DESCRIPTION");
685
686 row = rows.get(5);
687 assertThat(row).containsEntry("ORDER_ID", 2);
688 assertThat(row).containsEntry("QUANTITY", 1);
689 assertThat(row).containsEntry("DESCRIPTION", "Helmet");
690 }
691 }
692 }