Jdbc Batch Insert Get Generated Keys Spring
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.
Apr 13, 2007 I'm currently using a datareader to generate multiple calls to a stored procedure and batch execute them by means of a simple counter; this works fine. However, I'm a little concerned that it seems to take so long to execute the sql stored procs and was wondering if anyone may know of any methods to help speed it up either on the app side. Using auto generated keys.; 2 minutes to read +2; In this article. Download JDBC Driver. The Microsoft JDBC Driver for SQL Server supports the optional JDBC 3.0 APIs to retrieve automatically generated row identifiers.
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 ConnectionHalo 2 vista activation key generator. 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.
Spring-framework / spring-jdbc / src / main / java / org / springframework / jdbc / core / simple / SimpleJdbcInsertOperations.java. Specify the names of any columns that have auto generated keys. @param columnNames one or more column names. Execute a batch insert using the batch of values passed in.
Next, you supply values for placeholders by calling setYYY() method of the PreparedStatement object.
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.
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.
- Spring JDBC Tutorial
- Basic CRUD Examples
- Advanced JDBC Examples
- Spring JDBC Batch Examples
- Spring JDBC Objects
- Spring JDBC Useful Resources
- Selected Reading
Following example will demonstrate how to make a batch update using Spring JDBC. We'll update the available records in Student table in a single batch operation.
Jdbc Batch Insert Get Generated Keys Spring Break
Syntax
Where,
SQL − Update query to update student's age.
jdbcTemplateObject − StudentJDBCTemplate object to update student object in database. Generator test engineer key accomplishments.
BatchPreparedStatementSetter − Batch executor, set values in PerparedStatement per item identified by list of objects student and index i. getBatchSize() returns the size of the batch.
updateCounts − Int array containing updated row count per update query.
To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will update a batch operation. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application.
| Step | Description |
|---|---|
| 1 | Update the project Student created under chapter Spring JDBC - First Application. |
| 2 | Update the bean configuration and run the application as explained below. |
Following is the content of the Data Access Object interface file StudentDAO.java.
Following is the content of the Student.java file.
Following is the content of the StudentMapper.java file.
Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.
Following is the content of the MainApp.java file.
Jdbc Batch Insert Get Generated Keys Spring Break
Following is the configuration file Beans.xml.
Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message.