Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2

Question

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?

Answer 1

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.