JDBC query RETURNING value

Question

I have a problem trying to figure out how to get the id of the last inserted row using PostgreSQL and JDBC.

    CREATE TABLE vet_care (
        id serial PRIMARY KEY,
        deworming int,
        castration int
    );

My query is

String insertVetCare = "INSERT INTO vet_care(castration,deworming) VALUES("+castrated+","+dewormed+") RETURNING id";

I want the id value (which is serial) for later use. I tried to execute the query like so:

int id = statement.executeUpdate(insertVetCare);

But this says after compilation, that "A result was returned when none was expected." and it does not insert the other values into table.

How can I get this to work?

Please don't do string concatenation like this. It's a bad habit, even if `castrated` and `dewormed` are boolean or integer and thus safe in this case. SQL injection ahoy. — Craig Ringer, Apr 19 '15 at 14:07

score 3 · Answer 1 · edited May 23 '17 at 11:43

If "id" means "generated identity key", then you've got it wrong.

The executeUpdate() method returns the number of affected rows, according to the javadocs.

You want it to return auto generated keys, like this.

More advice: Use PreparedStatement and bind values. Building up a SQL query that way is asking for a SQL injection attack.

// Why make the gc work?  This query never changes.
private static final String INSERT_VET_CARE = "INSERT INTO vet_care(castration,deworming) VALUES(?, ?)";


PreparedStatement ps = connection.prepareStatement(INSERT_VET_CARE,  Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, castration);
ps.setInt(2, deworming);

JDBC query RETURNING value

1 Answers1