package tk.mybatis.mapper.test.example;
import org.apache.ibatis.session.SqlSession;
import org.junit.Assert;
import org.junit.Test;
import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.mapper.CountryMapper;
import tk.mybatis.mapper.mapper.MybatisHelper;
import tk.mybatis.mapper.model.Country;
import tk.mybatis.mapper.util.Sqls;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* @author *
* @date 2017/11/18
*/
public class TestExampleBuilder {
@Test
public void testExampleBuilder() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class).build();
List<Country> countries = mapper.selectByExample(example);
Assert.assertEquals(183, countries.size());
// 下面的查询会有缓存
Example example0 = Example.builder(Country.class)
.select().build();
List<Country> countries0 = mapper.selectByExample(example0);
Assert.assertEquals(183, countries0.size());
} finally {
sqlSession.close();
}
}
@Test
public void testDistinct() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.distinct()
.build();
List<Country> countries = mapper.selectByExample(example);
Assert.assertEquals(183, countries.size());
// distinct和order by冲突问题
Example example0 = Example.builder(Country.class)
.selectDistinct("id", "countryname").build();
List<Country> countries0 = mapper.selectByExample(example0);
Assert.assertEquals(183, countries0.size());
} finally {
sqlSession.close();
}
}
@Test
public void testForUpdate() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.select("countryname")
.where(Sqls.custom().andGreaterThan("id", 100))
.orderByAsc("countrycode")
.forUpdate()
.build();
List<Country> countries = mapper.selectByExample(example);
Assert.assertEquals(83, countries.size());
} finally {
sqlSession.close();
}
}
@Test
public void testEqualTo() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.where(Sqls.custom().andEqualTo("id", "35"))
.build();
List<Country> countries = mapper.selectByExample(example);
Country country = countries.get(0);
Assert.assertEquals(Integer.valueOf(35), country.getId());
Assert.assertEquals("China", country.getCountryname());
Assert.assertEquals("CN", country.getCountrycode());
} finally {
sqlSession.close();
}
}
@Test
public void testBetween() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.where(Sqls.custom().andBetween("id", 34, 35))
.build();
List<Country> countries = mapper.selectByExample(example);
Country country35 = countries.get(0);
Assert.assertEquals(Integer.valueOf(35), country35.getId());
Assert.assertEquals("China", country35.getCountryname());
Assert.assertEquals("CN", country35.getCountrycode());
Country country34 = countries.get(1);
Assert.assertEquals(Integer.valueOf(34), country34.getId());
Assert.assertEquals("Chile", country34.getCountryname());
Assert.assertEquals("CL", country34.getCountrycode());
} finally {
sqlSession.close();
}
}
@Test
public void testIn() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.where(Sqls.custom().andIn("id", new ArrayList<Integer>(Arrays.asList(35, 183))))
.build();
List<Country> countries = mapper.selectByExample(example);
Country country35 = countries.get(1);
Assert.assertEquals(Integer.valueOf(35), country35.getId());
Assert.assertEquals("China", country35.getCountryname());
Assert.assertEquals("CN", country35.getCountrycode());
Country country183 = countries.get(0);
Assert.assertEquals(Integer.valueOf(183), country183.getId());
Assert.assertEquals("Zambia", country183.getCountryname());
Assert.assertEquals("ZM", country183.getCountrycode());
} finally {
sqlSession.close();
}
}
/*
* @description: 单个where组合查询测试
* 直接把example的构造放到selectByExample()函数里
* */
@Test
public void testWhereCompound0() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
List<Country> countries = mapper.selectByExample(
Example.builder(Country.class)
.where(Sqls.custom()
.andEqualTo("countryname", "China")
.andEqualTo("id", 35)
.orIn("id", new ArrayList<Integer>(Arrays.asList(35, 183)))
.orLike("countryname","Ye%")
)
.build());
Country country35 = countries.get(2);
Assert.assertEquals(Integer.valueOf(35), country35.getId());
Assert.assertEquals("China", country35.getCountryname());
Assert.assertEquals("CN", country35.getCountrycode());
Country country183 = countries.get(0);
Assert.assertEquals(Integer.valueOf(183), country183.getId());
Assert.assertEquals("Zambia", country183.getCountryname());
Assert.assertEquals("ZM", country183.getCountrycode());
Country country179 = countries.get(1);
Assert.assertEquals(Integer.valueOf(179), country179.getId());
Assert.assertEquals("Yemen", country179.getCountryname());
Assert.assertEquals("YE", country179.getCountrycode());
} finally {
sqlSession.close();
}
}
/*
* @description: 单个where组合查询测试
* */
@Test
public void testWhereCompound1() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.where(Sqls.custom()
.andBetween("id", 35, 50)
.orLessThan("id", 40)
.orIsNull("countryname")
)
.build();
List<Country> countries = mapper.selectByExample(example);
Assert.assertEquals(50, countries.size());
} finally {
sqlSession.close();
}
}
/*
* @description: 多个where连接的查询语句测试
* */
@Test
public void testWhereAndWhereCompound() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.where(Sqls.custom()
.andEqualTo("countryname", "China")
.andEqualTo("id", 35)
)
.andWhere(Sqls.custom()
.andEqualTo("id", 183)
)
.build();
List<Country> countries = mapper.selectByExample(example);
Assert.assertEquals(0, countries.size());
} finally {
sqlSession.close();
}
}
/*
* @description: 多个where连接的查询语句测试
* */
@Test
public void testWhereOrWhereCompound() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.where(Sqls.custom()
.andEqualTo("countryname", "China")
.andEqualTo("id", 35)
)
.orWhere(Sqls.custom()
.andEqualTo("id", 183)
)
.build();
List<Country> countries = mapper.selectByExample(example);
Assert.assertEquals(2, countries.size());
} finally {
sqlSession.close();
}
}
/*
* @description: 多个where连接的查询语句测试
* */
@Test
public void testMultiWhereCompound() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.selectDistinct()
.where(Sqls.custom()
.andEqualTo("countryname", "China")
.andEqualTo("id", 35)
)
.orWhere(Sqls.custom()
.andBetween("countryname", 'C', 'H')
.andNotLike("countryname", "Co%")
)
.andWhere(Sqls.custom()
.andLessThan("id", "100")
.orGreaterThan("id", "55")
)
.orWhere(Sqls.custom()
.andEqualTo("countryname", "Cook Is.")
)
.orderByAsc("id", "countryname")
.orderByDesc("countrycode")
.forUpdate()
.build();
List<Country> countries = mapper.selectByExample(example);
Assert.assertEquals(35, countries.size());
} finally {
sqlSession.close();
}
}
/*
* @description: 测试order by
* orderBy()默认为Asc（升序），与orderByAsc()一样
* */
@Test
public void testOrderBy() {
SqlSession sqlSession = MybatisHelper.getSqlSession();
try {
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
Example example = Example.builder(Country.class)
.where(Sqls.custom().andBetween("id", 50, 55))
.orderBy("id").orderByAsc("countryname").orderByDesc("countrycode")
.build();
List<Country> countries = mapper.selectByExample(example);
for (Country country :countries) {
System.out.println(country.getId() + " " + country.getCountryname() + " " + country.getCountrycode());
}
Assert.assertEquals(6, countries.size());
} finally {
sqlSession.close();
}
}
}x
314
import tk.mybatis.mapper.util.Sqls;
1
package tk.mybatis.mapper.test.example;
2
3
import org.apache.ibatis.session.SqlSession;
4
import org.junit.Assert;
5
import org.junit.Test;
6
import tk.mybatis.mapper.entity.Example;
7
import tk.mybatis.mapper.mapper.CountryMapper;
8
import tk.mybatis.mapper.mapper.MybatisHelper;
9
import tk.mybatis.mapper.model.Country;
10
import tk.mybatis.mapper.util.Sqls;
11
12
import java.util.ArrayList;
13
import java.util.Arrays;
14
import java.util.List;
15
16
/**
17
* @author *
18
* @date 2017/11/18
19
*/
20
public class TestExampleBuilder {
21
22
@Test
23
public void testExampleBuilder() {
24
SqlSession sqlSession = MybatisHelper.getSqlSession();
25
try {
26
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
27
Example example = Example.builder(Country.class).build();
28
List<Country> countries = mapper.selectByExample(example);
29
Assert.assertEquals(183, countries.size());
30
31
// 下面的查询会有缓存
32
Example example0 = Example.builder(Country.class)
33
.select().build();
34
List<Country> countries0 = mapper.selectByExample(example0);
35
Assert.assertEquals(183, countries0.size());
36
} finally {
37
sqlSession.close();
38
}
39
}
40
41
@Test
42
public void testDistinct() {
43
SqlSession sqlSession = MybatisHelper.getSqlSession();
44
try {
45
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
46
Example example = Example.builder(Country.class)
47
.distinct()
48
.build();
49
List<Country> countries = mapper.selectByExample(example);
50
Assert.assertEquals(183, countries.size());
51
52
// distinct和order by冲突问题
53
Example example0 = Example.builder(Country.class)
54
.selectDistinct("id", "countryname").build();
55
List<Country> countries0 = mapper.selectByExample(example0);
56
Assert.assertEquals(183, countries0.size());
57
} finally {
58
sqlSession.close();
59
}
60
}
61
62
@Test
63
public void testForUpdate() {
64
SqlSession sqlSession = MybatisHelper.getSqlSession();
65
try {
66
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
67
Example example = Example.builder(Country.class)
68
.select("countryname")
69
.where(Sqls.custom().andGreaterThan("id", 100))
70
.orderByAsc("countrycode")
71
.forUpdate()
72
.build();
73
List<Country> countries = mapper.selectByExample(example);
74
Assert.assertEquals(83, countries.size());
75
} finally {
76
sqlSession.close();
77
}
78
}
79
80
@Test
81
public void testEqualTo() {
82
SqlSession sqlSession = MybatisHelper.getSqlSession();
83
try {
84
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
85
Example example = Example.builder(Country.class)
86
.where(Sqls.custom().andEqualTo("id", "35"))
87
.build();
88
List<Country> countries = mapper.selectByExample(example);
89
Country country = countries.get(0);
90
Assert.assertEquals(Integer.valueOf(35), country.getId());
91
Assert.assertEquals("China", country.getCountryname());
92
Assert.assertEquals("CN", country.getCountrycode());
93
94
} finally {
95
sqlSession.close();
96
}
97
}
98
99
@Test
100
public void testBetween() {
101
SqlSession sqlSession = MybatisHelper.getSqlSession();
102
try {
103
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
104
Example example = Example.builder(Country.class)
105
.where(Sqls.custom().andBetween("id", 34, 35))
106
.build();
107
List<Country> countries = mapper.selectByExample(example);
108
Country country35 = countries.get(0);
109
Assert.assertEquals(Integer.valueOf(35), country35.getId());
110
Assert.assertEquals("China", country35.getCountryname());
111
Assert.assertEquals("CN", country35.getCountrycode());
112
113
Country country34 = countries.get(1);
114
Assert.assertEquals(Integer.valueOf(34), country34.getId());
115
Assert.assertEquals("Chile", country34.getCountryname());
116
Assert.assertEquals("CL", country34.getCountrycode());
117
118
} finally {
119
sqlSession.close();
120
}
121
}
122
123
@Test
124
public void testIn() {
125
SqlSession sqlSession = MybatisHelper.getSqlSession();
126
try {
127
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
128
Example example = Example.builder(Country.class)
129
.where(Sqls.custom().andIn("id", new ArrayList<Integer>(Arrays.asList(35, 183))))
130
.build();
131
List<Country> countries = mapper.selectByExample(example);
132
Country country35 = countries.get(1);
133
Assert.assertEquals(Integer.valueOf(35), country35.getId());
134
Assert.assertEquals("China", country35.getCountryname());
135
Assert.assertEquals("CN", country35.getCountrycode());
136
137
Country country183 = countries.get(0);
138
Assert.assertEquals(Integer.valueOf(183), country183.getId());
139
Assert.assertEquals("Zambia", country183.getCountryname());
140
Assert.assertEquals("ZM", country183.getCountrycode());
141
142
} finally {
143
sqlSession.close();
144
}
145
}
146
/*
147
* @description: 单个where组合查询测试
148
* 直接把example的构造放到selectByExample()函数里
149
* */
150
@Test
151
public void testWhereCompound0() {
152
SqlSession sqlSession = MybatisHelper.getSqlSession();
153
try {
154
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
155
List<Country> countries = mapper.selectByExample(
156
Example.builder(Country.class)
157
.where(Sqls.custom()
158
.andEqualTo("countryname", "China")
159
.andEqualTo("id", 35)
160
.orIn("id", new ArrayList<Integer>(Arrays.asList(35, 183)))
161
.orLike("countryname","Ye%")
162
)
163
.build());
164
Country country35 = countries.get(2);
165
Assert.assertEquals(Integer.valueOf(35), country35.getId());
166
Assert.assertEquals("China", country35.getCountryname());
167
Assert.assertEquals("CN", country35.getCountrycode());
168
169
Country country183 = countries.get(0);
170
Assert.assertEquals(Integer.valueOf(183), country183.getId());
171
Assert.assertEquals("Zambia", country183.getCountryname());
172
Assert.assertEquals("ZM", country183.getCountrycode());
173
174
Country country179 = countries.get(1);
175
Assert.assertEquals(Integer.valueOf(179), country179.getId());
176
Assert.assertEquals("Yemen", country179.getCountryname());
177
Assert.assertEquals("YE", country179.getCountrycode());
178
179
} finally {
180
sqlSession.close();
181
}
182
}
183
184
/*
185
* @description: 单个where组合查询测试
186
* */
187
@Test
188
public void testWhereCompound1() {
189
SqlSession sqlSession = MybatisHelper.getSqlSession();
190
try {
191
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
192
Example example = Example.builder(Country.class)
193
.where(Sqls.custom()
194
.andBetween("id", 35, 50)
195
.orLessThan("id", 40)
196
.orIsNull("countryname")
197
)
198
.build();
199
List<Country> countries = mapper.selectByExample(example);
200
Assert.assertEquals(50, countries.size());
201
} finally {
202
sqlSession.close();
203
}
204
}
205
/*
206
* @description: 多个where连接的查询语句测试
207
* */
208
@Test
209
public void testWhereAndWhereCompound() {
210
SqlSession sqlSession = MybatisHelper.getSqlSession();
211
try {
212
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
213
Example example = Example.builder(Country.class)
214
.where(Sqls.custom()
215
.andEqualTo("countryname", "China")
216
.andEqualTo("id", 35)
217
)
218
.andWhere(Sqls.custom()
219
.andEqualTo("id", 183)
220
)
221
.build();
222
List<Country> countries = mapper.selectByExample(example);
223
Assert.assertEquals(0, countries.size());
224
225
} finally {
226
sqlSession.close();
227
}
228
}
229
230
/*
231
* @description: 多个where连接的查询语句测试
232
* */
233
@Test
234
public void testWhereOrWhereCompound() {
235
SqlSession sqlSession = MybatisHelper.getSqlSession();
236
try {
237
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
238
Example example = Example.builder(Country.class)
239
.where(Sqls.custom()
240
.andEqualTo("countryname", "China")
241
.andEqualTo("id", 35)
242
)
243
.orWhere(Sqls.custom()
244
.andEqualTo("id", 183)
245
)
246
.build();
247
List<Country> countries = mapper.selectByExample(example);
248
Assert.assertEquals(2, countries.size());
249
250
} finally {
251
sqlSession.close();
252
}
253
}
254
255
/*
256
* @description: 多个where连接的查询语句测试
257
* */
258
@Test
259
public void testMultiWhereCompound() {
260
SqlSession sqlSession = MybatisHelper.getSqlSession();
261
try {
262
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
263
Example example = Example.builder(Country.class)
264
.selectDistinct()
265
.where(Sqls.custom()
266
.andEqualTo("countryname", "China")
267
.andEqualTo("id", 35)
268
)
269
.orWhere(Sqls.custom()
270
.andBetween("countryname", 'C', 'H')
271
.andNotLike("countryname", "Co%")
272
)
273
.andWhere(Sqls.custom()
274
.andLessThan("id", "100")
275
.orGreaterThan("id", "55")
276
)
277
.orWhere(Sqls.custom()
278
.andEqualTo("countryname", "Cook Is.")
279
)
280
.orderByAsc("id", "countryname")
281
.orderByDesc("countrycode")
282
.forUpdate()
283
.build();
284
List<Country> countries = mapper.selectByExample(example);
285
Assert.assertEquals(35, countries.size());
286
287
} finally {
288
sqlSession.close();
289
}
290
}
291
292
/*
293
* @description: 测试order by
294
* orderBy()默认为Asc（升序），与orderByAsc()一样
295
* */
296
@Test
297
public void testOrderBy() {
298
SqlSession sqlSession = MybatisHelper.getSqlSession();
299
try {
300
CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
301
Example example = Example.builder(Country.class)
302
.where(Sqls.custom().andBetween("id", 50, 55))
303
.orderBy("id").orderByAsc("countryname").orderByDesc("countrycode")
304
.build();
305
List<Country> countries = mapper.selectByExample(example);
306
for (Country country :countries) {
307
System.out.println(country.getId() + " " + country.getCountryname() + " " + country.getCountrycode());
308
}
309
Assert.assertEquals(6, countries.size());
310
} finally {
311
sqlSession.close();
312
}
313
}
314
}