Insert data in batches using BatchPreparedStatementSetter of JdbcTemplate

Records: 397

Scenario: Use JdbcTemplate to find data in batches from one database and store them in List>, and write the result data in batches to another database.

Version: JDK 1.8, SpringBoot 2.6.3

Key class 01: org.springframework.jdbc.core.JdbcTemplate

Key class 02: org.springframework.jdbc.core.BatchPreparedStatementSetter

1. Batch query and batch write

public static void main(String[] args) {
  // 1. Get the JdbcTemplate of the two databases
  JdbcTemplate db01 = getJdbcTemplateDb01();
  JdbcTemplate db02 = getJdbcTemplateDb02();
  // 2. Query SQL and Insert SQL
  String db01SelectSQL = getSelectSQL();
  String db02InsertSQL = getInsertSQL();
  // 3. Batch query
  List<Map<String, Object>> db01Result = db01.queryForList(db01SelectSQL);
  // 4. Batch write
  db02.batchUpdate(db02InsertSQL, new BatchPreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
      Map<String, Object> oneRow = db01Result.get(i);
      ps.setObject(1, oneRow.get("CITY_ID"));
      ps.setObject(2, oneRow.get("CITY_NAME"));
      ps.setObject(3, oneRow.get("LAND_AREA"));
      ps.setObject(4, oneRow.get("POPULATION"));
      ps.setObject(5, oneRow.get("GROSS"));
      ps.setObject(6, oneRow.get("CITY_DESCRIBE"));
      ps.setObject(7, oneRow.get("DATA_YEAR"));
      ps.setObject(8, oneRow.get("UPDATE_TIME"));
    }
    @Override
    public int getBatchSize() {
        // There must be a value, usually the amount that needs to be written
        return db01Result. size();
    }
  });
}

2. Query SQL and write SQL

// query SQL
public static String getSelectSQL() {
  String selectSQL = "SELECT CITY_ID,CITY_NAME,LAND_AREA,POPULATION," +
          "GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME" +
          "FROM t_city";
  return selectSQL;
}
// Insert SQL
public static String getInsertSQL() {
  String insertSQL = "INSERT INTO t_city (\\
" +
          " CITY_ID,CITY_NAME,LAND_AREA,POPULATION,\\
" +
          "GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME)\\
" +
          "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
  return insertSQL;
}

3. Get JdbcTemplate

// operate the JdbcTemplate of the database hub_a_db
public static JdbcTemplate getJdbcTemplateDb01() {
  String username = "hub_a";
  String password = "12345678";
  String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/hub_a_db";
  String driverName = "com.mysql.jdbc.Driver";
  // com.alibaba.druid.pool.DruidDataSource
  DruidDataSource dataSource = new DruidDataSource();
  // Set data source property parameters
  dataSource.setPassword(password);
  dataSource.setUrl(jdbcUrl);
  dataSource.setUsername(username);
  dataSource.setDriverClassName(driverName);
  // Get spring's JdbcTemplate
  JdbcTemplate jdbcTemplate = new JdbcTemplate();
  // set the data source
  jdbcTemplate.setDataSource(dataSource);
  return jdbcTemplate;
}
// Operate the JdbcTemplate of the database hub_b_db
public static JdbcTemplate getJdbcTemplateDb02() {
  String username = "hub_b";
  String password = "12345678";
  String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/hub_b_db";
  String driverName = "com.mysql.jdbc.Driver";
  // com.alibaba.druid.pool.DruidDataSource
  DruidDataSource dataSource = new DruidDataSource();
  // Set data source property parameters
  dataSource.setPassword(password);
  dataSource.setUrl(jdbcUrl);
  dataSource.setUsername(username);
  dataSource.setDriverClassName(driverName);
  // Get spring's JdbcTemplate
  JdbcTemplate jdbcTemplate = new JdbcTemplate();
  // set the data source
  jdbcTemplate.setDataSource(dataSource);
  return jdbcTemplate;
}

4. Table creation statement

CREATE TABLE t_city (
  CITY_ID BIGINT(16) NOT NULL COMMENT 'Unique ID',
  CITY_NAME VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT 'city name',
  LAND_AREA DOUBLE DEFAULT NULL COMMENT 'city area',
  POPULATION BIGINT(16) DEFAULT NULL COMMENT 'city population',
  GROSS DOUBLE DEFAULT NULL COMMENT 'Gross production value',
  CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT 'City Description',
  DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT 'Data Year',
  UPDATE_TIME DATETIME DEFAULT NULL COMMENT 'update time'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='city information table';

5. Create database statement

// Create database hub_a_db and user hub_a
USE mysql;
CREATE DATABASE hub_a_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER hub_a@'%' IDENTIFIED BY '12345678';
GRANT ALL ON hub_a_db.* TO 'hub_a'@'%' IDENTIFIED BY '12345678';
FLUSH PRIVILEGES;
// Create database hub_b_db and user hub_b
USE mysql;
CREATE DATABASE hub_b_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER hub_b@'%' IDENTIFIED BY '12345678';
GRANT ALL ON hub_b_db.* TO 'hub_b'@'%' IDENTIFIED BY '12345678';
FLUSH PRIVILEGES;

6. Query all fields SQL of a table in MySQL

SELECT
  COLUMN_NAME
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 't_city';

7. Description of getBatchSize of BatchPreparedStatementSetter

The reason why getBatchSize of BatchPreparedStatementSetter needs to return a value is that before batchUpdate of JdbcTemplate is executed, it will first call getBatchSize to obtain the value and make subsequent judgments. The source code is as follows:

public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
  if (this. logger. isDebugEnabled()) {
      this.logger.debug("Executing SQL batch update [" + sql + "]");
  }
  int[] result = (int[])this.execute(sql, (ps) -> {
    try {
        int batchSize = pss. getBatchSize();
        InterruptibleBatchPreparedStatementSetter ipss = pss instanceof InterruptibleBatchPreparedStatementSetter ? (InterruptibleBatchPreparedStatementSetter)pss : null;
        if (!JdbcUtils. supportsBatchUpdates(ps. getConnection())) {
            List<Integer> rowsAffected = new ArrayList();
            int ix = 0;
            while(true) {
                if (ix < batchSize) {
                    pss. setValues(ps, ix);
                    if (ipss == null || !ipss.isBatchExhausted(ix)) {
                        rowsAffected.add(ps.executeUpdate());
                         + +ix;
                        continue;
                    }
                }
                int[] rowsAffectedArray = new int[rowsAffected. size()];
  
                for(int ixx = 0; ixx < rowsAffectedArray.length; + + ixx) {
                    rowsAffectedArray[ixx] = (Integer)rowsAffected. get(ixx);
                }
                int[] var13 = rowsAffectedArray;
                return var13;
            }
        } else {
            int i = 0;
            while(true) {
                if (i < batchSize) {
                    pss. setValues(ps, i);
                    if (ipss == null || !ipss.isBatchExhausted(i)) {
                        ps. addBatch();
                         + + i;
                        continue;
                    }
                }
  
                int[] var10 = ps. executeBatch();
                return var10;
            }
        }
    } finally {
        if (pss instanceof ParameterDisposer) {
            ((ParameterDisposer)pss).cleanupParameters();
        }
    }
  });
  Assert.state(result != null, "No result array");
  return result;
}

Above, thanks.

April 10, 2023