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:
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.
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
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
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?
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.
LocalDate.MIN: -999999999-01-01. See the new example app source code using LocalDate.MIN that I appended to my Answer. –
Wildee setObject way of doing things has the problem that there is an unwanted timezone conversion in there somewhere - the date represented by the LocalDate object gets shifted back by one day (I'm in timezone GMT+1)! Is that a sign of a non-compliant driver? Or should non-compliant drivers not have the setObject method at all? –
Fauteuil LocalDate objects being stored in a SQL-standard DATE type column. If you store January 23rd of 2019, you’ll get back the same, absolutely. You may be thinking of the terrible java.sql.Date class which pretends to represent a date-only but actually carries a time-of-day and UTC. Never use that legacy class. Use only java.time classes for all your date-to e handling. If you see some puzzling behavior, post a new Question with example code. –
Wildee setObject with the LocalDate to store, but I still get the day subtraction... I guess I'll post a new question... –
Fauteuil LocalDate objects to DATE column using PreparedStatement::setObject( … , LocalDate ld ); –
Wildee © 2022 - 2024 — McMap. All rights reserved.