Bogus data in ResultSet

Question

I'm having some issues with my ResultSet using JDBC.

Here's my relation:

create table person (
person_id   number(5) generated always as identity
            minvalue 1
            maxvalue 99999
            increment by 1 start with 1
            cycle
            cache 10,
firstname   varchar(10) not null,
lastname    varchar(10) not null,
);

I'm trying to insert a (firstname, lastname) into the tuple and then get the person_id that comes out of it. Here's my JDBC code:

//connection is taken care of beforehand and is named con
prep = con.prepareStatement("insert into person (firstname, lastname) values (?, ?)", Statement.RETURN_GENERATED_KEYS);
        prep.setString(1, firstname);
        prep.setString(2, lastname);
        prep.execute();
        ResultSet generated = prep.getGeneratedKeys();
        if (generated.next()) {
            String key = generated.getString("0");
            System.out.println(key);
        }

This works all fine. But my problem is that the key should be an integer, not a String. Every time I run this, I get a ResultSet that contains a string of "AAA3vaAAGAAAFwbAAG", or something along those lines. I want to get the person_id so I can use it later in my Java program.

Is there something I'm doing wrong in regards to searching through the ResultSet or the execution of the statement itself?

Answer 1

tl;dr

int id = generated.getInt( 1 ) ;

Details

Your Question seems confused.

There are two forms of each get… method on ResultSet.

• Pass a column number (an int)
• Pass a column name (a String)

You seem to have combined the two into this:

String key = generated.getString( "0" ) ;

I doubt that you have a column named with a single digit zero. Besides being a poor choice of name, standard SQL forbids starting an identifier with a digit.

So that line makes no sense. Perhaps you meant the first column by using a zero 0 and mistakenly wrapped it in quotes, thereby transforming your intended int into an actual String.

Even that intention would be wrong. The ResultSet::getString documentation incorrectly describes the int as an “columnIndex”. Usually “index” means a zero-based counting offset. But actually ResultSet::getString( int ) requires you pass an ordinal number with counting starting at one. So getString( 0 ) is never valid.

So if you want to retrieve the value of your result set’s first column as text, do this:

String key = generated.getString( 1 ) ; // Retrieve first column of result set as text.

Yet again, this would be wrong in the context of your code. You are apparently attempting to retrieve the primary key values being generated during the INSERT. Your primary key column person_id is defined as number(5) which is not a textual type. So retrieving as a String is not appropriate.

NUMBER(5) is not standard SQL. If you happen to be using Oracle database, the doc says that would be an integer type with a precision of five, meaning numbers with up to five digits. So retrieve that as a integer type in Java by calling ResultSet::getInt.

int id = generated.getInt( 1 ) ;  // Retrieve the new row’s ID from the first column of the result set of generated key values returned by the `INSERT` prepared statement.

My comments above are for databases in general. But for Oracle specifically, see the Answer by Mark Rotteveel explaining that Oracle database does not return the generated sequence number when calling getGeneratedKeys. Instead it returns ROWID pseudo-column.

Answer 2

Your problem is that Oracle by default returns the ROWID of the inserted record, and not the generated identifier. From Oracle JDBC Developer's Guide: Retrieval of Auto-Generated Keys:

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieve other columns.

So, if you use Statement.RETURN_GENERATED_KEYS, you'll get the ROWID, and you can then use that ROWID to select the inserted row to obtain the other values (including the generated identifier).

If you want to specifically retrieve the generated id, for Oracle you'll need to explicitly ask for that column as follows:

String[] columns = { "PERSON_ID" }
prep = con.prepareStatement(
        "insert into person (firstname, lastname) values (?, ?)", columns);
prep.setString(1, firstname);
prep.setString(2, lastname);
prep.executeUpdate();
ResultSet generated = prep.getGeneratedKeys();
if (generated.next()) {
    int key = generated.getInt("PERSON_ID");
    System.out.println(key);
}