Records: 397
Scenario: Use JdbcTemplate to find data in batches from one database and store them in List
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