文章目錄

• • • 1、數據庫 Schema 腳本如下：
    • 2、數據庫 Data 腳本：
    • 3. 實戰

現有一張 User 表，其表結構如下：

idnameageemail

1	Jone	18	test1@gblfy.com
2	Jack	20	test2@gblfy.com
3	Sandy	28	test3@gblfy.com
4	Jone	21	test4@gblfy.com
5	Billie	24	test5@gblfy.com

1、數據庫 Schema 腳本如下：

DROP TABLE IF EXISTS user;CREATE TABLE user (id BIGINT(20) NOT NULL COMMENT '主鍵ID',name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',age INT(11) NULL DEFAULT NULL COMMENT '年齡',email VARCHAR(50) NULL DEFAULT NULL COMMENT '郵箱',create_time DATETIME DEFAULT NULL COMMENT '創建時間',PRIMARY KEY (id) );

2、數據庫 Data 腳本：

DELETE FROM user;INSERT INTO user (id, name, age, email, create_time) VALUES (1, 'Jone', 18, 'test1@gblfy.com','2019-01-11 14:20:20'), (2, 'Jack', 20, 'test2@gblfy.com','2019-02-05 11:12:22'), (3, 'Tom', 28, 'test3@gblfy.com','2019-02-14 08:31:16'), (4, 'Sandy', 21, 'test4@gblfy.com','2019-01-14 09:15:15'), (5, 'Billie', 24, 'test5@gblfy.com','2019-01-14 09:48:16');

3. 實戰

package com.gblfy.springboot.mybatisplus.mapper;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.toolkit.StringUtils; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper; import com.gblfy.springboot.mybatisplus.entity.User; import lombok.extern.slf4j.Slf4j; import org.junit.Assert; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map;@RunWith(SpringJUnit4ClassRunner.class) @SpringBootTest @Slf4j public class UserSelectTest {@Autowiredprivate UserMapper userMapper;@Testpublic void selectList() {//1.測試是否聯通數據庫 查詢所有List<User> userList = userMapper.selectList(null);//2.斷言 查詢的數據是否是5條Assert.assertEquals(5, userList.size());//3.循環打印數據到控制臺userList.forEach(System.out::println);}@Testpublic void insert() {User user = new User().builder().name("gblfy").age(2).email("gblfy@qq.com").build();int rows = userMapper.insert(user);log.info("影響數據庫的條數: {}", rows);}/*** 通過主鍵id查詢用戶信息*/@Testpublic void selectUserById() {User user = userMapper.selectById(1238735228017610754l);System.out.println("通過主鍵id查詢用戶信息:" + user);}/*sql形式: SELECT id,name,age,email FROM user WHERE id=?*//*** 通過多個主鍵id查詢多個用戶信息*/@Testpublic void selectBatchIds() {List<Long> ids = Arrays.asList(1238735228017610754l, 1238735794525503489l);List<User> userList = userMapper.selectBatchIds(ids);userList.forEach(System.out::println);}/*sql形式: SELECT id,name,age,email FROM user WHERE id IN ( ? , ? )*//*** 通過map查詢一個用戶信息*/@Testpublic void selectByMap() {Map<String, Object> map = new HashMap<>();map.put("name", "Tom");map.put("age", 28);List<User> userList = userMapper.selectByMap(map);userList.forEach(System.out::println);}/*sql形式: SELECT id,name,age,email FROM user WHERE name = ? AND age = ?*//*** 通過map查詢多個用戶信息*/@Testpublic void selectByMap2() {Map<String, Object> map = new HashMap<>();map.put("age", 2);List<User> userList = userMapper.selectByMap(map);userList.forEach(System.out::println);}/*sql形式:SELECT id,name,age,email FROM user WHERE age = ?*/// ---------------------------------------進階------------------------------------/*** 1. 名字包含雨并且年齡小于40* sql：name like '%雨%' and age < 40* like '%值%'* it <* le =<* gt >* ge >=*/@Testpublic void selectByWrapper() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.like("name", "雨").lt("age", 40);List<User> userList = userMapper.selectList(queryWrapper);userList.forEach(System.out::println);}/*sql形式:SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age < ?*//*** 2. 名字包含雨并且年齡大于等于20且小于等于40并且郵箱不為空* sql：name like '%雨%' and age between 20 and 40 and email is not null*/@Testpublic void selectByWrapper2() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.like("name", "雨").between("age", 20, 28).isNotNull("email");List<User> userList = userMapper.selectList(queryWrapper);userList.forEach(System.out::println);}/*sql形式: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL*//*** 3. 名字為郭姓或者年齡大于等于25，按照年齡降序排列，年齡相同的按照id升序排列* sql：name like '郭%' or age >= 25 order by age desc,id asc*/@Testpublic void selectByWrapper3() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.likeRight("name", "郭").or().ge("age", 25).orderByDesc("age").orderByAsc("id");List<User> userList = userMapper.selectList(queryWrapper);userList.forEach(System.out::println);}/*sql形式: SELECT id,name,age,email FROM user WHERE (name LIKE ? OR age >= ?) ORDER BY age DESC,id ASC*//*** 7. (年齡小于40或者郵箱不為空)并且名字為郭姓* sql：(age < 40 or email id not null) and name like '郭%'*/@Testpublic void selectByWrapper7() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.nested(wq -> wq.lt("age", 40).or().isNotNull("email")).likeRight("name", "郭");List<User> userList = userMapper.selectList(queryWrapper);userList.forEach(System.out::println);}/*sql形式: SELECT id,name,age,email FROM user WHERE ((age < ? OR email IS NOT NULL) AND name LIKE ?)sql執行原則：1. 正常sql中,先執行and后執行or2. sql中存在括號(),先執行括號()里面的sql再執行，括號()外邊的sql場景1:SELECT id,name,age,email FROM user WHERE age < ? OR email IS NOT NULL AND name LIKE ?)場景2:SELECT id,name,age,email FROM user WHERE ((age < ? OR email IS NOT NULL) AND name LIKE ?)SELECT id,name,age,email FROM user WHERE (age < ? OR email IS NOT NULL) AND name LIKE ?*//*** 8. 年齡為30、31、34、35* age in (30、31、34、35 )*/@Testpublic void selectByWrapper8() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.in("age", Arrays.asList(18, 20, 22, 28));List<User> userList = userMapper.selectList(queryWrapper);userList.forEach(System.out::println);}/*sql形式: SELECT id,name,age,email FROM user WHERE (age IN (?,?,?,?))*/// -------------------------------------------Select語句中不列出全部字段---------------------------------------------------/*** 1. 名字包含雨并且年齡小于30* sql：name like '%雨%' and age < 30*/@Testpublic void selectByWrapperSupper() {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.like("name", "雨").lt("age", 30);List<User> userList = userMapper.selectList(queryWrapper);userList.forEach(System.out::println);}/*sql形式：SELECT id,name,age,email FROM user WHERE (name LIKE ? AND age < ?)這種情況，你查詢什么列，如果有值就會查詢出來，沒查詢的列的值默認為null*//*** 用like的Condition* <p>* 當點擊查詢按鈕時,查詢搜索框中默認為null,也支持傳入條件* 當傳入的條件不為null或者不為空""時,會拼接在where 的后邊當做條件** @param name* @param email*/private void condition(String name, String email) {QueryWrapper<User> queryWrapper = new QueryWrapper<>();queryWrapper.like(StringUtils.isNotEmpty(name), "name", name).like(StringUtils.isNotEmpty(email), "email", email);List<User> userList = userMapper.selectList(queryWrapper);userList.forEach(System.out::println);}/*sql形式：SELECT id,name,age,email FROM user WHERE (name LIKE ?)*//*** 測試Condition*/@Testpublic void testCondition() {String name = "郭";String email = "";condition(name, email);}// ---------------------------------建議使用LambdaQueryWrapper，防誤寫-------------------------------------/*** 這樣寫的好處防誤寫* 查詢用戶名字中包含雨并且年齡小于40*/@Testpublic void selectLambda() {LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();lambdaQuery.like(User::getName, "雨").lt(User::getAge, 40);List<Object> userList = userMapper.selectObjs(lambdaQuery);userList.forEach(System.out::println);}/*** sql形式：* SELECT id,name,age,email FROM user WHERE (name LIKE ? AND age < ?)*//*** 5. 名字為郭姓并且(年齡小于40或者郵箱不為空)* sql：name like '郭%' and (age < 40 or email is not null)* 只返回第一個字段的值 id*/@Testpublic void selectLambda2() {LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();lambdaQuery.likeRight(User::getName, "郭").lt(User::getAge, 40).isNotNull(User::getEmail);List<Object> userList = userMapper.selectObjs(lambdaQuery);userList.forEach(System.out::println);}/*** sql形式：* SELECT id,name,age,email FROM user WHERE (name LIKE ? AND age < ? AND email IS NOT NULL)*/@Testpublic void selectLambda3() {List<User> userList = new LambdaQueryChainWrapper<User>(userMapper).like(User::getName, "雨").ge(User::getAge, 20).list();userList.forEach(System.out::println);}/*** sql形式：* SELECT id,name,age,email FROM user WHERE (name LIKE ? AND age < ? AND email IS NOT NULL)*/// --------------------------------自定義sql------------------------------/*** 自定義sql*/@Testpublic void selectMy() {LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();lambdaQuery.likeRight(User::getName, "郭").and(lqw -> lqw.lt(User::getAge, 40).or().isNotNull(User::getEmail));//調用自定義sqlList<User> userList = userMapper.selectAll(lambdaQuery);userList.forEach(System.out::println);} // select * from user WHERE (name LIKE ? AND (age < ? OR email IS NOT NULL))}

總結

以上是生活随笔為你收集整理的MyBatisPlus_查询篇_入门试炼_01的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。