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 try (InputStream is = getClass().getResourceAsStream("/examples/animal/data/CreateAnimalData.sql")) {
55 assert is != null;
56 try (Connection connection = DriverManager.getConnection(JDBC_URL, "sa", "");
57 InputStreamReader isr = new InputStreamReader(is)) {
58 ScriptRunner sr = new ScriptRunner(connection);
59 sr.setLogWriter(null);
60 sr.runScript(isr);
61 }
62 }
63
64 UnpooledDataSource ds = new UnpooledDataSource(JDBC_DRIVER, JDBC_URL, "sa", "");
65 Environment environment = new Environment("test", new JdbcTransactionFactory(), ds);
66 Configuration config = new Configuration(environment);
67 config.addMapper(CommonSelectMapper.class);
68 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
69 }
70
71 @Test
72 void testBasicSubQuery() {
73 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
74 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
75 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
76
77 SelectStatementProvider selectStatement = select(animalName, rowNum)
78 .from(
79 select(id, animalName)
80 .from(animalData)
81 .where(id, isLessThan(22))
82 .orderBy(animalName.descending())
83 )
84 .where(rowNum, isLessThan(5))
85 .and(animalName, isLike("%a%"))
86 .build()
87 .render(RenderingStrategies.MYBATIS3);
88
89 assertThat(selectStatement.getSelectStatement()).isEqualTo(
90 "select animal_name, rownum() " +
91 "from (select id, animal_name " +
92 "from AnimalData where id < #{parameters.p1,jdbcType=INTEGER} " +
93 "order by animal_name DESC) " +
94 "where rownum() < #{parameters.p2} and animal_name like #{parameters.p3,jdbcType=VARCHAR}"
95 );
96 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
97 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
98 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
99
100 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
101 assertThat(rows).hasSize(4);
102
103 assertThat(rows.get(2)).containsEntry("ANIMAL_NAME", "Chinchilla");
104 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
105 }
106 }
107
108 @Test
109 void testSimpleAliases() {
110 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
111 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
112 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
113
114 SelectStatementProvider selectStatement = select(animalName, rowNum)
115 .from(
116 select(id, animalName)
117 .from(animalData, "a")
118 .where(id, isLessThan(22))
119 .orderBy(animalName.descending()),
120 "b"
121 )
122 .where(rowNum, isLessThan(5))
123 .and(animalName, isLike("%a%"))
124 .build()
125 .render(RenderingStrategies.MYBATIS3);
126
127 assertThat(selectStatement.getSelectStatement()).isEqualTo(
128 "select animal_name, rownum() " +
129 "from (select a.id, a.animal_name " +
130 "from AnimalData a where a.id < #{parameters.p1,jdbcType=INTEGER} " +
131 "order by animal_name DESC) b " +
132 "where rownum() < #{parameters.p2} and animal_name like #{parameters.p3,jdbcType=VARCHAR}"
133 );
134 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
135 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
136 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
137
138 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
139 assertThat(rows).hasSize(4);
140
141 assertThat(rows.get(2)).containsEntry("ANIMAL_NAME", "Chinchilla");
142 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
143 }
144 }
145
146 @Test
147 void testSimpleAliasesWithManualQualifiers() {
148 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
149 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
150 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
151
152 SelectStatementProvider selectStatement = select(animalName.qualifiedWith("b"), rowNum)
153 .from(
154 select(id, animalName)
155 .from(animalData, "a")
156 .where(id, isLessThan(22))
157 .orderBy(animalName.descending()),
158 "b"
159 )
160 .where(rowNum, isLessThan(5))
161 .and(animalName.qualifiedWith("b"), isLike("%a%"))
162 .build()
163 .render(RenderingStrategies.MYBATIS3);
164
165 assertThat(selectStatement.getSelectStatement()).isEqualTo(
166 "select b.animal_name, rownum() " +
167 "from (select a.id, a.animal_name " +
168 "from AnimalData a where a.id < #{parameters.p1,jdbcType=INTEGER} " +
169 "order by animal_name DESC) b " +
170 "where rownum() < #{parameters.p2} and b.animal_name like #{parameters.p3,jdbcType=VARCHAR}"
171 );
172 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
173 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
174 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
175
176 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
177 assertThat(rows).hasSize(4);
178
179 assertThat(rows.get(2)).containsEntry("ANIMAL_NAME", "Chinchilla");
180 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
181 }
182 }
183
184 @Test
185 void testBasicSubQueryWithAliases() {
186 try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
187 CommonSelectMapper mapper = sqlSession.getMapper(CommonSelectMapper.class);
188 DerivedColumn<Integer> rowNum = DerivedColumn.of("rownum()");
189 SqlColumn<String> outerAnimalName = animalName.qualifiedWith("b");
190 DerivedColumn<Integer> animalId = DerivedColumn.of("animalId", "b");
191
192 SelectStatementProvider selectStatement = select(outerAnimalName.asCamelCase(), animalId, rowNum)
193 .from(
194 select(id.as("animalId"), animalName)
195 .from(animalData, "a")
196 .where(id, isLessThan(22))
197 .orderBy(animalName.descending()),
198 "b"
199 )
200 .where(rowNum, isLessThan(5))
201 .and(outerAnimalName, isLike("%a%"))
202 .build()
203 .render(RenderingStrategies.MYBATIS3);
204
205 assertThat(selectStatement.getSelectStatement()).isEqualTo(
206 "select b.animal_name as \"animalName\", b.animalId, rownum() " +
207 "from (select a.id as animalId, a.animal_name " +
208 "from AnimalData a where a.id < #{parameters.p1,jdbcType=INTEGER} " +
209 "order by animal_name DESC) b " +
210 "where rownum() < #{parameters.p2} and b.animal_name like #{parameters.p3,jdbcType=VARCHAR}"
211 );
212 assertThat(selectStatement.getParameters()).containsEntry("p1", 22);
213 assertThat(selectStatement.getParameters()).containsEntry("p2", 5);
214 assertThat(selectStatement.getParameters()).containsEntry("p3", "%a%");
215
216 List<Map<String, Object>> rows = mapper.selectManyMappedRows(selectStatement);
217 assertThat(rows).hasSize(4);
218
219 assertThat(rows.get(2)).containsEntry("animalName", "Chinchilla");
220 assertThat(rows.get(2)).containsEntry("ANIMALID", 14);
221 assertThat(rows.get(2)).containsEntry("ROWNUM", 3);
222 }
223 }
224 }