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?
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.
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.
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.
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.
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"})
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);
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With