Custom type conversion TypeHandler of SpringBoot series Mybatis

Custom type conversion TypeHandler of SpringBoot series Mybatis

When using mybatis to perform db operations, one thing we often do is to map fields in db to java beans. Usually we use ResultMap to achieve the mapping. This tag can specify the binding relationship between the two, then if java The field type in the bean is different from that in the db, what should I do?

For example, the db is timestamp, but the java bean is defined as long

Realize custom type conversion through BaseTypeHandler

I. Environmental preparation

1. Database Preparation

Use mysql as the example database of this article, add a new table

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. Project environment

This article is developed with the help of SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA

pom depends on the following

<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

db configuration information application.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:

II. Demonstration

1. Entity definition

Note that the types create_at and update_at timestmap , and the Entity we defined is as follows

@Data
public class MoneyPo {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Long updateAt;
}

2. Mapper test interface

Define a simple query interface, and use the annotation method directly (as for the xml writing method is not much different)

/**
 * 主键查询
 *
 * @param id id
 * @return {@link MoneyPo}
 */
@Select("select * from money where id = #{id}")
@Results(id = "moneyResultMap", value = {
        @Result(property = "id", column = "id", id = true, jdbcType = JdbcType.INTEGER),
        @Result(property = "name", column = "name", jdbcType = JdbcType.VARCHAR),
        @Result(property = "money", column = "money", jdbcType = JdbcType.INTEGER),
        @Result(property = "isDeleted", column = "is_deleted", jdbcType = JdbcType.TINYINT),
        @Result(property = "createAt", column = "create_at", jdbcType = JdbcType.TIMESTAMP),
//            @Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP)})
        @Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP, typeHandler = Timestamp2LongHandler.class)})
MoneyPo getById(@Param("id") int id);

// 关于 SelectProvider 的使用，后面再说，主要是动态sql的演示
@SelectProvider(type = MoneyService.class, method = "getByIdSql")
@ResultMap(value = "moneyResultMap")
MoneyPo getByIdForProvider(@Param("id") int id);

description:

@Results : This annotation has the same effect as the ResultMap tag, and is mainly used to define the mapping relationship between db fields and java beans
id = "moneyResultMap" this id is defined, you can reuse achieve @Results
@Result : Pay attention to the updateAt of 061115110be826. Here, a custom TypeHandler is specified to realize JdbcType.TEMSTAMP and the long in Java Bean

3. Type conversion

Custom type conversion, mainly inherits the BaseTypeHandler class, and the generic type is the type in Java Bean

/**
 * 自定义类型转换：将数据库中的日期类型，转换成long类型的时间戳
 *
 * 三种注册方式：
 * 1.直接在 result标签中，指定typeHandler，如@Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP, typeHandler = Timestamp2LongHandler.class)
 * 2.在SqlSessionFactory实例中，注册 在SqlSessionFactory实例中.setTypeHandlers(new Timestamp2LongHandler());
 * 3.xml配置，<typeHandler handler="com.git.hui.boot.mybatis.handler.Timestamp2LongHandler"/>
 *
 * @author yihui
 * @date 2021/7/7
 */
@MappedTypes(value = Long.class)
@MappedJdbcTypes(value = {JdbcType.DATE, JdbcType.TIME, JdbcType.TIMESTAMP})
public class Timestamp2LongHandler extends BaseTypeHandler<Long> {

    /**
     * 将java类型，转换为jdbc类型
     *
     * @param preparedStatement
     * @param i
     * @param aLong             毫秒时间戳
     * @param jdbcType          db字段类型
     * @throws SQLException
     */
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Long aLong, JdbcType jdbcType) throws SQLException {
        if (jdbcType == JdbcType.DATE) {
            preparedStatement.setDate(i, new Date(aLong));
        } else if (jdbcType == JdbcType.TIME) {
            preparedStatement.setTime(i, new Time(aLong));
        } else if (jdbcType == JdbcType.TIMESTAMP) {
            preparedStatement.setTimestamp(i, new Timestamp(aLong));
        }
    }

    @Override
    public Long getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return parse2time(resultSet.getObject(s));
    }

    @Override
    public Long getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return parse2time(resultSet.getObject(i));
    }

    @Override
    public Long getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return parse2time(callableStatement.getObject(i));
    }

    private Long parse2time(Object value) {
        if (value instanceof Date) {
            return ((Date) value).getTime();
        } else if (value instanceof Time) {
            return ((Time) value).getTime();
        } else if (value instanceof Timestamp) {
            return ((Timestamp) value).getTime();
        }
        return null;
    }
}

setNonNullParameter: Convert java type to jdbc type
getNullableResult: Convert jdbc type to java type

4. TypeHandler registration

We define a TypeHandler ourselves, there is no problem, the next step is to need it to take effect. Generally speaking, there are the following ways

4.1 Specify in the result tag

Specified by typeHandler in the result tag

The way to use xml is as

<result column="update_at" property="updateAt" jdbcType="TIMESTAMP" typeHandler="com.git.hui.boot.mybatis.handler.Timestamp2LongHandler"/>

The way to annotate @Result is as

@Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP, typeHandler = Timestamp2LongHandler.class)

4.2 SqlSessionFactory global configuration

The above posture is precisely specified. If we want to apply to all scenes, we can achieve SqlSessionFactory

@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(
            // 设置mybatis的xml所在位置，这里使用mybatis注解方式，没有配置xml文件
            new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/*.xml"));
    // 注册typehandler，供全局使用
    bean.setTypeHandlers(new Timestamp2LongHandler());
    return bean.getObject();
}

4.3 Global xml configuration

In addition to the above case, there is another way mybatis-config.xml configuration file, such as

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//ibatis.apache.org//DTD Config 3.1//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!-- 驼峰下划线格式支持 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeHandlers>
        <typeHandler handler="com.git.hui.boot.mybatis.handler.Timestamp2LongHandler"/>
    </typeHandlers>
</configuration>

Note that to use the above configuration file, you need to specify the following configuration in SpringBoot, otherwise it will not take effect

mybatis:
  config-location: classpath:mybatis-config.xml

4.4 SpringBoot configuration method

Springboot configuration file, you can register type-handlers-package

mybatis:
  type-handlers-package: com.git.hui.boot.mybatis.handler

5. Summary

This article mainly introduces the mapping and adaptation strategies between the types in db and the types in java beans, mainly by inheriting BaseTypeHandler to achieve custom type conversion

To use a custom TypeHandler, there are two ways to take effect globally and specify precisely

@Result / <result> tags, specified by typeHandler
SqlSessionFactory global setting typeHandler
mybatis-config.xml configuration file settings typeHandlers

mybatis-config conversion configuration of hump and underscore. This is also a common configuration, which can be configured as follows in 061115110beae4

<setting name="mapUnderscoreToCamelCase" value="true"/>

The next question is, camel case and underscore can be converted to each other, so is there a way to implement a custom name mapping? If you have any friends, please feel free to advise

III. Source code and related knowledge points not to be missed

0. Project

Engineering: https://github.com/liuyueyi/spring-boot-demo
Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/104-mybatis-ano
Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/103-mybatis-xml

mybatis series of blog posts

1. A Grey Blog

It is not as good as the letter. The above content is purely a family statement. Due to limited personal ability, it is inevitable that there will be omissions and errors. If you find a bug or have a better suggestion, you are welcome to criticize and correct, and I am grateful

The following is a gray personal blog, which records all the blog posts in study and work. Welcome everyone to visit

Yi Hui Hui Blog Personal Blog https://blog.hhui.top
Blog-Spring Special Blog 161115110bee0c http://spring.hhui.top

一灰灰blog

Custom type conversion TypeHandler of SpringBoot series Mybatis

I. Environmental preparation

1. Database Preparation

2. Project environment

II. Demonstration

1. Entity definition

2. Mapper test interface

3. Type conversion

4. TypeHandler registration

4.1 Specify in the result tag

4.2 SqlSessionFactory global configuration

4.3 Global xml configuration

4.4 SpringBoot configuration method

5. Summary

III. Source code and related knowledge points not to be missed

0. Project

1. A Grey Blog

小灰灰Blog

引用和评论

SpringBoot系列之数据库初始化-datasource配置方式

状态码 406， Not Acceptable, (Spring Boot)

MySQL 亿级数据平滑迁移实战

AES 加密和解密demo

Thymeleaf的使用

数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI