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

Answer 1

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 ?";

Answer 2

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