spring mvc 批量操作

用SimpleJdbcTemplate实现批量新增和批量修改。

1)使用BeanPropertySqlParameterSource。

BeanPropertySqlParameterSource的父类实现了SqlParameterSource接口。

为了方便理解，我将实现过程，访问数据库放在一个类的一个方法中。

即使堆砌成山的代码，其思路有可能却是简单的。

import java.util.ArrayList; import java.util.List; import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport; import com.dto.mc.UserDto; public class TestBeanPropertySqlParameterSource extends SimpleJdbcDaoSupport{ private void batchUpdate4User(List<UserDto> userDtoList) throws Exception { 　　　　//将userDtoList转化成BeanPropertySqlParameterSource[]数组 List<BeanPropertySqlParameterSource> userSourceList = new ArrayList<BeanPropertySqlParameterSource>(); for (UserDto userDto : userDtoList) { userSourceList.add(new BeanPropertySqlParameterSource(userDto)); } BeanPropertySqlParameterSource[] beanSources = userSourceList.toArray(new BeanPropertySqlParameterSource[userSourceList.size()]); //userDto修改的字段与数据库的字段必须满足映射条件。 StringBuffer sql = new StringBuffer(); sql.append("update user set nickName = :nickName, update_time = :updateTime,") .append(" update_userName = :updateUserName where userId = :userId"); this.getSimpleJdbcTemplate().batchUpdate(sql.toString(), beanSources); } }

按 Ctrl+C 复制代码

2）使用SqlParameterSourceUtils.createBatch(list.toArray())

public void saveModifiedVendorTemp(List<UserDto> list) throws Exception { StringBuffer sql = new StringBuffer(); sql.append(" update user_ys set role = :role where userId = :userId"); this.getSimpleJdbcTemplate() .batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(list.toArray())); }

按 Ctrl+C 复制代码

源代码：org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils.createBatch方法

同样将数组转化成BeanPropertySqlParameterSource数组。

/** * * Create an array of BeanPropertySqlParameterSource objects populated * with data * from the values passed in. This will define what is included * in a batch operation. * @param beans object array of beans containing the * values to be used * @return an array of SqlParameterSource */ public static SqlParameterSource[] createBatch(Object[] beans) { BeanPropertySqlParameterSource[] batch = new BeanPropertySqlParameterSource[beans.length]; for (int i = 0; i < beans.length; i++) { Object bean = beans[i]; batch[i] = new BeanPropertySqlParameterSource(bean); } return batch; }

/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.bitbao.cm.dao.VerificationDao#addBatchVerificationItem(java.util.
	 * List)
	 */
	@Override
	public boolean addBatchVerificationItem(List<VerificationContentVO> itemList) {
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		def.setName("addBatchVerificationItem");
		def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
		TransactionStatus txStatus = this.txManager.getTransaction(def);

		try {
			for (VerificationContentVO map : itemList) {
				this.namedJdbcTemplate.update(SQL_INSERT, mappingParameter(map));
			}
		} catch (Exception e) {
			txManager.rollback(txStatus);
			CMLog.error("Exception to save verification", e);
			return false;
		} finally {
			txManager.commit(txStatus);
		}

		return true;
	}



@SuppressWarnings("unchecked")
    public RowMapper mappingResult(Class<?> resultBean) {
        return new BeanPropertyRowMapper(resultBean);
    }