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

<!-- more -->

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

• [DB series] SpringBoot series Mybatis Mapper interface binds several postures with Sql
• [DB series] SpringBoot series Mybatis Mapper registration methods
• [DB series] Mybatis-Plus multiple data source configuration
• [DB series] Mybatis realizes multi-data source switching based on AbstractRoutingDataSource and AOP
• [DB series] Mybatis multiple data source configuration and use
• [DB series] JdbcTemplate configuration and use of
• [DB series] Mybatis-Plus code is automatically generated
• [DB Series] MybatisPlus Integration Chapter
• [DB Series] Mybatis+ Annotation Integration Chapter
• [DB series] Mybatis+xml integration chapter

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