前言
在只使用mybatis框架作为DAO层开发时,会创建大量的Mapper.java接口、Mapper.xml配置,而且接口、配置中的分别的CRUD大致都一样,导致项目中的代码、文件很臃肿,难以维护。那有什么办法解决呢?
简介
MyBatis-Plus(简称 MP)是一个 MyBatis的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
mybatis与mybatis-plus的关系
mybatis-plus架构
mybatis与mybatis-plus优缺点对比
MyBatis
优点:
-
SQL语句可根据需求*编写,较为灵活
-
SQL与业务代码分离,易于阅读与维护
-
提供动态SQL语句,可以根据需求灵活控制
缺点:
-
简单的crud操作也必须提供对应SQL语句
-
必须维护大量的xxxMapper.xml文件
-
自身功能有限,要拓展只能依赖第三方插件(pagehelper)
MyBatis-plus 是在Mybatis的基础上进行二次开发,具有MyBatis所有功能,还弥补了mybatis的缺点
-
提供无sql 的crud操作
-
内置代码生成器,分页插件, 性能分析插件等
-
提供功能丰富的条件构造器快速进行无sql开发
入门案例
- 搭建springboot项目
- 导入依赖包
<!-- mybatis-plus启动器 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>provided</scope>
</dependency>
<!-- druid连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
<!-- mysql连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
- 数据库创建表
CREATE TABLE `employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`admin` bit(1) DEFAULT NULL,
`dept_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1327139013313564674 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
CREATE TABLE `department` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表示部门编号,由两位数字所组成',
`name` varchar(14) DEFAULT NULL COMMENT '部门名称,最多由14个字符所组成',
`sn` varchar(13) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
- 创建实体类
@Getter
@Setter
@ToString
public class Employee {
private Long id;
private String username;
private String password;
private String email;
private int age;
private Boolean admin;
private Long deptId;
}
- 创建mapper接口,继承BaseMapper
public interface EmployeeMapper extends BaseMapper<Employee> {
}
- 配置文件(application.properties)中配置连接四要素
#mysql
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis-plus?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
- 启动类中扫描mapper接口
@MapperScan(basePackages = "mapper包路径")
- 新建测试类
@SpringBootTest
public class CRUDTest {
@Autowired
private EmployeeMapper employeeMapper;
@Test
public void testSave(){
Employee employee = new Employee();
employee.setUsername("xiaoming");
employee.setPassword("123");
employee.setAdmin(true);
employee.setAge(17);
employee.setEmail("xiaoming@qq.com");
employee.setDeptId(1L);
employeeMapper.insert(employee);
}
}
好的,一个简单的mybatis-plus新增案例完成了,执行后,数据库增加一条数据。
提出问题
为什么EmployeeMapper中没有编写insert方法,但可以执行成功?
进入mapper继承的BaseMapper类中,发现里面有我们调用的方法,但方法中没有具体实现。
/**
* 插入一条记录
*
* @param entity 实体对象
*/
int insert(T entity);
为什么不写sql语句也能实现sql操作呢?
EmployeeMapper接口定义的时候需要明确指定操作的实体对象:Employee
public interface EmployeeMapper extends BaseMapper<Employee> {
}
mybatis-plus底层通过反射解析该实体类对象,获取实体类对象的类名、字段,作为拼接sql语句的表名和字段名。然后根据调用的方法,拼接出对应的sql语句。
注意:数据库的列名要和实体类的字段名一样,表名和实体类名一样
如果不一样怎么办?看看几个常用的注解!
常用注解
- @TableName
描述:表名注解
作用:指定当前实体类映射哪张数据库表, 默认是跟实体类名一致
/*
实体类中贴上@TableName(value = "employee")
表示该实体类的类名映射到表名为employee的表
*/
@Getter
@Setter
@ToString
@TableName(value = "employee")
public class Employee {
// ...
}
- @TableId
描述:字段注解(主键)
作用:指定当前属性映射数据库表哪一列, 默认是跟属性名一致
/*
在主键字段上贴@TableId(value = "id",type = IdType.AUTO)
value:表示该字段名映射到表中的列名
type:
IdType.AUTO:表示该字段使用自增长
*/
@Getter
@Setter
@ToString
@TableName(value = "employee")
public class Employee {
@TableId(value = "id",type = IdType.AUTO)
private Long id;
private String username;
private String password;
private String email;
private int age;
private Boolean admin;
private Long deptId;
}
- @TableField
描述:字段注解(非主键)
作用:指定当前属性映射数据库表哪一列, 默认是跟属性名一致
/*
在字段上贴@TableField(value = "username",exist = false)
value:表示该字段名映射到表中的列名
exist:表示当前属性是否映射数据库列
*/
@Getter
@Setter
@ToString
@TableName(value = "employee")
public class Employee {
@TableId(value = "id",type = IdType.AUTO)
private Long id;
@TableField(value = "username")
private String name;
private String password;
private String email;
private int age;
// 当表中不存在此列时使用exist = false
@TableField(value = "username",exist = false)
private Boolean admin;
private Long deptId;
}
注意:为了能演示效果,这里把username改成了name!!!
Mapper接口
配置文件配置sql日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
所有方法中参数的T泛型类型是由EmployeeMapper继承的BaseMapper<Employee> 这里指定的。
增
方法
/**
* 插入一条记录
*
* @param entity 实体对象
*/
int insert(T entity);
使用
@Test
public void testSave(){
Employee employee = new Employee();
employee.setName("xiaoming");
employee.setPassword("123");
employee.setAdmin(true);
employee.setAge(17);
employee.setEmail("xiaoming@qq.com");
employee.setDeptId(1L);
// INSERT INTO employee ( username, password, email, age, dept_id ) VALUES ( ?, ?, ?, ?, ? )
employeeMapper.insert(employee);
}
删
方法
/**
* 根据 ID 删除
*
* @param id 主键ID
*/
int deleteById(Serializable id);
/**
* 根据 columnMap 条件,删除记录
*
* @param columnMap 表字段 map 对象
*/
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
/**
* 根据 entity 条件,删除记录
*
* @param wrapper 实体对象封装操作类(可以为 null)
*/
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
/**
* 删除(根据ID 批量删除)
*
* @param idList 主键ID列表(不能为 null 以及 empty)
*/
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
使用
// 删除单条数据
// 根据id值删除数据
@Test
public void testDelete1() {
// DELETE FROM employee WHERE id=?
employeeMapper.deleteById(1);
}
// 删除多条数据
// 根据id集合批量删除数据
@Test
public void testDelete2() {
// DELETE FROM employee WHERE id IN ( ? , ? )
employeeMapper.deleteBatchIds(Arrays.asList(2, 3));
}
// 删除多条数据
// HashMap<列, 值>,批量匹配列上的值删除数据
// 拼接条件,多个用and拼接
@Test
public void testDelete3() {
HashMap<String, Object> map = new HashMap<>();
map.put("id", 4);
map.put("username", "xiaoming");
// DELETE FROM employee WHERE id = ? AND username = ?
employeeMapper.deleteByMap(map);
}
// 删除多条数据
// Wrapper:只有两种类型,update和query
// 更新使用update,其余使用query
// 泛型指定操作表对应的实体类
// eq:拼接条件,多个用and拼接
@Test
public void testDelete4() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("id", 5);
wrapper.eq("username", "xiaoming");
// DELETE FROM employee WHERE (id = ? AND username = ?)
employeeMapper.delete(wrapper);
}
改
方法
/**
* 根据 ID 修改
*
* @param entity 实体对象
*/
int updateById(@Param(Constants.ENTITY) T entity);
/**
* 根据 whereEntity 条件,更新记录
*
* @param entity 实体对象 (set 条件值,可以为 null)
* @param updateWrapper 实体对象封装操作类(可以为 null,里面的 entity 用于生成 where 语句)
*/
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);
注意:先把实体类的@TableField(exist = false)去掉,不然修改就为null了
使用
// 修改所有的字段
@Test
public void testUpdate1() {
Employee employee = new Employee();
employee.setId(6L);
employee.setName("daming");
employee.setPassword("123");
employee.setAdmin(true);
employee.setAge(17);
employee.setEmail("daming@qq.com");
employee.setDeptId(1L);
// UPDATE employee SET username=?, password=?, email=?, age=?, dept_id=? WHERE id=?
employeeMapper.updateById(employee);
}
// 根据id修改部分字段
@Test
public void testUpdate2() {
Employee employee = new Employee();
employee.setId(1L);
employee.setName("xiaoming");
// UPDATE employee SET username=?, age=? WHERE id=?
// 除了age是基本类型,其他都是包装类型
// 拼接sql时,所有非null 字段都进行set 拼接
employeeMapper.updateById(employee);
}
// 使用updateById删除部分字段时出现问题:拼接sql时,所有非null 字段都进行set 拼接,有些没指定修改的字段也被修改了
// 解决办法有两个
// 解决修改部分字段遇到的问题,方式一
// 把实体类的字段修改成包装类
@Test
public void testUpdate3() {
Employee employee = new Employee();
employee.setId(1L);
employee.setName("daming");
// UPDATE employee SET username=? WHERE id=?
employeeMapper.updateById(employee);
}
// 解决修改部分字段遇到的问题,方式二
// 使用update方法:
// 参数一:通过对象方式指定要更新列与数据
// 参数二:指定更新查询条件
// 这里参数一不使用,在wrapper中指定要修改的列和修改条件
@Test
public void testUpdate4() {
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id", 1L);
wrapper.set("username", "xiaoming");
// UPDATE employee SET username=? WHERE (id = ?)
employeeMapper.update(null, wrapper);
}
// 使用参数一的话还是不能解决上述问题,只是实现多条件更新
@Test
public void testUpdate5() {
Employee employee = new Employee();
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("id", 1L);
employee.setName("daming");
employeeMapper.update(employee, wrapper);
// UPDATE employee SET username=?, age=? WHERE (id = ?)
}
查
方法
/**
* 根据 ID 查询
*
* @param id 主键ID
*/
T selectById(Serializable id);
/**
* 查询(根据ID 批量查询)
*
* @param idList 主键ID列表(不能为 null 以及 empty)
*/
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
/**
* 查询(根据 columnMap 条件)
*
* @param columnMap 表字段 map 对象
*/
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
/**
* 根据 entity 条件,查询一条记录
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 Wrapper 条件,查询总记录数
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 entity 条件,查询全部记录
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 Wrapper 条件,查询全部记录
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 Wrapper 条件,查询全部记录
* <p>注意: 只返回第一个字段的值</p>
*
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 entity 条件,查询全部记录(并翻页)
*
* @param page 分页查询条件(可以为 RowBounds.DEFAULT)
* @param queryWrapper 实体对象封装操作类(可以为 null)
*/
<E extends IPage<T>> E selectPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
/**
* 根据 Wrapper 条件,查询全部记录(并翻页)
*
* @param page 分页查询条件
* @param queryWrapper 实体对象封装操作类
*/
<E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
使用
// 单条查询
// 根据id查询数据
@Test
public void testSelect1() {
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE id=?
employeeMapper.selectById(1L);
}
// 多条查询
// 根据id集合批量查询数据
@Test
public void testSelect2() {
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE id IN ( ? , ? )
List<Employee> employees = employeeMapper.selectBatchIds(Arrays.asList(1L, 2L));
}
// 多条查询
// HashMap<列, 值>,批量匹配列上的值删除数据
// 拼接条件,多个用and拼接
@Test
public void testSelect3() {
HashMap<String, Object> map = new HashMap<>();
map.put("id", 1L);
map.put("username", "admin");
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE id = ? AND username = ?
List<Employee> employees = employeeMapper.selectByMap(map);
}
// 查询符合条件的数量
@Test
public void testSelect4() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("id", 1L);
wrapper.eq("username", "admin");
// SELECT COUNT( 1 ) FROM employee WHERE (id = ? AND username = ?)
Integer count = employeeMapper.selectCount(wrapper);
}
// 多条查询
// 根据条件查询
@Test
public void testSelect5() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("username", "admin");
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (username = ?)
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
// 多条查询
// 当查询出来的数据不能封装成对象时,可以使用selectMaps查询
@Test
public void testSelect6() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
// 没有条件查询所有
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
for (Map<String, Object> map : maps) {
System.out.println(map);
}
}
// 分页查询
// 先在启动类中配置mybatis-plus分页拦截器
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
paginationInnerInterceptor.setOverflow(true); //合理化
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
@Test
public void testSelect7() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
IPage<Employee> page = new Page<>(2,3);
// 返回的值就是参数中的page
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee LIMIT ?
employeeMapper.selectPage(page, wrapper);
System.out.println("当前页:" + page.getCurrent());
System.out.println("总页数:" + page.getPages());
System.out.println("当前显示数据:" + page.getRecords());
System.out.println("每页显示记录数:" + page.getSize());
System.out.println("总条数:" + page.getTotal());
}
注意:
List<T> selectList(@Param(Constants.WRAPPER) Wrapper queryWrapper);
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper queryWrapper);
开发选用:这两个所需参数一样,返回值不一样,一个返回List,一个返回Map。查询返回的数据无法封装到对象中时,就需要用Map封装,用不了List<T>去封装
条件构造器
条件构造器:可以理解为mybatis中的动态SQL或者理解为SQL语句片段<sql></sql>
看看条件构造器的继承体系
更新使用UpdateWrapper、LambdaUpdateWrapper,其余使用QueryWrapper、LambdaQueryWrapper
通过Wrappers
获取条件构造器(列举部分,还有很多重载方法)
不要纠结它的格式,泛型写上需要被操作的实体类
Wrappers.<Employee>query();
Wrappers.<Employee>update();
Wrappers.<Employee>lambdaQuery();
Wrappers.<Employee>lambdaUpdate();
wapper介绍 :
- Wrapper : 条件构造抽象类,最顶端父类,抽象类中提供4个方法西面贴源码展示
- AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
- AbstractLambdaWrapper : Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
- LambdaQueryWrapper :看名称也能明白就是用于Lambda语法使用的查询Wrapper
- LambdaUpdateWrapper : Lambda 更新封装Wrapper
- QueryWrapper : Entity 对象封装操作类,不是用lambda语法
- UpdateWrapper : Update 条件封装,用于Entity对象更新操作
AbstractWrapper具有它们一些共同的条件方法
共同的条件方法
参数说明:
boolean condition
:该条件是否加入最后生成的sql中
params
:key
为数据库字段名,value
为字段值
null2IsNull
: 为true
则在map
的value
为null
时调用isNull
方法,为false
时则忽略value
为null
的
filter
: 过滤函数,是否允许字段传入比对条件中
allEq、eq、ne(是否等于)
allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
@Test
public void testSelect1() {
QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
Map<String, Object> map = new HashMap<>();
map.put("id",2);
map.put("username","xiaoming");
map.put("email",null);
wrapper.allEq(true,map,true);
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (id = ? AND email IS NULL AND username = ?)
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
eq(R column, Object val)
eq(boolean condition, R column, Object val)
ne(R column, Object val)
ne(boolean condition, R column, Object val)
@Test
public void testSelect2() {
QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
// eq
// wrapper.eq(true,"username","xiaoming");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username = ?)
// ne
wrapper.ne(true,"username","dafei");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username <> ?)
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
gt、ge、lt、le(大小于)
gt(R column, Object val)
gt(boolean condition, R column, Object val)
ge(R column, Object val)
ge(boolean condition, R column, Object val)
lt(R column, Object val)
lt(boolean condition, R column, Object val)
le(R column, Object val)
le(boolean condition, R column, Object val)
@Test
public void testSelect3() {
QueryWrapper<Employee> wrapper = Wrappers.query();
// gt lt
// wrapper.gt(true,"age",10).lt(true,"age",30);
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (age > ? AND age < ?)
// ge le
wrapper.ge(true,"age",10).le(true,"age",30);
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (age >= ? AND age <= ?)
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
between、notBetween(范围查询)
between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)
notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)
@Test
public void testSelect4(){
QueryWrapper<Employee> wrapper = Wrappers.query();
// between
//wrapper.between(true,"age",20,30);
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (age BETWEEN ? AND ?)
// notBetween
wrapper.notBetween(true,"age",20,30);
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (age NOT BETWEEN ? AND ?)
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
like、notLike、likeLeft、likeRight(模糊查询)
like(R column, Object val)
like(boolean condition, R column, Object val)
notLike(R column, Object val)
notLike(boolean condition, R column, Object val)
likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)
likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)
@Test
public void testSelect5(){
QueryWrapper<Employee> wrapper = Wrappers.query();
// like
//wrapper.like(true,"username","f");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE '%f%')
// not like
//wrapper.notLike(true,"username","f");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username NOT LIKE '%f%');
// likeLeft
//wrapper.likeLeft(true,"username","f");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE '%f');
// likeRight
wrapper.likeRight(true,"username","f");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE 'f%');
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
isNull、isNotNull(是否为Null)
isNull(R column)
isNull(boolean condition, R column)
isNotNull(R column)
isNotNull(boolean condition, R column)
@Test
public void testSelect6(){
QueryWrapper<Employee> wrapper = Wrappers.query();
// isNull
//wrapper.isNull(true,"email");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (email IS NULL)
// isNotNull
wrapper.isNotNull(true,"email");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (email IS NOT NULL)
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
in、notIn、inSql、notInSql(是否包含)
in 和 notIn 是用于字段范围
inSql 和 notInSql 是用于子查询
in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)
in(R column, Object... values)
in(boolean condition, R column, Object... values)
notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)
notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)
inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)
notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)
@Test
public void testSelect7(){
QueryWrapper<Employee> wrapper = Wrappers.query();
// in
//wrapper.in("id",1,2,3);
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id IN (?,?,?))
// notIn
//wrapper.notIn("id",1,2,3);
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id NOT IN (?,?,?))
// inSql
//wrapper.inSql("id","select id from employee where age > 20 and age < 40");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id IN (select id from employee where age > 20 and age < 40))
// inSql
wrapper.notInSql("id","select id from employee where age > 20 and age < 40");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id NOT IN (select id from employee where age > 20 and age < 40))
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
groupBy、having(分组、过滤)
having:参数一是条件语句,参数二是条件语句中所需值
条件语句使用{?}作占位符,与参数二顺序对应
groupBy(R... columns)
groupBy(boolean condition, R... columns)
having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)
@Test
public void testSelect8(){
QueryWrapper<Employee> wrapper = Wrappers.query();
// groupBy
//wrapper.groupBy("dept_id");
// SELECT id,username AS name,password,email,age,dept_id FROM employee GROUP BY dept_id;
wrapper.groupBy("dept_id").select("username","count(1) count").having("count > {0} and count < {1}",1,5);
// SELECT username,count(1) count FROM employee GROUP BY dept_id HAVING count > 1 and count < 5;
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
orderByAsc、orderByDesc、orderBy (排序)
orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)
orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)
@Test
public void testSelect9() {
QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
// 根据age升序
wrapper.orderByAsc("age");
// 根据id降序
wrapper.orderByDesc("id");
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id DESC
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
orderBy(boolean condition, boolean isAsc, R... columns)
isAsc
:true表示升序(Asc),false表示降序(Desc)
@Test
public void testSelect10() {
QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
wrapper.orderBy(true,true,"age","id");
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee ORDER BY age ASC,id ASC
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
当对多个列进行同一种排序时可使用orderBy
统一指定,不需要一个列使用一个orderByAsc
或orderByDesc
func
func 方法(主要方便在出现if…else下调用不同方法能不断链)
func(Consumer<Children> consumer)
func(boolean condition, Consumer<Children> consumer)
or、and(或与条件)
or()
or(boolean condition)
or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)
and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)
@Test
public void testSelect11(){
QueryWrapper<Employee> wrapper = Wrappers.query();
// or
//wrapper.eq("id",1).or().eq("id",2).or().eq("id",3);
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (id = 1 OR id = 2 OR id = 3);
//and用于解决a and (b or c)问题
// and
// w:QueryWrapper对象
wrapper.like("username","李").and(
w -> w.ge("age",20).
or().
le("age",30)
);
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (username LIKE '%李%' AND (age >= 20 OR age <= 30));
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
nested(嵌套)
nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)
@Test
public void testSelect12(){
QueryWrapper<Employee> wrapper = Wrappers.query();
wrapper.eq("username","xiaoming").nested(
w -> w.gt("age",10).or().lt("age",20)
);
// 正常嵌套 不带 AND 或者 OR,也可以解决上面or出现的问题
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (username = ? AND (age > ? OR age < ?))
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
apply(拼接 sql)
apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)
注意事项:
该方法可用于数据库函数 动态入参的
params
对应前面applySql
内部的{index}
部分.这样是不会有sql注入风险的,反之会有!
@Test
public void testSelect13() {
QueryWrapper<Employee> wrapper = Wrappers.query();
wrapper.apply("age > {0} and age < {1}", "18", "28");
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee WHERE (age > ? and age < ?)
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
last(sql拼接)
last(String lastSql)
last(boolean condition, String lastSql)
无视优化规则直接拼接到 sql 的最后
注意事项:
只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用
@Test
public void testSelect14() {
QueryWrapper<Employee> wrapper = Wrappers.query();
wrapper.last(true,"limit 1,2");
// SELECT id,username AS name,password,email,age,admin,dept_id FROM employee limit 1,2
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
exists、notExists(是否存在)
EXISTS 指定一个子查询,检测 行 的存在。
语法:EXISTS(子查询)
参数: 子查询 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)
结果类型: 如果子查询包含行,则返回 TRUE ,否则返回 FLASE
-
在子查询中使用 NULL 仍然返回结果集
select * from employee where exists(select null)
等同于: select * from employee -
比较使用 EXISTS 和 IN 的查询。注意两个查询返回相同的结果。
select * from employee where exists(select name from department where name=“财务部”)
select * from employee where dept_id in(select id from department)
EXISTS:子查询只要存在行,就查询employee 表的所有数据
In:子查询存在什么dept_id ,就查询对应的那条数据出来,employee 与department需要有关联的列
-
比较使用 EXISTS 和 = ANY 的查询。注意两个查询返回相同的结果。
SELECT * FROM employee where dept_id in (SELECT id from department)
SELECT * FROM employee where dept_id = any(SELECT id from department)结果一样的,any里面只能使用子查询,in里面还可以使用(1,2,3)数组
exists(String existsSql)
exists(boolean condition, String existsSql)
notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)
@Test
public void testSelect15() {
QueryWrapper<Employee> wrapper = Wrappers.query();
// exists
//wrapper.exists("select id from employee where age = 19");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (EXISTS (select id from employee where age = 19))
// not Exists
wrapper.notExists("select id from employee where age = 19");
// SELECT id,username AS name,password,email,age,dept_id FROM employee WHERE (NOT EXISTS (select id from employee where age = 19))
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
开发选用
EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引,但要看实际情况具体使用:
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
独有的条件方法
更新(设置 SET 部分 SQL)
set
set(String column, Object val)
set(boolean condition, String column, Object val)
@Test
public void testUpdate16() {
UpdateWrapper<Employee> updateWrapper = Wrappers.update();
updateWrapper.set(true,"age",18).eq("id",3);
// UPDATE employee SET age=? WHERE (id = ?)
employeeMapper.update(null,updateWrapper);
}
setSql(参数写Sql)
setSql(String sql)
setSql(boolean condition, String sql)
@Test
public void testUpdate17() {
UpdateWrapper<Employee> updateWrapper = Wrappers.update();
updateWrapper.setSql(true,"age = 18 where id = 3");
// UPDATE employee SET age = 18 where id = 3
employeeMapper.update(null,updateWrapper);
}
查询(设置查询字段)
select
说明:
方法分为两类.
第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要wrapper
内的entity
属性有值! 这两类方法重复调用以最后一次为准
select(String... sqlSelect)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)
@Test
public void testSelect18() {
QueryWrapper<Employee> wrapper = Wrappers.<Employee>query();
// 查询指定的列
wrapper.select("id","username", "age");
// SELECT id,username,age FROM employee
List<Employee> employeeList = employeeMapper.selectList(wrapper);
}
到此,上面所有的条件构造器使用的都是普通的Wrapper,没有使用过Lambda类型的Wrapper
下面使用Lambda实现set和select两个条件方法
@Test
public void testUpdate19(){
LambdaUpdateWrapper<Employee> wrapper = Wrappers.lambdaUpdate();
wrapper.eq(Employee::getId,1).set(Employee::getName,"xiaoming");
// UPDATE employee SET username=? WHERE (id = ?)
employeeMapper.update(null,wrapper);
}
@Test
public void testSelect20(){
LambdaQueryWrapper<Employee> wrapper = Wrappers.lambdaQuery();
wrapper.select(Employee::getId,Employee::getName,Employee::getAge);
// SELECT id,username,age AS name FROM employee
List<Employee> employee = employeeMapper.selectList(wrapper);
}
开发选用
一般会使用Lambda类型去实现,因为使用Employee::getId
比使用"id"
更容易发现编写错误,当Employee::getId
使用错误时,编译阶段就能发现,而"id"
要等到运行期才能发现
自定义SQL
XML
其实和使用mybatis时没有两样
public interface EmployeeMapper extends BaseMapper<Employee> {
// 在Mapper接口中自定义方法
List<Employee> listUseXml();
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.hao.mybatisplus.mapper.EmployeeMapper">
<resultMap id="BaseResultMap" type="com.hao.mybatisplus.domain.Employee">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="username" property="name"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="password" property="password" jdbcType="VARCHAR"/>
<result column="email" property="email" jdbcType="VARCHAR"/>
<result column="age" property="age" jdbcType="INTEGER"/>
<result column="admin" property="admin" jdbcType="BIT"/>
<result column="dept_id" property="deptId" jdbcType="BIGINT"/>
</resultMap>
<select id="listUseXml" resultMap="BaseResultMap">
select * from employee
</select>
</mapper>
@Test
public void testSelect(){
List<Employee> employeeList = employeeMapper.listByXmlSingle();
}
注解
单表查询的时候还是很方便的,但要连表时就得很恶心了
public interface EmployeeMapper extends BaseMapper<Employee> {
@Select(value = "select * from employee")
List<Employee> listUseAnnotation();
}
@Test
public void testSelect2(){
List<Employee> employeeList = employeeMapper.listUseAnnotation();
System.out.println(employeeList);
}
Service接口
为什么要使用Mybatis-plus的Service接口呢?
还是和传统Mapper接口一样的原因,简单的CRUD还是重复又重复的写。
接下来看看怎么写Mybatis-plus的Service接口吧!
编写一个Service接口,继承IService<T>
接口,泛型添上要被操作的实体类
public interface IEmployeeService extends IService<Employee> {
}
编写子类继承Service接口,再继承一个ServiceImpl<M, T>
类
参数一:要被操作的实体类Mapper接口
参数二:要被操作的实体类
@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {
}
这样就编写好Mybatis-plus的Service接口了
// 调用mybatis-plus封装好的list方法
@Test
public void testSelect2(){
LambdaQueryWrapper<Employee> wrapper = Wrappers.lambdaQuery();
wrapper.select(Employee::getId,Employee::getName);
// SELECT id,username AS name FROM employee
List<Employee> employeeList = employeeService.list(wrapper);
}
进入list方法中
/**
* 查询列表
*
* @param queryWrapper 实体对象封装操作类 {@link com.baomidou.mybatisplus.core.conditions.query.QueryWrapper}
*/
default List<T> list(Wrapper<T> queryWrapper) {
return getBaseMapper().selectList(queryWrapper);
}
getBaseMapper():其实就是我们之前service实现类继承的ServiceImpl<EmployeeMapper, Employee> 类中的EmployeeMapper
selectList(queryWrapper):其实就是mapper接口中的方法
最终得出:service接口其实只是对mapper接口进行了再一次的封装
上面讲到一个分页的实现,现在在Service接口中实现。
Service接口没有提供合适我们的分页方法,我们需要自定义方法。
先自定义一个类封装分页所需参数。
@Getter
@Setter
public class EmployeeQuery{
// 当前页
private Integer currentPage = 1;
// 每页条数
private Integer pageSize = 1;
}
public interface IEmployeeService extends IService<Employee> {
IPage<Employee> queryPage(EmployeeQuery employeeQuery);
}
@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeService {
@Override
public IPage<Employee> queryPage(EmployeeQuery employeeQuery) {
IPage<Employee> objectIPage = new Page<>(employeeQuery.getCurrentPage(),employeeQuery.getPageSize());
// 1.<E extends IPage<T>> E selectPage(E page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// IPage<Employee> page = super.getBaseMapper().selectPage(objectIPage, null);
// 2.不要Wrapper(条件)的分页
return super.page(objectIPage);
}
}
@Test
public void testSelect(){
EmployeeQuery employeeQuery = new EmployeeQuery();
employeeQuery.setCurrentPage(2);
employeeQuery.setPageSize(3);
// 调用自定义service分页方法
IPage<Employee> pages = employeeService.queryPage(employeeQuery);
}