How to Insert and Retrieve `java.time.LocalDate` Objects in an H2 Database Using JDBC?

Use JDBC to insert and retrieve java.time.LocalDate objects in SQL databases such as H2 database

Question:

How to insert and retrieve java.time types (e.g. LocalDate) in a SQL database like H2 database engine using JDBC?

Answer:

Method 1: Driver compatible with JDBC 4.2

For JDBC drivers compliant with the JDBC 4.2 specification or higher, you can directly use the setObject and getObject methods to handle java.time objects. The driver automatically detects Java types and converts them to corresponding SQL types. For example:

preparedStatement.setObject(1, myLocalDate); // LocalDate转换为SQL DATE
LocalDate localDate = myResultSet.getObject("my_date_column_", LocalDate.class); // 指定预期类以确保类型安全

Method 2: Old version driver before JDBC 4.2

For drivers that are not JDBC 4.2 compliant, you must briefly convert a java.time object to an equivalent java.sql type and vice versa. Use the conversion method added to the legacy class:

java.sql.Date mySqlDate = java.sql.Date.valueOf(myLocalDate);
preparedStatement.setDate(1, mySqlDate);
java.sql.Date sqlDate = myResultSet.getDate("date_"); //尽可能简短地处理转换
LocalDate localDate = sqlDate.toLocalDate();

JDBC 4.2 compatible driver example:

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

public class LocalDateExample {

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:h2:mem:test_db"; // 更改为您的数据库URL
        String user = "user";
        String password = "password";

        try (
                Connection conn = DriverManager.getConnection(url, user, password);
                Statement stmt = conn.createStatement();
        ) {
            stmt.execute("CREATE TABLE IF NOT EXISTS employees (id INT PRIMARY KEY, name VARCHAR(255), birthday DATE)");

            // 插入LocalDate值
            LocalDate today = LocalDate.now(ZoneId.of("America/Montreal"));

            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees (name, birthday) VALUES (?, ?)");
            pstmt.setString(1, "John Doe");
            pstmt.setObject(2, today); // 直接传递LocalDate
            pstmt.executeUpdate();

            // 检索LocalDate值
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
            while (rs.next()) {
                LocalDate birthday = rs.getObject("birthday", LocalDate.class); // 指定预期类
                System.out.println("员工：" + rs.getString("name") + "，生日：" + birthday);
            }

            rs.close();
            pstmt.close();
            stmt.close();
        }
    }
}

Legacy driver example:

import java.sql.*;
import java.time.LocalDate;

public class LocalDateExample {

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:h2:mem:test_db"; // 更改为您的数据库URL
        String user = "user";
        String password = "password";

        try (
                Connection conn = DriverManager.getConnection(url, user, password);
                Statement stmt = conn.createStatement();
        ) {
            stmt.execute("CREATE TABLE IF NOT EXISTS employees (id INT PRIMARY KEY, name VARCHAR(255), birthday DATE)");

            // 插入LocalDate值
            LocalDate today = LocalDate.now();
            java.sql.Date sqlDate = java.sql.Date.valueOf(today);

            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees (name, birthday) VALUES (?, ?)");
            pstmt.setString(1, "John Doe");
            pstmt.setDate(2, sqlDate); // 将LocalDate转换为java.sql.Date
            pstmt.executeUpdate();

            // 检索LocalDate值
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
            while (rs.next()) {
                java.sql.Date sqlDate = rs.getDate("birthday");
                LocalDate birthday = sqlDate.toLocalDate(); // 将java.sql.Date转换为LocalDate
                System.out.println("员工：" + rs.getString("name") + "，生日：" + birthday);
            }

            rs.close();
            pstmt.close();
            stmt.close();
        }
    }
}

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