如何将sql DATE映射到LocalDate [英] How to map sql DATE to LocalDate
问题描述
我想在 DATE
列中存储 LocalDate
并保持不变。根据定义, DATE
和 LocalDate
都是本地类型。因此, timezone 的概念不应以任何方式干扰。
I want to store a LocalDate
in a DATE
column and retrieve it unchanged. Both DATE
and LocalDate
are "local" types by definition. Therefore, the concept of timezone should not interfere in any way.
下面的代码是创建一个<$的表的最小示例c $ c> DATE 内存数据库中的列。 maven工件 com.h2database:h2:1.4.192
必须在类路径中。
The code below is a minimal example that creates a table with a DATE
column in a in-memory database. The maven artifact com.h2database:h2:1.4.192
must be in the classpath.
首先,定义方法 insert
和 retrieve
:
static void insert(DataSource ds, String date) throws SQLException {
try (Connection conn = ds.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute("CREATE TABLE people (id BIGINT NOT NULL AUTO_INCREMENT"
+ ", born DATE NOT NULL, PRIMARY KEY (id) );");
stmt.execute("INSERT INTO people (born) VALUES ('" + date + "')");
}
}
static LocalDate retrieve(DataSource ds) throws SQLException {
try (Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM people limit 1")) {
if (rs.next()) {
java.sql.Date retrieved = java.sql.Date.valueOf(rs.getString("born"));
return retrieved.toLocalDate();
}
throw new IllegalStateException("No data");
}
}
请注意插入
方法在单引号中使用 LocalDate
的 toString
值,因此Java没有机会™创造时区歧义。现在调用插入
一次,然后多次检索
,每次使用不同的timzone设置:
Notice that the insert
method uses the toString
value of the LocalDate
in single quotes, so there's no opportunity for Java™ to create timezone ambiguity. Now call insert
once and then several times retrieve
, with different timzone settings each time:
public static void main(String[] args) throws Exception {
DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test", "sa", "sa");
LocalDate born = LocalDate.parse("2015-05-20");
insert(ds, born.toString());
System.out.println("Inserted: " + born);
for (int i : new int[]{-14, 0, 12}) {
TimeZone z = TimeZone.getTimeZone(String.format("Etc/GMT%+02d", i));
TimeZone.setDefault(z);
System.out.println("Retrieved: " + retrieve(ds));
}
}
然后打印以下内容:
Inserted: 2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-19
Retrieved: 2015-05-18
如何编写检索
方法,以便假设数据库表没有改变,它返回与无条件相同的值?
How to write the retrieve
method so that it returns the same value that was inserted unconditionally, assuming that the database table doesn't change?
推荐答案
<我只是尝试对你的检索
方法进行以下修改,它对我有用:
I just tried the following modification to your retrieve
method and it worked for me:
DATE类型的H2文档表示它是
日期数据类型。格式是yyyy-MM-dd。
The date data type. The format is yyyy-MM-dd.
所以,而不是你的......
So, instead of your ...
java.sql.Date retrieved = (java.sql.Date) rs.getObject("born");
return retrieved.toLocalDate();
...我刚刚使用过......
... I just used ...
return LocalDate.parse(rs.getString("born"));
...我的代码已生成
... and my code produced
Inserted: 2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20
这篇关于如何将sql DATE映射到LocalDate的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!