spring jdbc and composite primary keys

Question

Hi

Is there a way in spring jdbc to return a composite primary key when a row is inserted. This composite primary key is made up of values from separate sequences

Any help is greatly appreciated

Regards Damien

Answer 1

Here is the basic idea for a single key. The long id at the end is the key. If you have multiple sequences, I would recommend just using two separate statements to get each generated key.

JdbcTemplate template = getJdbcTemplate();
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(...);
            return ps;
        }
    },
    keyHolder);
long id = keyHolder.getKey().longValue();

Answer 2

What database server are you using? MySQL only allows one auto_increment field per table and I'd imagine this is often the case, but without knowing your setup it's hard to say. Assuming there is only one auto_generated field in your table, your INSERT would have had to be aware of the value going into the second PK field. Robert's code should work for retrieving the generated key value, and the cleanest solution would probably be to perform a SELECT after the fact using this generated key and the value which you had a hold of already.

Answer 3

I think what you need is GeneratedKeyHolder.getKeys(). Code would look like this example, except you will have to call

keyHolder.getKeys()

instead of

keyHolder.getKey()

Answer 4

Here is a full example (tested on PostgreSQL 8.4):

My table:

CREATE TABLE test
(
  id serial NOT NULL,
  otherid serial NOT NULL,
  val text,
  CONSTRAINT test_pkey PRIMARY KEY (id, otherid)
)

This is how you get keys back:

public void doStuff() {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(
            new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement("insert into test(val) values (?)", Statement.RETURN_GENERATED_KEYS);
                    ps.setInt(1, 42);
                    return ps;
                }
            },
            keyHolder);

    keyHolder.getKeys().get("id");
    keyHolder.getKeys().get("otherid");
}

Now, if you want to get your composite key as an instance of some class directly from keyHolder, it is not simple.

JdbcTemplate uses ColumnMapRowMapper to map generated keys (generated keys are returned as result set, at least on PostgreSQL. It actually returns the whole row as if you were executing select on the row you just inserted). Same ColumnMapRowMapper is used in number of other places in JdbcTemplate.

The only possible point of extension here is KeyHolder itself. Here is what you can do:

public void doStuff() {
    CompositeKeyHolder keyHolder = new CompositeKeyHolder();
    ... same code here ...

    keyHolder.getCompositeKey();
}


class CompositeKeyHolder extends GeneratedKeyHolder {
    private boolean converted;

    public CompositeKey getCompositeKey() {
        return new CompositeKey((Integer)this.getKeys().get("id"), (Integer)this.getKeys().get("otherid"));
    }
}


class CompositeKey {

    private Integer id;

    private Integer otherId;

    CompositeKey(Integer id, Integer otherId) {
        this.id = id;
        this.otherId = otherId;
    }

    public Integer getId() {
        return id;
    }

    public Integer getOtherId() {
        return otherId;
    }

}