不处理ZonedDateTime

Question

我在DATE类型的数据库表中有列，我希望mybatis将其转换为java.time.ZonedDateTime，但似乎内置的ZonedDateTimeHandler不起作用。我得到以下异常

Caused by: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'UPDATED_DATE' from result set.  Cause: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:87)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createUsingConstructor(DefaultResultSetHandler.java:711)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createByConstructorSignature(DefaultResultSetHandler.java:694)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:658)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:631)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:398)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:355)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:329)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:302)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:195)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
    ... 36 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getObject(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getObject(Unknown Source)
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java)
    at org.apache.ibatis.type.ZonedDateTimeTypeHandler.getNullableResult(ZonedDateTimeTypeHandler.java:38)
    at org.apache.ibatis.type.ZonedDateTimeTypeHandler.getNullableResult(ZonedDateTimeTypeHandler.java:28)
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85)
    ... 53 common frames omitted
Caused by: org.hsqldb.HsqlException: incompatible data type in conversion
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    ... 60 common frames omitted

Answer 1

HSQL不支持ZonedDateTime，但它支持TIMESTAMP WITH TIME ZONE类型的列(参见HSQL日期时间类型)。HSQL抛出了内置OffsetDateTimeTypeHandler的异常，因此我不得不编写一个自定义TypeHandler。

public class HsqlOffsetDateTimeTypeHandler extends BaseTypeHandler<OffsetDateTime> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, OffsetDateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setObject(i, parameter);
    }

    @Override
    public OffsetDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return (OffsetDateTime) rs.getObject(columnName);
    }

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

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

我是通过

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) {
        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        Environment environment = new Environment("default", transactionFactory, dataSource);
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration(environment);
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        typeHandlerRegistry.register(OffsetDateTime.class, new HsqlOffsetDateTimeTypeHandler());
        configuration.addMapper(MyMapper.class);
        ...
        return new DefaultSqlSessionFactory(configuration);
    }           

我还必须将我的列声明为TIMESTAMP WITH TIME ZONE而不是DATE。

CREATE TABLE MY_TABLE (
    PK            NUMBER(12),
    NAME          VARCHAR2(255),
    CREATED_DATE  TIMESTAMP WITH TIME ZONE NOT NULL,
    UPDATED_DATE  TIMESTAMP WITH TIME ZONE,
    DELETED_DATE  TIMESTAMP WITH TIME ZONE,
);

在注册自定义TypeHandler之前，我得到了以下异常

Caused by: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'UPDATED_DATE' from result set.  Cause: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:87)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createParameterizedResultObject(DefaultResultSetHandler.java:678)
    ... 78 more
Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getObject(Unknown Source)
    at org.hsqldb.jdbc.JDBCResultSet.getObject(Unknown Source)
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java)
    at org.apache.ibatis.type.OffsetDateTimeTypeHandler.getNullableResult(OffsetDateTimeTypeHandler.java:38)
    at org.apache.ibatis.type.OffsetDateTimeTypeHandler.getNullableResult(OffsetDateTimeTypeHandler.java:28)
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85)
    ... 79 more
Caused by: org.hsqldb.HsqlException: incompatible data type in conversion
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    ... 86 more