参考教程:http://mp.baomidou.com/guide/
MyBatis-Plus(简称 MP)是一个 MyBatis 的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
特点
<!--确定spring boot的版本-->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.5.RELEASE</version>
</parent>
<dependencies>
<!-- web 开发 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--MySQL数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--支持lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--测试-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
</dependencies>
步骤三:创建yml文件,配置数据库相关
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/cloud_db1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username: root
password: 1234
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #输出日志
CREATE TABLE `tmp_customer` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(50) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
`telephone` varchar(11) DEFAULT NULL,
`money` double DEFAULT NULL,
`version` int(11) DEFAULT NULL,
`create_time` date DEFAULT NULL,
`update_time` date DEFAULT NULL,
PRIMARY KEY (`cid`)
);
insert into `tmp_customer`(`cid`,`cname`,`password`,`telephone`,`money`,`version`,`create_time`,`update_time`)
values (1,'jack','1234','110',1000,NULL,NULL,NULL),(2,'rose','1234','112',1000,NULL,NULL,NULL),(3,'tom','1234','119',1000,NULL,NULL,NULL);
@TableName
表名注解,value属性设置表名
package com.czxy.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.util.List;
@Data
@TableName("tmp_customer")
public class Customer {
@TableId(type = IdType.AUTO)
private Integer cid;
private String cname;
private String password;
private String telephone;
private String money;
private Integer version;
@TableField(exist = false)
private List<Integer> ids;
}
package com.czxy.mp.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.mp.domain.Customer;
import org.apache.ibatis.annotations.Mapper;
/**
* Created by liangtong.
*/
@Mapper
public interface CustomerMapper extends BaseMapper<Customer> {
}
package com.czxy.mp;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* Created by liangtong.
*/
@SpringBootApplication
public class MybatisPlusApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisPlusApplication.class, args);
}
}
package com.czxy;
import com.czxy.mp.MybatisPlusApplication;
import com.czxy.mp.domain.Customer;
import com.czxy.mp.mapper.CustomerMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;
/**
* Created by liangtong.
*/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = MybatisPlusApplication.class)
public class TestDemo01 {
@Resource
private CustomerMapper customerMapper;
@Test
public void testFindAll() {
List<Customer> list = customerMapper.selectList(null);
list.forEach(System.out::println);
}
}
BaseMapper 封装CRUD操作,泛型 T
为任意实体对象
方法名 | 描述 |
---|---|
int insert(T entity) | 插入一条记录,entity 为 实体对象 |
int delete(Wrapper<T> wrapper) | 根据 entity 条件,删除记录,wrapper 可以为 null |
int deleteBatchIds(Collection idList) | 根据ID 批量删除 |
int deleteById(Serializable id) | 根据 ID 删除 |
int deleteByMap(Map<String, Object> map) | 根据 columnMap 条件,删除记录 |
int update(T entity, Wrapper<T> updateWrapper) | 根据 whereEntity 条件,更新记录 |
int updateById(T entity); | 根据 ID 修改 |
方法名 | 描述 |
---|---|
T selectById(Serializable id) | 根据 ID 查询 |
T selectOne(Wrapper<T> queryWrapper) | 根据 entity 条件,查询一条记录 |
List<T> selectBatchIds(Collection idList) | 根据ID 批量查询 |
List<T> selectList(Wrapper<T> queryWrapper) | 根据 entity 条件,查询全部记录 |
List<T> selectByMap(Map<String, Object> columnMap) | 根据 columnMap 条件 |
List<Map<String, Object>> selectMaps(Wrapper<T> queryWrapper) | 根据 Wrapper 条件,查询全部记录 |
List<Object> selectObjs( Wrapper<T> queryWrapper) | 根据 Wrapper 条件,查询全部记录。注意: 只返回第一个字段的值 |
IPage<T> selectPage(IPage<T> page, Wrapper<T> queryWrapper) | 根据 entity 条件,查询全部记录(并翻页) |
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, Wrapper<T> queryWrapper) | 根据 Wrapper 条件,查询全部记录(并翻页) |
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper) | 根据 Wrapper 条件,查询总记录数 |
@Test
public void testInsert() {
Customer customer = new Customer();
customer.setCname("测试");
customerMapper.insert(customer);
}
获得自动增长列信息
@Test
public void testInsert() {
Customer customer = new Customer();
customer.setCname("测试");
customerMapper.insert(customer);
System.out.println(customer);
}
@Test
public void testUpdate() {
Customer customer = new Customer();
customer.setCid(15);
customer.setCname("测试777");
customer.setPassword("777");
// 需要给Customer设置@TableId
customerMapper.updateById(customer);
}
@Test
public void testUpdate2() {
Customer customer = new Customer();
customer.setCname("测试777");
customer.setPassword("777");
// 更新所有
customerMapper.update(customer,null);
}
@Test
public void testDelete() {
// 需要给Customer设置@TableId
int i = customerMapper.deleteById(11);
System.out.println(i);
}
@Test
public void testBatchDelete() {
// 需要给Customer设置@TableId
int i = customerMapper.deleteBatchIds(Arrays.asList(9,10));
System.out.println(i);
}
@Test
public void testMap(){
Map map = new HashMap();
map.put("cname","测试");
map.put("password","123456");
List list = customerMapper.selectByMap(map);
list.forEach(System.out::println);
}
方法名 | 描述 |
---|---|
selectOne | |
selectCount | |
selectList | |
selectMaps | |
selectObjs | |
update | |
delete |
查询方式 | 说明 |
---|---|
setSqlSelect | 设置 SELECT 查询字段 |
where | WHERE 语句,拼接 + WHERE 条件 |
and | AND 语句,拼接 + AND 字段=值 |
andNew | AND 语句,拼接 + AND (字段=值) |
or | OR 语句,拼接 + OR 字段=值 |
orNew | OR 语句,拼接 + OR (字段=值) |
eq | 等于= |
allEq | 基于 map 内容等于= |
ne | 不等于<> |
gt | 大于> |
ge | 大于等于>= |
lt | 小于< |
le | 小于等于<= |
like | 模糊查询 LIKE |
notLike | 模糊查询 NOT LIKE |
in | IN 查询 |
notIn | NOT IN 查询 |
isNull | NULL 值查询 |
isNotNull | IS NOT NULL |
groupBy | 分组 GROUP BY |
having | HAVING 关键词 |
orderBy | 排序 ORDER BY |
orderAsc | ASC 排序 ORDER BY |
orderDesc | DESC 排序 ORDER BY |
exists | EXISTS 条件语句 |
notExists | NOT EXISTS 条件语句 |
between | BETWEEN 条件语句 |
notBetween | NOT BETWEEN 条件语句 |
addFilter | 自由拼接 SQL |
last | 拼接在最后,例如:last(“LIMIT 1”) |
@Test
public void testWrapper(){
// 拼凑条件
QueryWrapper<Customer> queryWrapper = new QueryWrapper();
// 1)模糊查询
queryWrapper.like("cname","测试");
// 2)等值查询
queryWrapper.eq("password","777");
// 3)批量查询
queryWrapper.in("cid",1,2,3,4);
// 查询
List<Customer> list = customerMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
@Test
public void findCondition2() {
Customer customer = new Customer();
customer.setPassword("777");
customer.setCname("888");
customer.setIdList(Arrays.asList(2,3,4));
customer.setCid(3);
//条件查询
QueryWrapper<Customer> queryWrapper = new QueryWrapper<>();
// 1) 等值查询
queryWrapper.eq( customer.getPassword()!=null ,"password", customer.getPassword());
// 2) 模糊查询
queryWrapper.like(customer.getCname() != null , "cname",customer.getCname());
// 3) in语句
queryWrapper.in(customer.getIdList() != null , "cid",customer.getIdList());
// 4) 大于等于
queryWrapper.ge(customer.getCid() != null , "cid" , customer.getCid());
//查询
List<Customer> list = customerMapper.selectList(queryWrapper);
//list.forEach(customer-> System.out.println(customer));
list.forEach(System.out::println);
}
@Test
public void testWrapperUpdate(){
// 数据
Customer customer = new Customer();
customer.setCname("测试888");
// 拼凑条件
QueryWrapper<Customer> queryWrapper = new QueryWrapper();
queryWrapper.in("cid",1,2,3,4);
// 更新
int result = customerMapper.update(customer, queryWrapper);
System.out.println(result);
}
package com.czxy.mp.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MybatisPlusConfig {
/**
* 配置插件
* @return
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
// 分页插件
mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return mybatisPlusInterceptor;
}
/**
* 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
* @return
*/
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return configuration -> configuration.setUseDeprecatedExecutor(false);
}
}
@Test
public void testPage(){
// 分页数据
int pageNum = 1;
int pageSize = 3;
Page<Customer> page = new Page<>(pageNum , pageSize);
page.setSearchCount(true);
// 查询
customerMapper.selectPage(page, null);
// 分页数据
System.err.println("当前页码:" + page.getCurrent());
System.err.println("每页显示记录数:" + page.getSize());
System.err.println("总页数:" + page.getPages());
System.err.println("总记录数:" + page.getTotal());
System.err.println("是否有下一页:" + page.hasNext());
System.err.println("是否有上一页:" + page.hasPrevious());
// 分页数据列表
page.getRecords().forEach(System.err::println);
}
属性 | 描述 |
---|---|
value | 表名 |
keepGlobalPrefix | 是否保持使用全局的 tablePrefix 的值(如果设置了全局 tablePrefix 且自行设置了 value 的值) |
主键注解:@TableId
属性 | 描述 |
---|---|
value | 主键字段名 |
type | 主键类型 IdType.ASSIGN_UUID ,分配UUID IdType.ASSIGN_ID ,分配ID(默认使用雪花算法) |
字段注解(非主键):@TableField
属性 | 描述 |
---|---|
value | 数据库字段名 |
fill | 字段自动填充策略 FieldFill.INSERT 插入时填充字段 FieldFill.UPDATE 更新时填充字段 FieldFill.INSERT_UPDATE 插入和更新时填充字段 |
常见配置
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #输出日志
map-underscore-to-camel-case: true #驼峰命名
global-config:
db-config:
id-type: auto #全局配置,id自动增强
table-prefix: tmp_ #表名前缀
type-aliases-package: com.czxy.mp.domain #别名包扫描路径
mapper-locations: classpath*:/mapper/**/*.xml #映射文件位置
项目中经常会遇到一些数据,每次都使用相同的方式填充,例如记录的创建时间,更新时间等。
我们可以使用MyBatis Plus的自动填充功能,完成这些字段的赋值工作:
com.baomidou.mybatisplus.core.handlers.MetaObjectHandler
,确定填充具体操作
@TableField(fill = ...)
确定字段填充的时机
alter table tmp_customer add column create_time date;
alter table tmp_customer add column update_time date;
package com.czxy.mp.domain;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.util.Date;
/**
* Created by liangtong.
*/
@Data
@TableName("tmp_customer")
public class Customer {
@TableId(type = IdType.AUTO)
private Integer cid;
private String cname;
private String password;
private String telephone;
private String money;
@TableField(value="create_time",fill = FieldFill.INSERT)
private Date createTime;
@TableField(value="update_time",fill = FieldFill.UPDATE)
private Date updateTime;
}
步骤三:编写处理类
package com.czxy.mp.handler;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
/**
* 插入填充
* @param metaObject
*/
@Override
public void insertFill(MetaObject metaObject) {
this.setFieldValByName("createTime", new Date(), metaObject);
}
/**
* 更新填充
* @param metaObject
*/
@Override
public void updateFill(MetaObject metaObject) {
this.setFieldValByName("updateTime", new Date(), metaObject);
}
}
@Test
public void testInsert() {
Customer customer = new Customer();
customer.setCname("测试888");
customerMapper.insert(customer);
}
@Test
public void testUpdate() {
Customer customer = new Customer();
customer.setCid(11);
customer.setTelephone("999");
customerMapper.updateById(customer);
}
步骤一:修改表结构,添加version字段
步骤二:修改JavaBean,添加version属性
package com.czxy.mp.domain;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
/**
* Created by liangtong.
*/
@Data
@TableName("tmp_customer")
public class Customer {
@TableId(type = IdType.AUTO)
private Integer cid;
private String cname;
private String password;
private String telephone;
private String money;
@Version
@TableField(fill = FieldFill.INSERT)
private Integer version;
}
package com.czxy.mp.config;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
* Created by liangtong.
*/
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
/**
* 插入填充
* @param metaObject
*/
@Override
public void insertFill(MetaObject metaObject) {
this.setFieldValByName("createTime", new Date(), metaObject);
this.setFieldValByName("version", 1, metaObject);
}
/**
* 更新填充
* @param metaObject
*/
@Override
public void updateFill(MetaObject metaObject) {
this.setFieldValByName("updateTime", new Date(), metaObject);
}
}
package com.czxy.mp.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* Created by liangtong.
*/
@Configuration
public class MybatisPlusConfig {
*/
/**
* 配置插件
* @return
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
// 分页插件
mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
// 乐观锁
mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return mybatisPlusInterceptor;
}
}
@Test
public void testUpdate() {
Customer customer = new Customer();
customer.setCid(14);
customer.setCname("测试999");
// 与数据库中数据一致,将更新成功,否则返回失败。
customer.setVersion(1);
int i = customerMapper.updateById(customer);
System.out.println(i);
}
newVersion = oldVersion + 1
newVersion
会回写到 entity
中
updateById(id)
与 update(entity, wrapper)
方法
update(entity, wrapper)
方法下, wrapper
不能复用!!!
步骤一:修改表结构添加deleted字段
步骤二:修改JavaBean,给deleted字段添加@TableLogic
package com.czxy.mp.domain;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
/**
* Created by liangtong.
*/
@Data
@TableName("tmp_customer")
public class Customer {
@TableId(type = IdType.AUTO)
private Integer cid;
private String cname;
private String password;
private String telephone;
private String money;
@Version
@TableField(fill = FieldFill.INSERT)
private Integer version;
@TableLogic
@TableField(fill = FieldFill.INSERT)
private Integer deleted;
}
步骤三:添加数据时,设置默认“逻辑未删除值”
package com.czxy.mp.config;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
* Created by liangtong.
*/
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
/**
* 插入填充
* @param metaObject
*/
@Override
public void insertFill(MetaObject metaObject) {
this.setFieldValByName("createTime", new Date(), metaObject);
this.setFieldValByName("version", 1, metaObject);
this.setFieldValByName("deleted", 0, metaObject);
}
/**
* 更新填充
* @param metaObject
*/
@Override
public void updateFill(MetaObject metaObject) {
this.setFieldValByName("updateTime", new Date(), metaObject);
}
}
步骤四:测试
@Test
public void testDelete() {
// 删除时,必须保证deleted数据为“逻辑未删除值”
int i = customerMapper.deleteById(12);
System.out.println(i);
}
@TableLogic
mybatis-plus:
global-config:
db-config:
logic-delete-field: deleted # 局逻辑删除的实体字段名
logic-delete-value: 1 # 逻辑已删除值(默认为 1)
logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
标准service:接口 + 实现
package com.czxy.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.czxy.domain.Customer;
public interface CustomerService extends IService<Customer> {
}
service实现类
package com.czxy.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.czxy.domain.Customer;
import com.czxy.mapper.CustomerMapper;
import com.czxy.service.CustomerService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
@Transactional
public class CustomerServiceImpl extends ServiceImpl<CustomerMapper,Customer> implements CustomerService {
}