插入 &从 SQL 数据库(如 H2)获取 java.time.LocalDate 对象 [英] Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
问题描述
如何插入和获取
符合 JDBC 4.2 的驱动程序
H2 的内置 JDBC 驱动程序(截至 2017-03)似乎符合 JDBC 4.2.
兼容驱动程序现在知道 java.time 类型.但 JDBC 委员会并没有添加 setLocalDate
/getLocalDate
之类的方法,而是添加了 setObject
/getObject
方法.
要将数据发送到数据库,只需将您的 java.time 对象传递给 PreparedStatement::setObject
.驱动程序检测到您传递的参数的 Java 类型并将其转换为适当的 SQL 类型.Java LocalDate
转换为 SQL DATE
类型.请参阅 JDBC 维护版本 4.2 PDF 文档的第 22 节获取这些映射的列表.
myPreparedStatement.setObject ( 1 , myLocalDate );//自动检测和转换数据类型.
要从数据库中检索数据,请调用 ResultSet::getObject
.我们可以传递一个额外的参数,而不是转换生成的 Object
对象,Class
我们期望接收的数据类型.通过指定预期的类别,我们获得了由您的 IDE 和编译器.
LocalDate localDate = myResultSet.getObject ( "my_date_column_" , LocalDate.class );
这是一个完整的工作示例应用程序,展示了如何将 LocalDate
值插入和选择到 H2 数据库中.
package com.example.h2localdate;导入 java.sql.*;导入 java.time.LocalDate;导入 java.time.ZoneId;导入 java.util.UUID;/*** 你好,世界!*/公共类应用{公共静态无效主(字符串[] args){应用程序=新应用程序();app.doIt();}私有无效 doIt ( ) {尝试 {Class.forName(org.h2.Driver");} catch ( ClassNotFoundException e ) {e.printStackTrace();}尝试 (Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_") ;语句stmt = conn.createStatement();){String tableName = "test_";String sql =创建表";+ 表名 + "(\n" +"id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n"+"日期_日期非空\n"+");";stmt.execute ( sql );//插入行.sql = "INSERT INTO test_ (date_) "+ "值 (?) ;";尝试 ( PreparedStatement PreparedStatement = conn.prepareStatement ( sql ) ; ) {LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal") );PreparedStatement.setObject ( 1, today.minusDays ( 1 ) );//昨天.prepareStatement.executeUpdate();PreparedStatement.setObject(1,今天);//今天.prepareStatement.executeUpdate();PreparedStatement.setObject ( 1, today.plusDays ( 1 ) );//明天.prepareStatement.executeUpdate();}//查询所有.sql = "SELECT * FROM test_";尝试(结果集 rs = stmt.executeQuery(sql);){while ( rs.next ( ) ) {//按列名检索UUID id = rs.getObject (id_", UUID.class);//传递类是类型安全的,而不是转换返回值.LocalDate localDate = rs.getObject ( "date_", LocalDate.class );//同上,为类型安全传递类.//显示值System.out.println ( "id_: " + id + " | date_: " + localDate );}}} catch ( SQLException e ) {e.printStackTrace();}}}
运行时.
<块引用>id_: e856a305-41a1-45fa-ab69-cfa676285461 |日期_:2017-03-26
<块引用>
id_:a4474e79-3e1f-4395-bbba-044423b37b9f |日期_:2017-03-27
<块引用>
id_: 5d47bc3d-ebfa-43ab-bbc2-7bb2313b33b0 |日期_:2017-03-28
不合规的驱动程序
对于H2,上面显示的代码是我推荐你走的路.但是仅供参考,对于其他不符合 JDBC 4.2 的数据库,我可以向您展示如何在 java.time 和 java.sql 类型之间进行简要转换.如下所示,这种转换代码肯定会在 H2 上运行,但现在我们有了上面显示的更简单的方法,所以这样做很愚蠢.
要将数据发送到数据库,请将您的 LocalDate
转换为 java.sql.Date
使用添加到旧类的新方法的对象.
java.sql.Date mySqlDate = java.sql.Date.valueOf( myLocalDate );
然后传递给 PreparedStatement::setDate
方法.
preparedStatement.setDate ( 1, mySqlDate );
要从数据库中检索,请调用 ResultSet::getDate
以获取 java.sql.Date
对象.
java.sql.Date mySqlDate = myResultSet.getDate( 1 );
然后立即转换为 LocalDate
.您应该尽可能简短地处理 java.sql 对象.仅使用 java.time 类型完成所有业务逻辑和其他工作.
LocalDate myLocalDate = mySqlDate.toLocalDate();
这是一个完整的示例应用程序,展示了在 H2 数据库中使用 java.sql 类型和 java.time 类型.
package com.example.h2localdate;导入 java.sql.*;导入 java.time.LocalDate;导入 java.time.ZoneId;导入 java.util.UUID;/*** 你好,世界!*/公共类应用{公共静态无效主(字符串[] args){应用程序=新应用程序();app.doIt();}私有无效 doIt ( ) {尝试 {Class.forName(org.h2.Driver");} catch ( ClassNotFoundException e ) {e.printStackTrace();}尝试 (Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_") ;语句stmt = conn.createStatement();){String tableName = "test_";String sql =创建表";+ 表名 + "(\n" +"id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n"+"日期_日期非空\n"+");";stmt.execute ( sql );//插入行.sql = "INSERT INTO test_ (date_) "+ "值 (?) ;";尝试 ( PreparedStatement PreparedStatement = conn.prepareStatement ( sql ) ; ) {LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal") );PreparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.minusDays ( 1 ) ) );//昨天.prepareStatement.executeUpdate();PreparedStatement.setDate ( 1, java.sql.Date.valueOf ( today ) );//今天.prepareStatement.executeUpdate();PreparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.plusDays ( 1 ) ) );//明天.prepareStatement.executeUpdate();}//查询所有.sql = "SELECT * FROM test_";尝试(结果集 rs = stmt.executeQuery(sql);){while ( rs.next ( ) ) {//按列名检索UUID id = (UUID) rs.getObject (id_");//如果您的驱动程序不支持 JDBC 4.2 及其为类型安全传递预期返回类型的能力,则将 `Object` 对象强制转换为 UUID.java.sql.Date sqlDate = rs.getDate (date_");LocalDate localDate = sqlDate.toLocalDate();//立即转换成java.time.尽量减少 java.sql 类型的使用.//显示值System.out.println ( "id_: " + id + " | date_: " + localDate );}}} catch ( SQLException e ) {e.printStackTrace();}}}
为了好玩,让我们尝试另一个.这次使用DataSource
实现 从中获取连接.这次尝试 LocalDate.MIN
在 ISO 8601 中是大约 10 亿年前的常数,-999999999-01-01.
package work.basil.example;导入 java.sql.*;导入 java.time.LocalDate;导入 java.time.ZoneId;导入 java.util.UUID;公共类 LocalDateMin{public static void main ( String[] args ){LocalDateMin app = new LocalDateMin();app.doIt();}私有无效 doIt(){org.h2.jdbcx.JdbcDataSource ds = new org.h2.jdbcx.JdbcDataSource();ds.setURL( "jdbc:h2:mem:localdate_min_example_db_;DB_CLOSE_DELAY=-1");ds.setUser(斯科特");ds.setPassword(老虎");尝试 (连接 conn = ds.getConnection() ;语句 stmt = conn.createStatement() ;){String tableName = "test_";String sql =创建表";+ 表名 + "(\n" +"id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n"+"日期_日期非空\n"+");";stmt.execute(sql);//插入行.sql = "INSERT INTO test_ (date_) "+ "值 (?) ;";尝试 ( PreparedStatement PreparedStatement = conn.prepareStatement( sql ) ; ){LocalDate today = LocalDate.now( ZoneId.of( "America/Montreal") );prepareStatement.setObject( 1 , LocalDate.MIN );//最小值 =prepareStatement.executeUpdate();}//查询所有.sql = "SELECT * FROM test_";尝试 ( 结果集 rs = stmt.executeQuery( sql ) ; ){而 ( rs.next() ){//按列名检索UUID id = rs.getObject( "id_", UUID.class);//传递类是类型安全的,而不是转换返回值.LocalDate localDate = rs.getObject( "date_", LocalDate.class );//同上,为类型安全传递类.//显示值System.out.println( "id_: " + id + " | date_: " + localDate );}}} catch ( SQLException e ){e.printStackTrace();}}}
<块引用>
id_: 4b0ba138-d7ae-469b-854f-5cbe7430026f |日期_:-999999999-01-01
关于java.time
java.time 框架内置于 Java 8 及更高版本中.这些类取代了麻烦的旧 legacy 日期时间类,例如 java.util.Date
, 日历
, &SimpleDateFormat
.
要了解更多信息,请参阅 Oracle 教程.并在 Stack Overflow 上搜索许多示例和解释.规范是 JSR 310.
Joda-Time 项目,现在在 维护模式,建议迁移到 java.time 类.
您可以直接与您的数据库交换 java.time 对象.使用符合 JDBC 驱动程序/jeps/170" rel="nofollow noreferrer">JDBC 4.2 或更高版本.不需要字符串,不需要 java.sql.*
类.休眠 5 &JPA 2.2 支持 java.time.
从哪里获取 java.time 类?
- Java SE 8、Java SE 9、Java SE 10、Java SE 11 及更高版本 - 具有捆绑实现的标准 Java API 的一部分.
- Java 9 带来了一些小功能和修复.
- Java SE 6 和 Java SE 7
- 大部分 java.time 功能都向后移植到 Java 6 &ThreeTen-Backport 中的 7 个.
- Android
- 更高版本的 Android (26+) 捆绑了 java.time 类的实现.
- 对于早期的Android(<26),API 脱糖 带来了 子集em>java.time 最初不是内置于 Android 中的功能.
- 如果脱糖不能满足您的需求,ThreeTenABP 项目改编了 ThreeTen-Backport(上面提到的)到Android.请参阅如何使用 ThreeTenABP....
ThreeTen-Extra 项目扩展了 java.time与额外的课程.该项目是未来可能添加到 java.time 的试验场.您可能会在这里找到一些有用的类,例如 间隔
,YearWeek
, YearQuarter
和 更多.
How to insert and fetch java.time types such as LocalDate
via JDBC to an SQL database such as the H2 Database Engine?
The old way using PreparedStatement::setDate
and ResultSet::getDate
works for the legacy java.sql.Date
type. I want to avoid using these troublesome old date-time classes.
What is the modern way for sending java.time types through a JDBC driver?
We have two routes to exchanging java.time objects through JDBC:
- JDBC 4.2 compliant drivers
If your JDBC driver complies with the JDBC 4.2 specification or later, you can deal directly with the java.time objects. - Older drivers, before JDBC 4.2
If your JDBC driver does not yet comply with JDBC 4.2 or later, then you briefly convert your java.time objects to their equivalent java.sql type or vice-versa. Look to new conversion methods added to the old classes.
The legacy date-time classes such as java.util.Date
, java.util.Calendar
, and the related java.sql
classes such as java.sql.Date
are an awful mess. Built with a poorly-designed hacked approach, they have proven to be flawed, troublesome, and confusing. Avoid them whenever possible. Now supplanted by the java.time classes.
JDBC 4.2 compliant drivers
The built-in JDBC driver for H2 (as of 2017-03) appears to comply with JDBC 4.2.
Compliant drivers are now aware of the java.time types. But rather than adding setLocalDate
/getLocalDate
sorts of methods, the JDBC committee added setObject
/getObject
methods.
To send data to the database, simply pass your java.time object to PreparedStatement::setObject
. The Java type of your passed argument is detected by the driver and converted to the appropriate SQL type. A Java LocalDate
is converted to a SQL DATE
type. See section 22 of JDBC Maintenance Release 4.2 PDF document for a list of these mappings.
myPreparedStatement.setObject ( 1 , myLocalDate ); // Automatic detection and conversion of data type.
To retrieve data from the database, call ResultSet::getObject
. Rather than casting the resulting Object
object, we can pass an extra argument, the Class
of the data type we expect to receive. By specifying the expected class, we gain type-safety checked and verified by your IDE and compiler.
LocalDate localDate = myResultSet.getObject ( "my_date_column_" , LocalDate.class );
Here is an entire working example app showing how to insert and select LocalDate
values into an H2 database.
package com.example.h2localdate;
import java.sql.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.UUID;
/**
* Hello world!
*/
public class App {
public static void main ( String[] args ) {
App app = new App ( );
app.doIt ( );
}
private void doIt ( ) {
try {
Class.forName ( "org.h2.Driver" );
} catch ( ClassNotFoundException e ) {
e.printStackTrace ( );
}
try (
Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_" ) ;
Statement stmt = conn.createStatement ( ) ;
) {
String tableName = "test_";
String sql = "CREATE TABLE " + tableName + " (\n" +
" id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
" date_ DATE NOT NULL\n" +
");";
stmt.execute ( sql );
// Insert row.
sql = "INSERT INTO test_ ( date_ ) " + "VALUES (?) ;";
try ( PreparedStatement preparedStatement = conn.prepareStatement ( sql ) ; ) {
LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal" ) );
preparedStatement.setObject ( 1, today.minusDays ( 1 ) ); // Yesterday.
preparedStatement.executeUpdate ( );
preparedStatement.setObject ( 1, today ); // Today.
preparedStatement.executeUpdate ( );
preparedStatement.setObject ( 1, today.plusDays ( 1 ) ); // Tomorrow.
preparedStatement.executeUpdate ( );
}
// Query all.
sql = "SELECT * FROM test_";
try ( ResultSet rs = stmt.executeQuery ( sql ) ; ) {
while ( rs.next ( ) ) {
//Retrieve by column name
UUID id = rs.getObject ( "id_", UUID.class ); // Pass the class to be type-safe, rather than casting returned value.
LocalDate localDate = rs.getObject ( "date_", LocalDate.class ); // Ditto, pass class for type-safety.
//Display values
System.out.println ( "id_: " + id + " | date_: " + localDate );
}
}
} catch ( SQLException e ) {
e.printStackTrace ( );
}
}
}
When run.
id_: e856a305-41a1-45fa-ab69-cfa676285461 | date_: 2017-03-26
id_: a4474e79-3e1f-4395-bbba-044423b37b9f | date_: 2017-03-27
id_: 5d47bc3d-ebfa-43ab-bbc2-7bb2313b33b0 | date_: 2017-03-28
Non-compliant drivers
For H2, the code shown above is the road I recommend you take. But FYI, for other databases that do not comply yet with JDBC 4.2, I can show you how to briefly convert between java.time and java.sql types. This kind of conversion code certainly runs on H2 as I show below, but doing so is silly now that we have the simpler approach shown above.
To send data to the database, convert your LocalDate
to a java.sql.Date
object using new methods added to that old class.
java.sql.Date mySqlDate = java.sql.Date.valueOf( myLocalDate );
Then pass to the PreparedStatement::setDate
method.
preparedStatement.setDate ( 1, mySqlDate );
To retrieve from the database, call ResultSet::getDate
to obtain a java.sql.Date
object.
java.sql.Date mySqlDate = myResultSet.getDate( 1 );
Then immediately convert to a LocalDate
. You should handle the java.sql objects as briefly as possible. Do all your business logic and other work using only the java.time types.
LocalDate myLocalDate = mySqlDate.toLocalDate();
Here is an entire example app showing this use of java.sql types with java.time types in an H2 database.
package com.example.h2localdate;
import java.sql.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.UUID;
/**
* Hello world!
*/
public class App {
public static void main ( String[] args ) {
App app = new App ( );
app.doIt ( );
}
private void doIt ( ) {
try {
Class.forName ( "org.h2.Driver" );
} catch ( ClassNotFoundException e ) {
e.printStackTrace ( );
}
try (
Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db_" ) ;
Statement stmt = conn.createStatement ( ) ;
) {
String tableName = "test_";
String sql = "CREATE TABLE " + tableName + " (\n" +
" id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
" date_ DATE NOT NULL\n" +
");";
stmt.execute ( sql );
// Insert row.
sql = "INSERT INTO test_ ( date_ ) " + "VALUES (?) ;";
try ( PreparedStatement preparedStatement = conn.prepareStatement ( sql ) ; ) {
LocalDate today = LocalDate.now ( ZoneId.of ( "America/Montreal" ) );
preparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.minusDays ( 1 ) ) ); // Yesterday.
preparedStatement.executeUpdate ( );
preparedStatement.setDate ( 1, java.sql.Date.valueOf ( today ) ); // Today.
preparedStatement.executeUpdate ( );
preparedStatement.setDate ( 1, java.sql.Date.valueOf ( today.plusDays ( 1 ) ) ); // Tomorrow.
preparedStatement.executeUpdate ( );
}
// Query all.
sql = "SELECT * FROM test_";
try ( ResultSet rs = stmt.executeQuery ( sql ) ; ) {
while ( rs.next ( ) ) {
//Retrieve by column name
UUID id = ( UUID ) rs.getObject ( "id_" ); // Cast the `Object` object to UUID if your driver does not support JDBC 4.2 and its ability to pass the expected return type for type-safety.
java.sql.Date sqlDate = rs.getDate ( "date_" );
LocalDate localDate = sqlDate.toLocalDate (); // Immediately convert into java.time. Mimimize use of java.sql types.
//Display values
System.out.println ( "id_: " + id + " | date_: " + localDate );
}
}
} catch ( SQLException e ) {
e.printStackTrace ( );
}
}
}
For fun let's try another. This time using a DataSource
implementation from which to get a connection. And this time trying LocalDate.MIN
which is a constant for about a billion years ago in ISO 8601, -999999999-01-01.
package work.basil.example;
import java.sql.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.UUID;
public class LocalDateMin
{
public static void main ( String[] args )
{
LocalDateMin app = new LocalDateMin();
app.doIt();
}
private void doIt ()
{
org.h2.jdbcx.JdbcDataSource ds = new org.h2.jdbcx.JdbcDataSource();
ds.setURL( "jdbc:h2:mem:localdate_min_example_db_;DB_CLOSE_DELAY=-1" );
ds.setUser( "scott" );
ds.setPassword( "tiger" );
try (
Connection conn = ds.getConnection() ;
Statement stmt = conn.createStatement() ;
)
{
String tableName = "test_";
String sql = "CREATE TABLE " + tableName + " (\n" +
" id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
" date_ DATE NOT NULL\n" +
");";
stmt.execute( sql );
// Insert row.
sql = "INSERT INTO test_ ( date_ ) " + "VALUES (?) ;";
try ( PreparedStatement preparedStatement = conn.prepareStatement( sql ) ; )
{
LocalDate today = LocalDate.now( ZoneId.of( "America/Montreal" ) );
preparedStatement.setObject( 1 , LocalDate.MIN ); // MIN =
preparedStatement.executeUpdate();
}
// Query all.
sql = "SELECT * FROM test_";
try ( ResultSet rs = stmt.executeQuery( sql ) ; )
{
while ( rs.next() )
{
//Retrieve by column name
UUID id = rs.getObject( "id_" , UUID.class ); // Pass the class to be type-safe, rather than casting returned value.
LocalDate localDate = rs.getObject( "date_" , LocalDate.class ); // Ditto, pass class for type-safety.
//Display values
System.out.println( "id_: " + id + " | date_: " + localDate );
}
}
} catch ( SQLException e )
{
e.printStackTrace();
}
}
}
id_: 4b0ba138-d7ae-469b-854f-5cbe7430026f | date_: -999999999-01-01
About java.time
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date
, Calendar
, & SimpleDateFormat
.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.*
classes. Hibernate 5 & JPA 2.2 support java.time.
Where to obtain the java.time classes?
- Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
- Java 9 brought some minor features and fixes.
- Java SE 6 and Java SE 7
- Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
- Android
- Later versions of Android (26+) bundle implementations of the java.time classes.
- For earlier Android (<26), the process of API desugaring brings a subset of the java.time functionality not originally built into Android.
- If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval
, YearWeek
, YearQuarter
, and more.
这篇关于插入 &从 SQL 数据库(如 H2)获取 java.time.LocalDate 对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!