通用Mapper新特性:ExampleBuilder 2017年12月18日

时间:2024-05-06 09:37:50
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
}