Insert & Fetch Java.Time.Localdate Objects To/From an SQL Database Such as H2

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

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.

Value of Date type is getting icremented by 1 month

Date takes months from 0 to 11 and not 1 to 12. So, new Date(2020,08,15); is actually 15th September 2020

And when date is printed/formatted, actual month is printed (values 1 to 12).
According to docs

A month is represented by an integer from 0 to 11; 0 is January, 1 is February, and so forth; thus 11 is December.

Note : Always prefer java.time API over java.util.Date

Is it possible to choose which overridden method to use in Java?

java.time and JDBC 4.2

I recommend that you stick to java.time, the modern Java date and time API to which Instant belongs and forget about the two Date classes mentioned in the question. They are both poorly designed and both long outdated. Since JDBC 4.2 we can retrieve java.time types from a ResultSet.

I am assuming that your SQL query is returning SQL datatype date.

    PreparedStatement stmt = yourDatabaseConnection
            .prepareStatement("select your_date_column from your_table;");
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        LocalDate date = rs.getObject("your_date_column", LocalDate.class);
        // do something with date
    }

You want to convert the date to an Instant? That conversion doesn’t readily make sense. An SQL date usually is a calendar date defined by year, month and day of month, without time zone. An Instant is a point in time, a completely different beast. If you can decide on a time of day and a time zone to use, a conversion is possible. One option is:

        Instant inst = date.atStartOfDay(ZoneId.systemDefault()).toInstant();

If you cannot avoid getting a java.sql.Date

Having a java.sql.Date, again we need a time of day and a time zone if we want to convert to Instant. There is a reason why java.sql.Date.toInstant() unconditionally throws UnsupportedOperationException (which I guess caused you to ask the question). I would convert the Date to a LocalDate and then proceed as before:

    java.sql.Date oldfashionedSqlDate = getFromSomewhere();
    Instant inst = oldfashionedSqlDate.toLocalDate()
            .atStartOfDay(ZoneId.systemDefault())
            .toInstant();

A shorter but more low-level and cryptic alternative is:

    Instant inst = Instant.ofEpochMilli(oldfashionedSqlDate.getTime());

The two ways may not always give the same result, which should be your first guidance for choosing. In case the Date contrary to the specification holds a time of day other than the start of day, the latter method will give you that time, whereas the former will give you the start of the day as the code says. BTW the latter is what java.util.Date.toInstant() does.

In general is it possible to circumvent the method implementation in the subclass and call the superclass method directly?

Is it possible to choose which overridden method to use in Java?

No, that is not possible in the language. Java doesn’t offer any syntax for such a trick. Holger’s comment under this answer seems to suggest that it is possible through reflection under some circumstances such as you being able to open the java.base module. See the last link at the bottom for more details.

Links

• Related question: Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
• Oracle tutorial: Date Time explaining how to use java.time.
• Related question: I want to print hi GrandFather;but it seems to print hi father

Converting String date to SQL Date

Use a SimpleDateFormat:

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yy");
String stringWithDate = "16/07/13";
Date theDate = sdf.parse(stringWithDate);
//store theDate variable in your database...

Note that SimpleDateFormat#parse throws ParseException.

UPDATE: Using Java 8 and LocalDate

LocalDate localDate = LocalDate.from(
    DateTimeFormatter.ofPattern("dd/MM/yy")
        .parse(fecha));

If you need to pass this to java.sql time objects, you can see this answer on How to get a java.time object from a java.sql.Timestamp without a JDBC 4.2 driver?

---

Related Topics