How can I efficiently insert and retrieve Java `java.time` objects into and from a Postgres or H2 database using JDBC?

Efficiently use JDBC to operate Java java.timeObjects interact with PostgreSQL or H2 databases

This article explores how to efficiently use JDBC to insert and retrieve Java java.time objects, such as LocalDate, in a PostgreSQL or H2 database.

Traditional method: use PreparedStatement::setDate and ResultSet::getDate

The traditional approach relies on the obsolete java.sql.Date type, using PreparedStatement::setDate and ResultSet::getDate for operations. However, to avoid these problems, modern methods are recommended.

Modern approach: direct manipulation of java.timetypes

Driver compatible with JDBC 4.2 and above

The most effective method is to use a driver compatible with JDBC 4.2 and above. These drivers support java.time objects directly, without type conversion.

Insert data: use PreparedStatement::setObject(1, myLocalDate), where myLocalDate is a LocalDate object instance. The driver automatically detects and converts to the appropriate SQL type.

Retrieve data: Use ResultSet::getObject("my_date_column_", LocalDate.class) to retrieve LocalDate objects. Specifying the expected class ensures type safety.

Legacy driver (below JDBC 4.2)

For drivers that are not compatible with JDBC 4.2 and above, it is necessary to temporarily convert java.time objects to equivalent java.sql types and vice versa.

Use java.sql.Date.valueOf(myLocalDate) to convert LocalDate to java.sql.Date.

Insert data: use preparedStatement.setDate(1, java.sql.Date.valueOf(myLocalDate)).

Retrieve data: Use rs.getDate(1) to get a java.sql.Date object, then use sqlDate.toLocalDate() to convert it to a LocalDate.

Sample code using JDBC 4.2 compatible driver

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

public class App {

    public static void main(String[] args) {
        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 + " (" +
                    "id_ UUID DEFAULT random_uuid() PRIMARY KEY ," +
                    "date_ DATE NOT NULL" +
                    ");";
            stmt.execute(sql);

            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));
                preparedStatement.executeUpdate();
                preparedStatement.setObject(1, today);
                preparedStatement.executeUpdate();
                preparedStatement.setObject(1, today.plusDays(1));
                preparedStatement.executeUpdate();
            }

            sql = "SELECT * FROM test_";
            try (ResultSet rs = stmt.executeQuery(sql);) {
                while (rs.next()) {
                    UUID id = rs.getObject("id_", UUID.class);
                    LocalDate localDate = rs.getObject("date_", LocalDate.class);
                    System.out.println("id_: " + id + " | date_: " + localDate);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The running result is similar to:

<code>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</code>

Sample code using the old version of the driver (Same as the original code and will not be repeated here)

By choosing the right JDBC driver and using the appropriate methods, you can ensure efficient and type-safe interaction with your database. Remember to prioritize using drivers compatible with JDBC 4.2 and above to simplify code and improve efficiency.

The above is the detailed content of How can I efficiently insert and retrieve Java `java.time` objects into and from a Postgres or H2 database using JDBC?. For more information, please follow other related articles on the PHP Chinese website!