spring JdbcTemplate 批量插入

1、通过批量操作减少与数据库连接所消耗的资源。

 

2、JdbcTemplate的批量操作特性需要实现特定的接口BatchPreparedStatementSetter来进行的, 通过实现这个接口，并将其传入batchUpdate方法进行调用。 这个接口有两个方法需要实现。一个叫做getBatchSize来提供当前需要批量操作的数量。另外一个方法是setValues 允许你为prepared statement设置参数。这个方法将在整个过程中被调用的次数，则取决于你在getBatchSize中所指定的大小。

 

3、例子代码如下：

 

public void storeSalesOrder(List<Report> list) {
		System.out.format("执行保存数据 %s ...%n", ip);
		final List<Report> reportList = list;
		String sql = "insert into sell_order(traceNo, purchaseBatch, terminalNo, sellDate, "
				+ "category, weight, price, amount, createDate, status) values(?,?,?,?,?,?,?,?,?,?)";
		template.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i)throws SQLException {
				String[] text1 = StringUtils.stringToArrayCustom(reportList.get(i).text1,"##");
				Calendar cal = Calendar.getInstance(); 
				cal.set(reportList.get(i).year, reportList.get(i).month - 1, reportList.get(i).day,
						reportList.get(i).hour, reportList.get(i).minute,reportList.get(i).second);
				String text4 = reportList.get(i).text4.replace("#", "");
				
				String traceNo = reportList.get(i).text2;
				String purchaseBatch = (text1 != null && text1.length != 0)?text1[0].trim():"";
				String terminalNo = (text1 != null && text1.length != 0)?text1[1].trim():"";
				Date sellDate = cal.getTime();
				String category = text4;
				double weight = 0d;
				double price = 0d;
				if(kgFlag){
					weight = reportList.get(i).count.doubleValue();
					price = reportList.get(i).unitPrice.doubleValue();
				}else{
					weight = reportList.get(i).count.doubleValue()/2;
					price = reportList.get(i).unitPrice.doubleValue()*2;
				}
				double amount = reportList.get(i).price.doubleValue();
				
				ps.setString(1, traceNo);
				ps.setString(2, purchaseBatch);
				ps.setString(3, terminalNo);
				ps.setTimestamp(4, new Timestamp(sellDate.getTime()));
				ps.setString(5, category);
				ps.setDouble(6, weight);
				ps.setDouble(7, price);
				ps.setDouble(8, amount);
				ps.setTimestamp(9, new Timestamp(new Date().getTime()));
				ps.setInt(10, 0);
			}

			public int getBatchSize() {
				return reportList.size();
			}
		});

	}