Mybstis -- TypeHandler

前言

项目中原有的多时区解决方案是统一时区为UTC,数据库&应用设置时区为UTC,这种方式限制了平台使用环境,在扩展性上也有局限性。

优化方案:数据库存储层面将日期的存储类型转化为unsigned bigint,数据库&应用不再做UTC时区的设置,采用区域默认时区或维护的时区,不做特殊要求。

问题

实践中发现,

1、java Date不能直接转为unsigned bigint存储;

2、db的timestamp存入unsigne bigint字段,并不是真实时间戳,而是yyyyMMddHHmmssmsms格式。

解决方案

为了尽量减少代码改动,自定义TypeHandler,实现Java Date转Long类型时间戳,然后存入db。

sqlMap中jdbcType=timestamp,同步改为jdbcType=bigint

Mybatis TypeHandler

无论是MyBatis在预处理语句(PreparedStatement)中设置一个参数时,还是从结果集中取出一个值时,都会用类型处理器将获取的值以合适的方式转换成Java类型。Mybatis默认为我们实现了许多TypeHandler,当没有配置指定TypeHandler时,Mybatis会根据参数或返回结果的不同,默认选择合适的TypeHandler处理。

那么,Mybatis实现了哪些TypeHandler呢?如何自定义实现一个TypeHandler呢?

XMLConfigBuilder

XMLConfigBuilder是对mybatis的配置文件进行解析的类。

typeHandlerElement方法作用就是进行typeHandlers节点解析。

private void typeHandlerElement(XNode parent) throws Exception {
   if (parent != null) {
     for (XNode child : parent.getChildren()) {

             //子节点为package时,获取其name属性的值,然后自动扫描package下自定义的typeHandler

       if ("package".equals(child.getName())) {
         String typeHandlerPackage = child.getStringAttribute("name");
         typeHandlerRegistry.register(typeHandlerPackage);
       } else {

       //子节点为typeHandler时,可以指定javaType属性,也可以执行jdbcType,也可以两者都指定
       //javaType是指定java类型
       //jdbcType是指定jdbc类型(如:timestamp)

         String javaTypeName = child.getStringAttribute("javaType");
         String jdbcTypeName = child.getStringAttribute("jdbcType");

         //handle就是自定义配置的typeHandler

         String handlerTypeName = child.getStringAttribute("handler");
         Class<?> javaTypeClass = resolveClass(javaTypeName);
         JdbcType jdbcType = resolveJdbcType(jdbcTypeName);
         Class<?> typeHandlerClass = resolveClass(handlerTypeName);
         if (javaTypeClass != null) {
           if (jdbcType == null) {

           //注册typeHandler,typeHandler通过TypeHandlerRegistry类管理

             typeHandlerRegistry.register(javaTypeClass, typeHandlerClass);
           } else {
             typeHandlerRegistry.register(javaTypeClass, jdbcType, typeHandlerClass);
           }
         } else {
           typeHandlerRegistry.register(typeHandlerClass);
         }
       }
     }
   }
 }

TypeHandler管理注册类:TypeHandlerRegistry

/**
 * @author Clinton Begin
 * @author Kazuki Shimizu
 */
public final class TypeHandlerRegistry {

 ...

  public TypeHandlerRegistry() {
    register(Boolean.class, new BooleanTypeHandler());
    register(boolean.class, new BooleanTypeHandler());
    register(JdbcType.BOOLEAN, new BooleanTypeHandler());
    register(JdbcType.BIT, new BooleanTypeHandler());

    register(Byte.class, new ByteTypeHandler());
    register(byte.class, new ByteTypeHandler());
    register(JdbcType.TINYINT, new ByteTypeHandler());

    register(Short.class, new ShortTypeHandler());
    register(short.class, new ShortTypeHandler());
    register(JdbcType.SMALLINT, new ShortTypeHandler());

    register(Integer.class, new IntegerTypeHandler());
    register(int.class, new IntegerTypeHandler());
    register(JdbcType.INTEGER, new IntegerTypeHandler());

    register(Long.class, new LongTypeHandler());
    register(long.class, new LongTypeHandler());

    register(Float.class, new FloatTypeHandler());
    register(float.class, new FloatTypeHandler());
    register(JdbcType.FLOAT, new FloatTypeHandler());

    register(Double.class, new DoubleTypeHandler());
    register(double.class, new DoubleTypeHandler());
    register(JdbcType.DOUBLE, new DoubleTypeHandler());

    register(Reader.class, new ClobReaderTypeHandler());
    register(String.class, new StringTypeHandler());
    register(String.class, JdbcType.CHAR, new StringTypeHandler());
    register(String.class, JdbcType.CLOB, new ClobTypeHandler());
    register(String.class, JdbcType.VARCHAR, new StringTypeHandler());
    register(String.class, JdbcType.LONGVARCHAR, new ClobTypeHandler());
    register(String.class, JdbcType.NVARCHAR, new NStringTypeHandler());
    register(String.class, JdbcType.NCHAR, new NStringTypeHandler());
    register(String.class, JdbcType.NCLOB, new NClobTypeHandler());
    register(JdbcType.CHAR, new StringTypeHandler());
    register(JdbcType.VARCHAR, new StringTypeHandler());
    register(JdbcType.CLOB, new ClobTypeHandler());
    register(JdbcType.LONGVARCHAR, new ClobTypeHandler());
    register(JdbcType.NVARCHAR, new NStringTypeHandler());
    register(JdbcType.NCHAR, new NStringTypeHandler());
    register(JdbcType.NCLOB, new NClobTypeHandler());

    register(Object.class, JdbcType.ARRAY, new ArrayTypeHandler());
    register(JdbcType.ARRAY, new ArrayTypeHandler());

    register(BigInteger.class, new BigIntegerTypeHandler());
    register(JdbcType.BIGINT, new LongTypeHandler());

    register(BigDecimal.class, new BigDecimalTypeHandler());
    register(JdbcType.REAL, new BigDecimalTypeHandler());
    register(JdbcType.DECIMAL, new BigDecimalTypeHandler());
    register(JdbcType.NUMERIC, new BigDecimalTypeHandler());

    register(InputStream.class, new BlobInputStreamTypeHandler());
    register(Byte[].class, new ByteObjectArrayTypeHandler());
    register(Byte[].class, JdbcType.BLOB, new BlobByteObjectArrayTypeHandler());
    register(Byte[].class, JdbcType.LONGVARBINARY, new BlobByteObjectArrayTypeHandler());
    register(byte[].class, new ByteArrayTypeHandler());
    register(byte[].class, JdbcType.BLOB, new BlobTypeHandler());
    register(byte[].class, JdbcType.LONGVARBINARY, new BlobTypeHandler());
    register(JdbcType.LONGVARBINARY, new BlobTypeHandler());
    register(JdbcType.BLOB, new BlobTypeHandler());

    register(Object.class, UNKNOWN_TYPE_HANDLER);
    register(Object.class, JdbcType.OTHER, UNKNOWN_TYPE_HANDLER);
    register(JdbcType.OTHER, UNKNOWN_TYPE_HANDLER);

    register(Date.class, new DateTypeHandler());
    register(Date.class, JdbcType.DATE, new DateOnlyTypeHandler());
    register(Date.class, JdbcType.TIME, new TimeOnlyTypeHandler());
    register(JdbcType.TIMESTAMP, new DateTypeHandler());
    register(JdbcType.DATE, new DateOnlyTypeHandler());
    register(JdbcType.TIME, new TimeOnlyTypeHandler());

    register(java.sql.Date.class, new SqlDateTypeHandler());
    register(java.sql.Time.class, new SqlTimeTypeHandler());
    register(java.sql.Timestamp.class, new SqlTimestampTypeHandler());

    // mybatis-typehandlers-jsr310
    try {
      // since 1.0.0
      register("java.time.Instant", "org.apache.ibatis.type.InstantTypeHandler");
      register("java.time.LocalDateTime", "org.apache.ibatis.type.LocalDateTimeTypeHandler");
      register("java.time.LocalDate", "org.apache.ibatis.type.LocalDateTypeHandler");
      register("java.time.LocalTime", "org.apache.ibatis.type.LocalTimeTypeHandler");
      register("java.time.OffsetDateTime", "org.apache.ibatis.type.OffsetDateTimeTypeHandler");
      register("java.time.OffsetTime", "org.apache.ibatis.type.OffsetTimeTypeHandler");
      register("java.time.ZonedDateTime", "org.apache.ibatis.type.ZonedDateTimeTypeHandler");
      // since 1.0.1
      register("java.time.Month", "org.apache.ibatis.type.MonthTypeHandler");
      register("java.time.Year", "org.apache.ibatis.type.YearTypeHandler");
      // since 1.0.2
      register("java.time.YearMonth", "org.apache.ibatis.type.YearMonthTypeHandler");
      register("java.time.chrono.JapaneseDate", "org.apache.ibatis.type.JapaneseDateTypeHandler");

    } catch (ClassNotFoundException e) {
      // no JSR-310 handlers
    }

    // issue #273
    register(Character.class, new CharacterTypeHandler());
    register(char.class, new CharacterTypeHandler());
  }

...      
}

查看TypeHandlerRegistry类,可知mybatis实现了哪些TypeHandler。

DateTypeHandler

我们需求是java Date类型转为Long。

查看DateTypeHandler 是否满足。

/**
 * @author Clinton Begin
 */
public class DateTypeHandler extends BaseTypeHandler<Date> {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setTimestamp(i, new Timestamp((parameter).getTime()));
  }

  @Override
  public Date getNullableResult(ResultSet rs, String columnName)
      throws SQLException {
    Timestamp sqlTimestamp = rs.getTimestamp(columnName);
    if (sqlTimestamp != null) {
      return new Date(sqlTimestamp.getTime());
    }
    return null;
  }

  @Override
  public Date getNullableResult(ResultSet rs, int columnIndex)
      throws SQLException {
    Timestamp sqlTimestamp = rs.getTimestamp(columnIndex);
    if (sqlTimestamp != null) {
      return new Date(sqlTimestamp.getTime());
    }
    return null;
  }

  @Override
  public Date getNullableResult(CallableStatement cs, int columnIndex)
      throws SQLException {
    Timestamp sqlTimestamp = cs.getTimestamp(columnIndex);
    if (sqlTimestamp != null) {
      return new Date(sqlTimestamp.getTime());
    }
    return null;
  }
}

myabtis实现的DateType是将Java Date转为sql timestamp。虽然不满足我们的需求。

但可以照葫芦画瓢,我们自定义实现一个DateTypeHandler,将Java Date 转为Long。

package com.springboot.demo.typehandler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

/**
 * @author : zyh
 * @Description : java Date和Long互转
 * @date : 2019-05-26 20:49
 */
@MappedJdbcTypes(JdbcType.BIGINT)
@MappedTypes({java.util.Date.class})
public class DateTypeHandler extends BaseTypeHandler<Date> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        //将Java Date转为Long时间戳
        ps.setLong(i, parameter.getTime());
    }

    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String timestamp = rs.getString(columnName);
        //将db中存储的时间戳转为java Date
        if (timestamp != null) {
            return new Date(Long.valueOf(timestamp));
        }
        return null;
    }

    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String timestamp = rs.getString(columnIndex);
        if (timestamp != null) {
            return new Date(Long.valueOf(timestamp));
        }
        return null;
    }

    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String timestamp = cs.getString(columnIndex);
        if (timestamp != null) {
            return new Date(Long.valueOf(timestamp));
        }
        return null;
    }
}

mybatis-config.xml

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--useGeneratedKeys参数只针对insert语句生效,默认为false,当设置为true时,表示如果插入的表以自增列为主键,
    则允许JDBC支持自动生成主键,并可将自动生成的主键返回,-->
    <settings>
        <setting name="useGeneratedKeys" value="true"/>
    </settings>

    <typeAliases>
        <package name="com.xx.xx" />
    </typeAliases>

    <typeHandlers>
        <typeHandler handler="com.xx.xx.typeHandler.DateTypeHandler" />
    </typeHandlers>

</configuration>

经测试,java Date在db存入timstamp。

Donate here