Using auto-generated keys
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 autogenerated 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 AdventureWorks2022 sample database:
CREATE TABLE TestTable
(Col1 int IDENTITY,
Col2 varchar(50),
Col3 int);
In the following example, an open connection to the AdventureWorks2022 sample database is passed in to the function, a SQL statement is constructed that will add data to the table, and then the statement is run and the IDENTITY column value is displayed.
public static void executeInsertWithKeys(Connection con) {
try(Statement stmt = con.createStatement();) {
String SQL = "INSERT INTO TestTable (Col2, Col3) VALUES ('S', 50)";
int count = stmt.executeUpdate(SQL, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
do {
for (int i=1; i<=columnCount; i++) {
String key = rs.getString(i);
System.out.println("KEY " + i + " = " + key);
}
} while(rs.next());
}
else {
System.out.println("NO KEYS WERE GENERATED.");
}
}
// Handle any errors that may have occurred.
catch (SQLException e) {
e.printStackTrace();
}
}