如何在JDBC中获得插入ID ?

如果它是一个自动生成的键，那么你可以为此使用Statement#getGeneratedKeys()。你需要在与INSERT相同的Statement上调用它。你首先需要创建语句，使用Statement.RETURN_GENERATED_KEYS通知JDBC驱动程序返回键。

这里有一个基本的例子:

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.");
}
}
}
}

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

对于Oracle，你可以在同一个事务中的INSERT之后直接调用带有RETURNING子句或SELECT CURRVAL(sequencename)(或任何特定于db的语法)的CallableStatement来获得最后生成的密钥。另见这个答案。

我正在从一个基于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;
}

我正在使用SQLServer 2008，但我有一个开发限制:我不能为它使用一个新的驱动程序，我必须使用“com.microsoft.jdbc.sqlserver。SQLServerDriver”(我不能使用“com.microsoft.sqlserver.jdbc.SQLServerDriver”)。

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)
{
}
}
}

这个方法对我很有效!

我希望这能有所帮助!

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

其中BATCHID是自动生成的id。

1. 创建生成的列

   String generatedColumns[] = { "ID" };
   

2. Pass this geneated Column to your statement

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

3. Use ResultSet object 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
   }
   

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

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

接口java.sql.PreparedStatement

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

   «< em >语法

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

   例子:

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

2. columnNames«列出像__ABC0这样的columnname。在包含应返回的自动生成键的目标表中。如果SQL语句不是INSERT语句，驱动程序将忽略它们。

   语法«

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

   例子:

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

完整的例子:

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

使用Hibernate的NativeQuery，您需要返回一个ResultList而不是SingleResult，因为Hibernate修改了一个本机查询

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

就像

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

如果你试图得到一个单一的结果，这将导致大多数数据库(至少PostgreSQL)抛出一个语法错误。然后，您可以从列表中获取结果id(通常只包含一个项)。

它也可以与普通的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.");
}
}

在我的例子中是>

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

如果您正在使用Spring JDBC，您可以使用Spring的GeneratedKeyHolder类来获取插入的ID。

看到这个答案… 如何使用Spring Jdbctemplate获取插入id。update(String sql, obj…args) < / p >

您可以使用以下java代码来获取新的插入id。

ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, quizid);
ps.setInt(2, userid);
ps.executeUpdate();


ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
lastInsertId = rs.getInt(1);
}

如果您正在使用JDBC(用MySQL测试)，并且只想要最后插入的ID，有一种简单的方法可以获得它。我使用的方法如下:

public static Integer insert(ConnectionImpl connection, String insertQuery){


Integer lastInsertId = -1;
try{
final PreparedStatement ps = connection.prepareStatement(insertQuery);
ps.executeUpdate(insertQuery);
final com.mysql.jdbc.PreparedStatement psFinal = (com.mysql.jdbc.PreparedStatement) ps;
lastInsertId = (int) psFinal.getLastInsertID();
connection.close();
} catch(SQLException ex){
System.err.println("Error: "+ex);
}


return lastInsertId;
}

此外，(以防万一)获取ConnectionImpl的方法如下:

public static ConnectionImpl getConnectionImpl(){
ConnectionImpl conexion = null;


final String dbName = "database_name";
final String dbPort = "3306";
final String dbIPAddress = "127.0.0.1";
final String connectionPath = "jdbc:mysql://"+dbIPAddress+":"+dbPort+"/"+dbName+"?autoReconnect=true&useSSL=false";
    

final String dbUser = "database_user";
final String dbPassword = "database_password";
try{
conexion = (ConnectionImpl) DriverManager.getConnection(connectionPath, dbUser, dbPassword);
}catch(SQLException e){
System.err.println(e);
}
    

return conexion;
}

记住要将连接器/ J添加到项目引用库中。

在我的例子中，连接器/J版本是5.1.42。如果你想使用一个更现代的连接器/J版本，比如8.0.28版本，也许你将不得不对connectionPath应用一些更改。

在文件中，请记住导入以下资源:

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.jdbc.ConnectionImpl;

希望这对你有所帮助。

大多数人都建议使用JDBC API来实现这一点，但就我个人而言，我发现使用大多数驱动程序是非常痛苦的。实际上，你可以只使用原生T-SQL特性OUTPUT子句:

try (
Statement s = c.createStatement();
ResultSet rs = s.executeQuery(
"""
INSERT INTO t (a, b)
OUTPUT id
VALUES (1, 2)
"""
);
) {
while (rs.next())
System.out.println("ID = " + rs.getLong(1));
}

这是SQL Server以及其他一些SQL方言的最简单的解决方案(例如，Firebird, MariaDB, PostgreSQL，其中您将使用RETURNING而不是OUTPUT)。

关于这个话题，我已经在这里写过更详细的博客。