A JDBC batch update is multiple updates using the same database session. That is, we don't have to open connections multiple times.
In our previous example, let's say we want to insert multiple Person objects in the database. Followings are the various ways to do that in Spring.
Using JdbcTemplate
-
batchUpdate(String... sql) :
jdbcTemplate.batchUpdate(
"insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values ('Dana', 'Whitley', '464 Gorsuch Drive')",
"insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values ('Robin', 'Cash', '64 Zella Park')"
);
-
batchUpdate(String sql, List<Object[]> batchArgs) :
jdbcTemplate.batchUpdate(
"insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values (?, ?, ?)",
Arrays.asList(new Object[]{"Dana", "Whitley", "464 Gorsuch Drive"},
new Object[]{"Robin", "Cash", "64 Zella Park"})
);
-
batchUpdate(String sql, List <Object[]> batchArgs, int[] argTypes) :
jdbcTemplate.batchUpdate(
"insert into PERSON (FIRST_NAME, LAST_NAME, ADDRESS) values (?, ?, ?)",
Arrays.asList(new Object[]{"Dana", "Whitley", "464 Gorsuch Drive"},
new Object[]{"Robin", "Cash", "64 Zella Park"}),
new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}
);
batchUpdate(String sql, BatchPreparedStatementSetter pss)
final List<Person> persons = Arrays.asList(
Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
Person.create("Robin", "Cash", "64 Zella Park")
);
String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";
int[] updateCounts = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, persons.get(i).getFirstName());
ps.setString(2, persons.get(i).getLastName());
ps.setString(3, persons.get(i).getAddress());
}
@Override
public int getBatchSize() {
return persons.size();
}
});
-
batchUpdate(String sql, Collection<T> batchArgs, int batchSize, ParameterizedPreparedStatementSetter <T> pss) . This method can break the batch updates into serveral smaller batches specified by batchSize.
final List<Person> persons = Arrays.asList(
Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
Person.create("Robin", "Cash", "64 Zella Park")
);
String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";
int[][] updateCounts = jdbcTemplate.batchUpdate(sql, persons, persons.size(),
new ParameterizedPreparedStatementSetter<Person>() {
@Override
public void setValues(PreparedStatement ps, Person person) throws SQLException {
ps.setString(1, person.getFirstName());
ps.setString(2, person.getLastName());
ps.setString(3, person.getAddress());
}
});
Using NamedParameterJdbcTemplate
batchUpdate(String sql, Map <String,?>[] batchValues)
List<Person> persons = Arrays.asList(
Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
Person.create("Robin", "Cash", "64 Zella Park")
);
String sql = "insert into Person (first_Name, Last_Name, Address) " +
"values (:firstName, :lastName, :address)";
List<Map<String, Object>> batchValues = new ArrayList<>(persons.size());
for (Person person : persons) {
batchValues.add(
new MapSqlParameterSource("firstName", person.getFirstName())
.addValue("lastName", person.getLastName())
.addValue("address", person.getAddress())
.getValues());
}
int[] updateCounts = namedParamJdbcTemplate.batchUpdate(sql,
batchValues.toArray(new Map[persons.size()]));
batchUpdate(String sql, SqlParameterSource[] batchArgs)
List<Person> persons = Arrays.asList(
Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
Person.create("Robin", "Cash", "64 Zella Park")
);
String sql = "insert into Person (first_Name, Last_Name, Address) " +
"values (:firstName, :lastName, :address)";
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(persons.toArray());
int[] updateCounts = namedParamJdbcTemplate.batchUpdate(sql, batch);
Using SimpleJdbcInsert
executeBatch(Map<String,?>... batch)
List<Map<String, Object>> batchValues = new ArrayList<>(persons.size());
for (Person person : persons) {
Map<String, Object> map = new HashMap<>();
map.put("first_Name", person.getFirstName());
map.put("last_Name", person.getLastName());
map.put("address", person.getAddress());
batchValues.add(map);
}
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("PERSON");
int[] ints = simpleJdbcInsert.executeBatch(batchValues.toArray(new Map[persons.size()]));
public int[] executeBatch(SqlParameterSource... batch). We don't have to specify any column to bean field name mapping. It can figure out the differences like underscores in the table column names. For example bean#firstName is automatically mapped to column FIRST_NAME.
List<Person> persons = Arrays.asList(
Person.create("Dana", "Whitley", "464 Gorsuch Drive"),
Person.create("Robin", "Cash", "64 Zella Park")
);
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
.withTableName("PERSON")
.usingGeneratedKeyColumns("id");
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(persons.toArray());
int[] ints = simpleJdbcInsert.executeBatch(batch);
Using BatchSqlUpdate
Batch Sql Update can be used as reusable compiled SQL object to perform batch updates. Please check out our related tutorial.
Example Project
This example project uses ParameterizedPreparedStatementSetter for batch operation. We added a new method buildSave in one of our previous examples. Dependencies and Technologies Used: - Spring Context 4.2.3.RELEASE: Spring Context.
- Spring JDBC 4.2.3.RELEASE: Spring JDBC.
- H2 Database Engine 1.4.190: H2 Database Engine.
- JDK 1.8
- Maven 3.0.4
|
|
