View Javadoc
1   /*
2    *    Copyright 2016-2026 the original author or authors.
3    *
4    *    Licensed under the Apache License, Version 2.0 (the "License");
5    *    you may not use this file except in compliance with the License.
6    *    You may obtain a copy of the License at
7    *
8    *       https://www.apache.org/licenses/LICENSE-2.0
9    *
10   *    Unless required by applicable law or agreed to in writing, software
11   *    distributed under the License is distributed on an "AS IS" BASIS,
12   *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   *    See the License for the specific language governing permissions and
14   *    limitations under the License.
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.orderDate;
22  import static examples.joins.OrderMasterDynamicSQLSupport.orderMaster;
23  import static examples.joins.UserDynamicSQLSupport.user;
24  import static org.assertj.core.api.Assertions.assertThat;
25  import static org.assertj.core.api.Assertions.assertThatExceptionOfType;
26  import static org.assertj.core.api.Assertions.entry;
27  import static org.mybatis.dynamic.sql.SqlBuilder.*;
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.exception.DuplicateTableAliasException;
47  import org.mybatis.dynamic.sql.render.RenderingStrategies;
48  import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
49  import org.mybatis.dynamic.sql.util.Messages;
50  import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
51  import org.mybatis.dynamic.sql.where.condition.IsEqualToColumn;
52  
53  class JoinMapperTest {
54  
55      private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
56      private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
57  
58      private SqlSessionFactory sqlSessionFactory;
59  
60      @BeforeEach
61      void setup() throws Exception {
62          Class.forName(JDBC_DRIVER);
63          try (InputStream is = getClass().getResourceAsStream("/examples/joins/CreateJoinDB.sql")) {
64              assert is != null;
65              try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "");
66                   InputStreamReader isr = new InputStreamReader(is)) {
67                  ScriptRunner sr = new ScriptRunner(connection);
68                  sr.setLogWriter(null);
69                  sr.runScript(isr);
70              }
71          }
72  
73          UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
74          Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
75          Configuration config = new Configuration(environment);
76          config.addMapper(JoinMapper.class);
77          config.addMapper(CommonSelectMapper.class);
78          sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
79      }
80  
81      @Test
82      void testSingleTableJoin1() {
83          try (SqlSession session = sqlSessionFactory.openSession()) {
84              JoinMapper mapper = session.getMapper(JoinMapper.class);
85  
86              SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
87                      .from(orderMaster, "om")
88                      .join(orderDetail, "od").on(orderMaster.orderId, isEqualTo(orderDetail.orderId))
89                      .build()
90                      .render(RenderingStrategies.MYBATIS3);
91  
92              String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
93                      + " from OrderMaster om join OrderDetail od on om.order_id = od.order_id";
94              assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
95  
96              List<OrderMaster> rows = mapper.selectMany(selectStatement);
97  
98              assertThat(rows).hasSize(2);
99              OrderMaster orderMaster = rows.get(0);
100             assertThat(orderMaster.getId()).isEqualTo(1);
101             assertThat(orderMaster.getDetails()).hasSize(2);
102             OrderDetail orderDetail = orderMaster.getDetails().get(0);
103             assertThat(orderDetail.lineNumber()).isEqualTo(1);
104             orderDetail = orderMaster.getDetails().get(1);
105             assertThat(orderDetail.lineNumber()).isEqualTo(2);
106 
107             orderMaster = rows.get(1);
108             assertThat(orderMaster.getId()).isEqualTo(2);
109             assertThat(orderMaster.getDetails()).hasSize(1);
110             orderDetail = orderMaster.getDetails().get(0);
111             assertThat(orderDetail.lineNumber()).isEqualTo(1);
112         }
113     }
114 
115     @Test
116     void testSingleTableJoin2() {
117         try (SqlSession session = sqlSessionFactory.openSession()) {
118             JoinMapper mapper = session.getMapper(JoinMapper.class);
119 
120             SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
121                     .from(orderMaster, "om")
122                     .join(orderDetail, "od", on(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
123                     .build()
124                     .render(RenderingStrategies.MYBATIS3);
125 
126             String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
127                     + " from OrderMaster om join OrderDetail od on om.order_id = od.order_id";
128             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
129 
130             List<OrderMaster> rows = mapper.selectMany(selectStatement);
131 
132             assertThat(rows).hasSize(2);
133             OrderMaster orderMaster = rows.get(0);
134             assertThat(orderMaster.getId()).isEqualTo(1);
135             assertThat(orderMaster.getDetails()).hasSize(2);
136             OrderDetail orderDetail = orderMaster.getDetails().get(0);
137             assertThat(orderDetail.lineNumber()).isEqualTo(1);
138             orderDetail = orderMaster.getDetails().get(1);
139             assertThat(orderDetail.lineNumber()).isEqualTo(2);
140 
141             orderMaster = rows.get(1);
142             assertThat(orderMaster.getId()).isEqualTo(2);
143             assertThat(orderMaster.getDetails()).hasSize(1);
144             orderDetail = orderMaster.getDetails().get(0);
145             assertThat(orderDetail.lineNumber()).isEqualTo(1);
146         }
147     }
148 
149     @Test
150     void testCompoundJoin1() {
151         // this is a nonsensical join, but it does test the "and" capability
152         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
153                 .from(orderMaster, "om")
154                 .join(orderDetail, "od").on(orderMaster.orderId, isEqualTo(orderDetail.orderId), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
155                 .build()
156                 .render(RenderingStrategies.MYBATIS3);
157 
158         String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
159                 + " from OrderMaster om join OrderDetail od on om.order_id = od.order_id and om.order_id = od.order_id";
160         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
161     }
162 
163     @Test
164     void testCompoundJoin2() {
165         // this is a nonsensical join, but it does test the "and" capability
166         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
167                 .from(orderMaster, "om")
168                 .join(orderDetail, "od").on(orderMaster.orderId, isEqualTo(orderDetail.orderId))
169                 .configureStatement(c -> c.setNonRenderingWhereClauseAllowed(true))
170                 .and(orderMaster.orderId, isEqualTo(orderDetail.orderId))
171                 .build()
172                 .render(RenderingStrategies.MYBATIS3);
173 
174         String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
175                 + " from OrderMaster om join OrderDetail od on om.order_id = od.order_id and om.order_id = od.order_id";
176         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
177     }
178 
179     @Test
180     void testCompoundJoin3() {
181         // this is a nonsensical join, but it does test the "and" capability
182         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
183                 .from(orderMaster, "om")
184                 .join(orderDetail, "od", on(orderMaster.orderId, isEqualTo(orderDetail.orderId)), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
185                 .build()
186                 .render(RenderingStrategies.MYBATIS3);
187 
188         String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
189                 + " from OrderMaster om join OrderDetail od on om.order_id = od.order_id and om.order_id = od.order_id";
190         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
191     }
192 
193     @Test
194     void testCompoundJoin4() {
195         // this is a nonsensical join, but it does test the "and" capability
196         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
197                 .from(orderMaster, "om")
198                 .leftJoin(orderDetail, "od", on(orderMaster.orderId, isEqualTo(orderDetail.orderId)), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
199                 .build()
200                 .render(RenderingStrategies.MYBATIS3);
201 
202         String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
203                 + " from OrderMaster om left join OrderDetail od on om.order_id = od.order_id and om.order_id = od.order_id";
204         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
205     }
206 
207     @Test
208     void testCompoundJoin4WithoutAliases() {
209         // this is a nonsensical join, but it does test the "and" capability
210         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
211                 .from(orderMaster)
212                 .leftJoin(orderDetail, on(orderMaster.orderId, isEqualTo(orderDetail.orderId)), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
213                 .build()
214                 .render(RenderingStrategies.MYBATIS3);
215 
216         String expectedStatement = "select OrderMaster.order_id, OrderMaster.order_date, OrderDetail.line_number, OrderDetail.description, OrderDetail.quantity"
217                 + " from OrderMaster left join OrderDetail on OrderMaster.order_id = OrderDetail.order_id and OrderMaster.order_id = OrderDetail.order_id";
218         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
219     }
220 
221     @Test
222     void testCompoundJoin5() {
223         // this is a nonsensical join, but it does test the "and" capability
224         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
225                 .from(orderMaster, "om")
226                 .rightJoin(orderDetail, "od", on(orderMaster.orderId, isEqualTo(orderDetail.orderId)), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
227                 .build()
228                 .render(RenderingStrategies.MYBATIS3);
229 
230         String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
231                 + " from OrderMaster om right join OrderDetail od on om.order_id = od.order_id and om.order_id = od.order_id";
232         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
233     }
234 
235     @Test
236     void testCompoundJoin5WithoutAliases() {
237         // this is a nonsensical join, but it does test the "and" capability
238         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
239                 .from(orderMaster)
240                 .rightJoin(orderDetail, on(orderMaster.orderId, isEqualTo(orderDetail.orderId)), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
241                 .build()
242                 .render(RenderingStrategies.MYBATIS3);
243 
244         String expectedStatement = "select OrderMaster.order_id, OrderMaster.order_date, OrderDetail.line_number, OrderDetail.description, OrderDetail.quantity"
245                 + " from OrderMaster right join OrderDetail on OrderMaster.order_id = OrderDetail.order_id and OrderMaster.order_id = OrderDetail.order_id";
246         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
247     }
248 
249     @Test
250     void testCompoundJoin6() {
251         // this is a nonsensical join, but it does test the "and" capability
252         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
253                 .from(orderMaster, "om")
254                 .fullJoin(orderDetail, "od", on(orderMaster.orderId, isEqualTo(orderDetail.orderId)), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
255                 .build()
256                 .render(RenderingStrategies.MYBATIS3);
257 
258         String expectedStatement = "select om.order_id, om.order_date, od.line_number, od.description, od.quantity"
259                 + " from OrderMaster om full join OrderDetail od on om.order_id = od.order_id and om.order_id = od.order_id";
260         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
261     }
262 
263     @Test
264     void testCompoundJoin6WithoutAliases() {
265         // this is a nonsensical join, but it does test the "and" capability
266         SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
267                 .from(orderMaster)
268                 .fullJoin(orderDetail, on(orderMaster.orderId, isEqualTo(orderDetail.orderId)), and(orderMaster.orderId, isEqualTo(orderDetail.orderId)))
269                 .build()
270                 .render(RenderingStrategies.MYBATIS3);
271 
272         String expectedStatement = "select OrderMaster.order_id, OrderMaster.order_date, OrderDetail.line_number, OrderDetail.description, OrderDetail.quantity"
273                 + " from OrderMaster full join OrderDetail on OrderMaster.order_id = OrderDetail.order_id and OrderMaster.order_id = OrderDetail.order_id";
274         assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
275     }
276 
277     @Test
278     void testMultipleTableJoinWithWhereClause() {
279         try (SqlSession session = sqlSessionFactory.openSession()) {
280             JoinMapper mapper = session.getMapper(JoinMapper.class);
281 
282             SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
283                     .from(orderMaster, "om")
284                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
285                     .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
286                     .where(orderMaster.orderId, isEqualTo(2))
287                     .build()
288                     .render(RenderingStrategies.MYBATIS3);
289 
290             String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
291                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
292                     + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}";
293             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
294 
295             List<OrderMaster> rows = mapper.selectMany(selectStatement);
296 
297             assertThat(rows).hasSize(1);
298             OrderMaster orderMaster = rows.get(0);
299             assertThat(orderMaster.getId()).isEqualTo(2);
300             assertThat(orderMaster.getDetails()).hasSize(2);
301             OrderDetail orderDetail = orderMaster.getDetails().get(0);
302             assertThat(orderDetail.lineNumber()).isEqualTo(1);
303             orderDetail = orderMaster.getDetails().get(1);
304             assertThat(orderDetail.lineNumber()).isEqualTo(2);
305         }
306     }
307 
308     @Test
309     void testMultipleTableJoinWithApplyWhere() {
310         try (SqlSession session = sqlSessionFactory.openSession()) {
311             JoinMapper mapper = session.getMapper(JoinMapper.class);
312 
313             SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
314                     .from(orderMaster, "om")
315                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
316                     .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
317                     .applyWhere(where(orderMaster.orderId, isEqualTo(2)).toWhereApplier())
318                     .build()
319                     .render(RenderingStrategies.MYBATIS3);
320 
321             String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
322                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
323                     + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}";
324             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
325 
326             List<OrderMaster> rows = mapper.selectMany(selectStatement);
327 
328             assertThat(rows).hasSize(1);
329             OrderMaster orderMaster = rows.get(0);
330             assertThat(orderMaster.getId()).isEqualTo(2);
331             assertThat(orderMaster.getDetails()).hasSize(2);
332             OrderDetail orderDetail = orderMaster.getDetails().get(0);
333             assertThat(orderDetail.lineNumber()).isEqualTo(1);
334             orderDetail = orderMaster.getDetails().get(1);
335             assertThat(orderDetail.lineNumber()).isEqualTo(2);
336         }
337     }
338 
339     @Test
340     void testMultipleTableJoinCount() {
341         try (SqlSession session = sqlSessionFactory.openSession()) {
342             JoinMapper mapper = session.getMapper(JoinMapper.class);
343 
344             SelectStatementProvider selectStatement = countFrom(orderMaster, "om")
345                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
346                     .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
347                     .where(orderMaster.orderId, isEqualTo(2))
348                     .build()
349                     .render(RenderingStrategies.MYBATIS3);
350 
351             String expectedStatement = "select count(*)"
352                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
353                     + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}";
354             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
355 
356             long count = mapper.count(selectStatement);
357 
358             assertThat(count).isEqualTo(2);
359         }
360     }
361 
362     @Test
363     void testMultipleTableJoinCountNoSecondAlias() {
364         try (SqlSession session = sqlSessionFactory.openSession()) {
365             JoinMapper mapper = session.getMapper(JoinMapper.class);
366 
367             SelectStatementProvider selectStatement = countFrom(orderMaster, "om")
368                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
369                     .join(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
370                     .where(orderMaster.orderId, isEqualTo(2))
371                     .build()
372                     .render(RenderingStrategies.MYBATIS3);
373 
374             String expectedStatement = "select count(*)"
375                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster on ol.item_id = ItemMaster.item_id"
376                     + " where om.order_id = #{parameters.p1,jdbcType=INTEGER}";
377             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
378 
379             long count = mapper.count(selectStatement);
380 
381             assertThat(count).isEqualTo(2);
382         }
383     }
384 
385     @Test
386     void testMultipleTableJoinWithComplexWhereClause() {
387         try (SqlSession session = sqlSessionFactory.openSession()) {
388             JoinMapper mapper = session.getMapper(JoinMapper.class);
389 
390             SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
391                     .from(orderMaster, "om")
392                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
393                     .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
394                     .where(orderMaster.orderId, isEqualTo(2), and(orderLine.lineNumber, isEqualTo(2)))
395                     .build()
396                     .render(RenderingStrategies.MYBATIS3);
397 
398             String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
399                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
400                     + " where om.order_id = #{parameters.p1,jdbcType=INTEGER} and ol.line_number = #{parameters.p2,jdbcType=INTEGER}";
401             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
402 
403             List<OrderMaster> rows = mapper.selectMany(selectStatement);
404 
405             assertThat(rows).hasSize(1);
406             OrderMaster orderMaster = rows.get(0);
407             assertThat(orderMaster.getId()).isEqualTo(2);
408             assertThat(orderMaster.getDetails()).hasSize(1);
409             OrderDetail orderDetail = orderMaster.getDetails().get(0);
410             assertThat(orderDetail.lineNumber()).isEqualTo(2);
411         }
412     }
413 
414     @Test
415     void testMultipleTableJoinWithOrderBy() {
416         try (SqlSession session = sqlSessionFactory.openSession()) {
417             JoinMapper mapper = session.getMapper(JoinMapper.class);
418 
419             SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
420                     .from(orderMaster, "om")
421                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
422                     .join(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
423                     .orderBy(orderMaster.orderId)
424                     .build()
425                     .render(RenderingStrategies.MYBATIS3);
426 
427             String expectedStatement = "select om.order_id, om.order_date, ol.line_number, im.description, ol.quantity"
428                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id join ItemMaster im on ol.item_id = im.item_id"
429                     + " order by order_id";
430             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
431 
432             List<OrderMaster> rows = mapper.selectMany(selectStatement);
433 
434             assertThat(rows).hasSize(2);
435             OrderMaster orderMaster = rows.get(0);
436             assertThat(orderMaster.getId()).isEqualTo(1);
437             assertThat(orderMaster.getDetails()).hasSize(1);
438             OrderDetail orderDetail = orderMaster.getDetails().get(0);
439             assertThat(orderDetail.lineNumber()).isEqualTo(1);
440 
441             orderMaster = rows.get(1);
442             assertThat(orderMaster.getId()).isEqualTo(2);
443             assertThat(orderMaster.getDetails()).hasSize(2);
444             orderDetail = orderMaster.getDetails().get(0);
445             assertThat(orderDetail.lineNumber()).isEqualTo(1);
446             orderDetail = orderMaster.getDetails().get(1);
447             assertThat(orderDetail.lineNumber()).isEqualTo(2);
448         }
449     }
450 
451     @Test
452     void testMultipleTableJoinNoAliasWithOrderBy() {
453         try (SqlSession session = sqlSessionFactory.openSession()) {
454             JoinMapper mapper = session.getMapper(JoinMapper.class);
455 
456             SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderLine.lineNumber, itemMaster.description, orderLine.quantity)
457                     .from(orderMaster)
458                     .join(orderLine).on(orderMaster.orderId, isEqualTo(orderLine.orderId))
459                     .join(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
460                     .where(orderMaster.orderId, isEqualTo(2))
461                     .orderBy(orderMaster.orderId)
462                     .build()
463                     .render(RenderingStrategies.MYBATIS3);
464 
465             String expectedStatement = "select OrderMaster.order_id, OrderMaster.order_date, OrderLine.line_number, ItemMaster.description, OrderLine.quantity"
466                     + " from OrderMaster join OrderLine on OrderMaster.order_id = OrderLine.order_id join ItemMaster on OrderLine.item_id = ItemMaster.item_id"
467                     + " where OrderMaster.order_id = #{parameters.p1,jdbcType=INTEGER}"
468                     + " order by order_id";
469             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
470 
471             List<OrderMaster> rows = mapper.selectMany(selectStatement);
472 
473             assertThat(rows).hasSize(1);
474             OrderMaster orderMaster = rows.get(0);
475             assertThat(orderMaster.getId()).isEqualTo(2);
476             assertThat(orderMaster.getDetails()).hasSize(2);
477             OrderDetail orderDetail = orderMaster.getDetails().get(0);
478             assertThat(orderDetail.lineNumber()).isEqualTo(1);
479             orderDetail = orderMaster.getDetails().get(1);
480             assertThat(orderDetail.lineNumber()).isEqualTo(2);
481         }
482     }
483 
484     @Test
485     void testRightJoin() {
486         try (SqlSession session = sqlSessionFactory.openSession()) {
487             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
488 
489             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
490                     .from(orderLine, "ol")
491                     .rightJoin(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
492                     .orderBy(itemMaster.itemId)
493                     .build()
494                     .render(RenderingStrategies.MYBATIS3);
495 
496             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
497                     + " from OrderLine ol right join ItemMaster im on ol.item_id = im.item_id"
498                     + " order by item_id";
499             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
500 
501             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
502 
503             assertThat(rows).hasSize(5);
504             Map<String, Object> row = rows.get(2);
505             assertThat(row).containsEntry("ORDER_ID", 1);
506             assertThat(row).containsEntry("QUANTITY", 1);
507             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
508             assertThat(row).containsEntry("ITEM_ID", 33);
509 
510             row = rows.get(4);
511             assertThat(row).doesNotContainKey("ORDER_ID");
512             assertThat(row).doesNotContainKey("QUANTITY");
513             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
514             assertThat(row).containsEntry("ITEM_ID", 55);
515         }
516     }
517 
518     @Test
519     void testRightJoin2() {
520         try (SqlSession session = sqlSessionFactory.openSession()) {
521             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
522 
523             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
524                     .from(orderMaster, "om")
525                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
526                     .rightJoin(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
527                     .orderBy(orderLine.orderId, itemMaster.itemId)
528                     .build()
529                     .render(RenderingStrategies.MYBATIS3);
530 
531             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
532                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
533                     + " right join ItemMaster im on ol.item_id = im.item_id"
534                     + " order by order_id, item_id";
535             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
536 
537             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
538 
539             assertThat(rows).hasSize(5);
540             Map<String, Object> row = rows.get(0);
541             assertThat(row).doesNotContainKey("ORDER_ID");
542             assertThat(row).doesNotContainKey("QUANTITY");
543             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
544             assertThat(row).containsEntry("ITEM_ID", 55);
545 
546             row = rows.get(4);
547             assertThat(row).containsEntry("ORDER_ID", 2);
548             assertThat(row).containsEntry("QUANTITY", 1);
549             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
550             assertThat(row).containsEntry("ITEM_ID", 44);
551         }
552     }
553 
554     @Test
555     void testRightJoin3() {
556         try (SqlSession session = sqlSessionFactory.openSession()) {
557             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
558 
559             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
560                     .from(orderMaster, "om")
561                     .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
562                     .rightJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
563                     .orderBy(orderLine.orderId, itemMaster.itemId)
564                     .build()
565                     .render(RenderingStrategies.MYBATIS3);
566 
567             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
568                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
569                     + " right join ItemMaster im on ol.item_id = im.item_id"
570                     + " order by order_id, item_id";
571             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
572 
573             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
574 
575             assertThat(rows).hasSize(5);
576             Map<String, Object> row = rows.get(0);
577             assertThat(row).doesNotContainKey("ORDER_ID");
578             assertThat(row).doesNotContainKey("QUANTITY");
579             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
580             assertThat(row).containsEntry("ITEM_ID", 55);
581 
582             row = rows.get(4);
583             assertThat(row).containsEntry("ORDER_ID", 2);
584             assertThat(row).containsEntry("QUANTITY", 1);
585             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
586             assertThat(row).containsEntry("ITEM_ID", 44);
587         }
588     }
589 
590     @Test
591     void testRightJoinNoAliases() {
592         try (SqlSession session = sqlSessionFactory.openSession()) {
593             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
594 
595             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
596                     .from(orderMaster)
597                     .join(orderLine).on(orderMaster.orderId, isEqualTo(orderLine.orderId))
598                     .rightJoin(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
599                     .orderBy(orderLine.orderId, itemMaster.itemId)
600                     .build()
601                     .render(RenderingStrategies.MYBATIS3);
602 
603             String expectedStatement = "select OrderLine.order_id, OrderLine.quantity, ItemMaster.item_id, ItemMaster.description"
604                     + " from OrderMaster join OrderLine on OrderMaster.order_id = OrderLine.order_id"
605                     + " right join ItemMaster on OrderLine.item_id = ItemMaster.item_id"
606                     + " order by order_id, item_id";
607             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
608 
609             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
610 
611             assertThat(rows).hasSize(5);
612             Map<String, Object> row = rows.get(0);
613             assertThat(row).doesNotContainKey("ORDER_ID");
614             assertThat(row).doesNotContainKey("QUANTITY");
615             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
616             assertThat(row).containsEntry("ITEM_ID", 55);
617 
618             row = rows.get(4);
619             assertThat(row).containsEntry("ORDER_ID", 2);
620             assertThat(row).containsEntry("QUANTITY", 1);
621             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
622             assertThat(row).containsEntry("ITEM_ID", 44);
623         }
624     }
625 
626     @Test
627     void testLeftJoin() {
628         try (SqlSession session = sqlSessionFactory.openSession()) {
629             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
630 
631             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
632                     .from(itemMaster, "im")
633                     .leftJoin(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
634                     .orderBy(itemMaster.itemId)
635                     .build()
636                     .render(RenderingStrategies.MYBATIS3);
637 
638             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
639                     + " from ItemMaster im left join OrderLine ol on ol.item_id = im.item_id"
640                     + " order by item_id";
641             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
642 
643             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
644 
645             assertThat(rows).hasSize(5);
646             Map<String, Object> row = rows.get(2);
647             assertThat(row).containsEntry("ORDER_ID", 1);
648             assertThat(row).containsEntry("QUANTITY", 1);
649             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
650             assertThat(row).containsEntry("ITEM_ID", 33);
651 
652             row = rows.get(4);
653             assertThat(row).doesNotContainKey("ORDER_ID");
654             assertThat(row).doesNotContainKey("QUANTITY");
655             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
656             assertThat(row).containsEntry("ITEM_ID", 55);
657         }
658     }
659 
660     @Test
661     void testLeftJoin2() {
662         try (SqlSession session = sqlSessionFactory.openSession()) {
663             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
664 
665             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
666                     .from(orderMaster, "om")
667                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
668                     .leftJoin(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
669                     .orderBy(orderLine.orderId, itemMaster.itemId)
670                     .build()
671                     .render(RenderingStrategies.MYBATIS3);
672 
673             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
674                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
675                     + " left join ItemMaster im on ol.item_id = im.item_id"
676                     + " order by order_id, item_id";
677             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
678 
679             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
680 
681             assertThat(rows).hasSize(5);
682             Map<String, Object> row = rows.get(2);
683             assertThat(row).containsEntry("ORDER_ID", 2);
684             assertThat(row).containsEntry("QUANTITY", 6);
685             assertThat(row).doesNotContainKey("DESCRIPTION");
686             assertThat(row).doesNotContainKey("ITEM_ID");
687 
688             row = rows.get(4);
689             assertThat(row).containsEntry("ORDER_ID", 2);
690             assertThat(row).containsEntry("QUANTITY", 1);
691             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
692             assertThat(row).containsEntry("ITEM_ID", 44);
693         }
694     }
695 
696     @Test
697     void testLeftJoin3() {
698         try (SqlSession session = sqlSessionFactory.openSession()) {
699             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
700 
701             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
702                     .from(orderMaster, "om")
703                     .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
704                     .leftJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
705                     .orderBy(orderLine.orderId, itemMaster.itemId)
706                     .build()
707                     .render(RenderingStrategies.MYBATIS3);
708 
709             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
710                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
711                     + " left join ItemMaster im on ol.item_id = im.item_id"
712                     + " order by order_id, item_id";
713             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
714 
715             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
716 
717             assertThat(rows).hasSize(5);
718             Map<String, Object> row = rows.get(2);
719             assertThat(row).containsEntry("ORDER_ID", 2);
720             assertThat(row).containsEntry("QUANTITY", 6);
721             assertThat(row).doesNotContainKey("DESCRIPTION");
722             assertThat(row).doesNotContainKey("ITEM_ID");
723 
724             row = rows.get(4);
725             assertThat(row).containsEntry("ORDER_ID", 2);
726             assertThat(row).containsEntry("QUANTITY", 1);
727             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
728             assertThat(row).containsEntry("ITEM_ID", 44);
729         }
730     }
731 
732     @Test
733     void testLeftJoinNoAliases() {
734         try (SqlSession session = sqlSessionFactory.openSession()) {
735             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
736 
737             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
738                     .from(orderMaster)
739                     .join(orderLine).on(orderMaster.orderId, isEqualTo(orderLine.orderId))
740                     .leftJoin(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
741                     .orderBy(orderLine.orderId, itemMaster.itemId)
742                     .build()
743                     .render(RenderingStrategies.MYBATIS3);
744 
745             String expectedStatement = "select OrderLine.order_id, OrderLine.quantity, ItemMaster.item_id, ItemMaster.description"
746                     + " from OrderMaster join OrderLine on OrderMaster.order_id = OrderLine.order_id"
747                     + " left join ItemMaster on OrderLine.item_id = ItemMaster.item_id"
748                     + " order by order_id, item_id";
749             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
750 
751             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
752 
753             assertThat(rows).hasSize(5);
754             Map<String, Object> row = rows.get(2);
755             assertThat(row).containsEntry("ORDER_ID", 2);
756             assertThat(row).containsEntry("QUANTITY", 6);
757             assertThat(row).doesNotContainKey("DESCRIPTION");
758             assertThat(row).doesNotContainKey("ITEM_ID");
759 
760             row = rows.get(4);
761             assertThat(row).containsEntry("ORDER_ID", 2);
762             assertThat(row).containsEntry("QUANTITY", 1);
763             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
764             assertThat(row).containsEntry("ITEM_ID", 44);
765         }
766     }
767 
768     @Test
769     void testFullJoin() {
770         try (SqlSession session = sqlSessionFactory.openSession()) {
771             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
772 
773             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, orderLine.itemId.as("ol_itemid"), itemMaster.itemId.as("im_itemid"), itemMaster.description)
774                     .from(itemMaster, "im")
775                     .fullJoin(orderLine, "ol").on(itemMaster.itemId, isEqualTo(orderLine.itemId))
776                     .orderBy(orderLine.orderId, sortColumn("im_itemid"))
777                     .build()
778                     .render(RenderingStrategies.MYBATIS3);
779 
780             String expectedStatement = "select ol.order_id, ol.quantity, ol.item_id as ol_itemid, im.item_id as im_itemid, im.description"
781                     + " from ItemMaster im full join OrderLine ol on im.item_id = ol.item_id"
782                     + " order by order_id, im_itemid";
783             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
784 
785             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
786 
787             assertThat(rows).hasSize(6);
788             Map<String, Object> row = rows.get(0);
789             assertThat(row).doesNotContainKey("ORDER_ID");
790             assertThat(row).doesNotContainKey("QUANTITY");
791             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
792             assertThat(row).containsEntry("IM_ITEMID", 55);
793 
794             row = rows.get(2);
795             assertThat(row).containsEntry("ORDER_ID", 1);
796             assertThat(row).containsEntry("QUANTITY", 1);
797             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
798             assertThat(row).containsEntry("IM_ITEMID", 33);
799 
800             row = rows.get(3);
801             assertThat(row).containsEntry("ORDER_ID", 2);
802             assertThat(row).containsEntry("QUANTITY", 6);
803             assertThat(row).containsEntry("OL_ITEMID", 66);
804             assertThat(row).doesNotContainKey("DESCRIPTION");
805             assertThat(row).doesNotContainKey("IM_ITEMID");
806         }
807     }
808 
809     @Test
810     void testFullJoin2() {
811         try (SqlSession session = sqlSessionFactory.openSession()) {
812             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
813 
814             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
815                     .from(orderMaster, "om")
816                     .join(orderLine, "ol").on(orderMaster.orderId, isEqualTo(orderLine.orderId))
817                     .fullJoin(itemMaster, "im").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
818                     .orderBy(orderLine.orderId, itemMaster.itemId)
819                     .build()
820                     .render(RenderingStrategies.MYBATIS3);
821 
822             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
823                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
824                     + " full join ItemMaster im on ol.item_id = im.item_id"
825                     + " order by order_id, item_id";
826             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
827 
828             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
829 
830             assertThat(rows).hasSize(6);
831             Map<String, Object> row = rows.get(0);
832             assertThat(row).doesNotContainKey("ORDER_ID");
833             assertThat(row).doesNotContainKey("QUANTITY");
834             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
835             assertThat(row).containsEntry("ITEM_ID", 55);
836 
837             row = rows.get(3);
838             assertThat(row).containsEntry("ORDER_ID", 2);
839             assertThat(row).containsEntry("QUANTITY", 6);
840             assertThat(row).doesNotContainKey("DESCRIPTION");
841             assertThat(row).doesNotContainKey("ITEM_ID");
842 
843             row = rows.get(5);
844             assertThat(row).containsEntry("ORDER_ID", 2);
845             assertThat(row).containsEntry("QUANTITY", 1);
846             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
847             assertThat(row).containsEntry("ITEM_ID", 44);
848         }
849     }
850 
851     @Test
852     void testFullJoin3() {
853         try (SqlSession session = sqlSessionFactory.openSession()) {
854             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
855 
856             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
857                     .from(orderMaster, "om")
858                     .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
859                     .fullJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
860                     .orderBy(orderLine.orderId, itemMaster.itemId)
861                     .build()
862                     .render(RenderingStrategies.MYBATIS3);
863 
864             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
865                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
866                     + " full join ItemMaster im on ol.item_id = im.item_id"
867                     + " order by order_id, item_id";
868             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
869 
870             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
871 
872             assertThat(rows).hasSize(6);
873             Map<String, Object> row = rows.get(0);
874             assertThat(row).doesNotContainKey("ORDER_ID");
875             assertThat(row).doesNotContainKey("QUANTITY");
876             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
877             assertThat(row).containsEntry("ITEM_ID", 55);
878 
879             row = rows.get(3);
880             assertThat(row).containsEntry("ORDER_ID", 2);
881             assertThat(row).containsEntry("QUANTITY", 6);
882             assertThat(row).doesNotContainKey("DESCRIPTION");
883             assertThat(row).doesNotContainKey("ITEM_ID");
884 
885             row = rows.get(5);
886             assertThat(row).containsEntry("ORDER_ID", 2);
887             assertThat(row).containsEntry("QUANTITY", 1);
888             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
889             assertThat(row).containsEntry("ITEM_ID", 44);
890         }
891     }
892 
893     @Test
894     void testFullJoin4() {
895         try (SqlSession session = sqlSessionFactory.openSession()) {
896             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
897 
898             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.description)
899                     .from(orderMaster, "om")
900                     .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
901                     .fullJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
902                     .orderBy(orderLine.orderId, sortColumn("im", itemMaster.itemId))
903                     .build()
904                     .render(RenderingStrategies.MYBATIS3);
905 
906             String expectedStatement = "select ol.order_id, ol.quantity, im.description"
907                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
908                     + " full join ItemMaster im on ol.item_id = im.item_id"
909                     + " order by order_id, im.item_id";
910             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
911 
912             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
913 
914             assertThat(rows).hasSize(6);
915             Map<String, Object> row = rows.get(0);
916             assertThat(row).doesNotContainKey("ORDER_ID");
917             assertThat(row).doesNotContainKey("QUANTITY");
918             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
919 
920             row = rows.get(3);
921             assertThat(row).containsEntry("ORDER_ID", 2);
922             assertThat(row).containsEntry("QUANTITY", 6);
923             assertThat(row).doesNotContainKey("DESCRIPTION");
924 
925             row = rows.get(5);
926             assertThat(row).containsEntry("ORDER_ID", 2);
927             assertThat(row).containsEntry("QUANTITY", 1);
928             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
929         }
930     }
931 
932     @Test
933     void testFullJoin5() {
934         try (SqlSession session = sqlSessionFactory.openSession()) {
935             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
936 
937             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.description)
938                     .from(orderMaster, "om")
939                     .join(orderLine, "ol", on(orderMaster.orderId, isEqualTo(orderLine.orderId)))
940                     .fullJoin(itemMaster, "im", on(orderLine.itemId, isEqualTo(itemMaster.itemId)))
941                     .orderBy(orderLine.orderId, sortColumn("im", itemMaster.itemId).descending())
942                     .build()
943                     .render(RenderingStrategies.MYBATIS3);
944 
945             String expectedStatement = "select ol.order_id, ol.quantity, im.description"
946                     + " from OrderMaster om join OrderLine ol on om.order_id = ol.order_id"
947                     + " full join ItemMaster im on ol.item_id = im.item_id"
948                     + " order by order_id, im.item_id DESC";
949             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
950 
951             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
952 
953             assertThat(rows).hasSize(6);
954             Map<String, Object> row = rows.get(0);
955             assertThat(row).doesNotContainKey("ORDER_ID");
956             assertThat(row).doesNotContainKey("QUANTITY");
957             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
958 
959             row = rows.get(3);
960             assertThat(row).containsEntry("ORDER_ID", 2);
961             assertThat(row).containsEntry("QUANTITY", 6);
962             assertThat(row).doesNotContainKey("DESCRIPTION");
963 
964             row = rows.get(5);
965             assertThat(row).containsEntry("ORDER_ID", 2);
966             assertThat(row).containsEntry("QUANTITY", 1);
967             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
968         }
969     }
970 
971     @Test
972     void testFullJoinNoAliases() {
973         try (SqlSession session = sqlSessionFactory.openSession()) {
974             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
975 
976             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
977                     .from(orderMaster)
978                     .join(orderLine).on(orderMaster.orderId, isEqualTo(orderLine.orderId))
979                     .fullJoin(itemMaster).on(orderLine.itemId, isEqualTo(itemMaster.itemId))
980                     .orderBy(orderLine.orderId, itemMaster.itemId)
981                     .build()
982                     .render(RenderingStrategies.MYBATIS3);
983 
984             String expectedStatement = "select OrderLine.order_id, OrderLine.quantity, ItemMaster.item_id, ItemMaster.description"
985                     + " from OrderMaster join OrderLine on OrderMaster.order_id = OrderLine.order_id"
986                     + " full join ItemMaster on OrderLine.item_id = ItemMaster.item_id"
987                     + " order by order_id, item_id";
988             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
989 
990             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
991 
992             assertThat(rows).hasSize(6);
993             Map<String, Object> row = rows.get(0);
994             assertThat(row).doesNotContainKey("ORDER_ID");
995             assertThat(row).doesNotContainKey("QUANTITY");
996             assertThat(row).containsEntry("DESCRIPTION", "Catcher Glove");
997             assertThat(row).containsEntry("ITEM_ID", 55);
998 
999             row = rows.get(3);
1000             assertThat(row).containsEntry("ORDER_ID", 2);
1001             assertThat(row).containsEntry("QUANTITY", 6);
1002             assertThat(row).doesNotContainKey("DESCRIPTION");
1003             assertThat(row).doesNotContainKey("ITEM_ID");
1004 
1005             row = rows.get(5);
1006             assertThat(row).containsEntry("ORDER_ID", 2);
1007             assertThat(row).containsEntry("QUANTITY", 1);
1008             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
1009             assertThat(row).containsEntry("ITEM_ID", 44);
1010         }
1011     }
1012 
1013     @Test
1014     void testSelf() {
1015         try (SqlSession session = sqlSessionFactory.openSession()) {
1016             JoinMapper mapper = session.getMapper(JoinMapper.class);
1017 
1018             // create second table instance for self-join
1019             UserDynamicSQLSupport.User user2 = new UserDynamicSQLSupport.User();
1020 
1021             // get Bamm Bamm's parent - should be Barney
1022             SelectStatementProvider selectStatement = select(user.userId, user.userName, user.parentId)
1023                     .from(user, "u1")
1024                     .join(user2, "u2").on(user.userId, isEqualTo(user2.parentId))
1025                     .where(user2.userId, isEqualTo(4))
1026                     .build()
1027                     .render(RenderingStrategies.MYBATIS3);
1028 
1029             String expectedStatement = "select u1.user_id, u1.user_name, u1.parent_id"
1030                     + " from User u1 join User u2 on u1.user_id = u2.parent_id"
1031                     + " where u2.user_id = #{parameters.p1,jdbcType=INTEGER}";
1032             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1033 
1034             List<User> rows = mapper.selectUsers(selectStatement);
1035 
1036             assertThat(rows).hasSize(1);
1037             User row = rows.get(0);
1038             assertThat(row.userId()).isEqualTo(2);
1039             assertThat(row.userName()).isEqualTo("Barney");
1040             assertThat(row.parentId()).isNull();
1041         }
1042     }
1043 
1044     @Test
1045     void testSelfWithDuplicateAlias() {
1046         var dsl = select(user.userId, user.userName, user.parentId)
1047                 .from(user, "u1")
1048                 .join(user, "u2");
1049 
1050         IsEqualToColumn<Integer> condition = isEqualTo(user.parentId);
1051 
1052         assertThatExceptionOfType(DuplicateTableAliasException.class)
1053                 .isThrownBy(() -> dsl.on(user.userId, condition))
1054                 .withMessage(Messages.getString("ERROR.1", user.tableName(), "u2", "u1"));
1055     }
1056 
1057     @Test
1058     void testSelfWithNewAlias() {
1059         try (SqlSession session = sqlSessionFactory.openSession()) {
1060             JoinMapper mapper = session.getMapper(JoinMapper.class);
1061 
1062             // create second table instance for self-join
1063             UserDynamicSQLSupport.User user2 = user.withAlias("u2");
1064 
1065             // get Bamm Bamm's parent - should be Barney
1066             SelectStatementProvider selectStatement = select(user.userId, user.userName, user.parentId)
1067                     .from(user)
1068                     .join(user2).on(user.userId, isEqualTo(user2.parentId))
1069                     .where(user2.userId, isEqualTo(4))
1070                     .build()
1071                     .render(RenderingStrategies.MYBATIS3);
1072 
1073             String expectedStatement = "select User.user_id, User.user_name, User.parent_id"
1074                     + " from User join User u2 on User.user_id = u2.parent_id"
1075                     + " where u2.user_id = #{parameters.p1,jdbcType=INTEGER}";
1076             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1077 
1078             List<User> rows = mapper.selectUsers(selectStatement);
1079 
1080             assertThat(rows).hasSize(1);
1081             User row = rows.get(0);
1082             assertThat(row.userId()).isEqualTo(2);
1083             assertThat(row.userName()).isEqualTo("Barney");
1084             assertThat(row.parentId()).isNull();
1085         }
1086     }
1087 
1088     @Test
1089     void testSelfWithNewAliasAndOverride() {
1090         try (SqlSession session = sqlSessionFactory.openSession()) {
1091             JoinMapper mapper = session.getMapper(JoinMapper.class);
1092 
1093             // create second table instance for self-join
1094             UserDynamicSQLSupport.User user2 = user.withAlias("other_user");
1095 
1096             // get Bamm Bamm's parent - should be Barney
1097             SelectStatementProvider selectStatement = select(user.userId, user.userName, user.parentId)
1098                     .from(user, "u1")
1099                     .join(user2, "u2").on(user.userId, isEqualTo(user2.parentId))
1100                     .where(user2.userId, isEqualTo(4))
1101                     .build()
1102                     .render(RenderingStrategies.MYBATIS3);
1103 
1104             String expectedStatement = "select u1.user_id, u1.user_name, u1.parent_id"
1105                     + " from User u1 join User u2 on u1.user_id = u2.parent_id"
1106                     + " where u2.user_id = #{parameters.p1,jdbcType=INTEGER}";
1107             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1108 
1109             List<User> rows = mapper.selectUsers(selectStatement);
1110 
1111             assertThat(rows).hasSize(1);
1112             User row = rows.get(0);
1113             assertThat(row.userId()).isEqualTo(2);
1114             assertThat(row.userName()).isEqualTo("Barney");
1115             assertThat(row.parentId()).isNull();
1116         }
1117     }
1118 
1119     @Test
1120     void testLimitAndOffsetAfterJoin() {
1121         try (SqlSession session = sqlSessionFactory.openSession()) {
1122             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1123 
1124             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
1125                     .from(itemMaster, "im")
1126                     .leftJoin(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
1127                     .limit(2)
1128                     .offset(1)
1129                     .build()
1130                     .render(RenderingStrategies.MYBATIS3);
1131 
1132             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
1133                     + " from ItemMaster im left join OrderLine ol on ol.item_id = im.item_id"
1134                     + " limit #{parameters.p1} offset #{parameters.p2}";
1135             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1136 
1137             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1138 
1139             assertThat(rows).hasSize(2);
1140             Map<String, Object> row = rows.get(0);
1141             assertThat(row).containsEntry("ORDER_ID", 2);
1142             assertThat(row).containsEntry("QUANTITY", 1);
1143             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1144             assertThat(row).containsEntry("ITEM_ID", 22);
1145 
1146             row = rows.get(1);
1147             assertThat(row).containsEntry("ORDER_ID", 1);
1148             assertThat(row).containsEntry("QUANTITY", 1);
1149             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
1150             assertThat(row).containsEntry("ITEM_ID", 33);
1151         }
1152     }
1153 
1154     @Test
1155     void testLimitOnlyAfterJoin() {
1156         try (SqlSession session = sqlSessionFactory.openSession()) {
1157             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1158 
1159             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
1160                     .from(itemMaster, "im")
1161                     .leftJoin(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
1162                     .limit(2)
1163                     .build()
1164                     .render(RenderingStrategies.MYBATIS3);
1165 
1166             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
1167                     + " from ItemMaster im left join OrderLine ol on ol.item_id = im.item_id"
1168                     + " limit #{parameters.p1}";
1169             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1170 
1171             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1172 
1173             assertThat(rows).hasSize(2);
1174             Map<String, Object> row = rows.get(0);
1175             assertThat(row).containsEntry("ORDER_ID", 1);
1176             assertThat(row).containsEntry("QUANTITY", 1);
1177             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1178             assertThat(row).containsEntry("ITEM_ID", 22);
1179 
1180             row = rows.get(1);
1181             assertThat(row).containsEntry("ORDER_ID", 2);
1182             assertThat(row).containsEntry("QUANTITY", 1);
1183             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1184             assertThat(row).containsEntry("ITEM_ID", 22);
1185         }
1186     }
1187 
1188     @Test
1189     void testOffsetOnlyAfterJoin() {
1190         try (SqlSession session = sqlSessionFactory.openSession()) {
1191             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1192 
1193             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
1194                     .from(itemMaster, "im")
1195                     .leftJoin(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
1196                     .offset(2)
1197                     .build()
1198                     .render(RenderingStrategies.MYBATIS3);
1199 
1200             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
1201                     + " from ItemMaster im left join OrderLine ol on ol.item_id = im.item_id"
1202                     + " offset #{parameters.p1} rows";
1203             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1204 
1205             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1206 
1207             assertThat(rows).hasSize(3);
1208             Map<String, Object> row = rows.get(0);
1209             assertThat(row).containsEntry("ORDER_ID", 1);
1210             assertThat(row).containsEntry("QUANTITY", 1);
1211             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
1212             assertThat(row).containsEntry("ITEM_ID", 33);
1213 
1214             row = rows.get(1);
1215             assertThat(row).containsEntry("ORDER_ID", 2);
1216             assertThat(row).containsEntry("QUANTITY", 1);
1217             assertThat(row).containsEntry("DESCRIPTION", "Outfield Glove");
1218             assertThat(row).containsEntry("ITEM_ID", 44);
1219         }
1220     }
1221 
1222     @Test
1223     void testOffsetAndFetchFirstAfterJoin() {
1224         try (SqlSession session = sqlSessionFactory.openSession()) {
1225             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1226 
1227             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
1228                     .from(itemMaster, "im")
1229                     .leftJoin(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
1230                     .offset(1)
1231                     .fetchFirst(2).rowsOnly()
1232                     .build()
1233                     .render(RenderingStrategies.MYBATIS3);
1234 
1235             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
1236                     + " from ItemMaster im left join OrderLine ol on ol.item_id = im.item_id"
1237                     + " offset #{parameters.p1} rows fetch first #{parameters.p2} rows only";
1238             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1239 
1240             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1241 
1242             assertThat(rows).hasSize(2);
1243             Map<String, Object> row = rows.get(0);
1244             assertThat(row).containsEntry("ORDER_ID", 2);
1245             assertThat(row).containsEntry("QUANTITY", 1);
1246             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1247             assertThat(row).containsEntry("ITEM_ID", 22);
1248 
1249             row = rows.get(1);
1250             assertThat(row).containsEntry("ORDER_ID", 1);
1251             assertThat(row).containsEntry("QUANTITY", 1);
1252             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
1253             assertThat(row).containsEntry("ITEM_ID", 33);
1254         }
1255     }
1256 
1257     @Test
1258     void testFetchFirstOnlyAfterJoin() {
1259         try (SqlSession session = sqlSessionFactory.openSession()) {
1260             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1261 
1262             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
1263                     .from(itemMaster, "im")
1264                     .leftJoin(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
1265                     .fetchFirst(2).rowsOnly()
1266                     .build()
1267                     .render(RenderingStrategies.MYBATIS3);
1268 
1269             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
1270                     + " from ItemMaster im left join OrderLine ol on ol.item_id = im.item_id"
1271                     + " fetch first #{parameters.p1} rows only";
1272             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1273 
1274             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1275 
1276             assertThat(rows).hasSize(2);
1277             Map<String, Object> row = rows.get(0);
1278             assertThat(row).containsEntry("ORDER_ID", 1);
1279             assertThat(row).containsEntry("QUANTITY", 1);
1280             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1281             assertThat(row).containsEntry("ITEM_ID", 22);
1282 
1283             row = rows.get(1);
1284             assertThat(row).containsEntry("ORDER_ID", 2);
1285             assertThat(row).containsEntry("QUANTITY", 1);
1286             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1287             assertThat(row).containsEntry("ITEM_ID", 22);
1288         }
1289     }
1290 
1291     @Test
1292     void testJoinWithParameterValue() {
1293         try (SqlSession session = sqlSessionFactory.openSession()) {
1294             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1295 
1296             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
1297                     .from(itemMaster, "im")
1298                     .join(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
1299                     .and(orderLine.orderId, isEqualTo(1))
1300                     .build()
1301                     .render(RenderingStrategies.MYBATIS3);
1302 
1303             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
1304                     + " from ItemMaster im join OrderLine ol on ol.item_id = im.item_id"
1305                     + " and ol.order_id = #{parameters.p1,jdbcType=INTEGER}";
1306             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1307 
1308             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1309 
1310             assertThat(rows).hasSize(2);
1311             Map<String, Object> row = rows.get(0);
1312             assertThat(row).containsEntry("ORDER_ID", 1);
1313             assertThat(row).containsEntry("QUANTITY", 1);
1314             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1315             assertThat(row).containsEntry("ITEM_ID", 22);
1316 
1317             row = rows.get(1);
1318             assertThat(row).containsEntry("ORDER_ID", 1);
1319             assertThat(row).containsEntry("QUANTITY", 1);
1320             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
1321             assertThat(row).containsEntry("ITEM_ID", 33);
1322         }
1323     }
1324 
1325     @Test
1326     void testJoinWithConstant() {
1327         try (SqlSession session = sqlSessionFactory.openSession()) {
1328             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1329 
1330             SelectStatementProvider selectStatement = select(orderLine.orderId, orderLine.quantity, itemMaster.itemId, itemMaster.description)
1331                     .from(itemMaster, "im")
1332                     .join(orderLine, "ol").on(orderLine.itemId, isEqualTo(itemMaster.itemId))
1333                     .and(orderLine.orderId, isEqualTo(constant("1")))
1334                     .build()
1335                     .render(RenderingStrategies.MYBATIS3);
1336 
1337             String expectedStatement = "select ol.order_id, ol.quantity, im.item_id, im.description"
1338                     + " from ItemMaster im join OrderLine ol on ol.item_id = im.item_id"
1339                     + " and ol.order_id = 1";
1340             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1341 
1342             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1343 
1344             assertThat(rows).hasSize(2);
1345             Map<String, Object> row = rows.get(0);
1346             assertThat(row).containsEntry("ORDER_ID", 1);
1347             assertThat(row).containsEntry("QUANTITY", 1);
1348             assertThat(row).containsEntry("DESCRIPTION", "Helmet");
1349             assertThat(row).containsEntry("ITEM_ID", 22);
1350 
1351             row = rows.get(1);
1352             assertThat(row).containsEntry("ORDER_ID", 1);
1353             assertThat(row).containsEntry("QUANTITY", 1);
1354             assertThat(row).containsEntry("DESCRIPTION", "First Base Glove");
1355             assertThat(row).containsEntry("ITEM_ID", 33);
1356         }
1357     }
1358 
1359     @Test
1360     void testJoinWithGroupBy() {
1361         try (SqlSession session = sqlSessionFactory.openSession()) {
1362             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1363 
1364             SelectStatementProvider selectStatement = select(orderMaster.orderId, count().as("linecount"))
1365                     .from(orderMaster, "om")
1366                     .join(orderDetail, "od").on(orderMaster.orderId, isEqualTo(orderDetail.orderId))
1367                     .groupBy(orderMaster.orderId)
1368                     .orderBy(orderDetail.orderId)
1369                     .build()
1370                     .render(RenderingStrategies.MYBATIS3);
1371 
1372             String expectedStatement = "select om.order_id, count(*) as linecount from OrderMaster om join OrderDetail od on om.order_id = od.order_id group by om.order_id order by order_id";
1373             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1374 
1375             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1376 
1377             assertThat(rows).hasSize(2);
1378             assertThat(rows.get(0)).containsOnly(entry("ORDER_ID", 1), entry("LINECOUNT", 2L));
1379             assertThat(rows.get(1)).containsOnly(entry("ORDER_ID", 2), entry("LINECOUNT", 1L));
1380         }
1381     }
1382 
1383     @Test
1384     void testSubQuery() {
1385         try (SqlSession session = sqlSessionFactory.openSession()) {
1386             CommonSelectMapper mapper = session.getMapper(CommonSelectMapper.class);
1387 
1388             SelectStatementProvider selectStatement = select(orderMaster.orderId,
1389                     subQuery(select(count())
1390                             .from(orderDetail, "od")
1391                             .where(orderMaster.orderId, isEqualTo(orderDetail.orderId))
1392                     ).as("linecount"))
1393                     .from(orderMaster, "om")
1394                     .orderBy(orderMaster.orderId)
1395                     .build()
1396                     .render(RenderingStrategies.MYBATIS3);
1397 
1398             String expectedStatement = "select om.order_id, (select count(*) from OrderDetail od where om.order_id = od.order_id) as linecount from OrderMaster om order by order_id";
1399             assertThat(selectStatement.getSelectStatement()).isEqualTo(expectedStatement);
1400 
1401             List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
1402 
1403             assertThat(rows).hasSize(2);
1404             assertThat(rows.get(0)).containsOnly(entry("ORDER_ID", 1), entry("LINECOUNT", 2L));
1405             assertThat(rows.get(1)).containsOnly(entry("ORDER_ID", 2), entry("LINECOUNT", 1L));
1406         }
1407     }
1408 }