Batch Updates With JdbcTemplate
In the example below, we will explore how to insert thousands of records into a MySQL database using batchUpdate.
Join the DZone community and get the full member experience.
Join For FreeThere may come a time when you are using JdbcTemplate
and want to use a PreparedStatement
for a batch update. In the example below, we will explore how to insert thousands of records into a MySQL database using batchUpdate.
First, we must configure the datasource to use in our application.properties
.
spring.datasource.url=jdbc:mysql://localhost:3306/myurl
spring.datasource.username=root
spring.datasource.password=mypassword
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
Now in order to use this datasource in our Service.class
, we need to make it Autowired. Below, I place the @Autowired
annotation above the declaration of the DataSource
, but depending on your preferences, it could also be placed over the method constructor.
@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
In my example below, I have the method set to Private because the method should not be used outside of the class. However, depending on your own needs, you may make this Public.
Here is the full code. Below, I will explain the code line-by-line.
private void loadTheContent(String[] rows) throws SQLException {
String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.batchUpdate(Query, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
String[] col = new String[4];
col = rows[i].split("-");
ps.setString(1, col[0]);
ps.setString(2, col[1]);
ps.setString(3, col[2]);
ps.setString(4, col[3]);
}
@Override
public int getBatchSize() {
return rows.length;
}
});
return;
}
An an array of Strings is passed into the loadTheContent
method:
(String[] rows)
In my example, they are from a processed XML file. Covering XML processing is out of scope for this post, but will be covered in the future.
In order to successfully execute the batch prepared statement, we need to know the batch size — meaning how many records will be processed.
getBatchSize()
This is a request for the batch size. With the code below:
return rows.length;
...we instruct the program to measure the size of the rows array in order to calculate the batch size.
Next, the query is defined.
String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";
Take note that the query String cannot have a keyword such as final next to it as it is not immutable. Also, as this is not using a NamedParameterJdbcTemplate
, we must use ?
placeholders for the values.
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
The above instantiation of jdbcTemplate
allows us to execute the SQL to our correct database. Since dataSource
is autowired, this will execute correctly.
Next, we actually define the batchUpdate
. You may notice that the batchUpdate
looks somewhat similar to a regular jdbcTemplate
update.
BatchPreparedStatmentSetter
provides an interface to set values on the PreparedStatement
.
jdbcTemplate.batchUpdate(Query, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
The Query
parameter is already defined as a String, which I discussed above. The int i
is the current row that is being processed.
With this in mind, we set values for each column in the record. Below, you see that I create an Array of four values since I know that each record has four columns.
String[] col = new String[4];
col = rows[i].split("-");
ps.setString(1, col[0]);
ps.setString(2, col[1]);
ps.setString(3, col[2]);
ps.setString(4, col[3]);
I define each value in the col
array by splitting at the -
character in rows[i]
. Next, we use each value in the col
array to set the values for the SQL query.
This means that the ?
placeholders below are replaced by col[0]
, etc.
String Query = "INSERT INTO content (id, type, publisher, title) VALUES (?,?,?,?)";
For reference on JdbcTemplate.batchUpdate
, see this documentation.
If you have any questions, ask in the comments and I will try to help!
Published at DZone with permission of Michael Good, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments