1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package examples.simple;
17
18 import static examples.simple.PersonDynamicSqlSupport.id;
19 import static examples.simple.PersonDynamicSqlSupport.occupation;
20 import static examples.simple.PersonDynamicSqlSupport.person;
21 import static org.assertj.core.api.Assertions.assertThat;
22 import static org.assertj.core.api.Assertions.entry;
23 import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
24 import static org.mybatis.dynamic.sql.SqlBuilder.isLessThan;
25 import static org.mybatis.dynamic.sql.SqlBuilder.isNull;
26 import static org.mybatis.dynamic.sql.SqlBuilder.select;
27 import static org.mybatis.dynamic.sql.SqlBuilder.where;
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
35 import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
36 import org.apache.ibatis.jdbc.ScriptRunner;
37 import org.apache.ibatis.mapping.Environment;
38 import org.apache.ibatis.session.Configuration;
39 import org.apache.ibatis.session.SqlSession;
40 import org.apache.ibatis.session.SqlSessionFactory;
41 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
42 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
43 import org.junit.jupiter.api.BeforeEach;
44 import org.junit.jupiter.api.Test;
45 import org.mybatis.dynamic.sql.render.RenderingStrategies;
46 import org.mybatis.dynamic.sql.select.QueryExpressionModel;
47 import org.mybatis.dynamic.sql.select.SelectModel;
48 import org.mybatis.dynamic.sql.select.SubQuery;
49 import org.mybatis.dynamic.sql.select.aggregate.CountAll;
50 import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
51 import org.mybatis.dynamic.sql.where.WhereApplier;
52
53 class ReusableWhereTest {
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/simple/CreateSimpleDB.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(PersonMapper.class);
77 config.addMapper(PersonWithAddressMapper.class);
78 sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
79 }
80
81 @Test
82 void testCount() {
83 try (SqlSession session = sqlSessionFactory.openSession()) {
84 PersonMapper mapper = session.getMapper(PersonMapper.class);
85
86 long rows = mapper.count(c -> c.applyWhere(commonWhere));
87
88 assertThat(rows).isEqualTo(3);
89 }
90 }
91
92 @Test
93 void testDelete() {
94 try (SqlSession session = sqlSessionFactory.openSession()) {
95 PersonMapper mapper = session.getMapper(PersonMapper.class);
96
97 int rows = mapper.delete(c -> c.applyWhere(commonWhere));
98
99 assertThat(rows).isEqualTo(3);
100 }
101 }
102
103 @Test
104 void testSelect() {
105 try (SqlSession session = sqlSessionFactory.openSession()) {
106 PersonMapper mapper = session.getMapper(PersonMapper.class);
107
108 List<PersonRecord> rows = mapper.select(c ->
109 c.applyWhere(commonWhere)
110 .orderBy(id));
111
112 assertThat(rows).hasSize(3);
113 }
114 }
115
116 @Test
117 void testUpdate() {
118 try (SqlSession session = sqlSessionFactory.openSession()) {
119 PersonMapper mapper = session.getMapper(PersonMapper.class);
120
121 int rows = mapper.update(c ->
122 c.set(occupation).equalToStringConstant("worker")
123 .applyWhere(commonWhere));
124
125 assertThat(rows).isEqualTo(3);
126 }
127 }
128
129 @Test
130 void testTransformToCount() {
131 try (SqlSession session = sqlSessionFactory.openSession()) {
132 PersonMapper mapper = session.getMapper(PersonMapper.class);
133
134 SelectModel selectModel = select(PersonMapper.selectList)
135 .from(person)
136 .where(id, isLessThan(5))
137 .limit(2)
138 .build();
139
140 SelectStatementProvider selectStatement = selectModel.render(RenderingStrategies.MYBATIS3);
141
142 assertThat(selectStatement.getSelectStatement()).isEqualTo(
143 "select id as A_ID, first_name, last_name, birth_date, employed, occupation, address_id from Person where id < #{parameters.p1,jdbcType=INTEGER} limit #{parameters.p2}");
144 assertThat(selectStatement.getParameters()).containsOnly(entry("p1", 5), entry("p2", 2L));
145
146 SelectModel countModel = toCount(selectModel);
147 SelectStatementProvider countStatement = countModel.render(RenderingStrategies.MYBATIS3);
148
149 assertThat(countStatement.getSelectStatement()).isEqualTo(
150 "select count(*) from (select id as A_ID, first_name, last_name, birth_date, employed, occupation, address_id from Person where id < #{parameters.p1,jdbcType=INTEGER})");
151 assertThat(countStatement.getParameters()).containsOnly(entry("p1", 5));
152
153 long count = mapper.count(countStatement);
154
155 assertThat(count).isEqualTo(4);
156 }
157
158 }
159
160 private final WhereApplier commonWhere = where(id, isEqualTo(1)).or(occupation, isNull()).toWhereApplier();
161
162
163
164
165
166
167
168
169
170
171
172
173
174 static SelectModel toCount(SelectModel selectModel) {
175
176 SelectModel strippedSelectModel = SelectModel.withQueryExpressions(selectModel.queryExpressions().toList())
177 .withStatementConfiguration(selectModel.statementConfiguration())
178 .build();
179
180 QueryExpressionModel model = QueryExpressionModel
181 .withSelectList(List.of(new CountAll()))
182 .withTable(new SubQuery.Builder().withSelectModel(strippedSelectModel).build())
183 .build();
184
185 return SelectModel.withQueryExpressions(List.of(model))
186 .withStatementConfiguration(selectModel.statementConfiguration())
187 .build();
188 }
189 }