当前位置： 文江博客话题详情

JDBC中如何获取插入ID？

发布于 2024-08-15 15:45:07 字数 115 浏览 7 评论 0原文

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

原文

I want to INSERT a 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?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（16）

巡山小妖精 2024-08-22 15:45:07

如果它是自动生成的密钥，那么您可以使用 Statement#getGenerateKeys() 为此。您需要在与用于 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，您可以使用 RETURNING 子句或 SELECT CURRVAL(sequencename)（或任何特定于数据库的语法来执行此操作）调用 CallableStatement ) 直接在同一事务中的 INSERT 之后获取最后生成的密钥。另请参阅此答案。

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

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.

For Oracle, you can invoke a CallableStatement with a RETURNING clause or a SELECT CURRVAL(sequencename) (or whatever DB-specific syntax to do so) directly after the INSERT in the same transaction to obtain the last generated key. See also this answer.

回复 原文
倾城花音 2024-08-22 15:45:07

  1. 创建生成列

    String generatedColumns[] = { "ID" };

  2. 将此生成的列传递到您的语句

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

  3. 使用ResultSet对象获取语句上的GenerateKeys

    ResultSet rs = stmtInsert.getGenerateKeys();

如果 (rs.next()) {
    长 id = rs.getLong(1);
    System.out.println("插入的ID -" + id); // 显示插入的记录
}

  1. Create Generated Column

    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
}
回复 原文
拥抱我好吗 2024-08-22 15:45:07

使用 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。

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

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 BATCHID is the auto generated id.

回复 原文
独孤求败 2024-08-22 15:45:07

我从基于 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;
}

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

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:

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.

回复 原文
爱的那么颓废 2024-08-22 15:45:07

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

接口java. sql.PreparedStatement

  1. columnIndexes « 您可以使用接受columnIndexes 和SQL 语句的prepareStatement 函数。
    其中columnIndexes允许的常量标志是Statement.RETURN_GENERATED_KEYS1< /a> 或 Statement.NO_GENERATED_KEYS[2]，可能包含一个或多个“?”的 SQL 语句IN 参数占位符。

    语法 «

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

    示例：

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

  1. columnNames « 列出列名称，如 'id'、'uniqueID'、...。在包含应返回的自动生成键的目标表中。如果 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();
    }
}

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

Interface 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_KEYS1 or 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. 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 INSERT statement.

    SYNTAX «

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

    Example:

    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();
    }
}
回复 原文
⊕婉儿 2024-08-22 15:45:07

我正在使用 SQLServer 2008，但我有一个开发限制：我无法为其使用新的驱动程序，我必须使用“com.microsoft.jdbc.sqlserver.SQLServerDriver”（我无法使用“ com.microsoft.sqlserver.jdbc.SQLServerDriver”）。

这就是为什么解决方案 conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) 为我引发了 java.lang.AbstractMethodError 。
在这种情况下，我发现的一种可能的解决方案是微软建议的旧方案：
如何使用 JDBC 检索 @@IDENTITY 值

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

这个解决方案对我有用！

我希望这有帮助！

I'm using SQLServer 2008, 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").

That's why the solution conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) threw a java.lang.AbstractMethodError for me.
In this situation, a possible solution I found is the old one suggested by 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!

回复 原文
拥有 2024-08-22 15:45:07

您可以使用以下 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);
}

You can use following java code to get new inserted 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);
}
回复 原文
带刺的爱情 2024-08-22 15:45:07

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

It is possible to use it with normal Statement's as well (not just 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.");
  }
}
回复 原文
红墙和绿瓦 2024-08-22 15:45:07

使用 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。

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

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).

回复 原文
樱桃奶球 2024-08-22 15:45:07

大多数其他人建议为此使用 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）。

我在博客中详细介绍了此主题详细信息请参见此处。

Most others have suggested to use JDBC API for this, but personally, I find it quite painful to do with most drivers. When in fact, you can just use a native T-SQL feature, the OUTPUT clause:

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

This is the simplest solution for SQL Server as well as a few other SQL dialects (e.g. Firebird, MariaDB, PostgreSQL, where you'd use RETURNING instead of OUTPUT).

I've blogged about this topic more in detail here.

回复 原文
雨后彩虹 2024-08-22 15:45:07

就我而言 ->

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

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);
}
回复 原文
花辞树 2024-08-22 15:45:07

如果您使用的是Spring JDBC，则可以使用Spring的生成Keyolder类来获取插入的ID。

看到这个答案...
如何使用Spring jdbctemplate jdbctemplate in .update（字符串SQL，obj ... args）

If you are using Spring JDBC, you can use Spring's GeneratedKeyHolder class to get the inserted ID.

See this answer...
How to get inserted id using Spring Jdbctemplate.update(String sql, obj...args)

回复 原文
番薯 2024-08-22 15:45:07

如果您使用的是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;
}

请记住添加 connector/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;

希望这会有所帮助。

If you are using JDBC (tested with MySQL) and you just want the last inserted ID, there is an easy way to get it. The method I'm using is the following:

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

Also, (and just in case) the method to get the ConnectionImpl is the following:

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

Remember to add the connector/J to the project referenced libraries.

In my case, the connector/J version is the 5.1.42. Maybe you will have to apply some changes to the connectionPath if you want to use a more modern version of the connector/J such as with the version 8.0.28.

In the file, remember to import the following resources:

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

Hope this will be helpful.

回复 原文
话少情深 2024-08-22 15:45:07

您可以使用executeQuery（query）函数

，然后添加返回 *子句到查询
指定列名称

而是*您可以为示例

create表第一个

CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);

创建查询

val query: String = "INSERT INTO users (name) VALUES ("Bob"), ("Makr"), ("Adam") RETURNING id;"

应用查询

val statement = connection.prepareStatement(query)
val result = statement.executeQuery()
val keys = buildList {
    while (result.next()) { add(result.getLong(1)) }
}

键是您的结果。因此，您可以返回任何列集或完整对象

You can use executeQuery(query) function

and add RETURNING * clause to your query
instead * you can specify column name

For example

create table first

CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);

create query

val query: String = "INSERT INTO users (name) VALUES ("Bob"), ("Makr"), ("Adam") RETURNING id;"

apply query

val statement = connection.prepareStatement(query)
val result = statement.executeQuery()
val keys = buildList {
    while (result.next()) { add(result.getLong(1)) }
}

keys is your result. So you can return any column set or full object

回复 原文
东京女 2024-08-22 15:45:07

要获取 MSSQL 中表上最后插入的 ID，有多种选项：

我使用的是这个：

IDENT_CURRENT(‘TableName’)

还有一些其他选项，例如 SCOPE_IDENTITY() 和 @@IDENTITY也是如此，但上面的方法要容易得多。

示例：

String sql = "SELECT IDENT_CURRENT('TableName') AS ID;";
try ( ResultSet rs = con.execSQL (sql, sqlParameters)) {
    while (rs.next ()) {
        ret = rs.getInt ("ID");
    }
}
............

有关详细信息

我希望这有帮助......

To get the last inserted Id on a table in MSSQL there are various options:

What I use is this one:

IDENT_CURRENT(‘TableName’)

There are some other options like SCOPE_IDENTITY() and @@IDENTITY as well but the above is much easier.

Example:

String sql = "SELECT IDENT_CURRENT('TableName') AS ID;";
try ( ResultSet rs = con.execSQL (sql, sqlParameters)) {
    while (rs.next ()) {
        ret = rs.getInt ("ID");
    }
}
............

For more info check this out

I hope this helps...

回复 原文
万劫不复 2024-08-22 15:45:07
Connection cn = DriverManager.getConnection("Host","user","pass");
Statement st = cn.createStatement("Ur Requet Sql");
int ret  = st.execute();

Connection cn = DriverManager.getConnection("Host","user","pass");
Statement st = cn.createStatement("Ur Requet Sql");
int ret  = st.execute();
回复 原文
~没有更多了~

关于作者

清旖

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

留蓝

文章 0 评论 0

18790681156

文章 0 评论 0

zach7772

文章 0 评论 0

Wini

文章 0 评论 0

ayeshaaroy

文章 0 评论 0

初雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文