jdbi return autogenerated value on inserts

Question

I am playing with dropwizard and I want to build a REST application that has various foreign-key relations in the entities.

For example given the following 3 tables:

-- table persons
CREATE TABLE PUBLIC.PERSONS(
    ID BIGINT DEFAULT NOT NULL AUTOINCREMENT,
    FIRST_NAME VARCHAR(255),
    LAST_NAME VARCHAR(255),
    BIRTHDAY DATE,
    ADDRESS_ID BIGINT NOT NULL,
    CREATED TIMESTAMP DEFAULT 'current_timestamp',
    MODIFIED TIMESTAMP
);
ALTER TABLE PUBLIC.PERSONS ADD CONSTRAINT PUBLIC.PK_PERSONS PRIMARY KEY(ID);

-- table customers
CREATE TABLE PUBLIC.CUSTOMERS(
    ID BIGINT DEFAULT NOT NULL AUTOINCREMENT,
    PERSON_ID BIGINT NOT NULL,
    STATUS_CODE VARCHAR(100) DEFAULT 'ACQUISITION' NOT NULL,
    CREATED TIMESTAMP DEFAULT 'current_timestamp',
    MODIFIED TIMESTAMP
);
ALTER TABLE PUBLIC.CUSTOMERS ADD CONSTRAINT PUBLIC.PK_CUSTOMERS PRIMARY KEY(ID);

-- table addresses
CREATE TABLE PUBLIC.ADDRESSES(
    ID BIGINT DEFAULT NOT NULL AUTOINCREMENT,
    LINE_1 VARCHAR(255),
    LINE_2 VARCHAR(255),
    ZIP VARCHAR(255),
    CITY VARCHAR(255),
    COUNTRY_CODE VARCHAR(3),
    PHONE VARCHAR(255),
    FAX VARCHAR(255),
    CREATED TIMESTAMP DEFAULT 'current_timestamp',
    MODIFIED TIMESTAMP
);
ALTER TABLE PUBLIC.ADDRESSES ADD CONSTRAINT PUBLIC.PK_ADDRESSES PRIMARY KEY(ID);

-- and following forign key constraints:
ALTER TABLE PUBLIC.PERSONS ADD CONSTRAINT 
    PUBLIC.FK_PERSON_ADDRESS FOREIGN KEY(ADDRESS_ID) 
    REFERENCES PUBLIC.ADDRESSES(ID) ON DELETE SET NULL NOCHECK;

ALTER TABLE PUBLIC.CUSTOMERS ADD CONSTRAINT 
    PUBLIC.FK_CUSTOMER_PERSON FOREIGN KEY(PERSON_ID) 
    REFERENCES PUBLIC.PERSONS(ID) ON DELETE SET NULL NOCHECK;

I have started to implement a customerDAO that reads the data of the customer table an dthe referenced tables with one SQL query, that was not very complicated:

@RegisterMapper(CustomerResultMapper.class)
public interface CustomerDAO {

    @SqlQuery("select p.id as person_id, "
            + "p.first_name, p.last_name, p.birthday, "
            + "c.id as customer_id, c.status_code, "
            + "a.id as address_id, a.line_1, a.line_2, a.zip, "
            + "a.city, a.country_code, a.phone, a.fax "
            + "from customers c, persons p, addresses a "
            + "where c.id = :id "
            + "and c.person_id = p.id "
            + "and p.address_id = a.id")
    public Customer findCustomerById(@Bind("id") long id);
}

(For the sake of brevity, I skip the mapper, since that is not my actual question)

Now I want to insert a new customer, I have all the required data, including the data which belongs in the referenced tables.

I could not find a way how to execute multiple queries with jdbi annotations, so I figured, I have to create a DAO method for every one of the tables and insert the data from within java, updating the foreign key references manually.

But also this does not work because I could not find a way to read the autogenerated ID value after an insert.

Any idea how I could approach this problem so I can keep the references correct?

score 40 · Accepted Answer · edited Jul 12 '15 at 17:30

40

Never mind, I found the solution in the meantime, at least how to get the auto generated key:

in your DAO, add the @GetGeneratedKeys annotation, make sure that your return value of the method matches the value of the generated key:

@SqlUpdate("insert into addresses (line_1) values ('address line 1')")
@GetGeneratedKeys
public long insertAddress();

In your resource then, you can simply use the return value:

long id = customerDAO.insertAddress();
System.out.println("Found id " + id);

This works to get the generated key for one statement, however in my case, I still need to make multiple queries and fill in the correct references manually.

Should anyone have an idea how I could simplify this process and let the references be filled in automatically, I still would be interested to hear about.

Thanks.

edited Jul 12 '15 at 17:30

brezanac

8,750
4
38
57

answered Oct 01 '14 at 16:39

Alexander Köb

904
1
8
19

Have you tried making the method return a List? – David Phillips Oct 07 '14 at 03:30
No, as far as I understand the GetGeneratedKeys Annotation returns the value asint or long, you cannot return a list. – Alexander Köb Oct 09 '14 at 00:24
2

Looking at org.skife.jdbi.v2.GeneratedKeys, it might work. The code calls java.sql.Statement.getGeneratedKeys() which returns a ResultSet. Give it a try. – David Phillips Oct 09 '14 at 17:49
Yes, got that to work too, but still got to do: `myInstance.id = dao.insert(myInstance);` – arntg Oct 31 '18 at 16:47

score 21 · Answer 2 · answered Sep 24 '15 at 14:59

21

With postgres you can get not only one, but even all autogenerated values.

You can do it by changing @SqlUpdate to @SqlQuery and using INSERT ... RETURNING *, assuming that you have a mapper for Address:

@SqlQuery("insert into addresses (line_1) values ('address line 1') returning *")
public Address insertAddress(@BindBean Address address);

answered Sep 24 '15 at 14:59

Vasily Sulatskov

296
2
7

1

how awesome is that? – Christian Feb 02 '16 at 12:54
1

This should be the answer. – Jeremiah Adams Sep 30 '16 at 14:49

jdbi return autogenerated value on inserts

2 Answers2