Getting generated fields with jdbcTemplate
I won’t mention all the benefits of using Spring JdbcTemplate over a classic use of Jdbc. However, there is still one thing which bothers me from time to time. And, this is the absence of an easy way to get the generated fields after inserting a new row in the DB.
The use case is quite common especially nowadays with Rest APIs. Indeed, when you create an object you are suppose to return the link to the newly created resource. The problem is the ID of the resource is most likely generated via a sequence and by using the common jdbcTemplate methods you don’t have access to it. Fortunately, there is still a way to get it!
Config:
- Spring 4.x
- Oracle 12g
Pretending there is table NOTIFICATION and a SEQUENCE to increment the primary key.
CREATE TABLE NOTIFICATION(
"ID" NUMBER(5,0) NOT NULL,
"TEXT" VARCHAR2(4000) NOT NULL,
"TITLE" VARCHAR2(250) NOT NULL,
"NOTIFICATION_LEVEL" VARCHAR2(10) NOT NULL,
CONSTRAINT notification_pk PRIMARY KEY (ID)
);
CREATE SEQUENCE "NOTIFICATION_SEQ" START WITH 1;Here the Notification POJO:
public class Notification
{
private int id;
private String title;
private String text;
private String level;
// + plus all the getters and setters...
}So, how could we get the ID generated by the sequence? well, there is one method which can be used:
public int update(final PreparedStatementCreator psc,
final KeyHolder generatedKeyHolder)throws DataAccessExceptionThis method uses a keyHolder which will receive the value of the generated columns.
Let’s see how it works!
public int save(Notification notification) {
// insert query
String insertSql =
"Insert into NOTIFICATION
(ID, TEXT, TITLE, NOTIFICATION_LEVEL)
values
(NOTIFICATION_SEQ.NEXTVAL, ?, ?, ?)";
// this is the key holder
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
// the name of the generated column (you can track more than one column)
String id_column = "ID";
// the update method takes an implementation of PreparedStatementCreator which could be a lambda
jdbcTemplate.update(con -> {
PreparedStatement ps = con.prepareStatement(insertSql, new String[]{id_column});
ps.setString(1, notification.getText());
ps.setString(2, notification.getTitle());
ps.setString(3, notification.getLevel());
return ps;
}
, keyHolder);
// after the update executed we can now get the value of the generated ID
BigDecimal id = (BigDecimal) keyHolder.getKeys().get(id_column);
return id.intValue();
}As we can see this solution is not perfect because we still need to create the prepare statement and set the parameters in the right order with the right method according to the type. Well! it’s not too bad and it works with any kind of generated values, so it’s a good compromise.