How to execute composite sql queries in java?

Question

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:

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

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.

I don't think thats possible. Especially not with an INSERT and an SELECT. — Jacob
– Jacob, Commented Jul 21, 2011 at 8:54

zacheusz · Accepted Answer · 2011-07-21 09:06:31Z

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

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

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

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

Jasper · Accepted Answer · 2011-07-22 08:40:42Z

2

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 Statement class.

edited Jul 22, 2011 at 8:40

Jasper

2,1764 gold badges31 silver badges51 bronze badges

answered Jul 21, 2011 at 8:57

vstm

12.5k1 gold badge53 silver badges47 bronze badges

Comments

Jonas · Accepted Answer · 2011-07-21 08:56:11Z

1

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;

answered Jul 21, 2011 at 8:56

Jonas

131k103 gold badges330 silver badges408 bronze badges

2 Comments

tsds Over a year ago

Yes, is surely works, but my question is how to execute several queries at once

Jonas Over a year ago

@tsds: But two queries are always run as two queries.

Collectives™ on Stack Overflow

How to execute composite sql queries in java?

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related