mybatis不处理ZonedDateTime

Question

I have columns in a database table of type DATE which I'd like mybatis to convert to java.time.ZonedDateTime but it seems that the built in

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没有本机支持，但它确实支持offsetDateTime type timestamp with timestamp with time Zone>的列（请参阅 hsql dateTime类型）。 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，带有时区 而不是> 日期

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