Java 如何在JDBC中获取插入ID？

How to get the insert ID in JDBC?

提问by Satya

I want to INSERTa record in a database (which is Microsoft SQL Server in my case) using JDBC in Java. At the same time, I want to obtain the insert ID. How can I achieve this using JDBC API?

我想INSERT在 Java 中使用 JDBC 在数据库中记录（在我的例子中是 Microsoft SQL Server）。同时，我想获取插入ID。如何使用 JDBC API 实现这一点？

采纳答案by BalusC

If it is an auto generated key, then you can use Statement#getGeneratedKeys()for this. You need to call it on the same Statementas the one being used for the INSERT. You first needto create the statement using Statement.RETURN_GENERATED_KEYSto notify the JDBC driver to return the keys.

如果它是自动生成的密钥，那么您可以使用Statement#getGeneratedKeys()它。您需要与Statement用于INSERT. 您首先需要创建Statement.RETURN_GENERATED_KEYS用于通知 JDBC 驱动程序返回键的语句。

Here's a basic example:

这是一个基本示例：

public void create(User user) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                                      Statement.RETURN_GENERATED_KEYS);
    ) {
        statement.setString(1, user.getName());
        statement.setString(2, user.getPassword());
        statement.setString(3, user.getEmail());
        // ...

        int affectedRows = statement.executeUpdate();

        if (affectedRows == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                user.setId(generatedKeys.getLong(1));
            }
            else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        }
    }
}

Note that you're dependent on the JDBC driver as to whether it works. Currently, most of the last versions will work, but if I am correct, Oracle JDBC driver is still somewhat troublesome with this. MySQL and DB2 already supported it for ages. PostgreSQL started to support it not long ago. I can't comment about MSSQL as I've never used it.

请注意，您依赖于 JDBC 驱动程序是否工作。目前，大多数最新版本都可以工作，但如果我是对的，Oracle JDBC 驱动程序在这方面仍然有些麻烦。MySQL 和 DB2 已经支持它很久了。PostgreSQL 不久前开始支持它。我无法评论 MSSQL，因为我从未使用过它。

For Oracle, you can invoke a CallableStatementwith a RETURNINGclause or a SELECT CURRVAL(sequencename)(or whatever DB-specific syntax to do so) directly after the INSERTin the same transaction to obtain the last generated key. See also this answer.

对于 Oracle，您可以在同一事务中直接调用CallableStatementwith aRETURNING子句或 a SELECT CURRVAL(sequencename)（或任何特定于 DB 的语法来执行此操作）INSERT以获取最后生成的键。另请参阅此答案。

回答by ftexperts

I'm hitting Microsoft SQL Server 2008 R2 from a single-threaded JDBC-based application and pulling back the last ID without using the RETURN_GENERATED_KEYS property or any PreparedStatement. Looks something like this:

我正在从基于单线程 JDBC 的应用程序访问 Microsoft SQL Server 2008 R2，并在不使用 RETURN_GENERATED_KEYS 属性或任何 PreparedStatement 的情况下拉回最后一个 ID。看起来像这样：

private int insertQueryReturnInt(String SQLQy) {
    ResultSet generatedKeys = null;
    int generatedKey = -1;

    try {
        Statement statement = conn.createStatement();
        statement.execute(SQLQy);
    } catch (Exception e) {
        errorDescription = "Failed to insert SQL query: " + SQLQy + "( " + e.toString() + ")";
        return -1;
    }

    try {
        generatedKey = Integer.parseInt(readOneValue("SELECT @@IDENTITY"));
    } catch (Exception e) {
        errorDescription = "Failed to get ID of just-inserted SQL query: " + SQLQy + "( " + e.toString() + ")";
        return -1;
    }

    return generatedKey;
} 

This blog post nicely isolates three main SQL Server "last ID" options: http://msjawahar.wordpress.com/2008/01/25/how-to-find-the-last-identity-value-inserted-in-the-sql-server/- haven't needed the other two yet.

这篇博文很好地隔离了三个主要的 SQL Server“最后一个 ID”选项：http: //msjawahar.wordpress.com/2008/01/25/how-to-find-the-last-identity-value-inserted-in-the -sql-server/- 还不需要另外两个。

回答by xanblax

I'm using SQLServer2008, but I have a development limitation: I cannot use a new driver for it, I have to use "com.microsoft.jdbc.sqlserver.SQLServerDriver" (I cannot use "com.microsoft.sqlserver.jdbc.SQLServerDriver").

我正在使用SQLServer2008，但我有一个开发限制：我不能使用新的驱动程序，我必须使用“com.microsoft.jdbc.sqlserver.SQLServerDriver”（我不能使用“com.microsoft.sqlserver.jdbc .SQLServerDriver”）。

That's why the solution conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)threw a java.lang.AbstractMethodErrorfor me. In this situation, a possible solution I found is the old one suggested by Microsoft: How To Retrieve @@IDENTITY Value Using JDBC

这就是解决方案为我conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)抛出java.lang.AbstractMethodError的原因。在这种情况下，我发现一个可能的解决方案是 Microsoft 建议的旧解决方案： How To Retrieve @@IDENTITY Value Using JDBC

import java.sql.*; 
import java.io.*; 

public class IdentitySample
{
    public static void main(String args[])
    {
        try
        {
            String URL = "jdbc:microsoft:sqlserver://yourServer:1433;databasename=pubs";
            String userName = "yourUser";
            String password = "yourPassword";

            System.out.println( "Trying to connect to: " + URL); 

            //Register JDBC Driver
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

            //Connect to SQL Server
            Connection con = null;
            con = DriverManager.getConnection(URL,userName,password);
            System.out.println("Successfully connected to server"); 

            //Create statement and Execute using either a stored procecure or batch statement
            CallableStatement callstmt = null;

            callstmt = con.prepareCall("INSERT INTO myIdentTable (col2) VALUES (?);SELECT @@IDENTITY");
            callstmt.setString(1, "testInputBatch");
            System.out.println("Batch statement successfully executed"); 
            callstmt.execute();

            int iUpdCount = callstmt.getUpdateCount();
            boolean bMoreResults = true;
            ResultSet rs = null;
            int myIdentVal = -1; //to store the @@IDENTITY

            //While there are still more results or update counts
            //available, continue processing resultsets
            while (bMoreResults || iUpdCount!=-1)
            {           
                //NOTE: in order for output parameters to be available,
                //all resultsets must be processed

                rs = callstmt.getResultSet();                   

                //if rs is not null, we know we can get the results from the SELECT @@IDENTITY
                if (rs != null)
                {
                    rs.next();
                    myIdentVal = rs.getInt(1);
                }                   

                //Do something with the results here (not shown)

                //get the next resultset, if there is one
                //this call also implicitly closes the previously obtained ResultSet
                bMoreResults = callstmt.getMoreResults();
                iUpdCount = callstmt.getUpdateCount();
            }

            System.out.println( "@@IDENTITY is: " + myIdentVal);        

            //Close statement and connection 
            callstmt.close();
            con.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
        }

        try
        {
            System.out.println("Press any key to quit...");
            System.in.read();
        }
        catch (Exception e)
        {
        }
    }
}

This solution worked for me!

这个解决方案对我有用！

I hope this helps!

我希望这有帮助！

回答by Eitan Rimon

When encountering an 'Unsupported feature' error while using Statement.RETURN_GENERATED_KEYS, try this:

使用时遇到“不支持的功能”错误Statement.RETURN_GENERATED_KEYS，请尝试以下操作：

String[] returnId = { "BATCHID" };
String sql = "INSERT INTO BATCH (BATCHNAME) VALUES ('aaaaaaa')";
PreparedStatement statement = connection.prepareStatement(sql, returnId);
int affectedRows = statement.executeUpdate();

if (affectedRows == 0) {
    throw new SQLException("Creating user failed, no rows affected.");
}

try (ResultSet rs = statement.getGeneratedKeys()) {
    if (rs.next()) {
        System.out.println(rs.getInt(1));
    }
    rs.close();
}

Where BATCHIDis the auto generated id.

BATCHID自动生成的id在哪里。

回答by Harsh Maheswari

1. Create Generated Column

   String generatedColumns[] = { "ID" };
   

2. Pass this geneated Column to your statement

   PreparedStatement stmtInsert = conn.prepareStatement(insertSQL, generatedColumns);
   

3. Use ResultSetobject to fetch the GeneratedKeys on Statement

   ResultSet rs = stmtInsert.getGeneratedKeys();
   
   if (rs.next()) {
       long id = rs.getLong(1);
       System.out.println("Inserted ID -" + id); // display inserted record
   }
   

1. 创建生成的列

   String generatedColumns[] = { "ID" };
   

2. 将此生成的 Column 传递给您的语句

   PreparedStatement stmtInsert = conn.prepareStatement(insertSQL, generatedColumns);
   

3. 使用ResultSetobject 来获取 Statement 上的 GeneratedKeys

   ResultSet rs = stmtInsert.getGeneratedKeys();
   
   if (rs.next()) {
       long id = rs.getLong(1);
       System.out.println("Inserted ID -" + id); // display inserted record
   }
   

回答by Abdelkhalek Benhoumine

Connection cn = DriverManager.getConnection("Host","user","pass");
Statement st = cn.createStatement("Ur Requet Sql");
int ret  = st.execute();

回答by Yash

Instead of a comment, I just want to answer post.

而不是评论，我只想回答帖子。

---

---

Interface java.sql.PreparedStatement

接口java.sql.PreparedStatement

1. columnIndexes? You can use prepareStatement function that accepts columnIndexes and SQL statement. Where columnIndexes allowed constant flags are Statement.RETURN_GENERATED_KEYS1or Statement.NO_GENERATED_KEYS[2], SQL statement that may contain one or more '?' IN parameter placeholders.

   SYNTAX ?

   Connection.prepareStatement(String sql, int autoGeneratedKeys)
   Connection.prepareStatement(String sql, int[] columnIndexes)
   

   Example:

   PreparedStatement pstmt = 
       conn.prepareStatement( insertSQL, Statement.RETURN_GENERATED_KEYS );
   

1. 列索引？您可以使用接受 columnIndexes 和 SQL 语句的 prepareStatement 函数。 其中 columnIndexes 允许的常量标志是 Statement.RETURN_GENERATED_KEYS 1或 Statement.NO_GENERATED_KEYS[2]，SQL 语句可能包含一个或多个 '?' IN 参数占位符。

   句法 ？

   Connection.prepareStatement(String sql, int autoGeneratedKeys)
   Connection.prepareStatement(String sql, int[] columnIndexes)
   

   例子：

   PreparedStatement pstmt = 
       conn.prepareStatement( insertSQL, Statement.RETURN_GENERATED_KEYS );
   

---

---

1. columnNames? List out the columnNames like 'id', 'uniqueID', .... in the target table that contain the auto-generated keys that should be returned. The driver will ignore them if the SQL statement is not an INSERTstatement.

   SYNTAX ?

   Connection.prepareStatement(String sql, String[] columnNames)
   

   Example:

   String columnNames[] = new String[] { "id" };
   PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames );
   

2. 列名？列出 columnNames 之类的'id', 'uniqueID', ...。在包含应返回的自动生成的键的目标表中。如果 SQL 语句不是INSERT语句，驱动程序将忽略它们。

   句法 ？

   Connection.prepareStatement(String sql, String[] columnNames)
   

   例子：

   String columnNames[] = new String[] { "id" };
   PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames );
   

---

---

Full Example:

完整示例：

public static void insertAutoIncrement_SQL(String UserName, String Language, String Message) {
    String DB_URL = "jdbc:mysql://localhost:3306/test", DB_User = "root", DB_Password = "";

    String insertSQL = "INSERT INTO `unicodeinfo`( `UserName`, `Language`, `Message`) VALUES (?,?,?)";
            //"INSERT INTO `unicodeinfo`(`id`, `UserName`, `Language`, `Message`) VALUES (?,?,?,?)";
    int primkey = 0 ;
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection(DB_URL, DB_User, DB_Password);

        String columnNames[] = new String[] { "id" };

        PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames );
        pstmt.setString(1, UserName );
        pstmt.setString(2, Language );
        pstmt.setString(3, Message );

        if (pstmt.executeUpdate() > 0) {
            // Retrieves any auto-generated keys created as a result of executing this Statement object
            java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
            if ( generatedKeys.next() ) {
                primkey = generatedKeys.getInt(1);
            }
        }
        System.out.println("Record updated with id = "+primkey);
    } catch (InstantiationException | IllegalAccessException | ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    }
}

回答by Balin

With Hibernate's NativeQuery, you need to return a ResultList instead of a SingleResult, because Hibernate modifies a native query

使用Hibernate的NativeQuery，需要返回ResultList而不是SingleResult，因为Hibernate修改了原生查询

INSERT INTO bla (a,b) VALUES (2,3) RETURNING id

like

喜欢

INSERT INTO bla (a,b) VALUES (2,3) RETURNING id LIMIT 1

if you try to get a single result, which causes most databases (at least PostgreSQL) to throw a syntax error. Afterwards, you may fetch the resulting id from the list (which usually contains exactly one item).

如果您尝试获得单个结果，这会导致大多数数据库（至少是 PostgreSQL）抛出语法错误。之后，您可以从列表（通常只包含一个项目）中获取结果 id。

回答by rogerdpack

It is possible to use it with normal Statement's as well (not just PreparedStatement)

也可以将它与 normal 一起使用Statement（不仅仅是PreparedStatement）

Statement statement = conn.createStatement();
int updateCount = statement.executeUpdate("insert into x...)", Statement.RETURN_GENERATED_KEYS);
try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
  if (generatedKeys.next()) {
    return generatedKeys.getLong(1);
  }
  else {
    throw new SQLException("Creating failed, no ID obtained.");
  }
}

回答by TheSagya

In my case ->

就我而言->

ConnectionClass objConnectionClass=new ConnectionClass();
con=objConnectionClass.getDataBaseConnection();
pstmtGetAdd=con.prepareStatement(SQL_INSERT_ADDRESS_QUERY,Statement.RETURN_GENERATED_KEYS);
pstmtGetAdd.setString(1, objRegisterVO.getAddress());
pstmtGetAdd.setInt(2, Integer.parseInt(objRegisterVO.getCityId()));
int addId=pstmtGetAdd.executeUpdate();              
if(addId>0)
{
    ResultSet rsVal=pstmtGetAdd.getGeneratedKeys();
    rsVal.next();
    addId=rsVal.getInt(1);
}