How to Effectively Use java.time Types with JDBC for Database Interactions?

How to insert and fetch java.time types such as LocalDate to/from an SQL database such as H2

The old way of using PreparedStatement::setDate and ResultSet::getDate works for the legacy java.sql.Date type. However, it's better to avoid using these troublesome old date-time classes. Instead, use the modern java.time types.

There are 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. Compliant drivers are aware of the java.time types and have added setObject/getObject methods to handle them.

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.

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.

Older drivers, before JDBC 4.2

If your JDBC driver does not yet comply with JDBC 4.2 or later, then you need to 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.

Here's a complete working example app showing how to insert and select 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" ) );
                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 ( ) ) {
                    UUID id = rs.getObject ( "id_" , UUID.class );  // Pass the class to be type-safe.
                    LocalDate localDate = rs.getObject ( "date_", LocalDate.class );  // Pass class for type-safety.
                    System.out.println ( "id_: " + id + " | date_: " + localDate );
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace ( );
        }
    }
}

The above is the detailed content of How to Effectively Use java.time Types with JDBC for Database Interactions?. For more information, please follow other related articles on the PHP Chinese website!