How to Insert and Retrieve java.time Objects Using JDBC?

Inserting and Retrieving java.time Objects Using JDBC

JDBC offers two ways to exchange java.time objects through JDBC:

JDBC 4.2 Compliant Drivers

JDBC 4.2 compliant drivers let you interact with java.time objects directly, without conversions.

Insertion: Use setObject to pass your java.time object. The driver automatically converts it to the appropriate SQL type (e.g., LocalDate to SQL DATE).

Retrieval: Call getObject twice, first without arguments to retrieve the value as an Object, then with the expected class as an argument for type-safety.

Non-compliant Drivers

For drivers that don't support JDBC 4.2, you can convert between java.time and java.sql types using the following code:

Insertion:

LocalDate date = ...;
java.sql.Date sqlDate = java.sql.Date.valueOf(date);
preparedStatement.setDate(1, sqlDate);

Retrieval:

java.sql.Date sqlDate = resultSet.getDate(1);
LocalDate date = sqlDate.toLocalDate();

Example with H2

Using JDBC 4.2 with H2:

try {
    Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

try (
    Connection conn = DriverManager.getConnection("jdbc:h2:mem:mydb");
    Statement stmt = conn.createStatement();
) {
    String sql = "CREATE TABLE test (id UUID, date DATE);";
    stmt.execute(sql);

    LocalDate today = LocalDate.now();
    sql = String.format("INSERT INTO test (id, date) VALUES (%s, %s)", UUID.randomUUID(), today);
    stmt.executeUpdate(sql);

    sql = "SELECT id, date FROM test";
    try (ResultSet rs = stmt.executeQuery(sql)) {
        while (rs.next()) {
            UUID id = rs.getObject("id", UUID.class);
            LocalDate date = rs.getObject("date", LocalDate.class);
            System.out.println(String.format("id: %s, date: %s", id, date));
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Example with Non-compliant Drivers

Using non-JDBC 4.2 with H2:

try {
    Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

try (
    Connection conn = DriverManager.getConnection("jdbc:h2:mem:mydb");
    Statement stmt = conn.createStatement();
) {
    String sql = "CREATE TABLE test (id UUID, date DATE);";
    stmt.execute(sql);

    LocalDate today = LocalDate.now();
    java.sql.Date sqlDate = java.sql.Date.valueOf(today);
    sql = String.format("INSERT INTO test (id, date) VALUES (%s, %s)", UUID.randomUUID(), sqlDate);
    stmt.executeUpdate(sql);

    sql = "SELECT id, date FROM test";
    try (ResultSet rs = stmt.executeQuery(sql)) {
        while (rs.next()) {
            UUID id = (UUID) rs.getObject("id");
            java.sql.Date date = rs.getDate("date");
            LocalDate localDate = date.toLocalDate();
            System.out.println(String.format("id: %s, date: %s", id, localDate));
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

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