Automatically generated ID's are very handy, since you don't have to create the ID yourself and don't need a second roundtrip to the database as you would when you use a sequence
Example
Example table with books:CREATE TABLE AUTHOR (
ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
NAME VARCHAR(100) NOT NULL,
PRIMARY KEY(ID));
CREATE TABLE BOOK (
ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
TITLE VARCHAR(100) NOT NULL,
AUTHOR_ID BIGINT NOT NULL,
PRIMARY KEY(ID));
When you insert a new book into the database, it will generate the ID automatically. However, when you need to insert multiple records with foreign key relations, like a author and a book record, where the author_id is the generated id of the author record, you need to determine the generated id.
JdbcTemplate with PreparedStatementCreator and KeyHolder
When you use the Spring JdbcTemplate to insert a record, you can use the update variant with the PreparedStatementCreator and Keyholder:
KeyHolder keyHolder = new GeneratedKeyHolder();
JdbcTemplate template = new JdbcTemplate(getDataSource());
template.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps = connection.prepareStatement("INSERT INTO AUTHOR"
+ " (NAME) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, author.name);
return ps;
}
}, keyHolder);
Long generatedId = new Long(keyHolder.getKey().longValue());
book.setId(generatedId);
This solution requires the use of JDBC 3.0 and may differ on other database platforms. The Spring documentation shows a different variant, which should work on Oracle (this fails on DB2/400 with a "not supported" error):
PreparedStatement ps = connection.prepareStatement("INSERT INTO AUTHOR"
+ " (NAME) VALUES (?)", new String[] {"ID"});
Add this page to your favorite Social Bookmarking websites
