Prepared Statement Return Generated Keys

| 08-05-2021
[RAND-1-100] Comments

JDBC's auto-generated keys feature provides a way to retrieve valuesfrom columns that are part of an index or have a default value assigned. Derby supports the auto-incrementfeature, which allows users to create columns in tables for which the databasesystem automatically assigns increasing integer values. Users can call themethod Statement.getGeneratedKeys to retrieve the value of such a column.This method returns a ResultSet object with a column for theautomatically generated key. TheDerby implementation ofStatement.getGeneratedKeys returns meaningful results only if the laststatement was a single-row insert statement. If it was a multi-row insert,Statement.getGeneratedKeys will return a result set with only a singlerow, even though it should return one row for each inserted row.

When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically. The below example shows how to get this key after insert statement. After perfoming executeUpdate method on PreparedStatement, call getGeneratedKeys method on PreparedStatement. This query worked fine without the PreparedStatement.RETURNGENERATEDKEYS option, but when I add it suddenly executeUpdate throws an exception: com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update. If I take the PreparedStatement.RETURNGENERATEDKEYS out, it works again fine. Hi everyone, I need to insert a new record to a table in Mysql DB (using JDBC driver). After inserting is successful, I want to get the new ID number (it is a Primary key). Now I have problem using 'Statement.RETURNGENERATEDKEYS' in PreparedStatement. Describe how to use RETURNGENERATEDKEYS with prepared statements: Submitted: 24 Nov 2003 0:08: Modified: 24 Nov 2003 6:05: Reporter. But I tried to do this with my prepared statement and it appears that in prepared statements, the stmt.executeUpdate never has any arguments. First, I thought this may be only a documentation RFE, but. To this method pass the INSERT statement with bind variables in string format as one parameter and, Statement.RETURNGENERATEDKEYS as other parameter as. Finally, get the auto-incremented keys generated by this PreparedStatement object using the getGeneratedKeys method.

Calling ResultSet.getMetaData onthe ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData objectthat is similar to that returned by IDENTITY_VAL_LOCAL.

Users can indicate that auto-generated columns should be made availablefor retrieval by passing one of the following values as a second argumentto the Connection.prepareStatement, Statement.execute, or Statement.executeUpdate methods:

  • A constant indicating that auto-generated keys should be made available. The specific constant to use is Statement.RETURN_GENERATED_KEYS.
  • An array of the names of the columns in the inserted row that should be made available. If any column name in the array does not designatean auto-increment column, Derby will throw an error with the Derby embeddeddriver. With the client driver, the one element column name is ignored currently and the value returned corresponds to the identity column. To ensure compatibility with future changes an application should ensure the column described is the identity column. If the column name corresponds to another column or a non-existent column then future changes may result in a value for a different column being returned or an exception being thrown.
  • An array of the positions of the columns in the inserted row that shouldbe made available. If any column position in the array does not correlate to an auto-increment column, Derby willthrow an error with the Derby embeddeddriver. With the client driver, the one element position array is ignored currently and the value returned corresponds to the identity column. To ensure compatibility with future changes an application should ensure the column described is the identity column. If the position corresponds to another column or a non-existent column then future changes may result in a value for a different column being returned or an exception being thrown.
Statement

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. Some prepared statements return multiple results; the execute method handles these complex statements as well as the simpler form of statements handled. The driver will ignore the array if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys (the list of such statements is vendor-specific). In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts.

Example

Assume that we have a table TABLE1 definedas follows:

The following three code fragments will all do the same thing:that is, they will create a ResultSet that contains the value of C12 that is inserted into TABLE1.

Code fragment 1:

Code fragment 2:

Code fragment 3:

If there is no indication that auto-generated columns shouldbe made available for retrieval, a call to Statement.getGeneratedKeys will return a null ResultSet.

6.4 Retrieving AUTO_INCREMENT Column Values through JDBC

KeysPrepared statement return generated keys for free

Before version 3.0 of the JDBC API, there was no standard way of retrieving key values from databases that supported auto increment or identity columns. With older JDBC drivers for MySQL, you could always use a MySQL-specific method on the Statement interface, or issue the query SELECT LAST_INSERT_ID() after issuing an INSERT to a table that had an AUTO_INCREMENT key. Using the MySQL-specific method call isn't portable, and issuing a SELECT to get the AUTO_INCREMENT key's value requires another round-trip to the database, which isn't as efficient as possible. The following code snippets demonstrate the three different ways to retrieve AUTO_INCREMENT values. First, we demonstrate the use of the new JDBC 3.0 method getGeneratedKeys() which is now the preferred method to use if you need to retrieve AUTO_INCREMENT keys and have access to JDBC 3.0. The second example shows how you can retrieve the same value using a standard SELECT LAST_INSERT_ID() query. The final example shows how updatable result sets can retrieve the AUTO_INCREMENT value when using the insertRow() method.

Example 6.8 Connector/J: Retrieving AUTO_INCREMENT column values using Statement.getGeneratedKeys()


Prepared Statement Return Generated Keys 2017

Example 6.9 Connector/J: Retrieving AUTO_INCREMENT column values using SELECT LAST_INSERT_ID()


Example 6.10 Connector/J: Retrieving AUTO_INCREMENT column values in Updatable ResultSets


Java Preparedstatement Statement.return_generated_keys

Running the preceding example code should produce the following output:

Prepared Statement Return Generated Keys For Iphone

At times, it can be tricky to use the SELECT LAST_INSERT_ID() query, as that function's value is scoped to a connection. So, if some other query happens on the same connection, the value is overwritten. On the other hand, the getGeneratedKeys() method is scoped by the Statement instance, so it can be used even if other queries happen on the same connection, but not on the same Statement instance.