Вставка и извлечение объектов java.time.LocalDate в / из базы данных SQL, такой как H2
Переведено автоматически
Ответ 1
У нас есть два маршрута для обмена объектами java.time через JDBC:
- Драйверы, совместимые с JDBC 4.2
Если ваш драйвер JDBC соответствует спецификации JDBC 4.2 или более поздней версии, вы можете работать непосредственно с объектами java.time. - Более старые драйверы, до JDBC 4.2
Если ваш драйвер JDBC еще не соответствует JDBC 4.2 или более поздней версии, вы ненадолго преобразуете ваши объекты java.time в эквивалентный тип java.sql или наоборот. Обратите внимание на новые методы преобразования, добавленные к старым классам.
Устаревшие классы даты и времени, такие как java.util.Date, java.util.Calendar и связанные с ними java.sql классы, такие как java.sql.Date, представляют собой ужасный беспорядок. Созданные с использованием плохо продуманного взломанного подхода, они оказались ошибочными, хлопотными и сбивающими с толку. Избегайте их, когда это возможно. Теперь заменены классами java.time .
Драйверы, совместимые с JDBC 4.2
Встроенный драйвер JDBC для H2 (по состоянию на 2017-03), похоже, соответствует JDBC 4.2.
Совместимые драйверы теперь знают о типах java.time. Но вместо добавления методов типа setLocalDate/getLocalDate комитет JDBC добавил методы типа setObject/getObject.
Чтобы отправить данные в базу данных, просто передайте свой объект java.time в PreparedStatement::setObject. Тип Java вашего переданного аргумента определяется драйвером и преобразуется в соответствующий тип SQL. Java LocalDate преобразуется в тип SQL DATE. Список этих сопоставлений приведен в разделе 22 PDF-документа JDBC Maintenance Release 4.2.
myPreparedStatement.setObject ( 1 , myLocalDate ); // Automatic detection and conversion of data type.
Чтобы извлечь данные из базы данных, вызовите ResultSet::getObject. Вместо приведения результирующего Object объекта, мы можем передать дополнительный аргумент, Class типа данных, который мы ожидаем получить. Указывая ожидаемый класс, мы получаем безопасность типов, проверенную вашей IDE и компилятором.
LocalDate localDate = myResultSet.getObject ( "my_date_column_" , LocalDate.class );
Вот полный рабочий пример приложения, показывающий, как вставлять и выбирать LocalDate значения в базу данных H2.
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.