Jdbc Insert And Get Generated Key
- Related Questions & Answers
- Selected Reading
- Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. A ResultSet object containing the auto-generated key(s). The driver will ignore the array if the SQL statement is not an INSERT statement.
- Nov 07, 2013 For a programmer getting auto generated id of a newly inserted row in table is a little bit tricky. In this page we will learn how the spring provides an easy way to get that. Spring provides KeyHolder which helps to get auto generated key. KeyHolder is supported by JDBC 3.0.
Here's a quick look at how to get the generated key from a MySQL database table after performing a SQL INSERT statement on a table that has an autoincrement field. (Some databases also refer to this as an identity field.).
If you insert records into a table which contains auto-incremented column, using a Statement or, PreparedStatement objects.
You can retrieve the values of that particular column, generated by them 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 −
Retrieving auto-generated values (PreparedStatement object)
Following JDBC program inserts 3 records into the Sales table (created above) using PreparedStatement, retrieves and displays the auto-incremented values generated by it.
Example
Output
Retrieving auto-generated values (Statement object)
Following JDBC program inserts 3 records into the Sales table (created above) using Statement, retrieves and displays the auto-incremented values generated by it.
Jdbc Insert And Get Generated Key Download
Output
-->Jdbc Insert And Return Primary Key
The Microsoft JDBC Driver for SQL Server supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers. The main value of this feature is to provide a way to make IDENTITY values available to an application that is updating a database table without a requiring a query and a second round-trip to the server.
Jdbc Insert Example
Because SQL Server doesn't support pseudo columns for identifiers, updates that have to use the auto-generated key feature must operate against a table that contains an IDENTITY column. SQL Server allows only a single IDENTITY column per table. The result set that is returned by getGeneratedKeys method of the SQLServerStatement class will have only one column, with the returned column name of GENERATED_KEYS. If generated keys are requested on a table that has no IDENTITY column, the JDBC driver will return a null result set.
As an example, create the following table in the sample database:
In the following example, an open connection to the sample database is passed in to the function, an SQL statement is constructed that will add data to the table, and then the statement is run and the IDENTITY column value is displayed.