Spring Data JDBC – Use a sequence to generate primary keys
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
By default, Spring Data JDBC expects the database to provide a primary key value for every new record. The easiest way to achieve that is to use an autoincremented column. We used that in the Introduction to Spring Data JDBC guide. But what do you do if your table model uses a database sequence instead?
Spring Data JDBC can, of course, handle that as well. But it requires some extra code. Instead of relying on the default handling, you need to get the value from the database sequence and set the primary key attribute before the entity gets written to the database. The best way to do that is to implement a BeforeConvertCallback.
Implementing a BeforeConvertCallback to get a sequence value
You might already know the callback mechanism from other Spring Data modules. The Entity Callback API was introduced in Spring Data Commons in version 2.2, and it’s the officially recommended way to modify entity objects before or after certain lifecycle events. When using Spring Data JDBC, you can use that mechanism to automate the retrieval of a sequence value when persisting a new entity object.
Let’s use this approach to automatically get a primary key value from a database sequence before persisting the ChessGame aggregate.
public class ChessGame {
@Id
private Long id;
private String playerWhite;
private String playerBlack;
private List<ChessMove> moves = new ArrayList<>();
...
}
Without any additional changes, the following test case would persist the ChessGame aggregate and expect that the database provides a primary key value. As mentioned earlier, this is usually achieved by modeling the primary key column as an autoincremented column.
ChessGame game = new ChessGame();
game.setPlayerWhite("Thorben Janssen");
game.setPlayerBlack("A strong player");
ChessMove move1white = new ChessMove();
move1white.setMoveNumber(1);
move1white.setColor(MoveColor.WHITE);
move1white.setMove("e4");
game.getMoves().add(move1white);
ChessMove move1Black = new ChessMove();
move1Black.setMoveNumber(1);
move1Black.setColor(MoveColor.BLACK);
move1Black.setMove("e5");
game.getMoves().add(move1Black);
gameRepo.save(game);
If you want to use a different approach to generate a primary value, you can set it using a BeforeConvertCallback. Spring Data JDBC will execute the callback before it converts a ChessGame aggregate into a database change.
As you can see in the following code snippet, the implementation of such a callback is simple. You implement the BeforeConvertCallback interface and provide the class of your aggregate root as a type parameter.
@Component
public class GetSequenceValueCallback implements BeforeConvertCallback<ChessGame> {
private Logger log = LogManager.getLogger(GetSequenceValueCallback.class);
private final JdbcTemplate jdbcTemplate;
public GetSequenceValueCallback(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public ChessGame onBeforeConvert(ChessGame game) {
if (game.getId() == null) {
log.info("Get the next value from a database sequence and use it as the primary key");
Long id = jdbcTemplate.query("SELECT nextval('chessgame_seq')",
rs -> {
if (rs.next()) {
return rs.getLong(1);
} else {
throw new SQLException("Unable to retrieve value from sequence chessgame_seq.");
}
});
game.setId(id);
}
return game;
}
}
When implementing the interface, you should define a constructor that expects a JdbcTemplate. Spring will call it with a template that’s associated with the current transaction. You can then use that JdbcTemplate in your implementation of the onBeforeConvert method.
Spring Data JDBC triggers the BeforeConvertCallback for all insert and update operations. When implementing the onBeforeConvert method, you should, therefore, check if the primary key attribute is null. If that’s the case, we’re persisting a new aggregate and need to generate a unique primary key value. You can do this by using the JdbcTemplate to execute an SQL statement that gets the next value from a database sequence and setting that value as the primary key.
That’s all you need to do. If you rerun the same test case, you can see the message written by the GetSequenceValueCallback and the SQL statement to get the value from the database sequence in the log output.
16:00:22.891 INFO 6728 --- [ main] c.t.j.model.GetSequenceValueCallback : Get the next value from a database sequence and use it as the primary key
16:00:22.892 DEBUG 6728 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL query [SELECT nextval('chessgame_seq')]
16:00:22.946 DEBUG 6728 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL update
16:00:22.947 DEBUG 6728 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [INSERT INTO "chess_game" ("id", "player_black", "player_white") VALUES (?, ?, ?)]
16:00:22.969 DEBUG 6728 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL update and returning generated keys
16:00:22.970 DEBUG 6728 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [INSERT INTO "chess_move" ("chess_game", "chess_game_key", "color", "move", "move_number") VALUES (?, ?, ?, ?, ?)]
16:00:22.979 DEBUG 6728 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing SQL update and returning generated keys
16:00:22.980 DEBUG 6728 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [INSERT INTO "chess_move" ("chess_game", "chess_game_key", "color", "move", "move_number") VALUES (?, ?, ?, ?, ?)]
Conclusion
By default, Spring Data JDBC expects the database to provide a unique primary key value for every aggregate. Most DBAs use an autoincremented column for that.
As you saw in this article, you can easily provide your own primary key generation by implementing a BeforeConvertCallback. Spring Data JDBC automatically calls it when persisting or updating an aggregate. Due to that, you need to check if you need to generate the primary key value. If that’s the case, you can use a JdbcTemplate to execute a simple SQL statement that gets the next value from a database sequence.
Using sequences is the better way, but if you combine the insert statement with returning * , or returning just the id column with returning id then you save a round trip. It is similarly beneficial with other db generated columns like insertion time etc. Works wonders in e.g. PostgreSQL.
That’s not supported by the JPA specification and Hibernate. It also has the disadvantage, that Hibernate would have to execute the insert statement immediately, which prevents several performance optimizations.