CODEWITHSUNDEEP
javasqloracle-databaseprepared-statementauto-increment
arnehehe
arnehehe

Reputation: 1398

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

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

Upvotes: 6

Views: 10221

Answers (5)

jmrodrigg
jmrodrigg

Reputation: 600

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.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231761

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

Upvotes: 10

Marcos
Marcos

Reputation: 4643

I think this :

pStatement.setString('blabla');

should be:

pStatement.setString(1, 'blabla');

Hope it helps.

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 691973

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 (?, ?)

Upvotes: 1

ControlAltDel
ControlAltDel

Reputation: 35096

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

Upvotes: 1

Related Questions