侧边栏壁纸
博主头像
包包博主等级

talk is cheap,show me the code

  • 累计撰写 25 篇文章
  • 累计创建 59 个标签
  • 累计收到 55 条评论

SpringBoot整合MyBatis实战

包包
2021-06-26 / 0 评论 / 5 点赞 / 1,602 阅读 / 11,502 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-04-19,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

基本增删改查

1.准备工作

给数据库创建表tb_user

CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` varchar(30) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '邮箱',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `version` int(11) unsigned NOT NULL DEFAULT '1' COMMENT '乐观锁字段',
  `status` varchar(50) NOT NULL DEFAULT '' COMMENT '状态:启用ENABLE,禁用DISABLE',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='用户表';

编写表对应的实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserPO {
    private Long id;
    private String userName;
    private Integer age;
    private String email;
    private Date createTime;
    private Date updateTime;
    private Integer version;
    private UserStatusEnum status;
}

编写用户状态的枚举类

public enum UserStatusEnum {
    /**
     * 启用
     */
    ENABLE,
    /**
     * 禁用
     */
    DISABLE;
}

添加maven依赖

<!--引入整合MyBatis的启动器-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>

MyBatis-Spring-Boot-Starter依赖于MyBatis-Spring,它们与SpringBoot的版本关系如下

MyBatis-Spring-Boot-Starter MyBatis-Spring Spring Boot Java
2.2 2.0 (need 2.0.6+ for enable all features) 2.5 or higher 8 or higher
2.1 2.0 (need 2.0.6+ for enable all features) 2.1 - 2.4 8 or higher
1.3 1.3 1.5 6 or higher

yaml中配置数据库基本信息

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.157.130:3306/mybatis_plus?serverTimezone=GMT%2B8
    username: root
    password: root

2.mapper.xml文件版

编写UserMapper.java,添加增删改查方法,并标注@Mapper注解

@Mapper
public interface UserMapper {
    void insert(UserPO userPO);

    void update(UserPO userPO);

    void deleteById(Long userId);

    List<UserPO> getAll();
    
    UserPO getById(Long userId);
}

编写UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.baobao.mybatis.mapper.UserMapper">

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        insert into tb_user(
           <if test="userName != null and userName != ''">user_name,</if>
           <if test="age != null">age,</if>
           <if test="email != null and email != ''">email,</if>
           create_time, update_time
        ) values (
            <if test="userName != null and userName != ''">#{userName},</if>
            <if test="age != null">#{age},</if>
            <if test="email != null and email != ''">#{email},</if>
            sysdate(), sysdate()
        )
    </insert>

    <update id="update">
        update tb_user
        <set>
            <if test="userName != null and userName != ''">user_name = #{userName},</if>
            <if test="age != null">age = #{age},</if>
            <if test="email != null and email != ''">email = #{email},</if>
            <if test="status != null">status = #{status},</if>
            update_time = sysdate(), version = version + 1
        </set>
        where id = #{id}
    </update>

    <delete id="deleteById">
        delete from tb_user where id = #{userId}
    </delete>

    <select id="getAll" resultType="com.baobao.mybatis.entity.po.UserPO">
        select * from tb_user
    </select>

    <select id="getById" resultType="com.baobao.mybatis.entity.po.UserPO">
        select * from tb_user where id = #{userId}
    </select>
</mapper>

在yaml中配置mapper.xml文件的位置

编写测试类,测试插入

@Test
void testInsert() {
    UserPO userPO = new UserPO();
    userPO.setUserName("baobao");
    userPO.setAge(18);
    userMapper.insert(userPO);
}

测试更新

@Test
void testUpdate() {
    UserPO userPO = new UserPO();
    userPO.setId(12L);
    userPO.setUserName("baobao333");
    userPO.setStatus(UserStatusEnum.ENABLE);
    userMapper.update(userPO);
}

测试删除

@Test
void testDelete() {
    userMapper.deleteById(12L);
}

测试查询

@Test
void testQuery() {
    List<UserPO> all = userMapper.getAll();
    System.out.println(all);
    UserPO userPO = userMapper.getById(11L);
    System.out.println(userPO);
}

这里我们会发现像userNamecreateTime这种多单词组合的属性都没有查出来,原因是mysql中属性名称是类似user_namecreate_time这种下划线形式,有以下两种解决方案:

  1. UserMapper.xml中手动定义resultMap

  1. 配置驼峰转下划线字段映射。有3种配置方式:

    1. 在yaml中用config‐location指定mybatis全局xml配置文件的位置,然后在全局配置文件中配置map-underscore-to-camel-case

      mybatis:
          config‐location: classpath:mybatis/mybatis‐config.xml # 指定全局配置文件的位置
          mapper‐locations: classpath:mapper/*Mapper.xml  # 指定sql映射文件的位置
      
    2. 编写Mybatis配置类,在配置类中配置

      @Configuration
      public class MybatisConfig {
          // 自定义配置mybatis全局配置文件中的属性
          @Bean
          public ConfigurationCustomizer customizer(){
              return configuration -> {
                  // 设置驼峰名称映射
                  configuration.setMapUnderscoreToCamelCase(true);
              };
          }
      }
      
    3. 在yaml配置文件中配置configuration.map-underscore-to-camel-case

      mybatis:
        mapper-locations: classpath:mapper/*Mapper.xml
        configuration:  # mybatis全局配置文件中有的属性都可以在这里配置
          map-underscore-to-camel-case: true
      

yaml中可以支持的mybatis相关的所有配置如下:

Property Description
config-location Location of MyBatis xml config file.
check-config-location Indicates whether perform presence check of the MyBatis xml config file.
mapper-locations Locations of Mapper xml config file.
type-aliases-package Packages to search for type aliases. (Package delimiters are “,; \t\n”)
type-aliases-super-type The super class for filtering type alias. If this not specifies, the MyBatis deal as type alias all classes that searched from type-aliases-package.
type-handlers-package Packages to search for type handlers. (Package delimiters are “,; \t\n”)
executor-type Executor type: SIMPLE, REUSE, BATCH
default-scripting-language-driver The default scripting language driver class. This feature requires to use together with mybatis-spring 2.0.2+.
configuration-properties Externalized properties for MyBatis configuration. Specified properties can be used as placeholder on MyBatis config file and Mapper file. For detail see the MyBatis reference page.
lazy-initialization Whether enable lazy initialization of mapper bean. Set true to enable lazy initialization. This feature requires to use together with mybatis-spring 2.0.2+.
configuration.* Property keys for Configuration bean provided by MyBatis Core. About available nested properties see the MyBatis reference page. NOTE: This property cannot be used at the same time with the config-location.
scripting-language-driver.thymeleaf.* Property keys for ThymeleafLanguageDriverConfig bean provided by MyBatis Thymeleaf. About available nested properties see the MyBatis Thymeleaf reference page.
scripting-language-driver.freemarker.* Properties keys for FreeMarkerLanguageDriverConfig bean provided by MyBatis FreeMarker. About available nested properties see the MyBatis FreeMarker reference page. This feature requires to use together with mybatis-freemarker 1.2.0+.
scripting-language-driver.velocity.* Properties keys for VelocityLanguageDriverConfig bean provided by MyBatis Velocity. About available nested properties see the MyBatis Velocity reference page. This feature requires to use together with mybatis-velocity 2.1.0+.

如果我们的Mapper接口很多,在每个接口上都标注@Mapper非常麻烦,我们可以选择在配置类上标注@MapperScan,指定Mapper接口所在的包名,这样就省去了在每个Mapper接口上标注@Mapper

3.注解版

可以省去UserMapper.xml文件,直接在UserMapper.java接口中利用注解编写sql语句

@Mapper
public interface UserMapper {
    // 设置自增主键的值到department的id属性
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into tb_user (name, age, email) values(#{name}, #{age}, #{email})")
    void insert(UserPO userPO);

    @Update("update tb_user set name = #{name} where id = #{id}")
    void update(UserPO userPO);

    @Delete("delete from tb_user where id = #{userId}")
    void deleteById(Long userId);

    @Select("select * from tb_user")
    List<UserPO> getAll();

    @Select("select * from tb_user where id = #{userId}")
    UserPO getById(Long userId);
}

这种方式实际中不推荐,对于复杂sql语句的支持不友好

枚举处理

默认情况下,Mybatis会将枚举的名称字符串作为值保存到数据库指定字段,查询时再映射成枚举类型

如果我们想自定义插入时的值为枚举的代码,而不是名称字符串,需要自己实现枚举类型处理器

首先改造之前的用户状态枚举类,实现自定义代码的设置

@AllArgsConstructor
@Getter
public enum UserStatusEnum {
    /**
     * 启用
     */
    ENABLE(0, "启用"),
    /**
     * 禁用
     */
    DISABLE(1, "禁用");
    
    private final int code;
    private final String desc;
    
    public static UserStatusEnum getByCode(int code) {
        UserStatusEnum[] values = UserStatusEnum.values();
        for (UserStatusEnum instance : values) {
            if (instance.getCode() == code) {
                return instance;
            }
        }
        return null;
    }
}

然后编写自定义枚举处理类,实现TypeHandler接口,泛型指定我们需要处理的java类型。在类上标注@MappedTypes注解

@MappedTypes(UserStatusEnum.class)
public class UserStatusEnumTypeHandler implements TypeHandler<UserStatusEnum> {
    /**
     * 定义当前数据如何保存到数据库中
     */
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, UserStatusEnum userStatusEnum, JdbcType jdbcType) throws SQLException {
        preparedStatement.setInt(i, userStatusEnum.getCode());
    }

    /**
     * 从数据库取出的数据如何映射成java类
     */
    @Override
    public UserStatusEnum getResult(ResultSet resultSet, String columnName) throws SQLException {
        return UserStatusEnum.getByCode(resultSet.getInt(columnName));
    }

    @Override
    public UserStatusEnum getResult(ResultSet resultSet, int columnIndex) throws SQLException {
        return UserStatusEnum.getByCode(resultSet.getInt(columnIndex));
    }

    @Override
    public UserStatusEnum getResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
        return UserStatusEnum.getByCode(callableStatement.getInt(columnIndex));
    }
}

然后在yaml配置中指定自定义TypeHandler所在的包名

此时插入数据库的数据就变成了自定义枚举的code值(当然前提是要修改数据库表中status字段的类型)

整合分页插件

首先引入PageHelper依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>

在yaml中配置分页插件参数

pagehelper:
  helperDialect: mysql  # 数据库方言
  reasonable: true  # 分页合理化参数,默认值为false。当该参数设置为 true 时,pageNum<=0 时会查询第一页, pageNum>pages(超过总数时),会查询最后一页。默认false 时,直接根据参数进行查询

编写分页测试代码

@Test
void testPage() {
    // 分页,查询第2页,每页3条数据
    PageHelper.startPage(2, 3);
    // 紧跟在startPage之后的方法会被分页
    List<UserPO> list = userMapper.getAll();
    // 分页结果包装成分页信息
    PageInfo<UserPO> pageInfo = new PageInfo<>(list);
    // 遍历结果集
    for (UserPO userPO : list) {
        System.out.println(userPO);
    }
    // 打印分页信息
    System.out.println("当前页码:" + pageInfo.getPageNum());
    System.out.println("每页数据条数:" + pageInfo.getPageSize());
    System.out.println("总条数:" + pageInfo.getTotal());
    System.out.println("前一页页码:" + pageInfo.getPrePage());
    System.out.println("后一页页码:" + pageInfo.getNextPage());
    System.out.println("是否首页:" + pageInfo.isIsFirstPage());
    System.out.println("是否尾页:" + pageInfo.isIsLastPage());
    System.out.println("是否有前一页:" + pageInfo.isHasPreviousPage());
    System.out.println("是否有后一页:" + pageInfo.isHasNextPage());
}

批量插入或更新

我们首先尝试用最简单的循环插入单条数据来实现批量插入

@Test
void testBatchInsert() {
    long start = System.currentTimeMillis();
    // 插入10000条数据
    for (int i = 0; i < 10000; i++) {
        UserPO userPO = new UserPO();
        userPO.setUserName("baobao" + i);
        userPO.setAge(18 + i);
        userMapper.insert(userPO);
    }
    System.out.println(System.currentTimeMillis() - start);
}

最终的执行时间是13.328秒,非常慢

我们可以利用foreach标签优化批量插入速度。首先新建Mapper的批量插入方法

// UserMapper.java
void insertBatch(List<UserPO> userPOList);
<!--UserMapper.xml-->
<insert id="insertBatch">
    insert into tb_user(user_name, age) values
    <foreach collection="userPOList" item="user" separator=",">
        (#{user.userName}, #{user.age})
    </foreach>
</insert>

利用这个insertBatch测试批量插入数据

@Test
void testBatchInsert() {
    long start = System.currentTimeMillis();
    List<UserPO> userPOList = new ArrayList<>();
    for (int i = 0; i < 10000; i++) {
        UserPO userPO = new UserPO();
        userPO.setUserName("baobao" + i);
        userPO.setAge(18 + i);
        userPOList.add(userPO);
    }
    userMapper.insertBatch(userPOList);
    System.out.println(System.currentTimeMillis() - start);
}

耗时935毫秒,速度提升非常明显

但是当我们继续增大一次性插入的数据量,foreach的速度也将变得很慢。比如将插入数据量增加到80000条,此时耗时54.861秒

我们可以开启Batch类型的SqlSession来优化,yaml中配置mybatis.executor-type: batch

此时插入速度提升到31.826秒

进一步优化,配置mybatis.configuration.default-executor-type: batch

此时插入速度提升到1.986秒,效果非常明显

5

评论区