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 + " (
" +
" id_ UUID DEFAULT random_uuid() PRIMARY KEY ,
" +
" date_ DATE NOT NULL
" +
");";
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 + " (
" +
" id_ UUID DEFAULT random_uuid() PRIMARY KEY ,
" +
" date_ DATE NOT NULL
" +
");";
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 + " (
"
