Mybatis中自定义TypeHandler处理Date类型转换时遇到的坑（Bad format for Timestamp）

最新推荐文章于 2024-07-26 18:41:12 发布

古狗

最新推荐文章于 2024-07-26 18:41:12 发布

阅读量7.5k

点赞数 4

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/gugou123/article/details/80920431

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

先交代下背景，User类中有个属性是birthday，类型是java.util.Date，我希望在mysql数据库中以bigint类型或varchar类型存储birthday.getTime()生成的毫秒数存储，读取的时候再转回Date类型，因此我自定义了一个TypeHandler来做类型转换，以下是配置内容：

实体类User.class

public class User {
    private String id;
    private String username;
    private String password;
    private Integer age;
    private Date birthday;

getter、setter、constructor省略

映射文件UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.guli.mapper.UserMapper">
    <resultMap id="userResultMap" type="com.guli.bean.User">
        <result typeHandler="com.guli.typeHandler.DateTypeHandler" column="birthday" property="birthday" javaType="java.util.Date" jdbcType="BIGINT"/>
    </resultMap>
    
    <select id="getAllUsers" resultType="com.guli.bean.User">
        select * from User
    </select>
    <insert id="addUser" parameterType="com.guli.bean.User">
        insert into user(username,password,age,birthday) values(#{username},#{password},#{age},#{birthday,typeHandler=com.guli.typeHandler.DateTypeHandler})
    </insert>

    <select id="getUserById" parameterType="java.lang.Long" resultType="com.guli.bean.User">
        select * from user where id = #{id}
    </select>
</mapper>

自定义的TypeHandler

package com.guli.typeHandler;

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

import java.sql.*;
import java.util.Date;

@MappedJdbcTypes(JdbcType.BIGINT)
@MappedTypes(Date.class)
public class DateTypeHandler implements TypeHandler<Date> {
    public void setParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        long time = parameter.getTime();
        ps.setLong(i, time);
    }

    public Date getResult(ResultSet rs, String columnName) throws SQLException {
        long time = rs.getLong("birthday");
        return new Date(time);
    }

    public Date getResult(ResultSet rs, int columnIndex) throws SQLException {
        long time = rs.getLong(columnIndex);
        return new Date(time);
    }

    public Date getResult(CallableStatement cs, int columnIndex) throws SQLException {
        long time = cs.getLong(columnIndex);
        return new Date(time);
    }
}

Mybatis配置文件

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//BN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"/>
    <typeHandlers>
        <typeHandler jdbcType="BIGINT" javaType="java.util.Date" handler="com.guli.typeHandler.DateTypeHandler"/>
    </typeHandlers>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${db.driver}"/>
                <property name="url" value="${db.url}"/>
                <property name="username" value="${db.username}"/>
                <property name="password" value="${db.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.guli.mapper"/>
    </mappers>
</configuration>

测试类

public class Main {
    public static void main(String[] args) {
        SqlSessionFactory sessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory();
        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//添加用户
//        User user = new User("古狗", "123123", 23, new Date());
//        userMapper.addUser(user);
//查询用户
        User user = userMapper.getUserById(7L);
        System.out.println(user);

        sqlSession.commit();
        sqlSession.close();
    }
}

查询时报错

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLException: Bad format for Timestamp '1530716421820' in column 5.
### The error may exist in com/guli/mapper/UserMapper.xml
### The error may involve com.guli.mapper.UserMapper.getUserById
### The error occurred while handling results
### SQL: select * from user where id = ?
### Cause: java.sql.SQLException: Bad format for Timestamp '1530716421820' in column 5.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:66)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
	at com.sun.proxy.$Proxy0.getUserById(Unknown Source)
	at com.guli.Main.main(Main.java:17)
Caused by: java.sql.SQLException: Bad format for Timestamp '1530716421820' in column 5.
	at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1378)
	at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:127)
	at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6587)
	at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6187)
	at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6225)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:67)
	at com.sun.proxy.$Proxy3.getTimestamp(Unknown Source)
	at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:39)
	at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:28)
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:55)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:416)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:339)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:294)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:269)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:239)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:153)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:60)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
	... 6 more

问题描述

我在测试类中对user分别进行了插入和查询的操作，其中查询的时候采用ResultType获取返回对象，结果是插入成功了，取出时报错了，从报错信息中可以看到“Bad format for Timestamp '1530716421820' in column 5”的错误提示，说明已经查到了对应的数据，但是在类型转换的时候出了错，还可以看到出错来源中包含了“org.apache.ibatis.type.DateTypeHandler”类，可以猜测mybatis根本没用我自己定义的TypeHandler，这就很奇怪了，我明明已经在配置文件中配置了TypeHandler。

网上查了一圈只在stackoverflow上看到一哥们儿和我有类似的问题，最终解决办法是通过在ResultMap中指定TypeHandler来接收结果，我试了下是ok的。

不过仍然疑惑为什么不能直接用ResultType接收，我之前测试List和vachar做转换用ResultType就是没问题的，但Date就不行。