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 wuyi * @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 countries = mapper.selectByExample(example); Assert.assertEquals(183, countries.size()); // 下面的查询会有缓存 Example example0 = Example.builder(Country.class) .select().build(); List 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 countries = mapper.selectByExample(example); Assert.assertEquals(183, countries.size()); // distinct和order by冲突问题 Example example0 = Example.builder(Country.class) .selectDistinct("id", "countryname").build(); List 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 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 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 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 (Arrays.asList(35, 183)))) .build(); List 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 countries = mapper.selectByExample( Example.builder(Country.class) .where(Sqls.custom() .andEqualTo("countryname", "China") .andEqualTo("id", 35) .orIn("id", new ArrayList (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 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 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 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 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 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 wuyi
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
Listcountries = mapper.selectByExample(example);
29
Assert.assertEquals(183, countries.size());
30
31
// 下面的查询会有缓存
32
Example example0 = Example.builder(Country.class)
33
.select().build();
34
Listcountries0 = 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
Listcountries = 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
Listcountries0 = 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
Listcountries = 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
Listcountries = 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
Listcountries = 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 (Arrays.asList(35, 183)))) 130
.build();
131
Listcountries = 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
Listcountries = mapper.selectByExample(
156
Example.builder(Country.class)
157
.where(Sqls.custom()
158
.andEqualTo("countryname", "China") 159
.andEqualTo("id", 35) 160
.orIn("id", new ArrayList (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
Listcountries = 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
Listcountries = 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
Listcountries = 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
Listcountries = 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
Listcountries = 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
}