Problem with PostgreSQL - how to return id of row just written to database?

[Ivan Milosavljević]

Hi,

I use Dropwizard v0.7.1 and PostgreSQL v9.3 on Windows 7. My problem is that I can't find out value of auto-generated primary key of row I just created via createContact method.

In database I have table contact which is defined like this:
CREATE TABLE contact
(
  firstname character varying(255) NOT NULL,
  lastname character varying(255) NOT NULL,
  phone character varying(30) NOT NULL,
  id serial NOT NULL,
  CONSTRAINT "PK_CONTACT" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

In my app I have class ContactDAO with method for contact creation:
    @GetGeneratedKeys
    @SqlUpdate("insert into contact (firstName, lastName, phone) values (:firstName, :lastName, :phone) returning id")
    int createContact(@Bind("firstName") String firstName, @Bind("lastName") String lastName, @Bind("phone") String phone);

Class ContactResource has method:
    @POST
    public Response createContact(Contact contact) throws URISyntaxException {
        //store new contact
        int newContactId = contactDAO.createContact(contact.getFirstName(), contact.getLastName(), contact.getPhone());
        log.debug("newID=" + newContactId);
        return Response.created(new URI(String.valueOf(newContactId))).build();
    }

When I try to POST using cURL, I have following error in log file
ERROR [2014-10-21 17:11:37,639] io.dropwizard.jdbi.jersey.LoggingDBIExceptionMapper: Error handling a request: 5f9e618d1b5fd8b1
! org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"

That same INSERT INTO... RETURNING query executed from pgAdmin app passes without problem! I also tried to change annotation on createContact method like this
    @SqlUpdate("insert into contact (firstName, lastName, phone) values (:firstName, :lastName, :phone)")
but then I got this error (FOO2 is value of firstName parameter):
ERROR [2014-10-22 11:39:38,928] io.dropwizard.jdbi.jersey.LoggingDBIExceptionMapper: Error handling a request: dd53cc39a1e19b11
! org.postgresql.util.PSQLException: Bad value for type int : FOO2

Now, I'm fairly sure that what I'm trying to do is fairly common use case. Any help is welcome.

[Artem Prigoda]

Hi,

Actually, the problem is very simple. @GetGeneratedKeys is implemented at the JDBC-driver level by appending RETURNING * to the query.
So your query becomes: ''insert into contact (firstName, lastName, phone) values ($1, $2, $3) returning 'id' RETURNING *". Of course, the database returns an error.
 
You can fix it by removing RETURING clause and specifying a mapper for the generated id. Something like that:
   

    public static interface Updater {
        @GetGeneratedKeys(IdMapper.class)

        @SqlUpdate("insert into contact (firstName, lastName, phone) values (:firstName, :lastName, :phone)")

        int createContact(@Bind("firstName") String firstName, @Bind("lastName") String lastName, @Bind("phone") String phone);
    }

    public static class IdMapper implements ResultSetMapper<Integer> {
        @Override
        public Integer map(int index, ResultSet r, StatementContext ctx) throws SQLException {
            return r.getInt("id");
        }
    }

Artem

[Ivan Milosavljević]

Hi Artem,

it worked! Thank you so much. I didn't know it's possible to change mapper for @GetGeneratedKeys but it really is cool technique. Thanks oncer again!