CODEWITHSUNDEEP
javamysqljdbc
abksrv
abksrv

Reputation: 1405

Execute more than one statement at once in JDBC

I am using MySQL Database. The following piece creates a record and gets the id from the created record:


    insertStmt = connection
                    .prepareStatement("INSERT INTO bugs (summary, status, report_date) VALUES (?, ?, ? )");
            //...
    insertStmt.executeUpdate();
    idQuery = connection.prepareStatement("SELECT LAST_INSERT_ID()");
    rs = idQuery.executeQuery();
    if (rs != null) {
                rs.next();
                return new Long(rs.getLong(1)).toString();
    }

Now, if two threads execute this and their execution is interleaved, say, the first thread inserts the record followed by the insertion by the second thread, after which the first thread calls last_insert_id() which will be incorrect for this thread as the second thread has already inserted a record.
This might be overcome using synchronization, however. Is there a way we can execute the two statements in a single database call?

Upvotes: 1

Views: 2834

Answers (4)

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

LAST_INSERT_ID works per-connection, and as your question states you can have a race condition if two statements in two threads use the same connection.

You have two ways around this:

1: Use a separate connection per thread (not easy, but this is really the best option for scaling and sense; use connection pooling)

2: Use the form of executeUpdate that records the auto-generated key in the same API call, allowing you to read it back later using getGeneratedKeys so that you don't have to use LAST_INSERT_ID in a second query, so avoiding the race condition. There's a similar form of prepareStatement that you can use in prepared statements.

Option 2 is probably what you want in the short term. The link in option 2 goes straight to that API. This link is a MySQL article outlining how to use it.

Upvotes: 2

user330315
user330315

Reputation:

If you have to do this all on a single connection you can ask the driver to return the generated ID:

insertStmt = connection.prepareStatement("...",PreparedStatement.RETURN_GENERATED_KEYS );
insertStmt.executeUpdate();
ResultSet rs = insertStatement.getGeneratedKeys();
Long id = null;
if (rs != null) 
{
  rs.next();
  id = rs.getLong(1);
}
connection.commit();
return id;

Depending on the driver you might need a different prepareStatement() call that takes the column names as the second parameter:

insertStmt = connection.prepareStatement("INSERT ", new String[] {"ID"});

But even in with the above code you should be doing the concurrent inserts on different physical connections to be able to properly control your transactions.

Upvotes: 0

faster2b
faster2b

Reputation: 672

You can try using a Multiquery, combined the Insert and the Select Last_INSERT_ID() in the same string.

1) prepare the connection for using the multiquery: "jdbc:mysql://"+host+"/"+database+"?allowMultiQueries=true"

2) Combine The Insert Query with the Select:

multiQuerySqlString="INSERT INTO bugs (summary, status, report_date) VALUES (1, 2, 3 ); SELECT LAST_INSERT_ID()"

3) esecute the query and expecting multiple result sets:

boolean isResultSet = statement.execute();

ResultSet res = statement.getResultSet();

if isResultSet = statement.getMoreResults();

// Second ReulstSet object
res = cs.getResultSet();

I hope it works

Upvotes: 0

bchociej
bchociej

Reputation: 1399

According to https://dev.mysql.com/doc/refman/5.7/en/connector-j-reference-configuration-properties.html, you should be able to add ?allowMultiQueries=true to your JDBC connection string. Then you would be able to pass multiple statements, separated by semicolons, in Statement#execute(String sql) calls.

Edit: or, use a stored procedure that does what you want. Or, as you said, synchronize the Java code.

Upvotes: 0

Related Questions