Use JDBC to insert and retrieve java.time.LocalDate object

question:

How to insert and retrieve java.timetypes (e.g. LocalDate

answer:

Method 1: Driver compatible with JDBC 4.2

For JDBC drivers that comply with JDBC 4.2 specification or later, you can directly use the setObject and getObject methods to handle the java.time

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

Method 2: Old version of drivers before JDBC 4.2

For a driver that is not compatible with JDBC 4.2, you must briefly convert the java.time object to the equivalent 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();

Example of driver compatible with 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();
        }
    }
}

Example of legacy driver:

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