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.
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.
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. Method that provides execution of the insert using the passed-in Map of parameters and returning all generated keys. Parameters: args - a Map with parameter names and values to be used in insert. 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. 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.
SQL − Update query to update student's age.
jdbcTemplateObject − StudentJDBCTemplate object to update student object in database.
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.
|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.
Following is the configuration file Beans.xml.
Jdbc Batch Size
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.