使用UTC偏移(+00:00)的OffsetDateTime时HSQLDB MERGE操作失败

使用HSQLDB的MERGE语句时，传入偏移为+00:00的OffsetDateTime会导致逻辑执行失败，但其他时区偏移（如+01:00）的情况下可以正常运行。经排查推测，问题根源在于UTC偏移的OffsetDateTime的toString()方法返回带Z后缀的字符串，而非+00:00格式；且单独使用INSERT语句时不存在该问题。

相关代码 #

创建表SQL #

private static final String CREATE_TABLE_SQL = 
        "create table sample (" + 
           "code varchar2(50), " + 
           "updated timestamp with time zone" + 
        ")";

MERGE语句及原执行方法 #

private static final String MERGE_SQL =
        "merge into sample t " +
        "using (select ? as code, ? as updated from dual) val " +
        "on (t.code = val.code) " +
        "when matched then update set t.updated = val.updated " +
        "when not matched then insert(code, updated) values (val.code, val.updated)";

private int merge(String code, OffsetDateTime updated) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement(MERGE_SQL)) {
        statement.setString(1, code);
        statement.setObject(2, updated, Types.TIMESTAMP_WITH_TIMEZONE);
        return statement.executeUpdate();
    }
}

异常堆栈信息 #

java.sql.SQLDataException: data exception: invalid datetime format
            at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
            at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
            at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
            at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
            at com.sample.DeletemeHsqlBugTest.merge(DeletemeHsqlBugTest.java:79)
            at com.sample.DeletemeHsqlBugTest.testMerge(DeletemeHsqlBugTest.java:58)
            at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            ...
Caused by: org.hsqldb.HsqlException: data exception: invalid datetime format
            at org.hsqldb.error.Error.error(Unknown Source)
            at org.hsqldb.error.Error.error(Unknown Source)
            at org.hsqldb.types.DateTimeType.convertToDatetimeSpecial(Unknown Source)
            at org.hsqldb.types.DateTimeType.convertToType(Unknown Source)
            at org.hsqldb.ExpressionOp.getValue(Unknown Source)
            at org.hsqldb.StatementDML.getInsertData(Unknown Source)
            at org.hsqldb.StatementDML.executeMergeStatement(Unknown Source)
            at org.hsqldb.StatementDML.getResult(Unknown Source)
            at org.hsqldb.StatementDMQL.execute(Unknown Source)
            at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
            at org.hsqldb.Session.execute(Unknown Source)
            ... 73 more

临时解决方案（不够优雅） #

通过自定义DateTimeFormatter将UTC偏移的时间格式化为+00:00后缀，再以字符串形式传入参数：

private static final DateTimeFormatter FORMATTER = new DateTimeFormatterBuilder()
        .appendPattern("yyyy-MM-dd HH:mm:ss.SSS")
        .appendOffset("+HH:MM", "+00:00")
        .toFormatter();

private int merge(String code, OffsetDateTime updated) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement(MERGE_SQL)) {
        statement.setString(1, code);
        statement.setString(2, FORMATTER.format(updated));
        return statement.executeUpdate();
    }
}

内容的提问来源于stack exchange，提问作者lance-java