浅谈Mybatis+mysql 存储Date类型的坑

下面是详细讲解 “浅谈Mybatis+mysql 存储Date类型的坑”的完整攻略。

问题描述

在使用 Mybatis + mysql 存储 Date 类型的数据时，我们可能会遇到以下两个问题：

1. java.util.Date 类型无法直接存储到 mysql 数据库中；
2. 存储后读取出来的 Date 类型的数据丢失了时区信息。

接下来我们将着重分析这两个问题并提供解决方案。

问题一：java.util.Date 无法直接存储到 mysql 数据库中

若我们直接使用 Mybatis + mysql 存储 java.util.Date 类型的数据，往往会发生以下错误：

Cause: java.sql.SQLException: Value '2021-05-04 13:20:14.000000' can not be represented as java.sql.Timestamp

这是因为在 mysql 中，timestamp 类型支持的最大范围只到 2038 年，而 java.util.Date 类型支持的范围则远远超出了这个范围，因此我们需要将 Date 类型转换为 mysql 支持的类型后再进行存储。

解决方案是通过 MySQL 中的 TIMESTAMP 表示，将 java.util.Date 转换成 java.sql.Timestamp 再插入数据库。Mybatis 会自动将 java.sql.Timestamp 类型转换成 MySQL 的 timestamp 类型进行存储。

具体实现方法如下：

在 Mapper 接口中定义 insert 方法：

public interface UserMapper {
    void insert(User user);
}

在对应的 UserMapper.xml 文件中，写入 insert 的 SQL 语句：

<insert id="insert" parameterType="com.example.demo.entity.User">
    insert into user(id, name, birth)
    values (#{id}, #{name}, #{birth, jdbcType=TIMESTAMP})
</insert>

其中 jdbcType=TIMESTAMP 表示将 Java 对象的类型转换成 jdbc 中的数据类型。

在实体类 User 中将出生日期字段 birth 指定为 java.sql.Timestamp 类型即可：

public class User {
    private Integer id;
    private String name;
    private Timestamp birth;

    // getter & setter
}

这样，我们就成功地解决了将 Java 中的 Date 类型存储到 mysql 数据库中的问题。

问题二：Date 类型的数据丢失时区信息

在存储 Date 类型的数据到 mysql 数据库中时，我们可能会发现 Date 类型的数据丢失了时区信息。举个例子，如果在东八区本地时间为 2021 年 5 月 4 日 20 时 28 分时，插入数据库时得到的时间会变成 2021 年 5 月 4 日 12 时 28 分。这是因为 mysql 数据库存储的是 UTC 时间，而不是本地时间。

解决方法是将 Java 日期类型转换成 UTC 格式的字符串存储到数据库中，在读取数据时再将字符串转换成 Java 日期类型。具体实现方法如下：

在 Mapper 接口中定义 insert 和 selectById 两个方法：

public interface UserMapper {
    void insert(User user);

    @Select("select * from user where id = #{id}")
    @Results({
            @Result(property = "birth", column = "birth", jdbcType = JdbcType.VARCHAR, typeHandler = LocalDateStringTypeHandler.class)
    })
    User selectById(Integer id);
}

在对应的 UserMapper.xml 文件中，写入 insert 和 selectById 的 SQL 语句：

<insert id="insert" parameterType="com.example.demo.entity.User">
    insert into user(id, name, birth)
    values (#{id}, #{name}, #{birth, jdbcType=VARCHAR})
</insert>
<select id="selectById" parameterType="java.lang.Integer" resultType="com.example.demo.entity.User">
    select * from user where id = #{id}
</select>

其中，selectById 中的 typeHandler = LocalDateStringTypeHandler.class 表示读取数据时使用自定义类型处理器 LocalDateStringTypeHandler 对 VARCHAR 类型的数据进行转换。

创建一个 LocalDateStringTypeHandler 类，实现将 Java 日期类型转换成 UTC 格式的字符串，和将 UTC 格式的字符串转换成 Java 日期类型的方法：

public class LocalDateStringTypeHandler extends BaseTypeHandler<Date> {

    private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        // 将日期类型转换成 UTC 格式的字符串存储到数据库中
        ps.setString(i, dateFormat.format(parameter));
    }

    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        // 将 UTC 格式的字符串转换成本地日期类型
        return rs.getTimestamp(columnName);
    }

    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getTimestamp(columnIndex);
    }

    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getTimestamp(columnIndex);
    }
}

这样，我们的问题就完美地解决了。

示例说明

这里给出两个示例，用于说明上述两个问题的详细解决方案。

示例一：Java 代码存储 Date 类型到 MySQL

假设我们有一个 User 实体类，其中有一个生日字段 birth：

public class User {
    private Integer id;
    private String name;
    private Date birth;

    // getter & setter
}

我们需要将生日字段存储到 mysql 数据库中。首先，在 UserMapper 接口中定义 insert 方法：

public interface UserMapper {
    void insert(User user);
}

然后，在 UserMapper.xml 文件中，写入 insert 的 SQL 语句：

<insert id="insert" parameterType="com.example.demo.entity.User">
    insert into user(id, name, birth)
    values (#{id}, #{name}, #{birth, jdbcType=TIMESTAMP})
</insert>

其中 jdbcType=TIMESTAMP 表示将 Java 对象的类型转换成 JDBC 中的数据类型。

接下来，我们需要创建一个 mybatis-config.xml 文件，用于配置 Mybatis 的全局配置项。在 mybatis-config.xml 中加入如下内容：

<configuration>
    <typeHandlers>
        <typeHandler handler="com.example.demo.typehandler.LocalDateTimeTypeHandler" javaType="java.util.Date"/>
    </typeHandlers>
</configuration>

其中，com.example.demo.typehandler.LocalDateTimeTypeHandler 是我们自定义的类型处理器。

最后，我们需要在 UserMapper.xml 中配置插入生日字段的 TypeHandler：

<insert id="insert" parameterType="com.example.demo.entity.User">
    insert into user(id, name, birth)
    values (#{id}, #{name}, #{birth, typeHandler=com.example.demo.typehandler.LocalDateTimeTypeHandler})
</insert>

这样，我们就成功地将 Date 类型的数据存储到 mysql 数据库中了。

示例二：Java 代码读取 MySQL 中的 Date 类型数据

在读取 mysql 数据库中的 Date 类型数据时，我们需要将 UTC 格式的时间转换成本地时间。下面是示例代码：

public class UserService {

    @Autowired
    private UserMapper userMapper;

    public User selectById(Integer id) {
        User user = userMapper.selectById(id);
        user.setBirth(convertToLocalTime(user.getBirth()));
        return user;
    }

    private Date convertToLocalTime(Date utcTime) {
        TimeZone tz = TimeZone.getTimeZone("Asia/Shanghai");
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        df.setTimeZone(tz);
        String localTime = df.format(utcTime);
        try {
            return df.parse(localTime);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }
}

其中，convertToLocalTime 方法用于将传入的时间转换成本地时间。我们通过 java.util.TimeZone 类获取到中国时区，并通过 java.text.SimpleDateFormat 类将 UTC 格式的 Date 类型转换成本地时间类型。