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.