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

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.

解决方案

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

}

}

}