如何在Java中执行复合SQL查询? [英] How to execute composite sql queries in java?

问题描述

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

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?

我尝试执行以下操作:

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

但是它给我一个错误，executeQuery无法执行这样的查询， 我还尝试用以下命令替换executeQuery:

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

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

但是它给了我SQL语法错误:

but it gives me SQL syntax error:

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.

推荐答案

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

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

但这不是可移植查询.我建议改用Statement.getGeneratedKeys.请在此处查看: JDBC(MySQL)检索AUTO_INCREMENT列值.

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

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

Here is an example of properly used 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
        }
    }
}

，这里与getGeneratedKeys相同:

and here the same with 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
        }
    }
}

这篇关于如何在Java中执行复合SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！