Inserting & Fetching java.time.LocalDate Objects to/from an SQL Database Like H2

Question: How to handle java.time types like LocalDate via JDBC with an SQL database, such as H2?

Legacy Approach: Using PreparedStatement::setDate and ResultSet::getDate works for the outdated java.sql.Date type. However, it's preferable to avoid these problematic legacy classes.

Modern Approach: There are two main routes:

1. JDBC 4.2 Compliant Drivers: If the JDBC driver supports JDBC 4.2 or later, you can directly work with java.time objects. These drivers have setObject and getObject methods for data type conversions.
2. Older Drivers, Pre-JDBC 4.2: For drivers before JDBC 4.2, temporarily convert java.time objects to their java.sql equivalents and vice versa. Use new conversion methods added to legacy classes.

Example of Using JDBC 4.2 Compliant Driver:

// Insert LocalDate as SQL DATE
preparedStatement.setObject(1, myLocalDate);

// Retrieve LocalDate from SQL DATE
LocalDate localDate = myResultSet.getObject("my_date_column", LocalDate.class);

Example of Using Non-Compliant Driver:

// Convert LocalDate to java.sql.Date for insertion
java.sql.Date mySqlDate = java.sql.Date.valueOf(myLocalDate);
preparedStatement.setDate(1, mySqlDate);

// Convert java.sql.Date to LocalDate after retrieval
LocalDate localDate = mySqlDate.toLocalDate();

Recommendation: Use the JDBC 4.2 compliant approach whenever possible. It's simpler and type-safe. However, the non-compliant method can still be used for drivers that don't support JDBC 4.2.