TypechoJoeTheme

虾米皮皮乐工作室

文章目录

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

2021-01-28
/
0 评论
/
5 阅读
/
正在检测是否收录...
01/28

问题

最近在学习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
Mybatis
朗读
赞 · 0
版权属于:

虾米皮皮乐工作室

评论 (0)