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.OrderDetailDynamicSQLSupport.orderDetail;
20 import static examples.joins.OrderLineDynamicSQLSupport.orderLine;
21 import static examples.joins.OrderMasterDynamicSQLSupport.orderMaster;
22 import static org.assertj.core.api.Assertions.assertThat;
23 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
24 import static org.mybatis.dynamic.sql.SqlBuilder.select;
25 import static org.mybatis.dynamic.sql.SqlBuilder.sortColumn;
26
27 import java.io.InputStream;
28 import java.io.InputStreamReader;
29 import java.sql.Connection;
30 import java.sql.DriverManager;
31 import java.util.List;
32 import java.util.Map;
33
34 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
35 import org.apache.ibatis.jdbc.ScriptRunner;
36 import org.apache.ibatis.mapping.Environment;
37 import org.apache.ibatis.session.Configuration;
38 import org.apache.ibatis.session.SqlSession;
39 import org.apache.ibatis.session.SqlSessionFactory;
40 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
41 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
42 import org.junit.jupiter.api.BeforeEach;
43 import org.junit.jupiter.api.Test;
44 import org.mybatis.dynamic.sql.render.RenderingStrategies;
45 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
46 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
47
48 class JoinSubQueryTest {
49
50 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
51 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
52
53 private SqlSessionFactory sqlSessionFactory;
54
55 @BeforeEach
56 void setup() throws Exception {
57 Class.forName(JDBC_DRIVER);
58 InputStream is = getClass().getResourceAsStream("/examples/joins/CreateJoinDB.sql");
59 assert is != null;
60 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "")) {
61 ScriptRunner sr = new ScriptRunner(connection);
62 sr.setLogWriter(null);
63 sr.runScript(new InputStreamReader(is));
64 }
65
66 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
67 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
68 Configuration config = new Configuration(environment);
69 config.addMapper(JoinMapper.class);
70 config.addMapper(CommonSelectMapper.class);
71 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
72 }
73
74 @Test
75 void testSingleTableJoin1() {
76 try (SqlSession session = sqlSessionFactory.openSession()) {
77 JoinMapper mapper = session.getMapper(JoinMapper.class);
78
79 SelectStatementProvider selectStatement = select(orderMaster.orderId, orderMaster.orderDate,
80 orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
81 .from(orderMaster, "om")
82 .join(select(orderDetail.orderId, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
83 .from(orderDetail),
84 "od").on(orderMaster.orderId, isEqualTo(orderDetail.orderId.qualifiedWith("od")))
85 .build()
86 .render(RenderingStrategies.MYBATIS3);
87
88 String expectedStatement = "select om.order_id, om.order_date, line_number, description, quantity"
89 + " from OrderMaster om join "
90 + "(select order_id, line_number, description, quantity from OrderDetail) od on om.order_id = od.order_id";
91 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
92
93 List<OrderMaster> rows = mapper.selectMany(selectStatement);
94
95 assertThat(rows).hasSize(2);
96 OrderMaster orderMaster = rows.get(0);
97 assertThat(orderMaster.getId()).isEqualTo(1);
98 assertThat(orderMaster.getDetails()).hasSize(2);
99 OrderDetail orderDetail = orderMaster.getDetails().get(0);
100 assertThat(orderDetail.lineNumber()).isEqualTo(1);
101 orderDetail = orderMaster.getDetails().get(1);
102 assertThat(orderDetail.lineNumber()).isEqualTo(2);
103
104 orderMaster = rows.get(1);
105 assertThat(orderMaster.getId()).isEqualTo(2);
106 assertThat(orderMaster.getDetails()).hasSize(1);
107 orderDetail = orderMaster.getDetails().get(0);
108 assertThat(orderDetail.lineNumber()).isEqualTo(1);
109 }
110 }
111
112 @Test
113 void testMultipleTableJoinWithWhereClause() {
114 try (SqlSession session = sqlSessionFactory.openSession()) {
115 JoinMapper mapper = session.getMapper(JoinMapper.class);
116
117 SelectStatementProvider selectStatement = select(orderMaster.orderId, orderMaster.orderDate,
118 orderLine.lineNumber, itemMaster.description, orderLine.quantity)
119 .from(orderMaster, "om")
120 .join(select(orderLine.orderId, orderLine.itemId, orderLine.quantity, orderLine.lineNumber)
121 .from(orderLine),
122 "ol")
123 .on(orderMaster.orderId, isEqualTo(orderLine.orderId.qualifiedWith("ol")))
124 .join(select(itemMaster.itemId, itemMaster.description)
125 .from(itemMaster),
126 "im")
127 .on(orderLine.itemId.qualifiedWith("ol"), isEqualTo(itemMaster.itemId.qualifiedWith("im")))
128 .where(orderMaster.orderId, isEqualTo(2))
129 .build()
130 .render(RenderingStrategies.MYBATIS3);
131
132 String expectedStatement = "select om.order_id, om.order_date, line_number, description, quantity"
133 + " from OrderMaster om join "
134 + "(select order_id, item_id, quantity, line_number from OrderLine) ol on om.order_id = ol.order_id "
135 + "join (select item_id, description from ItemMaster) im on ol.item_id = im.item_id"
136 + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}";
137 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
138
139 List<OrderMaster> rows = mapper.selectMany(selectStatement);
140
141 assertThat(rows).hasSize(1);
142 OrderMaster orderMaster = rows.get(0);
143 assertThat(orderMaster.getId()).isEqualTo(2);
144 assertThat(orderMaster.getDetails()).hasSize(2);
145 OrderDetail orderDetail = orderMaster.getDetails().get(0);
146 assertThat(orderDetail.lineNumber()).isEqualTo(1);
147 orderDetail = orderMaster.getDetails().get(1);
148 assertThat(orderDetail.lineNumber()).isEqualTo(2);
149 }
150 }
151
152 @Test
153 void testMultipleTableJoinWithSelectStar() {
154 try (SqlSession session = sqlSessionFactory.openSession()) {
155 JoinMapper mapper = session.getMapper(JoinMapper.class);
156
157 SelectStatementProvider selectStatement = select(orderMaster.orderId, orderMaster.orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
158 .from(orderMaster, "om")
159 .join(select(orderLine.allColumns()).from(orderLine), "ol")
160 .on(orderMaster.orderId, isEqualTo(orderLine.orderId.qualifiedWith("ol")))
161 .join(select(itemMaster.allColumns()).from(itemMaster), "im")
162 .on(orderLine.itemId.qualifiedWith("ol"), isEqualTo(itemMaster.itemId.qualifiedWith("im")))
163 .where(orderMaster.orderId, isEqualTo(2))
164 .orderBy(orderMaster.orderId)
165 .build()
166 .render(RenderingStrategies.MYBATIS3);
167
168 String expectedStatement = "select om.order_id, om.order_date, line_number, description, quantity"
169 + " from OrderMaster om join (select * from OrderLine) ol on om.order_id = ol.order_id"
170 + " join (select * from ItemMaster) im on ol.item_id = im.item_id"
171 + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}"
172 + " order by order_id";
173 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
174
175 List<OrderMaster> rows = mapper.selectMany(selectStatement);
176
177 assertThat(rows).hasSize(1);
178 OrderMaster orderMaster = rows.get(0);
179 assertThat(orderMaster.getId()).isEqualTo(2);
180 assertThat(orderMaster.getDetails()).hasSize(2);
181
182 OrderDetail orderDetail = orderMaster.getDetails().get(0);
183 assertThat(orderDetail.lineNumber()).isEqualTo(1);
184
185 orderDetail = orderMaster.getDetails().get(1);
186 assertThat(orderDetail.lineNumber()).isEqualTo(2);
187 }
188 }
189
190 @Test
191 void testRightJoin() {
192 try (SqlSession session = sqlSessionFactory.openSession()) {
193 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
194
195 SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity,
196 itemMaster.itemId.qualifiedWith("im"), itemMaster.description)
197 .from(orderLine, "ol")
198 .rightJoin(select(itemMaster.allColumns()).from(itemMaster), "im")
199 .on(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
200 .orderBy(itemMaster.itemId)
201 .build()
202 .render(RenderingStrategies.MYBATIS3);
203
204 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, description"
205 + " from OrderLine ol right join (select * from ItemMaster) im on ol.item_id = im.item_id"
206 + " order by item_id";
207 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
208
209 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
210
211 assertThat(rows).hasSize(5);
212 Map<String, Object> row = rows.get(2);
213 assertThat(row).containsEntry("ORDER_ID", 1);
214 assertThat(row).containsEntry("QUANTITY", 1);
215 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
216 assertThat(row).containsEntry("ITEM_ID", 33);
217
218 row = rows.get(4);
219 assertThat(row).doesNotContainKey("ORDER_ID");
220 assertThat(row).doesNotContainKey("QUANTITY");
221 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
222 assertThat(row).containsEntry("ITEM_ID", 55);
223 }
224 }
225
226 @Test
227 void testRightJoin2() {
228 try (SqlSession session = sqlSessionFactory.openSession()) {
229 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
230
231 SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity,
232 itemMaster.itemId.qualifiedWith(("im")), itemMaster.description)
233 .from(orderMaster, "om")
234 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
235 .rightJoin(select(itemMaster.allColumns()).from(itemMaster), "im")
236 .on(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
237 .orderBy(orderLine.orderId, itemMaster.itemId)
238 .build()
239 .render(RenderingStrategies.MYBATIS3);
240
241 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, description"
242 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
243 + " right join (select * from ItemMaster) im on ol.item_id = im.item_id"
244 + " order by order_id, item_id";
245 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
246
247 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
248
249 assertThat(rows).hasSize(5);
250 Map<String, Object> row = rows.get(0);
251 assertThat(row).doesNotContainKey("ORDER_ID");
252 assertThat(row).doesNotContainKey("QUANTITY");
253 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
254 assertThat(row).containsEntry("ITEM_ID", 55);
255
256 row = rows.get(4);
257 assertThat(row).containsEntry("ORDER_ID", 2);
258 assertThat(row).containsEntry("QUANTITY", 1);
259 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
260 assertThat(row).containsEntry("ITEM_ID", 44);
261 }
262 }
263
264 @Test
265 void testLeftJoin() {
266 try (SqlSession session = sqlSessionFactory.openSession()) {
267 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
268
269 SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity,
270 itemMaster.itemId.qualifiedWith("im"), itemMaster.description)
271 .from(itemMaster, "im")
272 .leftJoin(select(orderLine.allColumns()).from(orderLine), "ol")
273 .on(orderLine.itemId.qualifiedWith("ol"), isEqualTo(itemMaster.itemId))
274 .orderBy(itemMaster.itemId)
275 .build()
276 .render(RenderingStrategies.MYBATIS3);
277
278 String expectedStatement = "select order_id, quantity, im.item_id, im.description"
279 + " from ItemMaster im"
280 + " left join (select * from OrderLine) ol on ol.item_id = im.item_id"
281 + " order by item_id";
282 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
283
284 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
285
286 assertThat(rows).hasSize(5);
287 Map<String, Object> row = rows.get(2);
288 assertThat(row).containsEntry("ORDER_ID", 1);
289 assertThat(row).containsEntry("QUANTITY", 1);
290 assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
291 assertThat(row).containsEntry("ITEM_ID", 33);
292
293 row = rows.get(4);
294 assertThat(row).doesNotContainKey("ORDER_ID");
295 assertThat(row).doesNotContainKey("QUANTITY");
296 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
297 assertThat(row).containsEntry("ITEM_ID", 55);
298 }
299 }
300
301 @Test
302 void testLeftJoin2() {
303 try (SqlSession session = sqlSessionFactory.openSession()) {
304 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
305
306 SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity,
307 itemMaster.itemId.qualifiedWith("im"), itemMaster.description)
308 .from(orderMaster, "om")
309 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
310 .leftJoin(select(itemMaster.allColumns()).from(itemMaster), "im")
311 .on(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
312 .orderBy(orderLine.orderId, itemMaster.itemId)
313 .build()
314 .render(RenderingStrategies.MYBATIS3);
315
316 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, description"
317 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
318 + " left join (select * from ItemMaster) im on ol.item_id = im.item_id"
319 + " order by order_id, item_id";
320 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
321
322 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
323
324 assertThat(rows).hasSize(5);
325 Map<String, Object> row = rows.get(2);
326 assertThat(row).containsEntry("ORDER_ID", 2);
327 assertThat(row).containsEntry("QUANTITY", 6);
328 assertThat(row).doesNotContainKey("DESCRIPTION");
329 assertThat(row).doesNotContainKey("ITEM_ID");
330
331 row = rows.get(4);
332 assertThat(row).containsEntry("ORDER_ID", 2);
333 assertThat(row).containsEntry("QUANTITY", 1);
334 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
335 assertThat(row).containsEntry("ITEM_ID", 44);
336 }
337 }
338
339 @Test
340 void testFullJoin() {
341 try (SqlSession session = sqlSessionFactory.openSession()) {
342 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
343
344 SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity,
345 orderLine.itemId.as("ol_itemid").qualifiedWith("ol"), itemMaster.itemId.as("im_itemid"), itemMaster.description)
346 .from(itemMaster, "im")
347 .fullJoin(select(orderLine.allColumns()).from(orderLine), "ol")
348 .on(itemMaster.itemId, isEqualTo(orderLine.itemId.qualifiedWith("ol")))
349 .orderBy(orderLine.orderId, sortColumn("im_itemid"))
350 .build()
351 .render(RenderingStrategies.MYBATIS3);
352
353 String expectedStatement = "select order_id, quantity, ol.item_id as ol_itemid, im.item_id as im_itemid, im.description"
354 + " from ItemMaster im"
355 + " full join (select * from OrderLine) ol on im.item_id = ol.item_id"
356 + " order by order_id, im_itemid";
357 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
358
359 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
360
361 assertThat(rows).hasSize(6);
362 Map<String, Object> row = rows.get(0);
363 assertThat(row).doesNotContainKey("ORDER_ID");
364 assertThat(row).doesNotContainKey("QUANTITY");
365 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
366 assertThat(row).containsEntry("IM_ITEMID", 55);
367
368 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("IM_ITEMID", 33);
373
374 row = rows.get(3);
375 assertThat(row).containsEntry("ORDER_ID", 2);
376 assertThat(row).containsEntry("QUANTITY", 6);
377 assertThat(row).containsEntry("OL_ITEMID", 66);
378 assertThat(row).doesNotContainKey("DESCRIPTION");
379 assertThat(row).doesNotContainKey("IM_ITEMID");
380 }
381 }
382
383 @Test
384 void testFullJoin2() {
385 try (SqlSession session = sqlSessionFactory.openSession()) {
386 CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
387
388 SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity,
389 itemMaster.itemId.qualifiedWith("im"), itemMaster.description)
390 .from(orderMaster, "om")
391 .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
392 .fullJoin(select(itemMaster.allColumns()).from(itemMaster), "im")
393 .on(orderLine.itemId, isEqualTo(itemMaster.itemId.qualifiedWith("im")))
394 .orderBy(orderLine.orderId, itemMaster.itemId)
395 .build()
396 .render(RenderingStrategies.MYBATIS3);
397
398 String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, description"
399 + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
400 + " full join (select * from ItemMaster) im on ol.item_id = im.item_id"
401 + " order by order_id, item_id";
402 assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
403
404 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
405
406 assertThat(rows).hasSize(6);
407 Map<String, Object> row = rows.get(0);
408 assertThat(row).doesNotContainKey("ORDER_ID");
409 assertThat(row).doesNotContainKey("QUANTITY");
410 assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
411 assertThat(row).containsEntry("ITEM_ID", 55);
412
413 row = rows.get(3);
414 assertThat(row).containsEntry("ORDER_ID", 2);
415 assertThat(row).containsEntry("QUANTITY", 6);
416 assertThat(row).doesNotContainKey("DESCRIPTION");
417 assertThat(row).doesNotContainKey("ITEM_ID");
418
419 row = rows.get(5);
420 assertThat(row).containsEntry("ORDER_ID", 2);
421 assertThat(row).containsEntry("QUANTITY", 1);
422 assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
423 assertThat(row).containsEntry("ITEM_ID", 44);
424 }
425 }
426 }