数据库多表操作事务处理

一、主要思路

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

我们可以通过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;}}