JdbcTemplate的四种批处理的用法

JdbcTemplate批量写入方法详解

再做详解之前，我们先来了解一下JdbcTemplate批量操作的5个API接口:

一. 使用提供的SQL语句执行批处理

这个方法相对来说比较简单，不做详细解析，该方法主要是将提供的sql列表作为一个batch进行执行，每个sql的update count可以通过返回int[]的长度计算来获得

二. 使用批处理更新和BatchPreparedStatementSetter

1. 接口API源码

int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException;

2. 接口API源码实现

public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
		if (logger.isDebugEnabled()) {
			logger.debug("Executing SQL batch update [" + sql + "]");
		}

		int[] result = execute(sql, (PreparedStatementCallback<int[]>) ps -> {
			try {
				int batchSize = pss.getBatchSize();
				InterruptibleBatchPreparedStatementSetter ipss =
						(pss instanceof InterruptibleBatchPreparedStatementSetter ?
						(InterruptibleBatchPreparedStatementSetter) pss : null);
				if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
					for (int i = 0; i < batchSize; i++) {
						pss.setValues(ps, i);
						if (ipss != null && ipss.isBatchExhausted(i)) {
							break;
						}
						ps.addBatch();
					}
					return ps.executeBatch();
				}
				else {
					List<Integer> rowsAffected = new ArrayList<>();
					for (int i = 0; i < batchSize; i++) {
						pss.setValues(ps, i);
						if (ipss != null && ipss.isBatchExhausted(i)) {
							break;
						}
						rowsAffected.add(ps.executeUpdate());
					}
					int[] rowsAffectedArray = new int[rowsAffected.size()];
					for (int i = 0; i < rowsAffectedArray.length; i++) {
						rowsAffectedArray[i] = rowsAffected.get(i);
					}
					return rowsAffectedArray;
				}
			}
			finally {
				if (pss instanceof ParameterDisposer) {
					((ParameterDisposer) pss).cleanupParameters();
				}
			}
		});

		Assert.state(result != null, "No result array");
		return result;
	

3. 使用方法

public int batchInsert(List<TblStockOrderItemBarcode> tblStockOrderItemBarcodes, JdbcTemplate jdbcTemplate) {
        String insertStockOrderItemBarcodeSql = "INSERT INTO tblStockOrderItemBarcode (lStockOrderItemID, lVoucherTypeID, lVoucherNumber, Stock_strBarcode, Stock_strPIN) " +
                "VALUES (?, ?, ?, ?, ?)";
        int[] updateCountArray = jdbcTemplate.batchUpdate(insertStockOrderItemBarcodeSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, tblStockOrderItemBarcodes.get(i).getLstockorderitemid());
                ps.setInt(2, tblStockOrderItemBarcodes.get(i).getLvouchertypeid());
                ps.setInt(3, tblStockOrderItemBarcodes.get(i).getLvouchernumber());
                ps.setString(4, tblStockOrderItemBarcodes.get(i).getStockStrbarcode());
                ps.setString(5, tblStockOrderItemBarcodes.get(i).getStockStrpin());
            }
            @Override
            public int getBatchSize() {
                return tblStockOrderItemBarcodes.size();
            }
        });
        int sumInsertedCount = 0;
        for (int a : updateCountArray) {
            sumInsertedCount += a;
        }
        return sumInsertedCount;
    }

三.使用提供的SQL语句和提供的参数列表执行批处理

1.接口API源码

int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException;

2.接口API源码实现

@Override
	public int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException {
		return batchUpdate(sql, batchArgs, new int[0]);
	}

	@Override
	public int[] batchUpdate(String sql, List<Object[]> batchArgs, final int[] argTypes) throws DataAccessException {
		if (batchArgs.isEmpty()) {
			return new int[0];
		}

		return batchUpdate(
				sql,
				new BatchPreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						Object[] values = batchArgs.get(i);
						int colIndex = 0;
						for (Object value : values) {
							colIndex++;
							if (value instanceof SqlParameterValue) {
								SqlParameterValue paramValue = (SqlParameterValue) value;
								StatementCreatorUtils.setParameterValue(ps, colIndex, paramValue, paramValue.getValue());
							}
							else {
								int colType;
								if (argTypes.length < colIndex) {
									colType = SqlTypeValue.TYPE_UNKNOWN;
								}
								else {
									colType = argTypes[colIndex - 1];
								}
								StatementCreatorUtils.setParameterValue(ps, colIndex, colType, value);
							}
						}
					}
					@Override
					public int getBatchSize() {
						return batchArgs.size();
					}
				});
	}

 3. 使用方法

public int batchUpdate(List<TblStock> tblStocks, JdbcTemplate jdbcTemplate) {
        String insertStockSql = "INSERT INTO tblStock (lVoucherTypeID, lVoucherNumber, nDuplicateNo, lStockLocationID, " +
                "lIssuedLocationID VALUES (?, ?, ?, ?, ?)";
        List<Object[]> batchArgs = tblStocks.stream().map(tblStock -> {
            return new Object[]{tblStock.getLvouchertypeid(), tblStock.getLvouchernumber(), tblStock.getNduplicateno(),
                    tblStock.getLstocklocationid(), tblStock.getLissuedlocationid()};
        }).collect(Collectors.toList());
        int[] updateCountArray = jdbcTemplate.batchUpdate(insertStockSql, batchArgs);
        return updateCountArray.length;
    }

四.使用提供的SQL语句和提供的参数集合执行多个批处理

1. 接口API源码

<T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize,
      ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException;

2. 接口API源码实现

public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize,
			final ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException {

		if (logger.isDebugEnabled()) {
			logger.debug("Executing SQL batch update [" + sql + "] with a batch size of " + batchSize);
		}
		int[][] result = execute(sql, (PreparedStatementCallback<int[][]>) ps -> {
			List<int[]> rowsAffected = new ArrayList<>();
			try {
				boolean batchSupported = JdbcUtils.supportsBatchUpdates(ps.getConnection());
				int n = 0;
				for (T obj : batchArgs) {
					pss.setValues(ps, obj);
					n++;
					if (batchSupported) {
						ps.addBatch();
						if (n % batchSize == 0 || n == batchArgs.size()) {
							if (logger.isTraceEnabled()) {
								int batchIdx = (n % batchSize == 0) ? n / batchSize : (n / batchSize) + 1;
								int items = n - ((n % batchSize == 0) ? n / batchSize - 1 : (n / batchSize)) * batchSize;
								logger.trace("Sending SQL batch update #" + batchIdx + " with " + items + " items");
							}
							rowsAffected.add(ps.executeBatch());
						}
					}
					else {
						int i = ps.executeUpdate();
						rowsAffected.add(new int[] {i});
					}
				}
				int[][] result1 = new int[rowsAffected.size()][];
				for (int i = 0; i < result1.length; i++) {
					result1[i] = rowsAffected.get(i);
				}
				return result1;
			}
			finally {
				if (pss instanceof ParameterDisposer) {
					((ParameterDisposer) pss).cleanupParameters();
				}
			}
		});

		Assert.state(result != null, "No result array");
		return result;
	}

3. 使用方法

public int batchUpdate(List<TblStock> tblStocks, JdbcTemplate jdbcTemplate) {
        String insertStockSql = "INSERT INTO tblStock (lVoucherTypeID, lVoucherNumber, nDuplicateNo, lStockLocationID, " +
                "lIssuedLocationID VALUES (?, ?, ?, ?, ?)";
        List<Object[]> batchArgs = tblStocks.stream().map(tblStock -> {
            return new Object[]{tblStock.getLvouchertypeid(), tblStock.getLvouchernumber(), tblStock.getNduplicateno(),
                    tblStock.getLstocklocationid(), tblStock.getLissuedlocationid()};
        }).collect(Collectors.toList());
        int[] updateCountArray = jdbcTemplate.batchUpdate(insertStockSql, batchArgs);
        return updateCountArray.length;
    }

3. 使用方法

public int batchInsert(List<TblStock> tblStocks, JdbcTemplate jdbcTemplate) {
        String insertStockSql = "INSERT INTO tblStock (lVoucherTypeID, lVoucherNumber, nDuplicateNo, lStockLocationID, lIssuedLocationID VALUES (?, ?, ?, ?, ?)";

        int[][] batchResult = jdbcTemplate.batchUpdate(insertStockSql, tblStocks, 100, ((ps, argument) -> {
            ps.setInt(1, argument.getLvouchertypeid());
            ps.setInt(2, argument.getLvouchernumber());
            ps.setByte(3, argument.getNduplicateno());
            ps.setInt(4, argument.getLstocklocationid());
            ps.setInt(5, argument.getLissuedlocationid());
        }));
        int sumInsertedCount = 0;
        for(int i = 0; i < batchResult.length; i++) {
            for(int j = 0; j < batchResult[0].length; j++) {
                sumInsertedCount += batchResult[i][j];
            }
        }
        return sumInsertedCount;
    }