Spring - Batch update operations using BatchSqlUpdate

[Updated: Feb 27, 2017, Created: Feb 27, 2017]
java.lang.ObjectObjectorg.springframework.jdbc.object.RdbmsOperationRdbmsOperationorg.springframework.beans.factory.InitializingBeanInitializingBeanorg.springframework.jdbc.object.SqlOperationSqlOperationorg.springframework.jdbc.object.SqlUpdateSqlUpdateorg.springframework.jdbc.object.BatchSqlUpdateBatchSqlUpdateLogicBig

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.sql

CREATE 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 Project

Dependencies 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

Batch Sql Update Example Select All Download
  • spring-batch-sql-update
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources

See Also