相关依赖
MyBatis-Plus涉及的依赖主要是Mybatis-start、和分页插件的依赖,不考虑使用额外分页插件的前提下,只需要mybatis-plus-boot-starter一个依赖即可与SpringBoot集成:
<!--Mybatis-plugs--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.2</version></dependency>
SpringBoot基本引入Mybatis-Plus用法
Mybatis-plus的配置详解
SpringBoot引入Mybatis-Plus遵循start原则,配置化继承自Mybatis,关于数据库配置保持一致即可
spring:#数据库连接配置datasource:url: jdbc:mysql://localhost:3306/db_blog?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghaiusername: rootpassword: 1234driver-class-name: com.mysql.cj.jdbc.Driver
以下是Mybatis-Plus独有的配置
# MyBatis-Plus 配置
mybatis-plus:# MyBatis-Plus 的全局配置global-config:#banner图是否在日志中输出banner: off# 数据库相关配置db-config:# 主键类型id-type: auto # 可选值:auto, assign_id, assign_uuid, input# MyBatis-Plus 的配置configuration:# 是否开启驼峰命名转换map-underscore-to-camel-case: true# 日志实现log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 控制台输出 SQL 日志# 类路径下的 Mapper XML 文件位置mapper-locations: classpath*:mapper/*.xml# 类型别名包type-aliases-package: com.example.myapp.entity # 实体类所在的包(默认不需要额外配置)
如无特殊要求情况下,只需要简化配置即可, 其他配置项会自动不使用或者使用缺省值,以下为一个简化配置样例:
mybatis-plus:global-config:banner: offmapper-locations: classpath*:mapper/*.xml#configuration:#log-impl: org.apache.ibatis.logging.stdout.StdOutImpl 调试环境下打开,开启sql日志打印
基本代码结构
实体类
实体类定义,实体类主要与表或者业务概念对应,一般与表对应时,可配合使用相关的@Table家族注解:
@Data
//指定数据库中的表
@TableName("tb_artitle_data")
public class ArticleEntity implements Serializable {@TableIdprivate Int id;private String artName;private String artDesc;private String artUrl;private String artTime;//表中无此字段,Mapper处理时可略过此字段@TableField(exist = false)private String auth;
}
创建Mapper,相比于Mybatis的Mapper需要与XML关联,Mybatis-Plus的优势在于,对于基础的增删改查,可以不需要额外创建XML,如下:
@Mapper
//只需要Mapper注解,然后继承BaseMapper即可,泛型指定为对应表的实体类
public interface ArticleMapper extends BaseMapper<ArticleEntity> {
}
简单使用,直接在Service代码中引入Mapper,使用BaseMapper中已经定义好的方法,结合QueryWrapper方法,省略SQL
@Service
public class ArticleServiceImpl implements ArticleService {@Autowiredprivate ArticleMapper articleMapper;// 新增文章@Overridepublic boolean addArticle(ArticleEntity article) {return articleMapper.insert(article) > 0;}// 删除文章@Overridepublic boolean deleteArticle(String id) {return articleMapper.deleteById(id) > 0;}// 更新文章@Overridepublic boolean updateArticle(ArticleEntity article) {return articleMapper.updateById(article) > 0;}// 根据ID查询文章@Overridepublic ArticleEntity getArticleById(String id) {return articleMapper.selectById(id);}// 查询所有文章@Overridepublic List<ArticleEntity> getAllArticles() {return articleMapper.selectList(null);}// 分页查询文章@Overridepublic IPage<ArticleEntity> getArticlesByPage(int currentPage, int pageSize) {Page<ArticleEntity> page = new Page<>(currentPage, pageSize);return articleMapper.selectPage(page, null);}// 多条件组合查询文章public List<ArticleEntity> getArticlesByMultipleConditions(String artName, String artDesc, String artUrl, String startTime, String endTime) {QueryWrapper<ArticleEntity> queryWrapper = new QueryWrapper<>();if (artName != null && !artName.isEmpty()) {queryWrapper.eq("art_name", artName);}if (artDesc != null && !artDesc.isEmpty()) {queryWrapper.like("art_desc", artDesc);}if (artUrl != null && !artUrl.isEmpty()) {queryWrapper.eq("art_url", artUrl);}if (startTime != null && !startTime.isEmpty() && endTime != null && !endTime.isEmpty()) {queryWrapper.between("art_time", startTime, endTime);}return articleMapper.selectList(queryWrapper);}
}
还可以基于Mybatis-Plus自带的IService风格进行开发:
public interface ArticleService extends IService<ArticleEntity> {// 多条件查询文章List<ArticleEntity> getArticlesByMultipleConditions(String artName, String artDesc, String artUrl, String startTime, String endTime);
}@Service
public class ArticleServiceImpl extends ServiceImpl<ArticleMapper, ArticleEntity> implements ArticleService {// 新增文章@Overridepublic boolean save(ArticleEntity entity) {return super.save(entity);}// 删除文章@Overridepublic boolean removeById(String id) {return super.removeById(id);}// 更新文章@Overridepublic boolean updateById(ArticleEntity entity) {return super.updateById(entity);}// 根据ID查询文章@Overridepublic ArticleEntity getById(String id) {return super.getById(id);}// 查询所有文章public List<ArticleEntity> listAll() {return super.list();}// 分页查询文章public IPage<ArticleEntity> pageList(Page<ArticleEntity> page) {return super.page(page);}// 多条件查询文章@Overridepublic List<ArticleEntity> getArticlesByMultipleConditions(String artName, String artDesc, String artUrl, String startTime, String endTime) {QueryWrapper<ArticleEntity> queryWrapper = new QueryWrapper<>();// 动态添加查询条件if (artName != null && !artName.isEmpty()) {queryWrapper.eq("art_name", artName);}if (artDesc != null && !artDesc.isEmpty()) {queryWrapper.like("art_desc", artDesc);}if (artUrl != null && !artUrl.isEmpty()) {queryWrapper.eq("art_url", artUrl);}if (startTime != null && !startTime.isEmpty() && endTime != null && !endTime.isEmpty()) {queryWrapper.between("art_time", startTime, endTime);}return list(queryWrapper);}
}
Mapper层加强
基于注解编写复杂SQL
MyBatis-Plus 提供了注解方式可以在 Mapper 层编写复杂的 SQL 语句。这种方式省略掉了繁重的XML文件
@Mapper
public interface ArticleMapper extends BaseMapper<ArticleEntity> {// 根据多个条件查询文章@Select({"<script>","SELECT * FROM tb_article_data","WHERE 1=1","<if test='artName != null and !artName.isEmpty()'> AND art_name = #{artName} </if>","<if test='artDesc != null and !artDesc.isEmpty()'> AND art_desc LIKE CONCAT('%', #{artDesc}, '%') </if>","<if test='artUrl != null and !artUrl.isEmpty()'> AND art_url = #{artUrl} </if>","<if test='startTime != null and !startTime.isEmpty() and endTime != null and !endTime.isEmpty()'> AND art_time BETWEEN #{startTime} AND #{endTime} </if>","</script>"})List<ArticleEntity> selectByMultipleConditions(@Param("artName") String artName,@Param("artDesc") String artDesc,@Param("artUrl") String artUrl,@Param("startTime") String startTime,@Param("endTime") String endTime);// 插入文章并返回自动生成的ID@Insert({"INSERT INTO tb_article_data (art_name, art_desc, art_url, art_time)","VALUES (#{artName}, #{artDesc}, #{artUrl}, #{artTime})","RETURNING id"})@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")int insertAndReturnId(@Param("article") ArticleEntity article);// 更新文章信息@Update({"<script>","UPDATE tb_article_data","SET art_name = #{artName}, art_desc = #{artDesc}, art_url = #{artUrl}, art_time = #{artTime}","WHERE id = #{id}","</script>"})int updateArticle(@Param("article") ArticleEntity article);// 删除文章@Delete("DELETE FROM tb_article_data WHERE id = #{id}")int deleteArticleById(@Param("id") String id);// 使用存储过程@Select("CALL GetArticleById(#{id})")@Results({@Result(property = "id", column = "id", jdbcType = JdbcType.VARCHAR),@Result(property = "artName", column = "art_name", jdbcType = JdbcType.VARCHAR),@Result(property = "artDesc", column = "art_desc", jdbcType = JdbcType.VARCHAR),@Result(property = "artUrl", column = "art_url", jdbcType = JdbcType.VARCHAR),@Result(property = "artTime", column = "art_time", jdbcType = JdbcType.VARCHAR)})ArticleEntity callGetArticleById(@Param("id") String id);// 使用XML中的SQL片段@Select("${sqlFragment}")List<ArticleEntity> selectBySqlFragment(@Param("sqlFragment") String sqlFragment);//查询文章数量@Select("SELECT COUNT(*) FROM tb_article_data")int countAllArticles();
}
基于XML编写复杂SQL
也可以沿用Mybatis的方式,使用XML进行复杂SQL的编写
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.mapper.ArticleMapper"><!-- 根据多个条件查询文章 --><select id="selectByMultipleConditions" resultType="com.example.entity.ArticleEntity">SELECT * FROM tb_article_data<where><if test="artName != null and artName != ''">AND art_name = #{artName}</if><if test="artDesc != null and artDesc != ''">AND art_desc LIKE CONCAT('%', #{artDesc}, '%')</if><if test="artUrl != null and artUrl != ''">AND art_url = #{artUrl}</if><if test="startTime != null and startTime != '' and endTime != null and endTime != ''">AND art_time BETWEEN #{startTime} AND #{endTime}</if></where></select><!-- 插入文章并返回自动生成的ID --><insert id="insertAndReturnId" useGeneratedKeys="true" keyProperty="id">INSERT INTO tb_article_data (art_name, art_desc, art_url, art_time)VALUES (#{artName}, #{artDesc}, #{artUrl}, #{artTime})</insert><!-- 更新文章信息 --><update id="updateArticle">UPDATE tb_article_dataSETart_name = #{artName},art_desc = #{artDesc},art_url = #{artUrl},art_time = #{artTime}WHERE id = #{id}</update><!-- 删除文章 --><delete id="deleteArticleById">DELETE FROM tb_article_data WHERE id = #{id}</delete><!-- 使用存储过程 --><select id="callGetArticleById" resultType="com.example.entity.ArticleEntity">CALL GetArticleById(#{id})</select><!-- 查询文章数量 --><select id="countAllArticles" resultType="int">SELECT COUNT(*) FROM tb_article_data</select></mapper>
Mybatis-Plus的分页实现
内置的分页拦截器
MyBatis-Plus 提供了内置的分页拦截器,可以通过配置来启用分页功能。
@Configuration
public class MyBatisPlusConfigure {/*** 分页插件,自动识别数据库类型*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// 添加分页插件interceptor.addInnerInterceptor(new PaginationInnerInterceptor());return interceptor;}}
使用IPage进行分页参数设置和结果获取
@Service
public class ArticleServiceImpl extends ServiceImpl<ArticleMapper, ArticleEntity> implements ArticleService {// 分页查询文章public IPage<ArticleEntity> pageList(int currentPage, int pageSize) {Page<ArticleEntity> page = new Page<>(currentPage, pageSize);return baseMapper.selectPage(page, null);}// 多条件分页查询文章public IPage<ArticleEntity> pageListByMultipleConditions(int currentPage, int pageSize, String artName, String artDesc, String artUrl, String startTime, String endTime) {Page<ArticleEntity> page = new Page<>(currentPage, pageSize);QueryWrapper<ArticleEntity> queryWrapper = new QueryWrapper<>();if (artName != null && !artName.isEmpty()) {queryWrapper.eq("art_name", artName);}if (artDesc != null && !artDesc.isEmpty()) {queryWrapper.like("art_desc", artDesc);}if (artUrl != null && !artUrl.isEmpty()) {queryWrapper.eq("art_url", artUrl);}if (startTime != null && !startTime.isEmpty() && endTime != null && !endTime.isEmpty()) {queryWrapper.between("art_time", startTime, endTime);}return baseMapper.selectPage(page, queryWrapper);}
}
使用XML的SQL分页,需要将Page作为参数给到Mapper层的方法
public interface ArticleMapper extends BaseMapper<ArticleEntity> {// 查询所有文章List<ArticleEntity> selectAllArticles();// 根据多个条件查询文章List<ArticleEntity> selectByMultipleConditions(@Param("page") Page<AtricleEntity> page,@Param("artName") String artName,@Param("artDesc") String artDesc,@Param("artUrl") String artUrl,@Param("startTime") String startTime,@Param("endTime") String endTime);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.mapper.ArticleMapper"><!-- 查询所有文章 --><select id="selectAllArticles" resultType="com.example.entity.ArticleEntity">SELECT * FROM tb_article_data</select><!-- 根据多个条件查询文章 --><select id="selectByMultipleConditions" resultType="com.example.entity.ArticleEntity">SELECT * FROM tb_article_data<where><if test="artName != null and artName != ''">AND art_name = #{artName}</if><if test="artDesc != null and artDesc != ''">AND art_desc LIKE CONCAT('%', #{artDesc}, '%')</if><if test="artUrl != null and artUrl != ''">AND art_url = #{artUrl}</if><if test="startTime != null and startTime != '' and endTime != null and endTime != ''">AND art_time BETWEEN #{startTime} AND #{endTime}</if></where></select></mapper>
@Service
public class ArticleServiceImpl extends ServiceImpl<ArticleMapper, ArticleEntity> implements ArticleService {// 使用 XML 定义的多条件查询方法进行分页查询public IPage<ArticleEntity> pageListByMultipleConditionsUsingXml(int currentPage, int pageSize, String artName, String artDesc, String artUrl, String startTime, String endTime) {Page<ArticleEntity> page = new Page<>(currentPage, pageSize);Ipage<ArticleEntity> data= baseMapper.selectByMultipleConditions(page ,artName, artDesc, artUrl, startTime, endTime);return data;}
}
使用第三方的分页插件
PageHelper 是一个非常流行的 MyBatis 分页插件,具有易用简便的特点,且与Mybatis-Plus无缝连接。
引入依赖:
<dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper</artifactId><version>5.2.0</version></dependency>
在 application.yml 中配置 PageHelper
pagehelper:helperDialect: mysqlreasonable: truesupportMethodsArguments: trueparams: count=countSql
使用PageHelper
@Service
public class ArticleServiceImpl extends ServiceImpl<ArticleMapper, ArticleEntity> implements ArticleService {// 分页查询文章public PageInfo<ArticleEntity> pageListWithPageHelper(int currentPage, int pageSize) {PageHelper.startPage(currentPage, pageSize);List<ArticleEntity> articles = baseMapper.selectList(null);return new PageInfo<>(articles);}// 多条件分页查询文章public PageInfo<ArticleEntity> pageListByMultipleConditionsWithPageHelper(int currentPage, int pageSize, String artName, String artDesc, String artUrl, String startTime, String endTime) {PageHelper.startPage(currentPage, pageSize);QueryWrapper<ArticleEntity> queryWrapper = new QueryWrapper<>();if (artName != null && !artName.isEmpty()) {queryWrapper.eq("art_name", artName);}if (artDesc != null && !artDesc.isEmpty()) {queryWrapper.like("art_desc", artDesc);}if (artUrl != null && !artUrl.isEmpty()) {queryWrapper.eq("art_url", artUrl);}if (startTime != null && !startTime.isEmpty() && endTime != null && !endTime.isEmpty()) {queryWrapper.between("art_time", startTime, endTime);}List<ArticleEntity> articles = baseMapper.selectList(queryWrapper);return new PageInfo<>(articles);}
}