使用JDBC在H2数据库等SQL数据库中插入和检索java.time.LocalDate对象

问题：

如何使用JDBC在H2数据库引擎等SQL数据库中插入和检索java.time类型（例如LocalDate）？

解答：

方法一：兼容JDBC 4.2的驱动程序

对于符合JDBC 4.2规范或更高版本的JDBC驱动程序，您可以直接使用setObject和getObject方法处理java.time对象。驱动程序会自动检测Java类型并将其转换为相应的SQL类型。例如：

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

方法二：JDBC 4.2之前的旧版驱动程序

对于不兼容JDBC 4.2的驱动程序，您必须将java.time对象短暂转换为等效的java.sql类型，反之亦然。使用添加到旧版类中的转换方法：

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的驱动程序示例：

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();
        }
    }
}

旧版驱动程序示例：

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();
        }
    }
}