SpringBoot Mybatis custom TypeHandler

When using MyBatis for DB operation, one thing we often do is to map db fields to Java beans, usually we use ResultMap to achieve the mapping, through this label can specify the binding relationship between the two. So what if the field type in the Java Bean is different from that in the DB?

For example, timestamp is defined in db, but long is defined in Java bean

  • throughBaseTypeHandlerTo implement custom type conversions

I. Environment preparation

1. Prepare the database

Using mysql as the instance database for this article, add a new table

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT 'money',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'.PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Copy the code

2. Project environment

This article is developed with SpringBoot 2.2.1.RELEASE + Maven 3.5.3 + IDEA

Pom dependencies are as follows

<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>
Copy the code

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:
Copy the code

II. Example demonstration

1. The entity definition

Notice that the create_AT and update_AT types in the above case are timestmap, and we define the Entity as follows

@Data
public class MoneyPo {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Long updateAt;
}
Copy the code

2. Mapper test interface

Define a simple query interface that uses annotations directly (there is little difference in how XML is written)

/** * primary key query **@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);

// More on the use of SelectProvider later, mainly a demonstration of dynamic SQL
@SelectProvider(type = MoneyService.class, method = "getByIdSql")
@ResultMap(value = "moneyResultMap")
MoneyPo getByIdForProvider(@Param("id") int id);
Copy the code

Description:

  • @Results: This annotation works as a ResultMap tag and is used to define the mapping between db fields and Java Beans
  • id = "moneyResultMap"This id definition, this id definition, allows you to reuse at sign Results
  • @ResultUnder:updateAtTypeHandler, which specifies a custom typeHandler, to implementJdbcType.TEMSTAMPConversion to long in Java Beans

3. Type conversion

Custom type conversions mainly inherit from the BaseTypeHandler class. Generic types are types in Java beans

/** * Custom type conversion: convert the date type in the database to a timestamp of type long ** three registration methods: * 1. Specify typeHandler directly in the Result tag, as in@Result(property = "updateAt", column = "update_at", jdbcType = JdbcType.TIMESTAMP, typeHandler = Timestamp2LongHandler.class) * 2. SetTypeHandlers (new Timestamp2LongHandler()); * 3. The XML configuration, < typeHandler handler = "com. Git. Hui. Boot. Mybatis. Handler. Timestamp2LongHandler" / > * *@author yihui
 * @date2021/7/7 * /
@MappedTypes(value = Long.class)
@MappedJdbcTypes(value = {JdbcType.DATE, JdbcType.TIME, JdbcType.TIMESTAMP})
public class Timestamp2LongHandler extends BaseTypeHandler<Long> {

    /** * Convert Java type to JDBC type **@param preparedStatement
     * @param i
     * @paramALong millisecond timestamp *@paramJdbcType Indicates the db field type *@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, newTimestamp(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; }}Copy the code
  • SetNonNullParameter: Converts Java type to JDBC type
  • GetNullableResult: Converts the JDBC type to the Java type

4. TypeHandler registration

There’s nothing wrong with defining a TypeHandler ourselves. The next step is to make it work. In general, there are several ways

4.1 Specified in the result tag

Specified by typeHandler in the Result tag

The way to use XML is as follows

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

Annotation @result in the following way

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

4.2 SqlSessionFactory Global configuration

The usage posture above is precisely specified, and if we want to apply it to all scenarios, we can do it through SqlSessionFactory

@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(
            // Set the XML location of Mybatis
            new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/*.xml"));
    // Register TypeHandler for global use
    bean.setTypeHandlers(new Timestamp2LongHandler());
    return bean.getObject();
}
Copy the code

4.3 Global XML Configuration

In addition to the above case, there is also a mybatis-config. XML configuration file to register, such as


      
<! DOCTYPEconfiguration
        PUBLIC "- / / ibatis.apache.org//DTD Config / 3.1 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <! -- Hump underline format support -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

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

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

mybatis:
  config-location: classpath:mybatis-config.xml
Copy the code

4.4 SpringBoot Configuration Mode

A Springboot configuration file that registers TypeHandler by specifying a type-handlers-package

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

5. Summary

This article mainly introduces the type in DB and the type in Java bean mapping adaptation strategy, mainly by inheriting BaseTypeHandler to achieve custom type conversion

There are two ways to use a custom TypeHandler: globally valid or precisely specified

  • @Result/<result>Tag, specified by typeHandler
  • SqlSessionFactory Sets typeHandler globally
  • mybatis-config.xmlConfiguration file SettingstypeHandlers

In addition, the configuration of this article also supports the interconversion configuration of hump and underscore. This is also a common configuration, which can be configured in mybatis-config as follows

<setting name="mapUnderscoreToCamelCase" value="true"/>
Copy the code

Hump and underline can rotate each other, so is there any way to implement custom name mapping? If you know, please feel free to advise

III. Can’t miss the source code and related knowledge points

0. Project

  • Project: github.com/liuyueyi/sp…
  • Source: github.com/liuyueyi/sp…
  • Source: github.com/liuyueyi/sp…

Mybatis series blog post

  • 【DB series 】SpringBoot series Mybatis Mapper interface and Sql binding several posture
  • 【DB series 】SpringBoot series Mybatis Mapper registration of several ways
  • 【DB series 】Mybatis-Plus multi-data source configuration
  • Mybatis based on DB series 】 【 AbstractRoutingDataSource multiple source switch with AOP implementation
  • 【DB series 】Mybatis multi-data source configuration and use
  • 【DB series 】 Multi-data source configuration and use of JdbcTemplate
  • 【DB series 】Mybatis-Plus code automatic generation
  • 【DB series 】MybatisPlus Integration
  • 【DB series 】Mybatis+ annotations integration
  • 【DB series 】Mybatis+ XML integration

1. Wechat official account: Yash Blog

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll

  • A grey Blog Personal Blog blog.hhui.top
  • A Grey Blog-Spring feature Blog Spring.hhui.top