- Related Questions & Answers
- 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.
MySQL Connector/J 8.0 Developer Guide / JDBC Concepts / Retrieving AUTOINCREMENT Column Values through JDBC 7.4 Retrieving AUTOINCREMENT Column Values through JDBC getGeneratedKeys is the preferred method to use if you need to retrieve AUTOINCREMENT keys and through JDBC; this is illustrated in the first example below. How to Retrieve Automatically Generated Keys in JDBC? You can retrieve automatically generated keys (also called auto-generated keys or auto increment) from a table using JDBC 3.0 methods getGeneratedKeys.The getGeneratedKeys provide a standard way to make auto-generated or identity column values available to an application that is updating a database table without a requiring a query. To get the generated key from a MySQL database table, just use the MySQL LASTINSERTID function, calling it as shown below, immediately after performing your INSERT command (and very importantly, also using the same connection): SELECT LASTINSERTID; Here's a blurb from this MySQL page. LASTINSERTID (with no argument) returns the first automatically generated value that was set for an AUTOINCREMENT column by the most recently executed INSERT statement to affect such a column.
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 generated by the Statement object
If you insert records into a table which contains auto-incremented column, using a Statement object.
You can retrieve the values of that particular column, generated by the current Statement object using the getGeneratedKeys()/sonic-foundry-acid-pro-40-key-generator.html. 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 multiple records into this table by executing the multiple-row INSERT statement using Statement 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 Statement object using the createStatement() method of the connection interface.
- Execute the multi-row INSERT query using the executeUpdate() method.To this method pass the multi-row INSERT statement in string format as one parameter and, Statement.RETURN_GENERATED_KEYS as other parameter as −
- 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 Statement, retrieves and displays the auto-incremented values generated by it.