数据库多表操作事务处理

一、主要思路

在需要同时插入多条数据时，这其中可能是同一个表的多条记录，也可能是多个不同表之间的数据同时更新。对此，我们需要保证其中的原子性和一致性，做到要么全部操作都能全部成功完成，否则全部不操作。

我们可以通过SQL的事务来对相关数据库操作进行处理，在开始conn.setAutoCommit(false);（conn是或得的连接）把本次运行的SQL操作改为非自动运行，在配置好各SQL语句之后，调用conn.commit();来运行，其中通过try{……}catch……来捕捉异常，如果遇到错误时，就调用conn.rollback();来对本次操作进行回滚到操作前的状态，防止存在错误数据和脏数据。

二、主要实现方法

/**
	 * 批量插入对象-同一个表多条记录
	 * <p>
	 * 注意：对象字段不能为数据库关键字
	 * @param list
	 * @return
	 * @throws SQLException
	 * @author lims
	 * @date 2015-08-28
	 */
	public int[] insertSameTable(List<Pojo> list) throws SQLException {
		if (list == null || list.size() == 0) {
			return null;
		}
		String sql = getInsertSql(list.get(0));
		PreparedStatement ps = null;
		String[] fields = null;
		int[] result = null;
		Connection conn=getConnection();
		try {
			ps = conn.prepareStatement(sql.toString());
			this.startTransaction(conn);
			for (Pojo obj : list) {
				Map<String, String> pojo_bean = obj.listInsertableFields();
				fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()]);
				for (int i = 0; i < fields.length; i++) {
					ps.setObject(i + 1, pojo_bean.get(fields[i]));
				}
				ps.addBatch();
			}
			result = ps.executeBatch();
			this.commitTransaction(conn);
		} catch(Exception e){
			conn.rollback();
			throw new RuntimeException(e);
		} finally {
			fields = null;
			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
			this.closeConnection(conn);
		}
		return result;
	}
	
	/**
	 * 批量插入对象-多表插入
	 * <p>
	 * 注意：对象字段不能为数据库关键字
	 * @param list
	 * @return
	 * @throws SQLException
	 * @author lims
	 * @date 2015-08-28
	 */
	public int insertMutilTable(List<Pojo> list) throws SQLException {
		if (list == null || list.size() == 0) {
			return 0;
		}
		String[] fields;
		PreparedStatement ps = null;
		int result = 0;
		Connection conn=getConnection();
		try {
			this.startTransaction(conn);
			for (Pojo obj : list) {
				Map<String, String> pojo_bean = obj.listInsertableFields();
				String sql = getInsertSql(obj);
				
				ps = conn.prepareStatement(sql.toString());
				
				fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()]);
				for (int i = 0; i < fields.length; i++) {
					ps.setObject(i + 1, pojo_bean.get(fields[i]));
				}
				result = ps.executeUpdate();
			}
			this.commitTransaction(conn);
		} catch(Exception e){
			conn.rollback();
			throw new RuntimeException(e);
		} finally {
			fields = null;
			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
			this.closeConnection(conn);
		}
		return result;
	}
	
	/**
	 * 批量更新同一个表的多条记录
	 * @param list
	 * @return
	 * @throws SQLException
	 * @author lims
	 * @date 2015-08-28
	 */
	public int[] updateSameTable(List<Pojo> list) throws SQLException {
		if (list == null || list.size() == 0) {
			return null;
		}
		String[] fields;
		PreparedStatement ps = null;
		int[] result = null;
		Connection conn=getConnection();
		try {
			this.startTransaction(conn);
			for (Pojo obj : list) {
				Map<String, String> pojo_bean = obj.listInsertableFields();
				fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()]);
				StringBuilder sql = new StringBuilder();
				sql.append("update "+getTableName(obj.getClass())+" set ");
				for (int i = 0; i < fields.length; i++) {
					if (i > 0)
						sql.append(',');
					sql.append(fields[i]).append(" = ? ");
				}
				sql.append(" where id=?");
				ps = conn.prepareStatement(sql.toString());
				
				fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()+1]);
				for (int i = 0; i < fields.length; i++) {
					if(i==fields.length-1) {
						ps.setObject(i + 1, obj.getId());
					}
					else {
						ps.setObject(i + 1, pojo_bean.get(fields[i]));
					}
					
				}
				ps.addBatch();
			}
			result = ps.executeBatch();
			this.commitTransaction(conn);
		} catch(Exception e){
			conn.rollback();
			throw new RuntimeException(e);
		} finally {
			ps.clearBatch();
			fields = null;
			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
			this.closeConnection(conn);
		}
		return result;
	}
	
	/**
	 * 多表更新
	 * @param list
	 * @return
	 * @throws SQLException
	 * @author lims
	 * @date 2015-08-28
	 */
	public int updateMutilTable(List<Pojo> list) throws SQLException {
		if (list == null || list.size() == 0) {
			return 0;
		}
		String[] fields;
		PreparedStatement ps = null;
		int result = 0;
		Connection conn=getConnection();
		try {
			this.startTransaction(conn);
			for (Pojo obj : list) {
				Map<String, String> pojo_bean = obj.listInsertableFields();
				String sql = getUpdateSql(obj);
				
				ps = conn.prepareStatement(sql.toString());
				
				fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()+1]);
				for (int i = 0; i < fields.length; i++) {
					if(i==fields.length-1) {
						ps.setObject(i + 1, obj.getId());
					}
					else {
						ps.setObject(i + 1, pojo_bean.get(fields[i]));
					}
					
				}
				result = ps.executeUpdate();
			}
			this.commitTransaction(conn);
		} catch(Exception e){
			conn.rollback();
			throw new RuntimeException(e);
		} finally {
			fields = null;
			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
			this.closeConnection(conn);
		}
		return result;
	}
	
	/**
	 * 插入数据和更新多表数据
	 * @param list
	 * @return
	 * @throws SQLException
	 * @author lims
	 * @date 2015-08-28
	 */
	public int insertAndUpdateMutilTable(List<Pojo> saveList,List<Pojo> updateList) throws SQLException {
		if (saveList == null || saveList.size() == 0 || updateList == null || updateList.size() == 0) {
			return 0;
		}
		String[] fields;
		PreparedStatement ps = null;
		int result = 0;
		Connection conn=getConnection();
		try {
			this.startTransaction(conn);
			for (Pojo obj : saveList) {//插入操作
				Map<String, String> pojo_bean = obj.listInsertableFields();
				String sql = getInsertSql(obj);
				
				ps = conn.prepareStatement(sql.toString());
				
				fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()]);
				for (int i = 0; i < fields.length; i++) {
					ps.setObject(i + 1, pojo_bean.get(fields[i]));
				}
				result = ps.executeUpdate();
			}
			for (Pojo obj : updateList) {//更新操作
				Map<String, String> pojo_bean = obj.listInsertableFields();
				String sql = getUpdateSql(obj);
				
				ps = conn.prepareStatement(sql.toString());
				
				fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()+1]);
				for (int i = 0; i < fields.length; i++) {
					if(i==fields.length-1) {
						ps.setObject(i + 1, obj.getId());
					}
					else {
						ps.setObject(i + 1, pojo_bean.get(fields[i]));
					}
					
				}
				result = ps.executeUpdate();
			}
			this.commitTransaction(conn);
		} catch(Exception e){
			conn.rollback();
			throw new RuntimeException(e);
		} finally {
			fields = null;
			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
			this.closeConnection(conn);
		}
		return result;
	}

三、相关调用方法

protected abstract Connection getConnection() throws SQLException;

	protected abstract void closeConnection(Connection conn);

	private void startTransaction(Connection conn) {
		try {
			if (conn != null) {
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	private void commitTransaction(Connection conn) {
		try {
			if (conn != null) {
				conn.commit();
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

/**
	 * 获取Pojo对应的数据库表名
	 * 
	 * @param c
	 * @return
	 */
	private static <T extends Pojo> String getTableName(Class<T> c) {
		try {
			String tn = tableNameCache.get(c.getSimpleName());
			if (tn == null) {
				tn = c.newInstance().tableName();
				tableNameCache.put(c.getSimpleName(), tn);
			}
			return tn;
		} catch (Exception e) {
			log.error("Get " + c.getSimpleName() + " name exception.");
			return null;
		}
	}

	/**
	 * 获取Pojo的插入sql语句
	 * 
	 * @param obj
	 * @return
	 */
	private static String getInsertSql(Pojo obj) {
		try {
			String insertSql = insertSqlCache.get(obj.getClass().getName());
			if (insertSql == null) {
				Map<String, String> pojo_bean = obj.listInsertableFields();
				String[] fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()]);			
				StringBuilder sql = new StringBuilder("INSERT INTO ");
				sql.append(obj.tableName());
				sql.append('(');
				for (int i = 0; i < fields.length; i++) {
					if (i > 0)
						sql.append(',');
					sql.append(fields[i]);
				}
				sql.append(") VALUES(");
				for (int i = 0; i < fields.length; i++) {
					if (i > 0)
						sql.append(',');
					sql.append('?');
				}
				sql.append(')');
				insertSql = sql.toString();
				sql = null;
				insertSqlCache.put(obj.getClass().getName(), insertSql);
			}
			return insertSql;
		} catch (Exception e) {
			log.error("Get " + obj.getClass().getSimpleName()
					+ " insertSql exception.");
			return null;
		}
	}
	
	/**
	 * 获取Pojo的更新sql语句
	 * 
	 * @param obj
	 * @return
	 * @author lims
	 * @date 2015-08-23
	 */
	private static String getUpdateSql(Pojo obj) {
		try {
			String updateSql = updateSqlCache.get(obj.getClass().getName());
			if (updateSql == null) {
				Map<String, String> pojo_bean = obj.listInsertableFields();
				String[] fields = pojo_bean.keySet().toArray(
						new String[pojo_bean.size()]);
				StringBuilder sql = new StringBuilder();
				sql.append("update "+getTableName(obj.getClass())+" set ");
				for (int i = 0; i < fields.length; i++) {
					if (i > 0)
						sql.append(',');
					sql.append(fields[i]).append(" = ? ");
				}
				sql.append(" where id=?");
				
				updateSql = sql.toString();
				sql = null;
				updateSqlCache.put(obj.getClass().getName(), updateSql);
			}
			return updateSql;
		} catch (Exception e) {
			log.error("Get " + obj.getClass().getSimpleName()
					+ " updateSql exception.");
			return null;
		}
	}

四、上面用到的持久化基类

import org.apache.commons.beanutils.BeanUtils;

import java.io.Serializable;
import java.util.Map;

/**
 * 持久化基类
 */
public abstract class Pojo implements Serializable {
	private static final long serialVersionUID = 1L;
	protected int id;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String tableName() {
		String tn=getClass().getSimpleName() ;
		if(tn.endsWith("Bean")) {
			tn=tn.substring(0,tn.length()-4);
		}
		tn = tn.toLowerCase();
		return tn;
	}

	protected String cacheRegion() {
		return this.getClass().getSimpleName();
	}
	
	/**
	 * 列出要插入到数据库的字段集合，子类可以按照实际需求覆盖
	 * @return
	 */
	public Map<String, String> listInsertableFields() {
		try {
			Map<String, String> props = BeanUtils.describe(this);
			props.remove("id");
			props.remove("class");
			for(String s:props.keySet()){
				if(s.endsWith("_"))props.remove(s);
			}
			return props;
		} catch (Exception e) {
			throw new RuntimeException("Exception when Fetching fields of "
					+ this);
		}
	}

	@Override
	public boolean equals(Object obj) {
		if (obj == null)
			return false;
		if (obj == this)
			return true;
		// 不同的子类尽管ID是相同也是不相等的
		if (!getClass().equals(obj.getClass()))
			return false;
		Pojo wb = (Pojo) obj;
		return wb.getId() == getId();
	}

}