How to Insert and Retrieve java.time.LocalDate Objects from an H2 Database using JDBC?

How to insert and retrieve java.time.LocalDate objects from an SQL database like H2

Problem

How can you insert and retrieve java.time types such as LocalDate into an SQL database like H2 using JDBC? The old method of using PreparedStatement::setDate and ResultSet::getDate works for the legacy java.sql.Date type, but you want to avoid using these troublesome old date-time classes. What's the modern way to send java.time types through a JDBC driver?

Solution

There are two ways to exchange 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 java.time objects.
• Older drivers, before JDBC 4.2: If your JDBC driver does not comply with JDBC 4.2 or later, you must briefly convert your java.time objects to their equivalent java.sql type or vice versa. Look for new conversion methods added to the old classes.

The legacy date-time classes like java.util.Date, java.util.Calendar, and their related java.sql counterparts such as java.sql.Date are notoriously problematic, having been designed with a flawed, hacked approach. They are error-prone, troublesome, and confusing. Avoid them whenever possible, as they have now been replaced by java.time classes.

Using JDBC 4.2 compliant drivers

The built-in H2 JDBC driver (as of 2017-03) seems to comply with JDBC 4.2. Compliant drivers are aware of java.time types. Instead of adding setLocalDate/getLocalDate 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.time.LocalDate is converted to a SQL DATE type. See section 22 of the 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, you can provide an additional argument specifying the Class of the data type you expect to receive. By explicitly specifying the expected class, you gain type-safety, which is checked and verified by your IDE and compiler.

LocalDate localDate = myResultSet.getObject("my_date_column_", LocalDate.class);

Code example:

Here is a complete working example of an app that inserts and selects LocalDate values into an H2 database:

import java.sql.*;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.UUID;

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" ) );

The above is the detailed content of How to Insert and Retrieve java.time.LocalDate Objects from an H2 Database using JDBC?. For more information, please follow other related articles on the PHP Chinese website!