java 如何在java中执行复合sql查询？

How to execute composite sql queries in java?

提问by tsds

How can I execute the following query and retrieve a result via prepared statement:

如何执行以下查询并通过准备好的语句检索结果：

INSERT INTO vcVisitors (sid) VALUES (?); SELECT LAST_INSERT_ID();

Is there a way to execute both two statements at once?

有没有办法同时执行两个语句？

I've tried to do the following:

我尝试执行以下操作：

Connection con = DbManager.getConnection();
PreparedStatement ps = con.PrepareStatement(
     "INSERT INTO vcVisitors (sid) VALUES (?); SELECT LAST_INSERT_ID();");
ps.setInt(1, 10);
ResultSet rs = ps.exequteQuery();
rs.next();
return rs.getInt("LAST_INSERT_ID()");

but it gives me an error that executeQuery can't execute such a query, I've also tried to replace executeQuery by the following:

但它给了我一个错误，即 executeQuery 无法执行这样的查询，我还尝试用以下内容替换 executeQuery：

ps.execute();
rs = ps.getResultSet();

but it gives me SQL syntax error:

但它给了我 SQL 语法错误：

You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'SELECT LAST_INSERT_ID()' at line 1

but there are no problems with executing query "INSERT INTO vcVisitors (sid) VALUES ('10'); SELECT LAST_INSERT_ID();"directly from mysql console.

但是执行查询“INSERT INTO vcVisitors (sid) VALUES ('10'); SELECT LAST_INSERT_ID();”没有问题 直接从mysql控制台。

采纳答案by zacheusz

While updating (inserting) data use executeUpdateinstead of executeQuery. Try executing SELECT LAST_INSERT_ID()as another query.

在更新（插入）数据时使用executeUpdate而不是executeQuery. 尝试SELECT LAST_INSERT_ID()作为另一个查询执行。

But it is not portable query. I suggest using Statement.getGeneratedKeysinstead. Please look here: JDBC (MySQL) Retrieving AUTO_INCREMENT Column Values.

但它不是便携式查询。我建议Statement.getGeneratedKeys改用。请看这里：JDBC (MySQL) Retrieving AUTO_INCREMENT Column Values。

Here is an example of properly used LAST_INSERT_ID():

以下是正确使用 LAST_INSERT_ID() 的示例：

    Statement stmt = null;
   ResultSet rs = null;

   try {

    //
    // Create a Statement instance that we can use for
    // 'normal' result sets.

    stmt = conn.createStatement();

    //
    // Issue the DDL queries for the table for this example
    //

    stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
    stmt.executeUpdate(
            "CREATE TABLE autoIncTutorial ("
            + "priKey INT NOT NULL AUTO_INCREMENT, "
            + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

    //
    // Insert one row that will generate an AUTO INCREMENT
    // key in the 'priKey' field
    //

    stmt.executeUpdate(
            "INSERT INTO autoIncTutorial (dataField) "
            + "values ('Can I Get the Auto Increment Field?')");

    //
    // Use the MySQL LAST_INSERT_ID()
    // function to do the same thing as getGeneratedKeys()
    //

    int autoIncKeyFromFunc = -1;
    rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");

    if (rs.next()) {
        autoIncKeyFromFunc = rs.getInt(1);
    } else {
        // throw an exception from here
    }

    rs.close();

    System.out.println("Key returned from " +
                       "'SELECT LAST_INSERT_ID()': " +
                       autoIncKeyFromFunc);

} finally {

    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException ex) {
            // ignore
        }
    }

    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException ex) {
            // ignore
        }
    }
}

and here the same with getGeneratedKeys:

在这里与 getGeneratedKeys 相同：

     Statement stmt = null;
   ResultSet rs = null;

   try {

    //
    // Create a Statement instance that we can use for
    // 'normal' result sets assuming you have a
    // Connection 'conn' to a MySQL database already
    // available

    stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                java.sql.ResultSet.CONCUR_UPDATABLE);

    //
    // Issue the DDL queries for the table for this example
    //

    stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
    stmt.executeUpdate(
            "CREATE TABLE autoIncTutorial ("
            + "priKey INT NOT NULL AUTO_INCREMENT, "
            + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

    //
    // Insert one row that will generate an AUTO INCREMENT
    // key in the 'priKey' field
    //

    stmt.executeUpdate(
            "INSERT INTO autoIncTutorial (dataField) "
            + "values ('Can I Get the Auto Increment Field?')",
            Statement.RETURN_GENERATED_KEYS);

    //
    // Example of using Statement.getGeneratedKeys()
    // to retrieve the value of an auto-increment
    // value
    //

    int autoIncKeyFromApi = -1;

    rs = stmt.getGeneratedKeys();

    if (rs.next()) {
        autoIncKeyFromApi = rs.getInt(1);
    } else {

        // throw an exception from here
    }

    rs.close();

    rs = null;

    System.out.println("Key returned from getGeneratedKeys():"
        + autoIncKeyFromApi);
} finally {

    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException ex) {
            // ignore
        }
    }

    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException ex) {
            // ignore
        }
    }
}

回答by vstm

As others have already said, this won't work. But it looks like you just want to get the ID of the last insert. To achieve that, you can use the getGeneratedKeys()method of the Statementclass.

正如其他人已经说过的那样，这行不通。但看起来您只想获取最后一次插入的 ID。为此，您可以使用类的getGeneratedKeys()方法Statement。

回答by Jonas

Try with:

尝试：

Connection con = DbManager.getConnection();
int lastid = 0;

PreparedStatement psInsert = con.PrepareStatement(
     "INSERT INTO vcVisitors (sid) VALUES (?)");
psInsert.setInt(1, 10);
psInsert.executeUpdate();

PreparedStatement psSelect = 
    con.PrepareStatement("SELECT LAST_INSERT_ID() AS lastid");
ResultSet rs = psSelect.executeQuery();
rs.next();

lastid = rs.getInt("lastid");

rs.close();
psInsert.close();
psSelect.close();
con.close();
return lastid;