英文:
Merge fails for OffsetDateTime when offset is UTC (+00:00)
问题
出现问题的代码逻辑是关于 HSQLDB 和 MERGE 语句中 OffsetDateTime 的使用，特别是在 UTC 偏移（+00:00）时出现的问题。解决方案是通过自定义 DateTimeFormatter 格式化 OffsetDateTime，将其偏移表示为 "+00:00" 形式。这样可以解决 MERGE 语句中的问题。
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();
}
}
英文:
I’m having an issue with HSQLDB and MERGE with the following merge logic if I use an OffsetDateTime with offset +00:00
private static final String CREATE_TABLE_SQL =
"create table sample (" +
"code varchar2(50), " +
"updated timestamp with time zone" +
")";
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();
}
}
The merge statement works fine for all offsets except UTC (eg offset +01:00 works)
I think this is caused by the fact that toString() of an OffsetDateTime at UTC has the Z suffix instead of +00:00. It seems that an INSERT statement does not have the same problem as MERGE
Exception stack trace
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
As a workaround I can do the following which feels hacky
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();
}
}
答案1
得分: 2
我在hsqldb-user邮件列表上发布了这个问题，并收到了以下回复，解决了我的问题。我在这里重新发布，因为在Stack Overflow上更容易找到：
> 如果您将所需类型添加到CAST，它应该可以工作：
"using (select ? as code, cast(? as timestamp with time zone) as updated from dual) val " +
更新
似乎这个问题已经修复，并将在HSQLDB的下一个版本中提供：
- https://sourceforge.net/p/hsqldb/feature-requests/362/
- https://sourceforge.net/p/hsqldb/svn/6638/
- https://github.com/ryenus/hsqldb/commit/a7cc02e47f93c5001d804d26f246cba7b2657f11
英文:
I posted this question on the hsqldb-user mailing list and got the following response which fixes my issue. Re-posting here since it's easier to find it here on Stack Overflow
> it should work if you add a CAST to the required type:
"using (select ? as code, cast(? as timestamp with time zone) as updated from dual) val " +
Update
It seems that this issue has been fixed and will be available in the next release of HSQLDB
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论