ZonedDateTimeTypeHandler assumes that the jdbc driver knows how to handle ZonedDateTime

[uklance]

The org.apache.ibatis.type.ZonedDateTimeTypeHandler assumes that the jdbc driver can handle setObject(ZonedDateTime) and getObject(x, ZonedDateTime.class)

For hsqldb this causes the following exception:

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

I had to override the default ZonedDateTimeTypeHandler with my own implementation that uses setTimestamp(...) and getTimestamp(...)

@AllArgsConstructor
public class ZonedDateTimeTypeHandler extends BaseTypeHandler<ZonedDateTime> {
    private final ZoneId zoneId;

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, ZonedDateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, Timestamp.from(parameter.toInstant()));
    }

    @Override
    public ZonedDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Timestamp ts = rs.getTimestamp(columnName);
        return ts == null ? null : ts.toInstant().atZone(zoneId);
    }

    @Override
    public ZonedDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Timestamp ts = rs.getTimestamp(columnIndex);
        return ts == null ? null : ts.toInstant().atZone(zoneId);
    }

    @Override
    public ZonedDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Timestamp ts = cs.getTimestamp(columnIndex);
        return ts == null ? null : ts.toInstant().atZone(zoneId);
    }
}

Please fix mybatis so that it uses setTimestamp(...) and getTimestamp(...) so that it can support all jdbc drivers rather than requiring the driver to support java.time.ZonedDateTime

[uklance]

Stack overflow Q&A here: https://stackoverflow.com/questions/72743313/mybatis-not-handling-zoneddatetime

[harawata]

Hello @uklance ,

Your type handler may work fine with many solutions, but it could return unexpected result depending on the default time zone of the client environment.
As HSQLDB engine does not support ZonedDateTime, it probably makes more sense to use OffsetDateTime, but if you have to use ZonedDateTime, writing a custom type handler is the right solution.

Related to #1081 #1438 #1473 #1526