一文掌握MyBatis?Plus的條件構(gòu)造器方法
1.組裝查詢條件
組裝查詢其實很簡單,可以支持條件的鏈?zhǔn)骄幊蹋?/p>
查詢用戶名包含a,年齡在 10 - 20 之間并且郵箱不為空的用戶:
@Test void contextLoads() { // 查詢用戶名包含a,年齡在 10 - 20 之間并且郵箱不為空的用戶 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like("name","a") .between("age",10,20) .isNotNull("email"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL) ==> Parameters: %a%(String), 10(Integer), 20(Integer) <== Columns: id, name, age, email, is_delete <== Row: 2, dada, 11, 111@qq.com, 0 <== Row: 4, dahe, 12, 34567@qq.com, 0 <== Total: 2
2.組裝排序條件
查詢用戶信息,按照年齡的降序排序,若年齡相同,則按照id升序排序:
@Test void contextLoads() { // 查詢用戶信息,按照年齡的降序排序,若年齡相同,則按照id升序排序 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.orderByDesc("age").orderByAsc("id"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 ORDER BY age DESC,id ASC ==> Parameters: <== Columns: id, name, age, email, is_delete <== Row: 3, dahe, 33, 34567@qq.com, 0 <== Row: 4, dahe, 12, 34567@qq.com, 0 <== Row: 2, dada, 11, 111@qq.com, 0 <== Total: 3
3.使用Lambda表達(dá)式解決條件優(yōu)先級
將用戶名中包含有a并且(年齡大于20或郵箱為null)的用戶信息查詢出來:
Lambda中的條件優(yōu)先執(zhí)行!
@Test void contextLoads() { // 將用戶名中包含有a并且(年齡大于20或郵箱為null)的用戶信息查詢出來 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like("name", "a") .and(i -> i.gt("age", 20).or().isNull("email")); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (name LIKE ? AND (age > ? OR email IS NULL)) ==> Parameters: %a%(String), 20(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, dahe, 33, 34567@qq.com, 0 <== Total: 1
4.組裝select語句
有時我們不想查詢數(shù)據(jù)庫某張表的所有字段,可以使用maps集合選擇只查詢某些字段的值:
@Test void contextLoads() { QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.select("name","email"); List<Map<String, Object>> maps = userMapper.selectMaps(wrapper); maps.forEach(System.out::println); }
==> Preparing: SELECT name,email FROM user WHERE is_delete=0 ==> Parameters: <== Columns: name, email <== Row: dada, 111@qq.com <== Row: dahe, 34567@qq.com <== Row: dahe, 34567@qq.com <== Total: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@237add] {name=dada, email=111@qq.com} {name=dahe, email=34567@qq.com} {name=dahe, email=34567@qq.com}
5.組裝子查詢
@Test void contextLoads() { QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.inSql("id","select id from user where id > 1"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (id IN (select id from user where id > 1)) ==> Parameters: <== Columns: id, name, age, email, is_delete <== Row: 2, dada, 11, 111@qq.com, 0 <== Row: 3, dahe, 33, 34567@qq.com, 0 <== Row: 4, dahe, 12, 34567@qq.com, 0 <== Total: 3
6.使用UpdateWrapper實現(xiàn)修改功能
將名字中包含a的記錄修改:
@Test void contextLoads() { // 將名字中包含a的記錄修改 UpdateWrapper<User> wrapper = new UpdateWrapper<>(); wrapper.like("name","a"); wrapper.set("name","hello").set("age",18); userMapper.update(null, wrapper); }
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@36c07c75] will not be managed by Spring ==> Preparing: UPDATE user SET name=?,age=? WHERE is_delete=0 AND (name LIKE ?) ==> Parameters: hello(String), 18(Integer), %a%(String) <== Updates: 3
7.模擬開發(fā)中組裝條件的情況
真實的開發(fā)中往往有這樣的情況:我們不知道前端傳來的條件是什么,此時無法正常的拼接sql,那么就需要在后端進(jìn)行相應(yīng)的判斷:
@Test void contextLoads() { String name = null; Integer ageBegin = 18; Integer ageEnd = 28; QueryWrapper<User> wrapper = new QueryWrapper<>(); if (StringUtils.isNotBlank(name)) { wrapper.like("name", name); } if (ageBegin != null) { wrapper.ge("age", ageBegin); } if (ageEnd != null) { wrapper.le("age", ageEnd); } List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (age >= ? AND age <= ?) ==> Parameters: 18(Integer), 28(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, hello, 18, 34567@qq.com, 0 <== Row: 4, hello, 18, 34567@qq.com, 0 <== Total: 2
但是,這樣的判斷我們認(rèn)為過于的簡陋和復(fù)雜,事實上,MyBatis Plus的條件構(gòu)造器中為我們封裝了解決此類問題的一些重載方法:
@Test void contextLoads() { String name = null; Integer ageBegin = 18; Integer ageEnd = 28; QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like(StringUtils.isNotBlank(name), "name", name); wrapper.ge(ageBegin != null, "age", ageBegin); wrapper.le(ageEnd != null, "age", ageEnd); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (age >= ? AND age <= ?) ==> Parameters: 18(Integer), 28(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, hello, 18, 34567@qq.com, 0 <== Row: 4, hello, 18, 34567@qq.com, 0 <== Total: 2
8.LambdaQueryWrapper和LambdaUpdateWrapper
使用LambdaQueryWrapper
和LambdaUpdateWrapper
可以防止我們的字段名修改導(dǎo)致的程序錯誤或字段名寫錯
例如:
@Test void contextLoads() { String name = null; Integer ageBegin = 18; Integer ageEnd = 28; LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>(); wrapper.like(StringUtils.isNotBlank(name), User::getName, name); wrapper.ge(ageBegin != null, User::getAge, ageBegin); wrapper.le(ageEnd != null, User::getAge, ageEnd); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (age >= ? AND age <= ?) ==> Parameters: 18(Integer), 28(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, hello, 18, 34567@qq.com, 0 <== Row: 4, hello, 18, 34567@qq.com, 0 <== Total: 2
LambdaUpdateWrapper
的使用方法類似
到此這篇關(guān)于MyBatis Plus的條件構(gòu)造器的文章就介紹到這了,更多相關(guān)MyBatis Plus的條件構(gòu)造器內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
使用JPA自定義VO類型轉(zhuǎn)換(EntityUtils工具類)
這篇文章主要介紹了使用JPA自定義VO類型轉(zhuǎn)換(EntityUtils工具類),具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2021-11-11IntelliJ IDEA報錯Error:java: Compilation failed: internal java
今天小編就為大家分享一篇關(guān)于IntelliJ IDEA報錯Error:java: Compilation failed: internal java compiler error的解決辦法,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2018-10-10Java多線程 ReentrantReadWriteLock原理及實例詳解
這篇文章主要介紹了Java多線程 ReentrantReadWriteLock原理及實例詳解,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-09-09Jdbctemplate多數(shù)據(jù)源配置方法詳解
這篇文章主要介紹了Jdbctemplate多數(shù)據(jù)源配置方法詳解,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-06-06基于Springboot+Junit+Mockito做單元測試的示例
本篇文章主要介紹了基于Springboot+Junit+Mockito做單元測試的示例,小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2018-02-02簡單了解java中靜態(tài)初始化塊的執(zhí)行順序
這篇文章主要介紹了簡單了解java中靜態(tài)初始化塊的執(zhí)行順序,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2019-10-10