Unable to find a generated key in Java using PreparedStatement's getGeneratedKeys()

Question

I have a query as follows:

String SQL = "insert into table (id, name) values (sequence.nextval, ?)";

I then make a PreparedStatement like this:

//initiate connection, statement etc
pStatement = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
pStatement.setString(1,'blabla');

pStatement.executeUpdate();
ResultSet rs = pStatement.getGeneratedKeys();

while (rs.next()){
  //debugging here to see what rs has
}

When executing and debugging at that debug point, I see my ResultSet only has one key, a string - not like the id I expect at all. When checking the database everything works fine, the id's get inserted and everything. There's something about the getGeneratedKeys(); that's confusing me.

What am I doing wrong?

Thanks in advance

You are not using 'generated keys', you are just using a generator in your INSERT statement. `getGeneratedKeys()` is used for returning keys generated by the DB as part of the INSERT (by an identity column or through a trigger). — Mark Rotteveel, Apr 03 '12 at 20:46

score 10 · Accepted Answer · answered Apr 03 '12 at 18:18

I expect that the "key" you're getting back that looks like a string is the ROWID-- that's the only key that the database is directly generating. You should be able to change that to get your id column back (this probably requires a moderately recent version of the JDBC driver).

//initiate connection, statement etc
String generatedColumns[] = {"ID"};
pStatement = connection.prepareStatement(SQL, generatedColumns);
pStatement.setString(1,'blabla');

pStatement.executeUpdate();
ResultSet rs = pStatement.getGeneratedKeys();

while (rs.next()){
  //debugging here to see what rs has
}

You could also change your query to explicitly add the RETURNING clause

String SQL = "insert into table (id, name) " + 
             "  values (sequence.nextval, ?) " + 
             "  returning id into ?";

Thank you. I have opted for your first option to include generatedColumns. Indeed, my ResultSet first gave me some row I didn't understand but now that I pass along the string[] {"id"} returns the newly generated id. — arnehehe, Apr 03 '12 at 18:30

score 1 · Answer 2 · answered Apr 03 '12 at 18:07

1

If this is not working, the problem could be with sequence.nextval. It seems like if you're using that, you are not technically autogenerating the key

answered Apr 03 '12 at 18:07

ControlAltDel

33,923
10
53
80

score 1 · Answer 3 · answered Apr 03 '12 at 18:08

I'm pretty sure that getGeneratedKeys won't return the value of a key that was initialized with the next value of a sequence. Indeed, in this case, the database doesn't generate the key by itself (like it would with an auto-increment column).

If you want to know the generated key, then execute a first query:

select sequence.nextval from dual

and then use the result of this first query to execute your prepared statement:

insert into table (id, name) values (?, ?)

score 1 · Answer 4 · answered Apr 03 '12 at 18:08

1

I think this :

pStatement.setString('blabla');

should be:

pStatement.setString(1, 'blabla');

Hope it helps.

answered Apr 03 '12 at 18:08

Marcos

4,643
7
33
60

Sorry - you are right. This is my fault for not copy pasting my code here but trying to type the example in more general terms to make the code more easily readable. In my code, however, I have done it correctly. So while you are correct this isn't the problem I'm struggling with. I have edited my post. – arnehehe Apr 03 '12 at 18:09
@arnehehe you're right, you can compile your code so that's not the error. My fault. – Marcos Apr 03 '12 at 18:11

jmrodrigg · Answer 5 · 2012-11-30T08:46:37.973

Your code has an error: Since you are using PreparedStatement, you should use its own RETURN_GENERATED_KEYS constant:

pStatement = connection.prepareStatement(SQL, PreparedStatement.RETURN_GENERATED_KEYS);

The getGeneratedKeys() function should be executed without issues, and you should be able to get the keys generated in the ResultSet variable.

By this way you don't need to specify any name for your row (as Justin solution suggests, which is pretty good). You only need to specify your row name if you access the retrieved keys using:

id = rs.getInt("id_row_name");

instead of:

id = rs.getInt(column_number); //One column for each key retrieved.

Unable to find a generated key in Java using PreparedStatement's getGeneratedKeys()

5 Answers5