Επεξεργασία

Using auto-generated keys

Download JDBC driver

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 pseudocolumns for identifiers, updates that 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 returned by the getGeneratedKeys method of the SQLServerStatement class has only one column, with the column name GENERATED_KEYS. Starting with JDBC Driver 13.6, if generated keys are requested for a statement that doesn't generate an IDENTITY value, the driver returns a non-null, empty result set and ResultSet.next() returns false.

As an example, create the following table in the AdventureWorks2025 sample database:

CREATE TABLE TestTable
   (Col1 int IDENTITY,
    Col2 varchar(50),
    Col3 int);

In the following example, an open connection to the AdventureWorks2025 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();
    }
}

Related content

  • Last updated on