MyBatisPlus的基本使用
一、入门案列
创建数据库
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 '邮箱',
PRIMARY KEY (id)
);
INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');
使用Spring Initializer初始化项目
加入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
编写实体类
@Data
public class User {
private Long id;
private String name;
private Integer age;
private String email;
}
编写mapper接口
public interface UserMapper extends BaseMapper<User>{
}
在启动类加上
MapperScan
注解扫描mapper接口,然后在测试类进行测试
@SpringBootTest
class DemoApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
void contextLoads() {
//查询全部User对象
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
}
输出结果:
User(id=1, name=Jone, age=18, email=test1@baomidou.com)
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
配置日志
在yml文件中配置
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
配置逻辑删除
首先需要在数据库和实体类中加上标识逻辑删除的字段，如：deleted。然后在mybatisPlus配置类中注入组件
@Bean
public ISqlInjector iSqlInjector(){
return new LogicSqlInjector();
}
在实体类上加上注解
@TableLogic
private Integer deleted;
官网上给出了一段yml配置
mybatis-plus:
global-config:
db-config:
logic-delete-field: flag # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2)
logic-delete-value: 1 # 逻辑已删除值(默认为 1)
logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
经试验，只要配置类中注入组件，并在deleted字段上加上注解已经不需要再去yml中配置了，配置完后delete操作会变成update，在select查询时也会加上逻辑删除字段作为条件。
二、基本CRUD操作
Insert操作
@Test
void insertUser() {
User user = new User();
user.setName("我是小学生");
user.setAge(3);
user.setEmail("1@.com");
int i = userMapper.insert(user);
System.out.println(i);
System.out.println(user);
}
输出:
==> Preparing: INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? )
==> Parameters: 1360941822158901249(Long), 我是小学生(String), 3(Integer), 1@.com(String)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ed3df3b]
1
User(id=1360941822158901249, name=我是小学生, age=3, email=1@.com)
Delete操作
@Test
void deleteUser(){
/**
* 删除id为1的记录
* Sql:
* ==> Preparing: DELETE FROM user WHERE id=?
* ==> Parameters: 1(Long)
*/
int i = userMapper.deleteById(1L);
/**
* 删除id为2,3的记录
* Sql:
* ==> Preparing: DELETE FROM user WHERE id IN ( ? , ? )
* ==> Parameters: 2(Integer), 3(Integer)
*/
int j = userMapper.deleteBatchIds(Arrays.asList(2, 3));
/**
* 传入map集合，删除name属性值为21的数据，可同时传入多个属性
* Sql:
* ==> Preparing: DELETE FROM user WHERE age = ?
* ==> Parameters: 21(Integer)
*/
Map<String,Object> params = new HashMap<>();
params.put("age",21);
userMapper.deleteByMap(params);
}
Update操作
@Test
void updateUser() {
/**
* 根据id修改数据
* Sql:
* ==> Preparing: UPDATE user SET name=?, update_time=? WHERE id=?
* ==> Parameters: 我是超人(String), 2021-02-16 17:56:00.636(Timestamp), 5(Long)
*/
User user = new User();
user.setId(5L);
user.setName("我是超人");
int i = userMapper.updateById(user);
}
Select操作
@Test
void selectUser(){
/**
* 查询全部user
* Sql:
* ==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user
* ==> Parameters:
* <== Columns: id, name, age, email, create_time, update_time, version
* <== Row: 5, 我是超人, 24, test5@baomidou.com, null, 2021-02-16 17:56:01, 1
* <== Row: 1360941822158901249, 我是中学生1, 3, 1@.com, null, 2021-02-14 21:53:33, 1
* <== Row: 1360949856155279361, 我是中学生1, 3, 1@.com, 2021-02-14 21:51:46, 2021-02-14 21:53:33, 1
* <== Row: 1361614516923772930, 我是大学学生, 3, 1@.com, 2021-02-16 17:52:53, 2021-02-16 17:52:53, 1
* <== Row: 1361614600646283266, 我是大学学生2, 18, 1@.com, 2021-02-16 17:53:13, 2021-02-16 17:53:13, 1
* <== Total: 5
*/
List<User> users = userMapper.selectList(null);
/**
* 根据Id查询
* Sql:
* ==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id=?
* ==> Parameters: 5(Long)
* <== Columns: id, name, age, email, create_time, update_time, version
* <== Row: 5, 我是超人, 24, test5@baomidou.com, null, 2021-02-16 17:56:01, 1
* <== Total: 1
*/
User user = userMapper.selectById(5L);
/**
* 根据Id查询多个
* Sql:
* ==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id IN ( ? , ? )
* ==> Parameters: 5(Integer), 6(Integer)
* <== Columns: id, name, age, email, create_time, update_time, version
* <== Row: 5, 我是超人, 24, test5@baomidou.com, null, 2021-02-16 17:56:01, 1
* <== Row: 6, 我是中学生1, 3, 1@.com, null, 2021-02-14 21:53:33, 1
* <== Total: 2
*/
userMapper.selectBatchIds(Arrays.asList(5,6));
/**
* 查询总条数
* Sql:
* ==> Preparing: SELECT COUNT(1) FROM user
* ==> Parameters:
* <== Columns: COUNT(1)
* <== Row: 5
* <== Total: 1
*/
Integer i = userMapper.selectCount(null);
/**
* 查询id为2name为我是超人的记录
* Sql:
* ==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE name = ? AND id = ?
* ==> Parameters: 我是超人(String), 5(Integer)
* <== Columns: id, name, age, email, create_time, update_time, version
* <== Row: 5, 我是超人, 24, test5@baomidou.com, null, 2021-02-16 17:56:01, 1
* <== Total: 1
*/
Map<String,Object> params = new HashMap<>();
params.put("id",5);
params.put("name","我是超人");
List<User> users = userMapper.selectByMap(params);
}
三、条件构造器Wrapper
@Test
void test1(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
//wrapper.ge("age",3);//所有age>=3
//wrapper.gt("age",3);//所有age>3
//wrapper.eq("age",3);//所有age=3
/* Map<String,Object> params = new HashMap<>();
params.put("name","我是大学学生");
params.put("age",3);
wrapper.allEq(params);*///查询name为我是大学生,age=3
//wrapper.ne("age",3);//所有age<>3
//wrapper.lt("age",3);//所有age<3
//wrapper.le("age",3);//所有age<=3
//wrapper.between("age",18,30);//查询age在18和30之间
//wrapper.notBetween("age",18,30);//查询age不在18和30之间
//wrapper.like("name","超人");//like查询 如：%超人%
//wrapper.likeLeft("name","超人");//左边通配 如：%超人
//wrapper.likeRight("name","我");//右边通配 如：我%
//更多用法请参考官网文档
userMapper.selectList(wrapper);
}
四、常用的几个组件
数据库时间字段的自动填充
简介:我们数据库表中通常会有create_time，和update_time这两个字段，用于记录该条数据的创建和更新时间，传统的方法就是new Date()，几乎每个方法中都能看到类似代码，很繁琐。mybatisPlus给我们提供了一种解决方案，使用它的自动填充功能能很好的解决这个问题。
实例类字段
@TableField(fill = FieldFill.INSERT)//在执行insert时候触发
private Date createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)//在执行insert和update都会触发
编写处理器类
@Slf4j
@Component
public class MyObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
log.info("start insert fill...");
this.setFieldValByName("createTime",new Date(),metaObject);
this.setFieldValByName("updateTime",new Date(),metaObject);
}
@Override
public void updateFill(MetaObject metaObject) {
log.info("start update fill...");
this.setFieldValByName("updateTime",new Date(),metaObject);
}
}
这样在进行数据的插入和更新的时候就不用我们手动的去设置时间了。
乐观锁实现方式:
- 取出记录时，获取当前版本号
- 更新时，带上这个版本号
- 执行更新时，版本号不对则更新失败
实现方法:
在数据库表和实体类中新增version字段,编写config配置类
@Configuration
public class MybatisPlusConfig {
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor(){
return new OptimisticLockerInterceptor();
}
}
//在实例类version字段上加上@version注解
@Version
private Integer version;
测试插入成功案例
//测试乐观锁成功
@Test
void testOptimisticSuccess(){
User user = userMapper.selectById(1L);
user.setName("111");
userMapper.updateById(user);
}
输出:
==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, age, email, create_time, update_time, version
<== Row: 1, 111, 18, test1@baomidou.com, null, 2021-02-14 22:14:50, 1
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76ececd]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@68f6e55d] was not registered for synchronization because synchronization is not active
2021-02-14 22:15:32.152 INFO 13996 --- [ main] c.m.demo.handler.MyObjectHandler : start update fill...
JDBC Connection [HikariProxyConnection@2107141507 wrapping com.mysql.cj.jdbc.ConnectionImpl@6c479fdf] will not be managed by Spring
==> Preparing: UPDATE user SET name=?, age=?, email=?, update_time=?, version=? WHERE id=? AND version=?
==> Parameters: 111(String), 18(Integer), test1@baomidou.com(String), 2021-02-14 22:15:32.152(Timestamp), 2(Integer), 1(Long), 1(Integer)
<== Updates: 1
测试插入失败案列
//测试乐观锁失败
@Test
void testOptimisticFailed(){
User user = userMapper.selectById(1L);
user.setName("111");
//模拟被另外一个线程抢先修改
User user1 = userMapper.selectById(1L);
user1.setName("222");
userMapper.updateById(user1);
userMapper.updateById(user);
}
输出:
==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, age, email, create_time, update_time, version
<== Row: 1, 111, 18, test1@baomidou.com, null, 2021-02-14 22:25:07, 2
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76ececd]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@428bdd72] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1695301724 wrapping com.mysql.cj.jdbc.ConnectionImpl@6c479fdf] will not be managed by Spring
==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, age, email, create_time, update_time, version
<== Row: 1, 111, 18, test1@baomidou.com, null, 2021-02-14 22:25:07, 2
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@428bdd72]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3aaa3c39] was not registered for synchronization because synchronization is not active
2021-02-14 22:26:09.513 INFO 21260 --- [ main] c.m.demo.handler.MyObjectHandler : start update fill...
JDBC Connection [HikariProxyConnection@753853622 wrapping com.mysql.cj.jdbc.ConnectionImpl@6c479fdf] will not be managed by Spring
==> Preparing: UPDATE user SET name=?, age=?, email=?, update_time=?, version=? WHERE id=? AND version=?
==> Parameters: 222(String), 18(Integer), test1@baomidou.com(String), 2021-02-14 22:26:09.513(Timestamp), 3(Integer), 1(Long), 2(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3aaa3c39]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@e784320] was not registered for synchronization because synchronization is not active
2021-02-14 22:26:09.729 INFO 21260 --- [ main] c.m.demo.handler.MyObjectHandler : start update fill...
JDBC Connection [HikariProxyConnection@1064040618 wrapping com.mysql.cj.jdbc.ConnectionImpl@6c479fdf] will not be managed by Spring
==> Preparing: UPDATE user SET name=?, age=?, email=?, update_time=?, version=? WHERE id=? AND version=?
==> Parameters: 111(String), 18(Integer), test1@baomidou.com(String), 2021-02-14 22:26:09.729(Timestamp), 3(Integer), 1(Long), 2(Integer)
<== Updates: 0
在上述失败案例中，user对象第一次查出version版本号为2，在它给name赋值的时候，模拟了另一个用户对象user1在它之前修改了name值并将version号改为3，当user对象在拿着version版本号为2的值取修改该数据时，数据修改失败，这就是乐观锁。
分页实现
在mybatisPlus配置类中加入
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
测试
@Test
void testPageForList(){
/**
* 构造方法第一个值current：当前页,第二个值size:页面大小
* Sql语句:
* SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 LIMIT 0,2
*/
Page<User> page = new Page<>(1,2);
userMapper.selectMapsPage(page,null);
}
防止全表更新和删除
修改配置类
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
List<ISqlParser> sqlParserList = new ArrayList<>();
// 攻击 SQL 阻断解析器、加入解析链
sqlParserList.add(new BlockAttackSqlParser());
paginationInterceptor.setSqlParserList(sqlParserList);
return paginationInterceptor;
}
在测试时，不加where条件进行全表的删除和更新会直接抛出异常。
性能分析插件
简介:用于开发中找出执行速度慢的sql语句。
在mybatisPlus的配置文件中新增配置
@Bean
@Profile({"dev","test"})//只在开发和测试环境中生效
public PerformanceInterceptor performanceInterceptor(){
PerformanceInterceptor interceptor = new PerformanceInterceptor();
interceptor.setMaxTime(1);//设置sql最大执行时间，单位毫秒
interceptor.setFormat(true);//sql格式化
return interceptor;
}
测试
@Test
void contextLoads() {
//查询全部User对象
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
我们配置sql的最大执行时间为一毫秒，超出一毫秒的SQL语句将会被抛出异常。
Time：30 ms - ID：com.mybatisplus.demo.mapper.UserMapper.selectList
Execute SQL：
SELECT
id,
name,
age,
email,
create_time,
update_time,
version,
deleted
FROM
user
### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: The SQL execution time is too large, please optimize !
五、代码生成器
简介:我们每在数据库中添加一张表，都相应的要在Java代码中写mapper接口、service接口、还有controller等，这些代码即没什么技术含量，又不得不去写。mybatisPlus提供了代码生成器的功能，我们只需要先设计好数据库表，就能一键生成这些代码，极大提高了开发的效率。
导入依赖，mybatisPlus默认是用velocity模板，如需自己定义，也可以将模板粘贴至resources目录下的templates，模板的位置在：
导入依赖
<!--velocity模板-->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>2.2</version>
</dependency>
<!--swagger2-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
编写测试配置
public class MyGenerate {
/**
* 控制台输入
* @param tip
* @return
*/
public static String scanner(String tip) {
Scanner scanner = new Scanner(System.in);
StringBuilder help = new StringBuilder();
help.append("请输入" + tip + "：");
System.out.println(help.toString());
if (scanner.hasNext()) {
String ipt = scanner.next();
if (StringUtils.isNotBlank(ipt)) {
return ipt;
}
}
throw new MybatisPlusException("请输入正确的" + tip + "！");
}
public static void main(String[] args) {
//构建一个代码生成器的对象
AutoGenerator generator = new AutoGenerator();
//全局配置
GlobalConfig gc = new GlobalConfig();
//获取当前项目的目录
String dir = System.getProperty("user.dir");
//代码输出路径
gc.setOutputDir(dir+"/src/main/java");
//设置作者
gc.setAuthor("supper");
//是否打开资源管理器
gc.setOpen(false);
//是否覆盖之前的
gc.setFileOverride(false);
//默认前面带字母I,%sService能去掉前缀I
gc.setServiceName("%sService");
//主键类型
gc.setIdType(IdType.ID_WORKER);
//配置swagger
gc.setSwagger2(true);
generator.setGlobalConfig(gc);
//设置数据源
DataSourceConfig dsc = new DataSourceConfig();
dsc.setUrl("jdbc:mysql://localhost:3306/mybatisplus?userSSL=false&userUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8");
dsc.setDriverName("com.mysql.cj.jdbc.Driver");
dsc.setUsername("root");
dsc.setPassword("123456");
dsc.setDbType(DbType.MYSQL);
generator.setDataSource(dsc);
//配置包名
PackageConfig packageConfig = new PackageConfig();
packageConfig.setParent("com.mybatisplus.demo");
packageConfig.setMapper("mapper");
packageConfig.setService("service");
packageConfig.setController("controller");
packageConfig.setEntity("pojo");
generator.setPackageInfo(packageConfig);
// 策略配置
StrategyConfig strategy = new StrategyConfig();
strategy.setInclude(scanner("表名，多个英文逗号分割").split(","));
strategy.setNaming(NamingStrategy.underline_to_camel);
strategy.setColumnNaming(NamingStrategy.underline_to_camel);
strategy.setTablePrefix("t_");//去掉表前缀
strategy.setSuperEntityColumns("id");//设置父类字段属性
//strategy.setSuperEntityClass("BaseEntity");
strategy.setEntityLombokModel(true);//自动生成lombook
strategy.setRestControllerStyle(true);//会自动生成restcontroller注解
strategy.setLogicDeleteFieldName("deleted");//逻辑删除字段
//自动填充
TableFill create_time = new TableFill("create_Time", FieldFill.INSERT);
TableFill update_time = new TableFill("update_Time", FieldFill.INSERT_UPDATE);
List<TableFill> list = new ArrayList<>();
list.add(create_time);
list.add(update_time);
strategy.setTableFillList(list);
strategy.setVersionFieldName("version");
generator.setStrategy(strategy);
//执行
generator.execute();
}
}
运行之后，在相应的位置就会生成相应的mapper、service等。
版权声明：本文为Supper_Shenchao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。