Retrieving auto-generated keys for an INSERT statement. With the IBM® Data Server Driver for JDBC and SQLJ, you can use JDBC 3.0 methods to retrieve the keys that are automatically generated when you execute an INSERT statement. To retrieve automatically generated keys that are generated by an INSERT statement, you need to perform these steps. 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 to get a generated key is to execute statements one at a time using the following method signature.
WebLogic Type 4 JDBC Drivers
The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC SQL Server driver:
Note: The BEA WebLogic Type 4 JDBC MS SQL Server driver (the subject of this chapter) replaces the WebLogic jDriver for Microsoft SQL Server, which is deprecated. The new driver offers JDBC 3.0 compliance, support for some JDBC 2.0 extensions, and better performance. BEA recommends that you use the new BEA WebLogic Type 4 JDBC MS SQL Server driver in place of the WebLogic jDriver for Microsoft SQL Server.
The BEA WebLogic Type 4 JDBC MS SQL Server driver (the 'SQL Server driver') supports the following database management system versions:
To use JDBC distributed transactions through JTA, you must install stored procedures for SQL Server. See Installing Stored Procedures for JTA for details.
The driver classes for the BEA WebLogic Type 4 JDBC MS SQL Server driver are:
XA:
weblogic.jdbcx.sqlserver.SQLServerDataSource
Non-XA:
weblogic.jdbc.sqlserver.SQLServerDriver
To connect to a Microsoft SQL Server database, use the following URL format:
Microsoft SQL Server supports multiple instances of a SQL Server database running concurrently on the same server. An instance is identified by an instance name.
To connect to a named instance using a connection URL, use the following URL format:
Note: The first back slash character (
) in
instance_name is an escape character.
where:
server_name is the IP address or hostname of the server.
instance_name is the name of the instance to which you want to connect on the server.
For example, the following connection URL connects to an instance named instance1 on server1:
Table 5-1 lists the JDBC connection properties supported by the SQL Server driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC data source configuration:
Note: All connection string property names are case-insensitive. For example, Password is the same as password.
Table 5-1 SQL Server Connection Properties
Table 5-2 lists the data types supported by the SQL Server driver in SQL Server 7 and SQL Server 2000 and how they are mapped to the JDBC data types.
Table 5-2 Data Types Supported by SQL Server 7 and SQL Server 2000
Table 5-3 lists additional data types supported by SQL Server 2000 only.
Table 5-3 Addition Data Types Supported by SQL Server 2000
See GetTypeInfo for more information about data types.
See SQL Escape Sequences for JDBC, for information about the SQL escape sequences supported by the SQL Server driver.
The SQL Server driver supports the Read Committed, Read Uncommitted, Repeatable Read, and Serializable isolation levels. The default is Read Committed.
The SQL Server driver supports scroll-sensitive result sets, scroll-insensitive result sets, and updatable result sets.
Note: When the SQL Server driver cannot support the requested result set type or concurrency, it automatically downgrades the cursor and generates one or more SQLWarnings with detailed information.
In most cases, using server-side updatable cursors is faster, but server-side updatable cursors cannot be used with insensitive result sets or with sensitive result sets that do not have a primary key. By default, the MS SQL Server driver allows insensitive result sets and sensitive result sets that do not contain a primary key to be updatable. To use server-side cursors when an updatable result set is requested, you set the
UseServerSideUpdatableCursors property.
When the
UseServerSideUpdatableCursors property is set to true and a scroll-insensitive updatable result set is requested, the driver downgrades the request to a scroll-insensitive read-only result set. Similarly, when a scroll-sensitive updatable result set is requested and the table does not contain a primary key, the driver downgrades the request to a scroll-sensitive read-only result set. In either case, a warning is generated.
Jdbc Batch Insert Generated Keys Download
When server-side updatable cursors are used with sensitive result sets that contain a primary key, any changes you make to the result set are visible. Using the default behavior of the driver, those changes would not be visible.
To use JDBC distributed transactions through JTA, your system administrator should use the following procedure to install Microsoft SQL Server JDBC XA procedures. This procedure must be repeated for each MS SQL Server installation that will be involved in a distributed transaction.
To install stored procedures for JTA:
Note: If you are installing stored procedures on a database server with multiple Microsoft SQL Server instances, each running SQL Server instance must be able to locate the
sqljdbc.dll file. Therefore the sqljdbc.dll file needs to be anywhere on the global PATH or on the application-specific path. For the application-specific path, place the sqljdbc.dll file into the <drive>:Program FilesMicrosoft SQL ServerMSSQL$<Instance 1 Name>Binn directory for each instance.
Although Microsoft SQL Server does not define a Blob or Clob data type, the SQL Server driver allows you to retrieve and update long data, specifically LONGVARBINARY and LONGVARCHAR data, using JDBC methods designed for Blobs and Clobs. When using these methods to update long data as Blobs or Clobs, the updates are made to the local copy of the data contained in the Blob or Clob object.
Retrieving and updating long data using JDBC methods designed for Blobs and Clobs provides some of the same advantages as retrieving and updating Blobs and Clobs. For example, using Blobs and Clobs:
To provide these advantages of Blobs and Clobs, data must be cached. Because data is cached, you will incur a performance penalty, particularly if the data is read once sequentially. This performance penalty can be severe if the size of the long data is larger than available memory.
The SQL Server driver implementation for batch Inserts and Updates is JDBC 3.0 compliant. When the SQL Server driver detects an error in a statement or parameter set in a batch Insert or Update, it generates a BatchUpdateException and continues to execute the remaining statements or parameter sets in the batch. The array of update counts contained in the BatchUpdateException contain one entry for each statement or parameter set. Any entries for statements or parameter sets that failed contain the value Statement.EXECUTE_FAILED.
The SQL Server driver supports returning parameter metadata for the following forms of SQL:
where
operator is any of the following SQL operators: = , < , > , <= , >= , and <> .
The SQL Server driver supports retrieving the values of auto-generated keys. An auto-generated key returned by the SQL Server driver is the value of an identity column.
How you return those values depends on whether you are using an Insert statement that contains parameters:
![]()
The application fetches the values of generated keys from the driver using the Statement.getGeneratedKeys () method.
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.
Spring Jdbc Batch Insert
Next, you supply values for placeholders by calling
setYYY() method of the PreparedStatement object.
Spring Jdbc Batch Insert Generated Keys
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.
Jdbc Batch Insert Generated Keys Download
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.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |