Getting Started with Spring Boot, Part 14 JdbcTemplate for Advanced Users
The JdbcTemplate class can be used to formulate database queries in Spring Boot without ugly boilerplate code. But the class can do even more than what we have already shown in the previous article.
Companies on the topic
Even for batch data processing, JdbcTemplate provides its own method.
(©monsitj – stock.adobe.com)
The following piece of code demonstrates how to insert a record into the database using JdbcTemplate’s update () method:
String INSERT = "INSERT INTO Person (id, firstname, lastname) VALUES (?, ?, ?)";
jdbcTemplate.update(INSERT, 3, "Rainer", "Wain");
Again, all the boilerpate code to open the database connection, create the statement, and handle the IOException falls away. The instance of JdbcTemplate is injected as shown in the previous post, for example using @Autowired.
Handling long SQL strings as above is common, but also quite error-prone. It becomes especially dangerous if the strings are assembled at runtime. But even in the simple example here there are already pitfalls.
The order of the parameters must be observed when passing the arguments so that the correct question mark is replaced. If an error occurs, this may not be immediately noticeable and the database fills up with incorrect data.
Simple inserts with SimpleJdbcInsert
Fortunately, this is also more elegant, for example with the class SimpleJdbcInsert. An instance of this type is passed the JdbcTemplate and the table name is communicated. The statement is executed with the execute () method, which is passed as an argument a map with column names and values to be set:
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("Person");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("ID", 3);
parameters.put("firstname", "Wilma");
parameters.put("lastname", "Ruhe");
simpleJdbcInsert.execute(parameters);
Dropping column names and values in the map does lead to a bit more code, but it’s less error-prone. The primary key was passed above, but you can also have it generated. The generated value is returned after executing the statement with the method executeAndReturnKey() :
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("Person")
.usingGeneratedKeyColumns("Id");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("firstname", "Wilma");
parameters.put("lastname", "Ruhe");
Number newId = simpleJdbcInsert.executeAndReturnKey(parameters);
Sorry, there is no “SimpleJdbcUpdate”for updating records. So here must be on JdbcTemplate.update() can be resorted to:
String UPDATE = "UPDATE Person SET firstname = ?, lastname = ? WHERE Id = ?";
jdbcTemplate.update(UPDATE, "Frank", "Reich", 3);
The same applies to deleting records.
Calling the child by name: NamedParameterJdbcTemplate
To minimize errors with the parameter order and to bring more readability into the code, the NamedParameterJdbcTemplate class is an alternative. The names of the parameters in the SQL string are written out with a preceding colon. Instead of the simple values, the update () method gets a map with the parameter names as keys:
String UPDATE = "UPDATE Person SET firstname = :firstname, lastname = :lastname WHERE Id = :id";Map<String, Object> params = new HashMap<>();
params.put("id", 2);
params.put("firstname", "Andy");
params.put("lastname", "Tür");namedParameterJdbcTemplate.update(UPDATE, params);
Like JdbcTemplate, NamedParameterJdbcTemplate can also be used for inserts, deletes and queries.
Batch processing
JdbcTemplate offers its own method for mass data processing, such as in the banking environment in nightly runs: batchUpdate (). This allows the same statement to be executed repeatedly with different data. In the following example, the records in the Person table are updated with a passed list of people.
The batchUpdate () method used expects an implementation of the BatchPreparedStatementSetter interface after the SQL string. It includes the setValues () methods for setting each attribute and the getBatchSize () method, which returns the number of records to update:
public int[] batchUpdate(final List<Person> persons) {
int[] updateCounts = jdbcTemplate.batchUpdate("UPDATE Person SET firstname = ?, lastname = ? where id = ?",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, persons.get(i).getFirstname());
ps.setString(2, persons.get(i).getLastname());
ps.setLong(3, persons.get(i).getId().longValue());
} public int getBatchSize() {
return persons.size();
}
});
return updateCounts;
}
An analog method with a customized SQL statement can also be used to insert records:
public int[] batchInsert(final List<Person> persons) {
int[] updateCounts = jdbcTemplate.batchUpdate("INSERT INTO Person (firstname, lastname ) VALUES (?, ?)",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, persons.get(i).getFirstname());
ps.setString(2, persons.get(i).getLastname());
} public int getBatchSize() {
return persons.size();
}
});
return updateCounts;
}
As the examples presented show, JdbcTemplate and the related classes not only help reduce stupidem code without a subject, they also offer clever solutions to common problems when working with databases. Anyone who cannot or does not want to use JPA for whatever reason has a powerful extension of the JDBC API at hand.
(ID: 47397822)
