Sqlite在Mybatis Plus中关于时间字段的处理

我的个人项目中，使用Mybatis-Plus 和 Sqlite数据库， 但是在存储和查询时间字段的时候，总是出现问题，记录下我解决问题的过程。

Sqlite会默认把时间字段转成时间戳存储到数据库的字段中，看起来不直观，所以我采用的是字符串的方式来存储时间字段。

表对象定义

对象中有 create_time，update_time ，expire 三个时间相关的字段。

对象定义：

项目启动的初始化脚本报错

数据库

insert into main.u_invite (id, sender, email, code, expire, status, create_time, update_time) 
values (1, 1, 'v.ccgux@yhtj.no', 'L9RD37', '2024-05-18 17:38:50', 2, '2024-05-11 17:38:32', '2024-05-11 17:46:26');

启动报错如下

Caused by: java.sql.SQLException: Error parsing time stamp
	at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:532)
	at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:585)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getTimestamp(HikariProxyResultSet.java)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:38)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:28)
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85)
	...... 154 more
Caused by: java.text.ParseException: Unparseable date: "2024-02-24 00:00:00" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)
	at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
	at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
	at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:529)
	... 79 more

从报错信息可以看出是时间字段存入数据库的时候，无法识别格式报错。

解决方式

方式一： 在jdbcurl后面追加 date_string_format 参数， 不推荐, 有其他问题。

注意： 虽然可以顺利解决插入sql中字符串时间的插入问题，但是有两个问题：

• 此方法只解决了插入sql中时间字段的识别问题，查询的时候还是无法识别和解析
• 使用Mybatis Plus的save方法保存到数据库中的时间字段还是时间戳

方式二： 定义LocalDateTimeTypeHandler ， 在对象的时间字段上添加注解 @TableField(typeHandler = LocalDateTimeTypeHandler::class)

注意： 可以解决问题，但是需要在每个时间字段都加注解，比较繁琐， 不推荐。

对象中加上注解如下：

增加的LocalDateTimeTypeHandler 类如下：

/**
 *
 * @author xiezc
 * @date 2024/5/11 15:51
 */
@MappedTypes(LocalDateTime::class)
@MappedJdbcTypes(JdbcType.VARCHAR)
class LocalDateTimeTypeHandler : BaseTypeHandler<LocalDateTime>() {

    override fun setNonNullParameter(ps: PreparedStatement, i: Int, parameter: LocalDateTime, jdbcType: JdbcType?) {
        val value = parameter.format(LocalDateTimeFormatter)
        ps.setString(i, value)
    }

    override fun getNullableResult(rs: ResultSet, columnName: String): LocalDateTime? {
        val timestamp = rs.getString(columnName)
        return getLocalDateTime(timestamp)
    }

    override fun getNullableResult(rs: ResultSet, columnIndex: Int): LocalDateTime? {
        val timestamp = rs.getString(columnIndex)
        return getLocalDateTime(timestamp)
    }

    override fun getNullableResult(cs: CallableStatement, columnIndex: Int): LocalDateTime? {
        val timestamp = cs.getString(columnIndex)
        return getLocalDateTime(timestamp)
    }

    private fun getLocalDateTime(timestamp: String?): LocalDateTime? {
        if (timestamp != null) {
            return LocalDateTime.parse(timestamp, LocalDateTimeFormatter)
        }
        return null
    }
}

方式三： 注册全局的TypeHandler类。

注册全局TypeHandler类没有问题， 但是LocalDateTimeTypeHandler 这个类，在Mybatis的原生包中已经存在了， 使用 mybatis-plus.type-handlers-package 这个配置来注册全局TypeHandler不会覆盖Mybatis的原生包中的TypeHandler。

下面截图中的方式只能注册 Mybatis中没有的TypeHandler

但是有其他的办法，就是创建同名类来覆盖引入包中的类

• 通过查找发现Mybatis中的LocalDateTimeTypeHandler存在org.apache.ibatis.type包下面， 我可以在自己项目中创建个同名包，并在包下创建同名类，覆盖它，问题完美解决。

总结

Sqlite数据库有自己单独的字段类型处理方式， Mybatis对其支持并不完善， 从发现问题到解决问题我中间尝试了各种方法， 最终还是通过覆盖Mybatis的类来解决问题了。

Sqlite对于时间自动转换成时间戳的方式也不太好，我改成了字符串的方式，查看和显示比较直观。