前言
项目中原有的多时区解决方案是统一时区为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。