Menu
- Mysql Jdbc Driver
- Return Auto Generated Key Query Jdbc Mysql Java Update
- Return Auto Generated Key Query Jdbc Mysql Java Download
- Related Questions & Answers
When we insert a record in a table that contains an auto increment column then we can easily obtain its id (primary key). Below example will show you how you can get the id of last inserted record in MySQL using Java (JDBC). The ResultSet that is returned by getGeneratedKeys method will have only one column, with the returned column name of GENERATEDKEYS. If generated keys are requested on a table that has no auto increment column, the JDBC driver will return a null result set. When you insert rows. Returning generated keys in MySql with JDBC PreparedStatement duplicate This question already has an answer here: I'm programming with plain JDBC a DAO layer because I only have 61.38 MB on Java Memory in my Tomcat (service hosting). I have a table with an AUTOINCREMENT column in MySQL. Spring JDBC FAQ: How do I retrieve the auto-generated database key for a serial field (also known as an autoincrement field in MySQL) after I perform a SQL INSERT using Spring JDBC? (I'm not phrasing that well, but by this question I mean the value of the primary key for the record I just inserted. 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). Or an SQL statement able to return auto-generated keys (the list of such statements is vendor-specific).
- Selected Reading
JDBCObject Oriented ProgrammingProgramming
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 −
Mysql Jdbc Driver
- Set values of each record to the bind variables using the setXXX() methods and, add it to batch.
After adding values of all the records to the batch, execute the batch using the executeBatch() method.
Return Auto Generated Key Query Jdbc Mysql Java Update
- 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.
Example
Output
7.4 Retrieving AUTO_INCREMENT
Column Values through JDBC
getGeneratedKeys()
is the preferred method to use if you need to retrieve AUTO_INCREMENT
keys and through JDBC; this is illustrated in the first example below. 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 7.8 Connector/J: Retrieving
AUTO_INCREMENT
column values using Statement.getGeneratedKeys()
Example 7.9 Connector/J: Retrieving
AUTO_INCREMENT
column values using SELECT LAST_INSERT_ID()
Example 7.10 Connector/J: Retrieving
AUTO_INCREMENT
column values in Updatable ResultSets
Running the preceding example code should produce the following output:
Return Auto Generated Key Query Jdbc Mysql Java Download
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. Games for windows live key generator download.