JDBC Statement RETURN_GENERATED_KEYS返回自动生成的ID

原创已于 2022-10-06 13:11:49 修改 · 1.8k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#java #sql #数据库

于 2022-10-06 12:24:19 首次发布

Java 语句 RETURN_GENERATED_KEYS 指示生成的密钥应可用于检索的常量。

语法

语句中的字段RETURN_GENERATED_KEYS（）声明为：

复制

    int RETURN_GENERATED_KEYS = 1;

例

下面的代码演示如何使用 Java Statement.RETURN_GENERATED_KEYS

示例 1

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
    private static final String URL = "jdbc:mysql://localhost/testdb";

    private static final String USERNAME = "root";

    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

        String insert = "INSERT INTO orders (username, order_date) VALUES ('foobar', '2007-12-13')";
        Statement stmt = conn.createStatement();

        stmt.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);

        ResultSet keys = stmt.getGeneratedKeys();
        int lastKey = 1;
        while (keys.next()) {
            lastKey = keys.getInt(1);//    ww  w  .    d e   m  o2    s . c   o   m
        }
        System.out.println("Last Key: " + lastKey);
        conn.close();
    }
}

另一种方法：


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

    private static final String URL = "jdbc:mysql://localhost/testdb";

    private static final String USERNAME = "root";

    private static final String PASSWORD = "root";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

        String insert = "INSERT INTO orders (username, order_date) VALUES ('foobar', '2007-12-13')";
        Statement stmt = conn.createStatement();

        String[] id_col = {"id"};
        stmt.executeUpdate(insert, id_col);

        ResultSet keys = stmt.getGeneratedKeys();
        int lastKey = 1;
        while (keys.next()) {
            lastKey = keys.getInt(1);
        }
        System.out.println("Last Key: " + lastKey);
        conn.close();
    }
}

示例 2

复制

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

public class Main {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost/testdb";
        String username = "root";
        String password = "";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = null;/*w   w  w   .d   e  m o  2   s .    c o   m*/
        try {
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);

            Statement st = conn.createStatement();
            st.execute("INSERT INTO orders (username, order_date) VALUES ('java', '2007-12-13')",
                    Statement.RETURN_GENERATED_KEYS);

            ResultSet keys = st.getGeneratedKeys();
            int id = 1;
            while (keys.next()) {
                id = keys.getInt(1);
            }
            PreparedStatement pst = conn.prepareStatement(
                    "INSERT INTO order_details (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)");
            pst.setInt(1, id);
            pst.setString(2, "1");
            pst.setInt(3, 10);
            pst.setDouble(4, 100);
            pst.execute();

            conn.commit();
            System.out.println("Transaction commit...");
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback();
                System.out.println("Connection rollback...");
            }
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }
}