Spring jdbc and compound primary keys

Question

Is there a way in spring jdbc to return a composite primary key when inserting a row. This composite primary key consists of values ​​from individual sequences.

Any help is appreciated

Relations Damien

Answer 1

Here is a complete 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)
)

Here's how you get the 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 a composite key as an instance of some class directly from keyHolder, this is not easy.

JdbcTemplate ColumnMapRowMapper ( , , PostgreSQL. , , ). ColumnMapRowMapper JdbcTemplate.

KeyHolder. :

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

}

Answer 2

. - . , .

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 3

? MySQL auto_increment , , , , , . , auto_generated, INSERT , PK. , , , SELECT , .

Answer 4

, GeneratedKeyHolder.getKeys(). , ,

keyHolder.getKeys()

keyHolder.getKey()