CODEWITHSUNDEEP
sqljdbc
user978404
user978404

Reputation:

JDBC getGeneratedKeys()

I obtained the following code example from http://docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/preparedstatement.html

I have three questions:

  1. What does 'keyColumn' refer to considering that there are only three columns - LAST, FIRST, HOME.
  2. Why is a loop used in iterating the generated keys? Are multiple rows returned for that one insert statement?
  3. Which databases support multiple generated keys per table? 
String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
                                     "(?, ?, ?, keyColumn)";

PreparedStatement addAuthor = con.prepareStatement(sql,
                             Statement.RETURN_GENERATED_KEYS);
addAuthor.setString(1, "Wordsworth");
addAuthor.setString(2, "William");
addAuthor.setString(3, "England");

int rows = addAuthor.executeUpdate();

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
    ResultSetMetaData rsmd = rs.getMetaData();
    int colCount = rsmd.getColumnCount();
    do {
        for (int i = 1; i <= colCount; i++) {
            String key = rs.getString(i);
            System.out.println("key " + i + "is " + key);
        }
    }
    while (rs.next();)
} 
else {
    System.out.println("There are no generated keys.");
}

Upvotes: 1

Views: 4758

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108971

Question 1. I think that keyColumn in the query on your link is simply an error in the example. The second and third example in that paragraph also contain serious syntax errors. I wouldn't dwell on it. This documentation has been removed entirely from more recent Java versions.

Question 2. Think about statements like INSERT INTO ... SELECT ... FROM ... or INSERT INTO ... VALUES (...), (...) which can produce multiple inserted rows. Also some database support returning values from other DML (eg UPDATE or DELETE), which can also affect multiple rows, that is why you need to consider a loop. In this specific example it is not necessary as you can be sure only one row will be inserted.

Question 3. This question is a bit more complex:

Some databases (or drivers) can't easily decide what is the actual generated column. For example because the database doesn't have IDENTITY columns, but use triggers to generate keys. Identifying generated keys would involve parsing all triggers on a table to check if it assigns a generated value to a (primary key or other) column, which is not easily done and would be error prone. And sometimes there are multiple generated columns (ie computed fields etc). You as the developer should know what fields you can or want to get back.

As it is hard (or inefficient) to decide which fields to return, some drivers (by default) return all columns of the inserted (or deleted/updated) row. For example the PostgreSQL and Firebird drivers do that. On the other hand some drivers just return the last-generated key even if the table does not contain an identity column (I believe MySQL does, not 100% sure though). And I seem to remember that the Oracle driver simply returns the ROWID, leaving it up to the user to retrieve actual values from the database using that ROWID.

If you specifically know which columns you want returned, you can specify that yourself using the alternative methods that accept an array of column ordinal indices or column names. Although again not all drivers support that.

Upvotes: 3

Related Questions