MyBatis的TypeHandler使用之 Java时间戳与PHP时间戳互转

问题

最近在学习MyBatis，使用的是Mapper方式查询数据库，查库的时候一直报错

Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'created' from result set.  Cause: java.lang.IllegalArgumentException: YEAR

意思很明现，就是数据中缺少年份，我想不应该的，数据都是原来用PHP框架写进去的。
Google了一下才发现原来Java的时间戳是毫秒的，比PHP的秒级时间戳多三位，那好说，查出来以后给数据补3个0就能转成Date类型了。

解决方法

我又刚好学到TypeHandler，就使用TypeHandler来实现时间戳的转换。
新建一个DateTypeHandler，要继承BaseTypeHandler<Date>

package ru.doufu.handler;

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

import java.sql.*;

public class DateTypeHandler extends BaseTypeHandler<Date> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        int time = (int)(date.getTime() / 1000);
        preparedStatement.setInt(i, time);
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        Long value = resultSet.getLong(s) * 1000;
        Date date = new Date(value);
        return date;
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        Long value = resultSet.getLong(i) * 1000;
        Date date = new Date(value);
        return date;
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        Long value = callableStatement.getLong(i)  * 1000;
        Date date = new Date(value);
        return date;
    }
}

然后去注册TypeHandler，不注册是不会调用的。修改MyBatis的核心配置

<typeHandlers>
    <typeHandler handler="ru.doufu.handler.DateTypeHandler"></typeHandler>
</typeHandlers>

结果提交还是报错Cause: java.lang.IllegalArgumentException: YEAR
看起来Handler没生效，那我换成插入操作试一下，还是报错 Cause: java.sql.SQLException: Data truncated for column 'created' at row 1，难道还是没有调用，我修改一下Mapper文件，强制指定Handler

    <insert id="insert" parameterType="User">
        insert into cr_users(`name`, `password`, `mail`, `created`) values(#{name}, #{password}, #{mail}, #{created, typeHandler=ru.doufu.handler.DateTypeHandler})
    </insert>

这回报错很明现了Cause: java.lang.ClassCastException: java.util.Date cannot be cast to java.sql.Date，靠我Alt+Enter导包选错了，把Handler的源码修改一下。

package ru.doufu.handler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.util.Date;
import java.sql.*;

public class DateTypeHandler extends BaseTypeHandler<Date> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        int time = (int)(date.getTime() / 1000);
        preparedStatement.setInt(i, time);
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
        Long value = resultSet.getLong(s) * 1000;
        Date date = new Date(value);
        return date;
    }

    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        Long value = resultSet.getLong(i) * 1000;
        Date date = new Date(value);
        return date;
    }

    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        Long value = callableStatement.getLong(i)  * 1000;
        Date date = new Date(value);
        return date;
    }
}

成功插入

DEBUG [main] (PooledDataSource.java:434) - Created connection 391877669.
DEBUG [main] (JdbcTransaction.java:101) - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@175b9425]
DEBUG [main] (BaseJdbcLogger.java:137) - ==>  Preparing: insert into cr_users(`name`, `password`, `mail`, `created`) values(?, ?, ?, ?)
DEBUG [main] (BaseJdbcLogger.java:137) - ==> Parameters: testu(String), 123(String), testu@localhost(String), 1611826969(Integer)
DEBUG [main] (BaseJdbcLogger.java:137) - <==    Updates: 1