mybatis-plus CRUD DQL编程控制

丘丘用户思思澪

已于 2023-03-23 16:31:59 修改

阅读量327

点赞数

分类专栏： spring学习笔记文章标签： mybatis java 数据库

于 2023-03-23 10:35:53 首次发布

本文链接：https://blog.csdn.net/qq_44021627/article/details/129709181

版权

spring学习笔记专栏收录该内容

25 篇文章 0 订阅

订阅专栏

假设数据库存在有一个表，表的结构内容如下所示：

create database if not exists mybatisplus_db character set utf8;
use mybatisplus_db;
CREATE TABLE user (
	id bigint(20) primary key auto_increment,
	name varchar(32) not null,
	password varchar(32) not null,
	age int(3) not null ,
	tel varchar(32) not null
);
insert into user values(1,'Tom','tom',3,'18866668888');
insert into user values(2,'Jerry','jerry',4,'16688886666');
insert into user values(3,'Jock','123456',41,'18812345678');
insert into user values(4,'传智播客','itcast',15,'4006184000');

实体类：

public class User {
	private Long id;
	private String name;
	private String password;
	private Integer age;
	private String tel;
	//setter...getter...toString方法略
}

接口：

@Mapper
public interface UserDao extends BaseMapper<User>{
}

标准CRUD使用

在这里插入图片描述

1.新增

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testSave() {
		User user = new User();
		user.setName("黑马程序员");
		user.setPassword("itheima");
		user.setAge(12);
		user.setTel("4006184000");
		userDao.insert(user);
	}
}

2. 删除

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testDelete() {
		userDao.deleteById(1401856123725713409L);
	}
}

3.修改

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {
	@Autowired
	private UserDao userDao;
		@Test
		void testUpdate() {
		User user = new User();
		user.setId(1L);
		user.setName("Tom888");
		user.setPassword("tom888");
		userDao.updateById(user);
	}
}

说明:修改的时候，只修改实体对象中有值的字段。

4.根据ID查询

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetById() {
		User user = userDao.selectById(2L);
		System.out.println(user);
	}
}

5.查询所有

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll() {
		List<User> userList = userDao.selectList(null);
		System.out.println(userList);
	}
}

6.分页功能

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {
	@Autowired
	private UserDao userDao;
	//分页查询
	@Test
	void testSelectPage(){
		//1 创建IPage分页对象,设置分页参数,1为当前页码，3为每页显示的记录数
		IPage<User> page=new Page<>(1,3);
		//2 执行分页查询
		userDao.selectPage(page,null);
		//3 获取分页结果
		System.out.println("当前页码值："+page.getCurrent());
		System.out.println("每页显示数："+page.getSize());
		System.out.println("一共多少页："+page.getPages());
		System.out.println("一共多少条数据："+page.getTotal());
		System.out.println("数据："+page.getRecords());
	}
}

设置分页拦截器（官方文档中有详细的说明）

@Configuration
public class MybatisPlusConfig {
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor(){
		//1 创建MybatisPlusInterceptor拦截器对象
		MybatisPlusInterceptor mpInterceptor=new MybatisPlusInterceptor();
		//2 添加分页拦截器
		mpInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
		return mpInterceptor;
	}
}

DQL编程控制

QueryWrapper

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		QueryWrapper qw = new QueryWrapper();
		qw.lt("age",18);
		List<User> userList = userDao.selectList(qw);
		System.out.println(userList);
	}
}

缺点：条件与数据库表的列名绑定，容易出错，比如age写错，就会导致查询不成功。一旦表的列名发生更改，涉及到变动的列名相关代码都需要更改。

QueryWrapper的基础上使用lambda

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		QueryWrapper<User> qw = new QueryWrapper<User>();
		qw.lambda().lt(User::getAge, 10);//添加条件
		List<User> userList = userDao.selectList(qw);
		System.out.println(userList);
	}
}

User::getAge,为lambda表达式中的，类名::方法名。构建LambdaQueryWrapper的时候泛型不能省，但是qw后面多了一层lambda()调用。

LambdaQueryWrapper

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
		lqw.lt(User::getAge, 10);
		List<User> userList = userDao.selectList(lqw);
		System.out.println(userList);
	}
}

1.条件查询

查询数据库表中，年龄在10岁到30岁之间的用户信息：

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
		lqw.lt(User::getAge, 30);
		lqw.gt(User::getAge, 10);
		//支持链式编程
		//lqw.lt(User::getAge, 30).gt(User::getAge, 10);
		List<User> userList = userDao.selectList(lqw);
		System.out.println(userList);
	}
}

selectList：查询结果为多个或者单个
selectOne:查询结果为单个

2.null值判定

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		//模拟页面传递过来的查询数据
		UserQuery uq = new UserQuery();
		uq.setAge(10);
		uq.setAge2(30);
		LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
		lqw.lt(null!=uq.getAge2(),User::getAge, uq.getAge2());
		lqw.gt(null!=uq.getAge(),User::getAge, uq.getAge());
		List<User> userList = userDao.selectList(lqw);
		System.out.println(userList);
	}
}

在这里插入图片描述
condition为boolean类型，返回true，则添加条件，返回false则不添加条件、

3.查询投影

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
		lqw.select(User::getId,User::getName,User::getAge);
		List<User> userList = userDao.selectList(lqw);
		System.out.println(userList);
		}
}

4.聚合查询

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		QueryWrapper<User> lqw = new QueryWrapper<User>();
		//lqw.select("count(*) as count");
		//SELECT count(*) as count FROM user
		//lqw.select("max(age) as maxAge");
		//SELECT max(age) as maxAge FROM use
		//lqw.select("min(age) as minAge");
		//SELECT min(age) as minAge FROM user
		//lqw.select("sum(age) as sumAge");
		//SELECT sum(age) as sumAge FROM user
		lqw.select("avg(age) as avgAge");
		//SELECT avg(age) as avgAge FROM user
		List<Map<String, Object>> userList = userDao.selectMaps(lqw);
		System.out.println(userList);
	}
}

5.分组查询

@SpringBootTest
class Mybatisplus02DqlApplicationTests {
	@Autowired
	private UserDao userDao;
	@Test
	void testGetAll(){
		QueryWrapper<User> lqw = new QueryWrapper<User>();
		lqw.select("count(*) as count,tel");
		lqw.groupBy("tel");
		List<Map<String, Object>> list = userDao.selectMaps(lqw);
		System.out.println(list);
	}
}

查询条件

1.等值查询
eq(R column, Object val); // 等价于 =

void testGetAll(){
	LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
	lqw.eq(User::getName, "Jerry").eq(User::getPassword, "jerry");
	User loginUser = userDao.selectOne(lqw);
	System.out.println(loginUser);
}

2.范围查询
ne(R column, Object val); // 等价于 <>
gt(R column, Object val); // 等价于 >
ge(R column, Object val); // 等价于 >=
lt(R column, Object val); // 等价于 <
le(R column, Object val); // 等价于 <=
between(R column, Object val1, Object val2); // 等价于 between a and b
notBetween(R column, Object val1, Object val2); // 等价于 not between a and b

void testGetAll(){
	LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
	lqw.between(User::getAge, 10, 30);
	//SELECT id,name,password,age,tel FROM user WHERE (age BETWEEN 10 AND 30)
	//lqw.lt(User::getAge, 30).gt(User::getAge, 10);
	List<User> userList = userDao.selectList(lqw);
	System.out.println(userList);
}

3.模糊查询
like(R column, Object val); // 等价于 LIKE ‘%值%’
notLike(R column, Object val); // 等价于 NOT LIKE ‘%值%’
likeLeft(R column, Object val); // 等价于 LIKE ‘%值’
likeRight(R column, Object val); // 等价于 LIKE ‘值%’

void testGetAll(){
LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
lqw.likeLeft(User::getName, "J");
//SELECT id,name,password,age,tel FROM user WHERE (name LIKE J%)
List<User> userList = userDao.selectList(lqw);
System.out.println(userList);
}

4.排序查询
orderByAsc(R… columns); // 等价于 ORDER BY 字段, … ASC
orderByDesc(R… columns); // 等价于 ORDER BY 字段, … DESC

void testGetAll(){
	LambdaQueryWrapper<User> lwq = new LambdaQueryWrapper<>();
	/**
	* condition ：条件，返回boolean，
	当condition为true，进行排序，如果为false，则不排序
	* isAsc:是否为升序，true为升序，false为降序
	* columns：需要操作的列
	*/
	lwq.orderBy(true,false, User::getId);
	userDao.selectList(lw
}

5.分组查询
groupBy(R… columns); // 等价于 GROUP BY 字段, …
having(String sqlHaving, Object… params); // 等价于 HAVING ( sql语句 )

void testGetAll(){
	LambdaQueryWrapper<User> lwq = new LambdaQueryWrapper<>();
	lqw.groupBy(User::getTel).having("count (0) >1");
	List<Map<String, Object>> list = userDao.selectMaps(lqw);
	System.out.println(list);
}

6.包含性匹配
in(R column, Object… values); // 等价于字段 IN (v0, v1, …)
notIn(R column, Object… values); // 等价于字段 NOT IN (v0, v1, …)
inSql(R column, Object… values); // 等价于字段 IN (sql 语句)
notInSql(R column, Object… values); // 等价于字段 NOT IN (sql 语句)

7.空值比较
isNull(R column); // 等价于 IS NULL
isNotNull(R column); // 等价于 IS NOT NULL

void testGetAll(){
	LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
	lqw.isNotNUll(User::getName).bewteen(User::getAge, 10, 30);
	User loginUser = userDao.selectList(lqw);
	System.out.println(loginUser);
}

8.拼接嵌套
or(); // 等价于 a or b
or(Consumer consumer); // 等价于 or(a or/and b)，or 嵌套。例: or(i -> i.lt(User::getAge, 10).gt(User::getAge, 30)) —> or (age > 10 and age < 30)
and(Consumer consumer); // 等价于 and(a or/and b)，and 嵌套。例: and(i -> i.lt(User::getAge, 10).gt(User::getAge, 30)) —> and (age > 10 and age < 30)
nested(Consumer consumer); // 等价于 (a or/and b)，普通嵌套。例: nest(i -> i.lt(User::getAge, 10).gt(User::getAge, 30)) —> (age > 10 and
apply(String applySql, Object… params); // 拼接sql（若不使用 params 参数，可能存在 sql 注入。例: apply(“date_format(dateColumn,‘%Y-%m-%d’) = {0}”, “2008-08-08”) —> date_format(dateColumn,‘%Y-%m-%d’) = ‘2008-08-08’")
last(String lastSql); // 无视优化规则直接拼接到 sql 的最后，可能存若在 sql 注入。
exists(String existsSql); // 拼接 exists 语句

void testGetAll(){
	LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
	lqw.lt(User::getAge, 10).or().gt(User::getAge, 30);
	List<User> userList = userDao.selectList(lqw);
	System.out.println(userList);
}

实体类优化

1.利用Lombok实现模型类的简化
Lombok常见的注解有:

@Setter:为模型类的属性提供setter方法
@Getter:为模型类的属性提供getter方法
@ToString:为模型类的属性提供toString方法
@EqualsAndHashCode:为模型类的属性提供equals和hashcode方法
@Data:是个组合注解，包含上面的注解的功能
@NoArgsConstructor:提供一个无参构造函数
@AllArgsConstructor:提供一个包含所有参数的构造函数

所以模型类的优化：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
	private Long id;
	private String name;
	private String password;
	private Integer age;
	private String tel;
}

2.映射匹配兼容性
在这里插入图片描述

@Data
public class User {
	private Long id;
	private String name;
	@TableField("pwd",select=false)
	//数据库列名为pwd,查询时将pwd隐藏
	private String password;
	private Integer age;
	private String tel;
	@TableField(exist=false)
	//排除数据库不存在的字段online
	private Boolean online; 
}

在这里插入图片描述

@Data
@TableName("tbl_user")
//数据库名为tbl_user
public class User {
	private Long id;
	private String name;
	@TableField("pwd",select=false)
	//数据库列名为pwd,查询时将pwd隐藏
	private String password;
	private Integer age;
	private String tel;
	@TableField(exist=false)
	//排除数据库不存在的字段online
	private Boolean online; 
}

丘丘用户思思澪

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mybatis-plus CRUD DQL编程控制

例: and(i -> i.lt(User::getAge, 10).gt(User::getAge, 30)) —> and (age > 10 and age < 30)例: or(i -> i.lt(User::getAge, 10).gt(User::getAge, 30)) —> or (age > 10 and age < 30)例: nest(i -> i.lt(User::getAge, 10).gt(User::getAge, 30)) —> (age > 10 and。
复制链接

扫一扫