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