Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PreparedStatement with Statement.RETURN_GENERATED_KEYS

Tags:

java

jdbc

The only way that some JDBC drivers to return Statement.RETURN_GENERATED_KEYS is to do something of the following:

long key = -1L;
Statement statement = connection.createStatement();
statement.executeUpdate(YOUR_SQL_HERE, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
if (rs != null && rs.next()) {
    key = rs.getLong(1);
}

Is there a way to do the same with PreparedStatement?


Edit

The reason I asked if I can do the same with PreparedStatement consider the following scenario:

private static final String SQL_CREATE = 
            "INSERT INTO
            USER(FIRST_NAME, MIDDLE_NAME, LAST_NAME, EMAIL_ADDRESS, DOB) 
            VALUES (?, ?, ?, ?, ?)";

In the USER table there's a PRIMARY KEY (USER_ID) which is a BIGINT AUTOINCREMENT (hence why you don't see it in the SQL_CREATE String.

Now, I populate the ? using PreparedStatement.setXXXX(index, value). I want to return ResultSet rs = PreparedStatement.getGeneratedKeys(). How can I achieve this?

like image 355
Buhake Sindi Avatar asked Nov 19 '10 10:11

Buhake Sindi


People also ask

What is Return_generated_keys?

RETURN_GENERATED_KEYS parameter, the data type of the automatically generated keys in the ResultSet is DECIMAL, regardless of the data type of the corresponding column.

What is Statement and PreparedStatement?

Both Statement and PreparedStatement can be used to execute SQL queries. These interfaces look very similar. However, they differ significantly from one another in features and performance: Statement – Used to execute string-based SQL queries. PreparedStatement – Used to execute parameterized SQL queries.

What is generated keys in JDBC?

JDBC's auto-generated keys feature provides a way to retrieve values from columns that are part of an index or have a default value assigned. Derby supports the auto-increment feature, which allows users to create columns in tables for which the database system automatically assigns increasing integer values.

Why do we use PreparedStatement instead of Statement?

1. PreparedStatement in Java allows you to write a parameterized query that gives better performance than the Statement class in Java. 2. In the case of PreparedStatement, the Database uses an already compiled and defined access plan, this allows the prepared statement query to run faster than a normal query.


3 Answers

You can either use the prepareStatement method taking an additional int parameter

PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)

For some JDBC drivers (for example, Oracle) you have to explicitly list the column names or indices of the generated keys:

PreparedStatement ps = con.prepareStatement(sql, new String[]{"USER_ID"})
like image 171
Jörn Horstmann Avatar answered Oct 19 '22 11:10

Jörn Horstmann


You mean something like this?

long key = -1L;

PreparedStatement preparedStatement = connection.prepareStatement(YOUR_SQL_HERE, PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.setXXX(index, VALUE);
preparedStatement.executeUpdate();

ResultSet rs = preparedStatement.getGeneratedKeys();

if (rs.next()) {
    key = rs.getLong(1);
}
like image 71
nanda Avatar answered Oct 19 '22 11:10

nanda


Not having a compiler by me right now, I'll answer by asking a question:

Have you tried this? Does it work?

long key = -1L;
PreparedStatement statement = connection.prepareStatement();
statement.executeUpdate(YOUR_SQL_HERE, PreparedStatement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
if (rs != null && rs.next()) {
    key = rs.getLong(1);
}

Disclaimer: Obviously, I haven't compiled this, but you get the idea.

PreparedStatement is a subinterface of Statement, so I don't see a reason why this wouldn't work, unless some JDBC drivers are buggy.

like image 10
darioo Avatar answered Oct 19 '22 11:10

darioo