1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.animal.data;
17
18 import static examples.animal.data.AnimalDataDynamicSqlSupport.*;
19 import static org.assertj.core.api.Assertions.assertThat;
20 import static org.mybatis.dynamic.sql.SqlBuilder.*;
21
22 import java.io.InputStream;
23 import java.io.InputStreamReader;
24 import java.sql.Connection;
25 import java.sql.DriverManager;
26 import java.util.List;
27 import java.util.Map;
28
29 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
30 import org.apache.ibatis.jdbc.ScriptRunner;
31 import org.apache.ibatis.mapping.Environment;
32 import org.apache.ibatis.session.Configuration;
33 import org.apache.ibatis.session.SqlSession;
34 import org.apache.ibatis.session.SqlSessionFactory;
35 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
36 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
37 import org.junit.jupiter.api.BeforeEach;
38 import org.junit.jupiter.api.Test;
39 import org.mybatis.dynamic.sql.DerivedColumn;
40 import org.mybatis.dynamic.sql.SqlColumn;
41 import org.mybatis.dynamic.sql.render.RenderingStrategies;
42 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
43 import org.mybatis.dynamic.sql.util.mybatis3.CommonSelectMapper;
44
45 class SubQueryTest {
46 private static final String JDBC_URL = "jdbc:hsqldb:mem:aname";
47 private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
48
49 private SqlSessionFactory sqlSessionFactory;
50
51 @BeforeEach
52 void setup() throws Exception {
53 Class.forName(JDBC_DRIVER);
54 InputStream is = getClass().getResourceAsStream("/examples/animal/data/CreateAnimalData.sql");
55 assert is != null;
56 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "")) {
57 ScriptRunner sr = new ScriptRunner(connection);
58 sr.setLogWriter(null);
59 sr.runScript(new InputStreamReader(is));
60 }
61
62 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
63 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
64 Configuration config = new Configuration(environment);
65 config.addMapper(CommonSelectMapper.class);
66 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
67 }
68
69 @Test
70 void testBasicSubQuery() {
71 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
72 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
73 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
74
75 SelectStatementProvider selectStatement = select(animalName, rowNum)
76 .from(
77 select(id, animalName)
78 .from(animalData)
79 .where(id, isLessThan(22))
80 .orderBy(animalName.descending())
81 )
82 .where(rowNum, isLessThan(5))
83 .and(animalName, isLike("%a%"))
84 .build()
85 .render(RenderingStrategies.MYBATIS3);
86
87 assertThat(selectStatement.getSelectStatement()).isEqualTo(
88 "select animal_name, rownum() " +
89 "from (select id, animal_name " +
90 "from AnimalData where id < #{parameters.p1,jdbcType=INTEGER} " +
91 "order by animal_name DESC) " +
92 "where rownum() < #{parameters.p2} and animal_name like #{parameters.p3,jdbcType=VARCHAR}"
93 );
94 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
95 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
96 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
97
98 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
99 assertThat(rows).hasSize(4);
100
101 assertThat(rows.get(2)).containsEntry("ANIMAL_NAME", "Chinchilla");
102 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
103 }
104 }
105
106 @Test
107 void testSimpleAliases() {
108 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
109 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
110 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
111
112 SelectStatementProvider selectStatement = select(animalName, rowNum)
113 .from(
114 select(id, animalName)
115 .from(animalData, "a")
116 .where(id, isLessThan(22))
117 .orderBy(animalName.descending()),
118 "b"
119 )
120 .where(rowNum, isLessThan(5))
121 .and(animalName, isLike("%a%"))
122 .build()
123 .render(RenderingStrategies.MYBATIS3);
124
125 assertThat(selectStatement.getSelectStatement()).isEqualTo(
126 "select animal_name, rownum() " +
127 "from (select a.id, a.animal_name " +
128 "from AnimalData a where a.id < #{parameters.p1,jdbcType=INTEGER} " +
129 "order by animal_name DESC) b " +
130 "where rownum() < #{parameters.p2} and animal_name like #{parameters.p3,jdbcType=VARCHAR}"
131 );
132 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
133 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
134 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
135
136 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
137 assertThat(rows).hasSize(4);
138
139 assertThat(rows.get(2)).containsEntry("ANIMAL_NAME", "Chinchilla");
140 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
141 }
142 }
143
144 @Test
145 void testSimpleAliasesWithManualQualifiers() {
146 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
147 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
148 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
149
150 SelectStatementProvider selectStatement = select(animalName.qualifiedWith("b"), rowNum)
151 .from(
152 select(id, animalName)
153 .from(animalData, "a")
154 .where(id, isLessThan(22))
155 .orderBy(animalName.descending()),
156 "b"
157 )
158 .where(rowNum, isLessThan(5))
159 .and(animalName.qualifiedWith("b"), isLike("%a%"))
160 .build()
161 .render(RenderingStrategies.MYBATIS3);
162
163 assertThat(selectStatement.getSelectStatement()).isEqualTo(
164 "select b.animal_name, rownum() " +
165 "from (select a.id, a.animal_name " +
166 "from AnimalData a where a.id < #{parameters.p1,jdbcType=INTEGER} " +
167 "order by animal_name DESC) b " +
168 "where rownum() < #{parameters.p2} and b.animal_name like #{parameters.p3,jdbcType=VARCHAR}"
169 );
170 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
171 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
172 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
173
174 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
175 assertThat(rows).hasSize(4);
176
177 assertThat(rows.get(2)).containsEntry("ANIMAL_NAME", "Chinchilla");
178 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
179 }
180 }
181
182 @Test
183 void testBasicSubQueryWithAliases() {
184 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
185 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
186 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
187 SqlColumn<String> outerAnimalName = animalName.qualifiedWith("b");
188 DerivedColumn<Integer> animalId = DerivedColumn.of("animalId", "b");
189
190 SelectStatementProvider selectStatement = select(outerAnimalName.asCamelCase(), animalId, rowNum)
191 .from(
192 select(id.as("animalId"), animalName)
193 .from(animalData, "a")
194 .where(id, isLessThan(22))
195 .orderBy(animalName.descending()),
196 "b"
197 )
198 .where(rowNum, isLessThan(5))
199 .and(outerAnimalName, isLike("%a%"))
200 .build()
201 .render(RenderingStrategies.MYBATIS3);
202
203 assertThat(selectStatement.getSelectStatement()).isEqualTo(
204 "select b.animal_name as \"animalName\", b.animalId, rownum() " +
205 "from (select a.id as animalId, a.animal_name " +
206 "from AnimalData a where a.id < #{parameters.p1,jdbcType=INTEGER} " +
207 "order by animal_name DESC) b " +
208 "where rownum() < #{parameters.p2} and b.animal_name like #{parameters.p3,jdbcType=VARCHAR}"
209 );
210 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
211 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
212 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
213
214 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
215 assertThat(rows).hasSize(4);
216
217 assertThat(rows.get(2)).containsEntry("animalName", "Chinchilla");
218 assertThat(rows.get(2)).containsEntry("ANIMALID", 14);
219 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
220 }
221 }
222 }