SpringBoot 整合 MyBatisPlus

一、整合 MyBatisPlus

1. 导入依赖

<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.1</version></dependency>

2. 配置文件

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=UTF-8username: root
    password: root

mybatis-plus:mapper-locations: classpath:/mapper/**/*.xmltypeAliasesPackage: com.cnbai.*.*global-config:db-config:id-type: AUTO
      # 数据库字段驼峰下划线转换db-column-underline:truerefresh-mapper:trueconfiguration:# 自动驼峰命名map-underscore-to-camel-case:true# 查询结果中包含空值的列，在映射的时候，不会映射这个字段call-setters-on-nulls:true# 开启 sql 日志log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 关闭 sql 日志# log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl

3. 配置类

importcom.baomidou.mybatisplus.annotation.DbType;importcom.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;importcom.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;importcom.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.context.annotation.Primary;@ConfigurationpublicclassMybatisPlusConfig{/**
     * 加载分页插件
     */@BeanpublicMybatisPlusInterceptormybatisPlusInterceptor(){MybatisPlusInterceptor interceptor =newMybatisPlusInterceptor();
        interceptor.addInnerInterceptor(newPaginationInnerInterceptor(DbType.MYSQL));return interceptor;}@Primary@Bean@ConfigurationProperties("mybatis-plus")publicMybatisPlusPropertiesmybatisPlusProperties(){returnnewMybatisPlusProperties();}}

4. 业务类

publicinterfaceUserServiceextendsIservice<User>{}@ServicepublicclassUserServiceImplextendsServiceImpl<UserDao,User>implementsUserService{}@MapperpublicinterfaceUserDaoextendsBaseMapper<User>{}

二、查询

1. 普通查询

publicList<User>queryUserList(String name){LambdaQueryWrapper<User> wrapper =newLambdaQueryWrapper<>();
    wrapper.eq(User::getName, name);returnlist(wrapper);}publicUserqueryUserById(String id){returngetBaseMapper().selectById(id);}

2. 拼接查询

LambdaQueryWrapper<User> queryWrapper =newLambdaQueryWrapper<>();
 queryWrapper
        .eq(User::getName,"张三").eq(User::getAge,26);List<User> list =list(queryWrapper);

3. 分页查询

// offset 默认从 1 开始，展示第一页publicPage<User>queryPage(Integer offset,Integer limit){return userDao.selectPage(PageDTO.of(offset, limit),newQueryWrapper<>());}

4. 查询部分字段

publicList<User>queryUserList(){LambdaQueryWrapper<User> wrapper =newLambdaQueryWrapper<>();
    wrapper.select(User::getName,User::getAge);returnlist(wrapper);}

三、常用条件构造器

1. 单条件

# name ='bai'eq("name","bai")

2. 拼接 AND

# name =?AND age =?.eq("name","张三").eq("age",26);

3. 拼接 OR

# id =1 or name ='bai'.eq("id",1).or().eq("name","bai")

4. 嵌套

# or ( name ='李白' and status <>'活着').or ( x -> x.eq("name","李白").ne("status","活着"))

# and (  name ='李白' or ( name ='张三' and age =12)).and(  x -> x.eq("name","李白").or( y -> y.eq("name","张三").eq("age",12)))

四、处理 Json 数据

1. 存储

实体类中某个字段属性是 List，Map 之类的可以转为 Json 格式，其在 MySQL 中存储字段类型可以设置为 Json 类型，添加注解将此类型映射为 Json 存入数据库中

@TableName(value ="t_user")publicclassUser{@TableIdprivateint id;@TableField(value ="user_info", typeHandler =JacksonTypeHandler.class)privateJSONArray userInfo;@TableField(value ="info", typeHandler =JacksonTypeHandler.class)privateJSONObject info;}

2. 取出

当没有使用到 xml 时

@TableName(value ="t_user", autoResultMap =true)publicclassUser{@TableIdprivateint id;@TableField(value ="user_info", typeHandler =JacksonTypeHandler.class)privateJSONArray userInfo;@TableField(value ="info", typeHandler =JacksonTypeHandler.class)privateJSONObject info;}

当使用了 xml 时

<resultproperty="userInfo"column="user_info"typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/><resultproperty="info"column="info"typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>

五、循环遍历集合

/**
 * List : User(id=1, name=zhangsan, age=21)
 * List : User(id=2, name=lisi, age=22)
 *
 * Map : User(id=2, name=lisi, age=22)
 * Map : User(id=3, name=wangwu, age=23)
 */publicvoidqueryTest(){
    userService.queryByList();
    userService.queryByMap();
    userService.updateByMap();
    userService.updateByList();}

1. UserService

publicclassUserService{@ResourceUserDao userDao;publicvoidqueryByList(){List<Integer> list =newArrayList<>();
        list.add(1);
        list.add(2);
        userDao.queryByList(list);}publicvoidqueryByMap(){Map<String,Object> map =newHashMap<>();
        map.put("name", username);
        userDao.queryByMap(map);}publicvoidupdateByMap(){Map<Integer,Integer> map =newHashMap<>();
        map.put(2,21);
        map.put(3,31);
        userDao.updateByMap(map);}publicvoidupdateByList(){List<User> list =newArrayList<>();
        list.add(user1);
        list.add(user2);
        userDao.updateByList(list);}}

2. UserDao

publicinterfaceUserDaoextendsBaseMapper<User>{// UserMapper.xml 中 collection 的值对应 @Param 里的值List<User>queryByList(@Param("list")List<Integer> userList);// 此处不能使用 @Param , 或者不用 Map 直接传参 -> queryByMap(@Param("name") String username);List<Map<String,Object>>queryByMap(Map<String,Object> map);voidupdateByMap(@Param("map")Map<Integer,Integer> map);voidupdateByList(@Param("list")List<User> userList);}

3. UserMapper

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.bai.dao.UserDao"><resultMapid="userMap"type="com.bai.entity.User"><idcolumn="id"jdbcType="INTEGER"property="id"/><resultcolumn="name"jdbcType="VARCHAR"property="name"/><resultcolumn="age"jdbcType="INTEGER"property="age"/></resultMap><selectid="queryByList"parameterType="java.util.List"resultMap="userMap">
    select * from user where
    <choose><whentest="list != null and list.size() > 0">
        id in
        <foreachcollection="list"item="id"open="("separator=","close=")">
              #{id}
            </foreach></when><otherwise>
        1 = 2
      </otherwise></choose></select><selectid="queryByMap"parameterType="java.util.Map"resultMap="java.util.Map">
    select id, name as username, age from user where
    <iftest="name != null and name != ''">
      name = #{name}
    </if></select><updateid="updateByMap"parameterType="java.util.Map"><foreachcollection="map"index="id"item="age"separator=";">
      update user set age = #{age} where id = #{id}
    </foreach></update><updateid="updateByList"parameterType="java.util.List"><foreachcollection="list"index="index"item="user">
      update user set age = #{user.age} where id = #{user.id}
    </foreach></update></mapper>

4. 增加配置参数

Mybatis 批量更新时需要在

url

后加上

&allowMultiQueries=true

application.yml

spring:datasource:username: root
    password: root
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=truedriver-class-name: com.mysql.jdbc.Driver

否则会报错

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You have an error in your SQL syntax; check the manual that corresponds toyourMySQL server version for the right syntax touse near 'update user set age =1 where id =3' at line 3
### The error may involve com.bai.faker.mapper.UserMapper.updateByMapThree-Inline
### The error occurred while setting parameters
### SQL: update user set age =? where id =?;        update user set age =? where id =?
### Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You have an error in your SQL syntax; check the manual that corresponds toyourMySQL server version for the right syntax touse near 'update user set age =1 where id =3' at line 3; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You have an error in your SQL syntax; check the manual that corresponds toyourMySQL server version for the right syntax touse near 'update user set age =1 where id =3' at line 3......