JDBC中获取自增主键：使用getGeneratedKeys()的专业指南

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class GetGeneratedKeysExample {

    public static int insertAndGetId(Connection connection, String someColumnValue) throws SQLException {
        String sql = "INSERT INTO the_table(some_column) VALUES (?)";
        PreparedStatement pstmt = null;
        ResultSet keys = null;
        int newId = -1;

        try {
            // 方式一：指定要返回的列名
            // pstmt = connection.prepareStatement(sql, new String[]{"id"}); 

            // 方式二：使用PreparedStatement.RETURN_GENERATED_KEYS常量 (推荐)
            pstmt = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

            pstmt.setString(1, someColumnValue); // 假设some_column是字符串类型

            int numRowsAffected = pstmt.executeUpdate(); // 执行插入操作

            if (numRowsAffected > 0) {
                keys = pstmt.getGeneratedKeys(); // 获取生成的键
                if (keys.next()) {
                    newId = keys.getInt(1); // 获取第一个生成的键（通常是自增ID）
                    System.out.println("成功插入数据，生成的ID为: " + newId);
                } else {
                    System.out.println("插入成功，但未能获取到生成的ID。");
                }
            } else {
                System.out.println("插入操作未影响任何行。");
            }
        } finally {
            // 确保资源被关闭
            if (keys != null) {
                try {
                    keys.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return newId;
    }

    // 假设有一个获取数据库连接的方法
    public static Connection getConnection() throws SQLException {
        // ... 实现获取数据库连接的逻辑，例如使用DriverManager.getConnection()
        // Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/mydb", "user", "password");
        // return conn;
        return null; // 实际应用中替换为真实的连接
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = getConnection();
            if (conn != null) {
                int generatedId = insertAndGetId(conn, "示例数据");
                if (generatedId != -1) {
                    System.out.println("最终获取到的ID: " + generatedId);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BatchInsertExample {

    public static List<Integer> batchInsertAndGetIds(Connection connection, List<String> values) throws SQLException {
        String sql = "INSERT INTO the_table(some_column) VALUES (?)";
        PreparedStatement pstmt = null;
        ResultSet keys = null;
        List<Integer> generatedIds = new ArrayList<>();

        try {
            pstmt = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

            // 添加批处理
            for (String value : values) {
                pstmt.setString(1, value);
                pstmt.addBatch();
            }

            int[] numRowsAffected = pstmt.executeBatch(); // 执行批处理

            // 检查受影响的行数，这里只是简单打印
            for (int count : numRowsAffected) {
                System.out.println("批处理中单次插入影响行数: " + count);
            }

            keys = pstmt.getGeneratedKeys(); // 获取所有生成的键
            while (keys.next()) {
                generatedIds.add(keys.getInt(1)); // 将每个生成的ID添加到列表中
            }

            System.out.println("成功插入数据，生成的ID列表为: " + generatedIds);

        } finally {
            if (keys != null) {
                try {
                    keys.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return generatedIds;
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = GetGeneratedKeysExample.getConnection(); // 使用之前的获取连接方法
            if (conn != null) {
                List<String> dataToInsert = List.of("数据A", "数据B", "数据C");
                List<Integer> ids = batchInsertAndGetIds(conn, dataToInsert);
                System.out.println("最终获取到的所有ID: " + ids);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}