spring jdbc and composite primary keys

Question

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 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;
    }

}