oracle - 使用 Types.TIME 将 java.time.LocalTime 持久化到 oracle 中会丢失小数秒精度
问题描述
我对 Oracle 有一种行为,我不确定是否有意。
我正在使用各种类型的 java 日期/时间 API,在 JDBC 级别使用它们。
我正在尝试将 java.time.LocalTime 的实例持久保存到 oracle 数据库中。
在 JDBC 级别,我的理解是我应该使用:
- 符合 JDBC 4.2 的驱动程序
- PreparedStatement.setObject(int parameterIndex, Object x, int targetSqlType)
- Types.TIME 作为 java.time.LocalTime 实例的 targetSqlType
由于 oracle 不提供 TIME 类型,我使用 TIMESTAMP 字段。
问题是如果我在 JDBC 级别使用 Types.TIME,我会失去小数秒精度。
如果我使用 Types.TIMESTAMP,精度保持不变。
如果可能的话,我想对 LocalTime 使用 JDBC 预期的标准映射。
这里是显示行为的测试:
@Test
public void testSetObjectWithSqlTypeTimestamp() {
testSetObjectWithSqlType("java.sql.Types.TIMESTAMP", Types.TIMESTAMP);
}
@Test
public void testSetObjectWithSqlTypeTime() {
testSetObjectWithSqlType("java.sql.Types.TIME", Types.TIME);
}
private void testSetObjectWithSqlType(String sqlTypeLabel, Integer sqlTypeValue) {
logger.info("Testing setObject with {} (value={})", sqlTypeLabel, sqlTypeValue);
LocalTime willBeInserted = LocalTime.of(21, 17, 23, 678_987_000);
Connection con = null;
PreparedStatement ps = null;
try {
con = dataSource.getConnection();
con.setAutoCommit(false);
ps = con.prepareStatement("insert into LOCAL_TIME_TABLE (FOO) values (?)");
ps.setObject(1, willBeInserted, sqlTypeValue);
ps.executeUpdate();
con.commit();
} catch (SQLException e) {
rollbackQuietly(con);
throw new RuntimeException("Error inserting test value", e);
} finally {
JdbcUtils.closeStatement(ps);
JdbcUtils.closeConnection(con);
}
List<LocalTime> results = jdbcTemplate.queryForList("select FOO from LOCAL_TIME_TABLE", LocalTime.class);
assertThat(results)
.containsExactlyInAnyOrder(willBeInserted)
.hasSize(1);
}
Types.TIMESTAMP 测试工作正常。
Types.TIME 测试有以下错误:
[ERROR] Failures:
[ERROR] LocalTimeSetObjectWithSqlTypeOracleBugTest.testSetObjectWithSqlTypeTime:44->testSetObjectWithSqlType:72
Expecting:
<[21:17:23]>
to contain exactly in any order:
<[21:17:23.678987]>
elements not found:
<[21:17:23.678987]>
and elements not expected:
<[21:17:23]>
这是一个 spring-boot 测试(DataSource 和 JdbcTemplate 由 spring 提供)。我没有详细说明所有的 spring 样板代码,这似乎与问题无关;但如果需要,我会添加它。
我的 FOO 表如下所示:
create table LOCAL_TIME_TABLE (
FOO timestamp
);
我正在使用以下 Oracle 映像:https ://hub.docker.com/r/wnameless/oracle-xe-11g-r2
以及以下驱动程序(maven 坐标):com.oracle.database.jdbc:ojdbc10:19.3.0.0
所以我想我的问题有两个:
- 我是否正确假设 LocalTime 的预期标准 JDBC 映射是: ps.setObject(1, localTime, Types.TIME) ?如果不是,那么预期的 JDBC 方式是什么?
- Oracle 行为是有意的吗?如果是,它是否记录在某处?
另外,这是我的第一个 SO 问题,欢迎提供任何反馈。
快乐编码!
解决方案
Oracle 数据库以 Oracle SQL DATE 格式存储 TIME 值。Oracle SQL DATE 不存储小数秒。如果小数秒很重要,则使用 JDBCType.TIMESTAMP。
SQL 规范很微妙(轻描淡写)。所有供应商的 SQL 实现略有不同。SQL 规范非常灵活,所有主要的 SQL 数据库都符合要求,包括 Oracle 数据库。供应商的数据库之间存在显着差异,随便阅读 SQL 规范可能会产生误导。允许将 TIME 表示为 DATE 的 Oracle 数据库不存储小数秒。