1.1 SpringBoot集成MyBatis-Plus
1.1.1 MyBatis-Plus简介
MyBatis-Plus是在MyBatis的基础上只做增强不做改变的一个框架,通过提供一系列的特性和工具,极大地简化了CRUD(创建、读取、更新、删除)操作和其他数据库交互的复杂性。其目标是为开发者提供更加便捷、高效的数据库操作方式,减少重复性代码编写,提高开发效率。
MyBatis-Plis特性
- 无侵入性:MyBatis-Plus的引入不会对现有工程产生影响,它只做增强而不做改变,使得集成和使用变得非常简单。
- 损耗小:启动即会自动注入基本CRUD,性能基本无损耗,直接面向对象操作。
- 支持Lambda形式调用:通过Lambda表达式,可以方便地编写各类查询条件,无需担心字段写错。
- 支持主键自动生成:支持多达4种主键策略(包括分布式唯一ID生成器),可自由配置,完美解决主键问题。
- 支持ActiveRecord模式:实体类只需继承Model类即可进行强大的CRUD操作。
- 内置代码生成器:采用代码或Maven插件可快速生成Mapper、Model、Service、Controller层代码,支持模板引擎,提供了丰富的自定义配置选项。
- 内置分页插件:基于MyBatis物理分页,开发者无需关心具体操作,配置好插件之后,写分页等同于普通List查询。分页插件支持多种数据库。
- 内置性能分析插件:可输出SQL语句及其执行时间,帮助开发者快速定位慢查询。
- 内置全局拦截插件:提供全表delete、update操作智能分析阻断功能,也可自定义拦截规则,预防误操作。
1.1.2 快速上手
创建MyBatis-Plus项目
关键依赖包
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- MyBatis -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-3-starter</artifactId>
<version>1.2.20</version>
</dependency>
<!-- 导入 junit 测试包 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<!-- 添加jstl标签库依赖 -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- 添加jsp引擎依赖,Spring Boot内置tomcar没有此依赖 -->
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
</dependency>
<dependency>
<groupId>org.webjars</groupId>
<artifactId>jquery</artifactId>
<version>3.7.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
application.yml 添加相关配置
spring:
#配置数据源
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/crm_db?useUnicode=true&characterEncoding=utf-8
username: root
password: 123456
type: com.alibaba.druid.pool.DruidDataSource #使用阿里巴巴Druid数据源
druid:
############ 连接池基础配置 ############
initial-size: 1 #初始化大小
max-active: 20 #最大连接数
min-idle: 1 #最小连接数
max-wait: 60000 #获取连接等待超时的时间(单位:毫秒)
time-between-eviction-runs-millis: 60000 #间隔多久检测一次需要关闭的空闲连接(单位:毫秒)
time-between-log-stats-millis: 60000 #日志统计信息之间的时间(单位:毫秒)
min-evictable-idle-time-millis: 300000 #一个连接在池中最小生存的时间(单位:毫秒)
max-evictable-idle-time-millis: 600000 #一个连接在池中最大生存的时间(单位:毫秒)
test-while-idle: true #应用向连接池申请连接,值为false时,连接池将会判断连接是否处于空闲状态,如果是则验证这条连接是否可用
test-on-borrow: false #默认值为false,如果为true,应用向连接池申请连接时连接池会判断这条连接是否是可用的
test-on-return: false #默认值为false,如果为true,当应用使用完连接,连接池回收连接的时候会判断该连接是否还可用
validation-query: select 1 #用来测试连接是否可用的SQL语句
validation-query-timeout: 60000 #连接是否可用测试超时时间(单位:毫秒)
# use-global-datasource-stat: true #使用全局数据源统计
keep-alive: true #连接池中的min-idle数量以内的连接,空闲时间超过max-evictable-idle-time-millis,则会执行KeepAlive操作
pool-prepared-statements: true #是否缓存PreparedStatement,也就是PSCache,对支持游标的数据库性能提升巨大,比如Oracle
max-open-prepared-statements: 20 #要启用PSCache,必须配置大于0(当大于0时,pool-prepared-statements自动触发修改为true)在Druid中,不会存在Oracle下PSCache占用内存过多的问题,可以把这个数值配置大一些,比如100
connection-properties: stat.mergeSql=true;stat.slowSqlMillis=5000 #设置连接属性,mergeSql合并SQL,慢SQL时长定义(单位:毫秒)
remove-abandoned: true #开启连接池回收(针对不活跃的连接)
remove-abandoned-timeout: 1800 #超时连接回收时间(单位:毫秒)
log-abandoned: true #回收连接时打印日志
############ 数据库连接监控 ############
aop-patterns: "com.bdqn.*.mapper.*" #Spring监控,利用AOP对指定接口的执行时间,JDBC数进行记录
filters: stat,wall,log4j2 #启用内置过滤器(第一个 stat必须,否则监控不到SQL)
filter: #监控统计拦截器
stat: #开启DruidDataSource的状态监控
enabled: true #启用DruidDataSource状态监控
db-type: mysql #数据库类型
log-slow-sql: true #开启慢SQL监控
slow-sql-millis: 2000 #超过2s就认为是慢SQL,记录到日志中
slf4j: #日志监控,使用slf4j进行日志输出
enabled: true #启用日志监控
statement-log-error-enabled: true
statement-create-after-log-enabled: false
statement-close-after-log-enabled: false
result-set-open-after-log-enabled: false
result-set-close-after-log-enabled: false
web-stat-filter: #配置WebStatFilter,用于采集Web关联监控的数据
enabled: true #启用WebStatFilter
url-pattern: /* #过滤所有URL
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" #排除一些不必要的URL
session-stat-enable: true #开启Session统计功能
session-stat-max-count: 100 #Session的最大个数(默认100)
stat-view-servlet: #配置StatViewServlet(监控页面),用于展示Druid的统计信息
enabled: true #启用StatViewServlet(监控页面)
url-pattern: /druid/* #访问内置监控页面的路径,内置监控页面的首页是/druid/index.html
reset-enable: false #不允许清空统计数据,重新计算
login-username: admin #监控页面访问账号
login-password: admin #监控页面访问密码
allow: 127.0.0.1,localhost #允许访问的地址,如果allow没有配置或者为空,则允许所有访问
deny: #拒绝访问的地址,deny优先于allow,如果在deny列表中,就算在allow列表中,也会被拒绝
#配置mybatis相关信息
#mybatis:
# config-location: classpath:mybatis/mybatis-config.xml
# mapper-locations: classpath:mybatis/mapper/*.xml
# type-aliases-package: com.bdqn.demo.pojo
#配置前端页面访问路径前缀和后缀
mvc:
view:
prefix: /WEB-INF/jsp/templates/
suffix: .jsp
#spring.jpa.properties.hibernate.
#指定数据库方言
jpa:
properties:
hibernate:
#配置jpa(Hibernate)相关信息
hbm2ddl:
auto: update
#指定hibernate方言
dialect: org.hibernate.dialect.MySQLDialect
#格式化sql语句,默认false,这个配置需要配合spring.jpa.show-sql=true使用,默认的sql语句是带括号的,开启这个配置后语句会换行
format_sql: true
#是否在日志中打印出自动生成的sql,方便调试的时候查看
show-sql: true
#配置日志
logging:
level:
root: warn
com.bdqn.demo.mapper: trace
pattern:
console: '%p%m%n'
# path: /tmp/log
# file: crm.log
server:
error:
path: /error
port: 8090
reactive:
session:
timeout: 60
tomcat:
threads:
max: 600
uri-encoding: UTF-8
basedir: /tmp/log
启动类
@SpringBootApplication
@MapperScan("com.bdqn.mapper")
public class MyBatisPlusApplication {
public static void main(String[] args) {
SpringApplication.run(MyBatisPlusApplication.class, args);
}
}
实体类:User.java
@Data
@TableName("sys_user")
public class User implements Serializable {
@TableId(type = IdType.AUTO)
@TableField("usr_id")
private Long usrId;
@TableField("usr_name")
private String usrName;
// @TableField("usr_password")
private String usrPassword;
// @TableField("usr_role_id")
private Long usrRoleId;
// @TableField("usr_flag")
private Integer usrFlag;
public User() {
}
public User(String usrName, String usrPassword, Long usrRoleId, Integer usrFlag) {
this.usrName = usrName;
this.usrPassword = usrPassword;
this.usrRoleId = usrRoleId;
this.usrFlag = usrFlag;
}
public User(Long usrId, String usrName, String usrPassword, Long usrRoleId, Integer usrFlag) {
this.usrId = usrId;
this.usrName = usrName;
this.usrPassword = usrPassword;
this.usrRoleId = usrRoleId;
this.usrFlag = usrFlag;
}
}
编写Mapper接口:UserMapper.java
public interface UserMapper extends BaseMapper<User> {
}
测试
@Test
public void testSelectById() {
User user = userMapper.selectById(1L);
System.out.println("usrName:" + user.getUsrName());
}
至此,Spring Boot集成MyBatis-Plus完成。通过几个简单得步骤就可以实现按sys_user表的按主键查询功能,甚至连SQL语句都不用写!
1.1.3 核心功能
从以上步骤中,我们可以看到集成MyBatis-Plus非常的简单,只需要引入starter启动器,并配置mapper扫描路径即可。
CRUD接口
MyBatis-Plus通用的CRUD功能都在BaseMapper<T>接口中,源代码如下
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//
package com.baomidou.mybatisplus.core.mapper;
import com.baomidou.mybatisplus.core.batch.BatchSqlSession;
import com.baomidou.mybatisplus.core.batch.MybatisBatch;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.override.MybatisMapperProxy;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.MybatisBatchUtils;
import com.baomidou.mybatisplus.core.toolkit.MybatisUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.core.toolkit.reflect.GenericTypeUtils;
import java.io.Serializable;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.BiPredicate;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.exceptions.TooManyResultsException;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.ognl.OgnlOps;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
public interface BaseMapper<T> extends Mapper<T> {
int insert(T entity);
default int deleteById(Serializable id) {
return this.deleteById(id, true);
}
default int deleteById(Object obj, boolean useFill) {
Class<?> entityClass = GenericTypeUtils.resolveTypeArguments(this.getClass(), BaseMapper.class)[0];
if (!entityClass.isAssignableFrom(obj.getClass()) && useFill) {
TableInfo tableInfo = TableInfoHelper.getTableInfo(entityClass);
if (tableInfo.isWithLogicDelete() && tableInfo.isWithUpdateFill()) {
T instance = tableInfo.newInstance();
tableInfo.setPropertyValue(instance, tableInfo.getKeyProperty(), new Object[]{OgnlOps.convertValue(obj, tableInfo.getKeyType())});
return this.deleteById(instance);
}
}
MybatisMapperProxy<?> mybatisMapperProxy = MybatisUtils.getMybatisMapperProxy(this);
SqlSession sqlSession = mybatisMapperProxy.getSqlSession();
return sqlSession.delete(mybatisMapperProxy.getMapperInterface().getName() + "." + SqlMethod.DELETE_BY_ID.getMethod(), obj);
}
int deleteById(T entity);
default int deleteByMap(Map<String, Object> columnMap) {
return this.delete((Wrapper)Wrappers.query().allEq(columnMap));
}
int delete(@Param("ew") Wrapper<T> queryWrapper);
/** @deprecated */
@Deprecated
default int deleteBatchIds(@Param("coll") Collection<?> idList) {
return this.deleteByIds(idList);
}
default int deleteByIds(@Param("coll") Collection<?> idList) {
return this.deleteByIds(idList, true);
}
default int deleteByIds(@Param("coll") Collection<?> collections, boolean useFill) {
MybatisMapperProxy<?> mybatisMapperProxy = MybatisUtils.getMybatisMapperProxy(this);
Class<?> entityClass = GenericTypeUtils.resolveTypeArguments(this.getClass(), BaseMapper.class)[0];
SqlSession sqlSession = mybatisMapperProxy.getSqlSession();
Class<?> mapperInterface = mybatisMapperProxy.getMapperInterface();
TableInfo tableInfo = TableInfoHelper.getTableInfo(entityClass);
Map<String, Object> params = new HashMap();
if (useFill && tableInfo.isWithLogicDelete() && tableInfo.isWithUpdateFill()) {
params.put("et", tableInfo.newInstance());
}
params.put("coll", collections);
return sqlSession.delete(mapperInterface.getName() + "." + SqlMethod.DELETE_BY_IDS.getMethod(), params);
}
int updateById(@Param("et") T entity);
int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);
default int update(@Param("ew") Wrapper<T> updateWrapper) {
return this.update((Object)null, updateWrapper);
}
T selectById(Serializable id);
List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);
void selectBatchIds(@Param("coll") Collection<? extends Serializable> idList, ResultHandler<T> resultHandler);
default List<T> selectByMap(Map<String, Object> columnMap) {
return this.selectList((Wrapper)Wrappers.query().allEq(columnMap));
}
default void selectByMap(Map<String, Object> columnMap, ResultHandler<T> resultHandler) {
this.selectList((Wrapper)Wrappers.query().allEq(columnMap), resultHandler);
}
default T selectOne(@Param("ew") Wrapper<T> queryWrapper) {
return this.selectOne(queryWrapper, true);
}
default T selectOne(@Param("ew") Wrapper<T> queryWrapper, boolean throwEx) {
List<T> list = this.selectList(queryWrapper);
int size = list.size();
if (size == 1) {
return list.get(0);
} else if (size > 1) {
if (throwEx) {
throw new TooManyResultsException("Expected one result (or null) to be returned by selectOne(), but found: " + size);
} else {
return list.get(0);
}
} else {
return null;
}
}
default boolean exists(Wrapper<T> queryWrapper) {
Long count = this.selectCount(queryWrapper);
return null != count && count > 0L;
}
Long selectCount(@Param("ew") Wrapper<T> queryWrapper);
List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);
void selectList(@Param("ew") Wrapper<T> queryWrapper, ResultHandler<T> resultHandler);
List<T> selectList(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
void selectList(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper, ResultHandler<T> resultHandler);
List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);
void selectMaps(@Param("ew") Wrapper<T> queryWrapper, ResultHandler<Map<String, Object>> resultHandler);
List<Map<String, Object>> selectMaps(IPage<? extends Map<String, Object>> page, @Param("ew") Wrapper<T> queryWrapper);
void selectMaps(IPage<? extends Map<String, Object>> page, @Param("ew") Wrapper<T> queryWrapper, ResultHandler<Map<String, Object>> resultHandler);
<E> List<E> selectObjs(@Param("ew") Wrapper<T> queryWrapper);
<E> void selectObjs(@Param("ew") Wrapper<T> queryWrapper, ResultHandler<E> resultHandler);
default <P extends IPage<T>> P selectPage(P page, @Param("ew") Wrapper<T> queryWrapper) {
page.setRecords(this.selectList(page, queryWrapper));
return page;
}
default <P extends IPage<Map<String, Object>>> P selectMapsPage(P page, @Param("ew") Wrapper<T> queryWrapper) {
page.setRecords(this.selectMaps(page, queryWrapper));
return page;
}
default boolean insertOrUpdate(T entity) {
Class<?> entityClass = GenericTypeUtils.resolveTypeArguments(this.getClass(), BaseMapper.class)[0];
TableInfo tableInfo = TableInfoHelper.getTableInfo(entityClass);
Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!", new Object[0]);
String keyProperty = tableInfo.getKeyProperty();
Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!", new Object[0]);
Object idVal = tableInfo.getPropertyValue(entity, tableInfo.getKeyProperty());
return !StringUtils.checkValNull(idVal) && !Objects.isNull(this.selectById((Serializable)idVal)) ? this.updateById(entity) > 0 : this.insert(entity) > 0;
}
default List<BatchResult> insert(Collection<T> entityList) {
return this.insert(entityList, 1000);
}
default List<BatchResult> insert(Collection<T> entityList, int batchSize) {
MybatisMapperProxy<?> mybatisMapperProxy = MybatisUtils.getMybatisMapperProxy(this);
MybatisBatch.Method<T> method = new MybatisBatch.Method(mybatisMapperProxy.getMapperInterface());
SqlSessionFactory sqlSessionFactory = MybatisUtils.getSqlSessionFactory(mybatisMapperProxy);
return MybatisBatchUtils.execute(sqlSessionFactory, entityList, method.insert(), batchSize);
}
default List<BatchResult> updateById(Collection<T> entityList) {
return this.updateById(entityList, 1000);
}
default List<BatchResult> updateById(Collection<T> entityList, int batchSize) {
MybatisMapperProxy<?> mybatisMapperProxy = MybatisUtils.getMybatisMapperProxy(this);
MybatisBatch.Method<T> method = new MybatisBatch.Method(mybatisMapperProxy.getMapperInterface());
SqlSessionFactory sqlSessionFactory = MybatisUtils.getSqlSessionFactory(mybatisMapperProxy);
return MybatisBatchUtils.execute(sqlSessionFactory, entityList, method.updateById(), batchSize);
}
default List<BatchResult> insertOrUpdate(Collection<T> entityList) {
return this.insertOrUpdate(entityList, 1000);
}
default List<BatchResult> insertOrUpdate(Collection<T> entityList, int batchSize) {
MybatisMapperProxy<?> mybatisMapperProxy = MybatisUtils.getMybatisMapperProxy(this);
Class<?> entityClass = GenericTypeUtils.resolveTypeArguments(this.getClass(), BaseMapper.class)[0];
TableInfo tableInfo = TableInfoHelper.getTableInfo(entityClass);
String keyProperty = tableInfo.getKeyProperty();
String statement = mybatisMapperProxy.getMapperInterface().getName() + "." + SqlMethod.SELECT_BY_ID.getMethod();
return this.insertOrUpdate(entityList, (sqlSession, entity) -> {
Object idVal = tableInfo.getPropertyValue(entity, keyProperty);
return StringUtils.checkValNull(idVal) || CollectionUtils.isEmpty(sqlSession.selectList(statement, entity));
}, batchSize);
}
default List<BatchResult> insertOrUpdate(Collection<T> entityList, BiPredicate<BatchSqlSession, T> insertPredicate) {
return this.insertOrUpdate(entityList, insertPredicate, 1000);
}
default List<BatchResult> insertOrUpdate(Collection<T> entityList, BiPredicate<BatchSqlSession, T> insertPredicate, int batchSize) {
MybatisMapperProxy<?> mybatisMapperProxy = MybatisUtils.getMybatisMapperProxy(this);
MybatisBatch.Method<T> method = new MybatisBatch.Method(mybatisMapperProxy.getMapperInterface());
SqlSessionFactory sqlSessionFactory = MybatisUtils.getSqlSessionFactory(mybatisMapperProxy);
return MybatisBatchUtils.saveOrUpdate(sqlSessionFactory, entityList, method.insert(), insertPredicate, method.updateById(), batchSize);
}
}
说明:
- BaseMapper接口 封装通用CRUD,为MyBatis-Plus启动时自动解析实体表关系映射转换为MyBatis内部对象注入容器
- 泛型T为任意实体对象
- 参数Serializable为任意类型主键,MyBatis-Plus不推荐使用复合主键,约定每一个表都有自己唯一的id主键
- 对象Wrapper为条件构造器
使用CRUD其他测试方法
@Test
public void testInsert() {
userMapper.insert(new User("MyBatis-Plus", "123456", 2L, 1));
}
@Test
public void testFinAll() {
List<User> list = userMapper.selectList(null);
for (User user : list) {
System.out.println("usrName:" + user.getUsrName());
}
}
@Test
public void testFind() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("usr_name", "Mybatis-Plus");
wrapper.eq("usr_password", "123456");
List<User> list = userMapper.selectList(wrapper);
for (User user : list) {
System.out.println("usrId:" + user.getUsrId());
}
}
@Test
public void testPageination() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("usr_role_id", 2L);
Page<User> page = new Page<>(1,2);
IPage<User> userIPage = userMapper.selectPage(page, wrapper);
System.out.println("总记录数 ----------> " + userIPage.getTotal());
System.out.println("总页数 ------------> " + userIPage.getPages());
System.out.println("当前页数 ----------> " + userIPage.getCurrent());
System.out.println("每页记录数 --------> " + userIPage.getSize());
System.out.println("当前记录: ---------> ");
for (User user : userIPage.getRecords()) {
System.out.println("usrID:" + user.getUsrId());
}
}