|
BatchSqlUpdate is a subclass of SqlUpdate which is used to perform bulk updates.
Instead of performing straight database updates, this class queues up each update() call along with provided parameters. When a predefined batch size is reached or when flush() method is explicitly called, a single database update is perform with the bulk data. The method Batch Sql Update# reset() should also be called to clear queued parameters and number of row effected count. Note that Batch Sql Update is a not thread-safe object so we have to use a different instance for different threads. All other classes in org. springframework. jdbc. object package are thread-safe. Check out this tutorial as well to know different other ways to perform batch updates. |
Example using BatchSqlUpdate
public class BatchSqlUpdateExample {
@Autowired
private DataSource dataSource;
private BatchSqlUpdate updateSql;
private BatchSqlUpdate insertSql;
private GenericSqlQuery<Person> loadSqlQuery;
@PostConstruct
private void postConstruct() {
initLoadSql();
initInsertSql();
initUpdateSql();
}
private void initInsertSql() {
insertSql = new BatchSqlUpdate();
insertSql.setDataSource(dataSource);
insertSql.setSql("insert into PERSON (first_Name, last_Name, address) values (?, ?, ?)");
insertSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "first_Name"));
insertSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "last_Name"));
insertSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "address"));
}
private void initUpdateSql() {
updateSql = new BatchSqlUpdate();
updateSql.setDataSource(dataSource);
updateSql.setSql("update PERSON set address = ? where id = ?");
updateSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "address"));
updateSql.declareParameter(new SqlParameterValue(Types.BIGINT, "id"));
}
private void initLoadSql() {
loadSqlQuery = new GenericSqlQuery<>();
loadSqlQuery.setDataSource(dataSource);
loadSqlQuery.setSql("select * from Person");
loadSqlQuery.setRowMapper(createPersonRowMapper());
}
private RowMapper<Person> createPersonRowMapper() {
return (rs, rowNum) -> {
return toPerson(rs);
};
}
private Person toPerson(ResultSet rs) throws SQLException {
Person person = new Person();
person.setId(rs.getLong("ID"));
person.setFirstName(rs.getString("FIRST_NAME"));
person.setLastName(rs.getString("LAST_NAME"));
person.setAddress(rs.getString("ADDRESS"));
return person;
}
public void runExmaple() {
//inserting records
insertSql.update("Dana", "Whitley", "464 Gorsuch Drive");
insertSql.update("Robin", "Cash", "64 Zella Park");
insertSql.flush();
System.out.printf("Records inserted, update count %s%n",
Arrays.toString(insertSql.getRowsAffected()));
insertSql.reset();
//loading all person
List<Person> list = loadSqlQuery.execute();
System.out.printf("Records loaded: %s%n", list);
//updating
updateSql.update("121 Marswood dr", 1);
updateSql.update("121 Marswood dr", 2);
updateSql.flush();
System.out.printf("Records inserted, update count %s%n",
Arrays.toString(updateSql.getRowsAffected()));
updateSql.reset();
//loading again
System.out.println("Loading again after update");
List<Person> list2 = loadSqlQuery.execute();
System.out.printf("Records loaded after update: %s%n", list2);
}
}
src/main/resources/createPersonTable.sqlCREATE TABLE PERSON
(
ID BIGINT PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);
Configuration and main class
@Configuration
public class AppConfig {
@Bean
public DataSource h2DataSource() {
return new EmbeddedDatabaseBuilder()
.setType(EmbeddedDatabaseType.H2)
.addScript("createPersonTable.sql")
.build();
}
@Bean
public BatchSqlUpdateExample sqlUpdateExample() {
return new BatchSqlUpdateExample();
}
public static void main(String[] args) {
AnnotationConfigApplicationContext context =
new AnnotationConfigApplicationContext(AppConfig.class);
System.out.println("-- Running BatchSqlUpdate example --");
context.getBean(BatchSqlUpdateExample.class).runExmaple();
}
}Output-- Running BatchSqlUpdate example --
Records inserted, update count [1, 1]
Records loaded: [Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}, Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}]
Records inserted, update count [1, 1]
Loading again after update
Records loaded after update: [Person{id=1, firstName='Dana', lastName='Whitley', address='121 Marswood dr'}, Person{id=2, firstName='Robin', lastName='Cash', address='121 Marswood dr'}]
INFO: Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'
INFO: Executing SQL script from class path resource [createPersonTable.sql]
INFO: Executed SQL script from class path resource [createPersonTable.sql] in 11 ms.
Example ProjectDependencies and Technologies Used: - spring-context 4.3.6.RELEASE: Spring Context.
- spring-jdbc 4.3.6.RELEASE: Spring JDBC.
- h2 1.4.193: H2 Database Engine.
- JDK 1.8
- Maven 3.3.9
|
|
