Menu
Arthur Branham opened SPR-1836 and commented I realize that generated keys are not returned in a batchUpdate even if multiple insert statements are executed for a table with an autoincrement column. Currently in JDBCTemplate the only way. Apr 13, 2007 JDBC Batch Insert + GetGeneratedKeys Apr 13, 2007. I'm using MS JDBC driver to connect to SQLServer 2005 and trying to perform batch insert. Here is the code i'm using. Mysql jdbc batch inserts and updates into different tables. Greenhorn Posts: 28. Posted 4 years ago. Because the table-per-table option is probably the easiest solution to insert all records in batch. I think Oracles RETURNING mechanism is what its JDBC driver uses for the generated keys functionality, under the hood.
- Related Questions & Answers
- 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.
Using cmd to generate ssh key. 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.
Jdbc Batch Insert Generated Keys 2017
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 −
![Jdbc Batch Insert Generated Keys Jdbc Batch Insert Generated Keys](https://dzone.com/storage/temp/12771851-batch-inserts-including-associations-no-order-of-i.png)
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 −
- 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.
- 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
In this tutorial, you will learn how to use PreparedStatement object to insert data into MySQL table.
In the previous tutorial, we have shown you how to use the PreparedStatement object to update data. When you call the
executeUpdate()
method, you get the number of rows affected. When you insert a record into a table, you may want to get the inserted ID back to the program for further processing. Let’s see how we can do it.First, as always, you open a new connection to MySQL. You can utilized the utility class
MySQLJDBCUtil
that we developed in the previous tutorial.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.RETURN_GENERATED_KEYS
as the the second argument to the method. The second argument instructs JDBC to give the inserted ID back.Next, you supply values for placeholders by calling
setYYY()
method of the PreparedStatement
object.Jdbc Batch Insert Generated Keys Download
After that, you call the
executeUpdate()
method to execute the INSERT
statement. This method returns the number of rows affected. We check the return value to see if the record has been inserted successfully.Finally, to get the inserted id, you call the
getGeneratedKeys()
method of the PreparedStatement
object. The method returns a ResultSet
. You just need to get data out of this ResultSet
as follows:The following is the complete example of inserting data into the
candidates
table and get the inserted ID back.Let’s run the program.
It shows that you have successfully inserted a new candidate into the
candidates
table with id 134.Jdbc Batch Query
In this tutorial, we have shown you how to use PreparedStatement object to insert a new record into a MySQL table and get the inserted ID back for further processing.