Java Prepared Statement Get Generated Keys
- Related Questions & Answers
- An interface for a precompiled SQL Statement. An SQL Statement is put into a PreparedStatement and is precompiled so that it can be executed efficiently multiple times. Setter methods are supplied in the PreparedStatement interface for the setting of IN parameters for the statement.
- Statement.executeUpdate(sql-statement, Statement.RETURNGENERATEDKEYS);The following forms are valid only if the data source supports SELECT FROM INSERT statements. Sql-statement can be a single-row INSERT statement or a multiple-row INSERT statement.
- Return generated keys /. Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED. Use of this software is authorized pursuant to the terms of the license found at.
Then, you construct an INSERT statement with placeholders and create a new PreparedStatement object by calling the prepareStatement method of the Connection object. You pass the INSERT statement as the first argument and an integer with value Statement.RETURNGENERATEDKEYS as the the second argument to the method. The second argument. 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). This method should be used when the returned row count may exceed Integer.MAXVALUE.
- Selected Reading
While creating a table, in certain scenarios, we need values to column such as ID, to be generated/incremented automatically. Various databases support this feature in different ways.
In MySQL database you can declare a column auto increment using the following syntax.
While inserting records in a table there is no need to insert value under the auto-incremented column. These will be generated automatically.
For example, in a table if we have a column with name ID and data type INT, which is auto-incremented and, if we already have 6 records in that table. When you insert the next record using the INSERT statement the ID value of the new record will be 7 and the ID value of its next record will be 8.
(You can specify the initial value and interval for these auto-incremented columns).
Retrieving the auto-incremented values
If you insert records into a table which contains auto-incremented column, using a PreparedStatement object.
You can retrieve the values of that particular column, generated by the current PreparedStatement object using the getGeneratedKeys() method.
Example
Let us create a table with name sales in MySQL database, with one of the columns as auto-incremented, using CREATE statement as shown below −
Now, to insert records into this table using PreparedStatement object and, to retrieve the auto-incremented values generated by it −
- Register the Driver class of the desired database using the registerDriver() method of the DriverManager class or, the forName() method of the class named Class.
- Create a Connection object by passing the URL of the database, user-name and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.
- Create a PreparedStatement object using the prepareStatement() method of the connection interface.
To this method pass the INSERT statement with bind variables in string format as one parameter and, Statement.RETURN_GENERATED_KEYS as other parameter as − /generate-ssh-keys-in-linux.html.
- Set values of each record to the bind variables using the setXXX() methods and, add it to batch.
Java Prepared Statement Get Generated Keys In Windows 10
After adding values of all the records to the batch, execute the batch using the executeBatch() method.
- Finally, get the auto-incremented keys generated by this PreparedStatement object using the getGeneratedKeys() method.
Following JDBC program inserts 5 records into the Sales table (created above) using PreparedStatement, retrieves and displays the auto-incremented values generated by it.