java 从插入语句获取结果集

Getting resultset from insert statement

提问by Le_Master

i have the below code, where I'm inserting records to a table. When I try to get resultset, it returns null. How to get the latest added row into a resultset?

我有以下代码，我将记录插入到表中。当我尝试获取结果集时，它返回 null。如何将最新添加的行放入结果集中？

String sql1 = "INSERT INTO [xxxx].[dbo].[xxxxxx](WORKFLOW_SEQ_NBR," +
                      " WORKFLOW_LOG_TYPE_CODE, WORKFLOW_STATUS_CODE, DISP_CODE, DISP_USER, DISP_COMMENT, DISP_TITLE, DISP_TS)" +
                      "VALUES(?,?,?,?,?,?,?,?)";
        PreparedStatement pst = connect.prepareStatement(sql1);
        pst.setString(1, ...);
        pst.setString(2, ...);
        ...
        ...
        ...
        pst.executeUpdate();

        ResultSet rstest = pst.executeQuery();
//          ResultSet rstest = pst.getResultSet();

EDIT: Resolved

编辑：已解决

added following method to go to the last added row

添加了以下方法以转到最后添加的行

st.execute("Select * from [xxxx].[dbo].[xxxxxxxxx]");
        ResultSet rstest = st.getResultSet();
        rstest.afterLast();
        GETLASTINSERTED:
        while(rstest.previous()){
            System.out.println(rstest.getObject(1));
            break GETLASTINSERTED;//to read only the last row
        } 

回答by Pierre Rust

When using a SQL statement such as INSERT, UPDATEor DELETEwith a PreparedStatement, you must use executeUpdate, which will return the number of affeted rows. In this case there is simply no ResultSetproduced by the sql operation and thus calling executeQuery will throw a SQLException.

当使用一个SQL语句，例如INSERT，UPDATE或DELETE用PreparedStatement，您必须使用executeUpdate，这将返回affeted的行数。在这种情况下ResultSet，sql 操作根本就没有产生，因此调用 executeQuery 将抛出一个SQLException.

If you actually need a ResultSetyou must make another statement with a SELECTSQL operation.

如果您确实需要一个ResultSet，则必须使用SELECTSQL 操作创建另一个语句。

See the javadoc for PreparedStatement#executeQueryand PreparedStatement#executeUpdate

请参阅PreparedStatement#executeQuery和PreparedStatement#executeUpdate的 javadoc

回答by rasellers0

Seems like this is an older question, but i'm looking for a similar solution, so maybe people will still need this.

似乎这是一个较旧的问题，但我正在寻找类似的解决方案，所以也许人们仍然需要这个。

If you're doing an insert statement, you can use the : Connection.PreparedStatement(String, String[])constructor, and assign those to a ResultSetwith ps.getGeneratedKeys().

如果您正在执行插入语句，则可以使用 : Connection.PreparedStatement(String, String[])构造函数，并将它们分配给ResultSetwith ps.getGeneratedKeys()。

It would look something like this:

它看起来像这样：

public void sqlQuery() {
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection conn; //Assume this is a properly defined Connection
    String sql = "select example from examples";
    ps = conn.prepareStatement(sql, new String[]{"example"});
        //do anything else you need to do with the preparedStatement
    ps.execute;
    rs = ps.getGeneratedKeys();
    while(rs.next()){
        //do whatever is needed with the ResultSet
    }
     ps.close();
     rs.close();
}

回答by SparkOn

Connection#prepareStatement()- Creates a PreparedStatementobject for sending parameterized SQL statements to the database.

Connection#prepareStatement()- 创建一个PreparedStatement对象，用于将参数化的 SQL 语句发送到数据库。

which means connect.prepareStatement(sql1);created the PreparedStatementobject using your insert query.

这意味着使用您的插入查询connect.prepareStatement(sql1);创建了PreparedStatement对象。

and when you did pst.executeUpdate();it will return the row count for SQL Data Manipulation Language (DML) statements or 0 for SQL statements that return nothing

并且当您这样做时pst.executeUpdate();，它将返回 SQL 数据操作语言 (DML) 语句的行数或 0 为不返回任何内容的 SQL 语句

Now if you again want to fetch the data inserted you need to create a new PreparedStatementobject with Selectquery.

现在，如果您再次想要获取插入的数据，您需要创建一个PreparedStatement带有Select查询的新对象。

    PreparedStatement pstmt = connect.prepareStatement("SELECT * FROM tableName");

then this shall give you the ResultSetobject that contains the data produced by the query

那么这将为您提供ResultSet包含查询生成的数据的对象

    ResultSet rstest = pstmt.executeQuery();