本文由我司收集整编，推荐下载，如有疑问，请与我司联系
数据库多表操作事务处理
一、主要思路
在需要同时插入多条数据时，这其中可能是同一个表的多条记录，也可能是多
个不同表之间的数据同时更新。对此，我们需要保证其中的原子性和一致性，做到 要么全部操作都能全部成功完成，否则全部不操作。
我们可以通过 SQL 的事务来对相关数据库操作进行处理，在开始
conn.setAutoCommit(false);（conn 是或得的连接）把本次运行的 SQL 操作改为非自 动运行，在配置好各 SQL 语句之后，调用 connmit();来运行，其中通过
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();
thismitTransaction(conn); } catch(Exception e){ conn.rollback(); throw new RuntimeException(e);
}
finally
{
fields
=
null;
return
org.apachemons.dbutils.DbUtils.closeQuietly(ps); result; * 批量插入对象-多表插入 * p
this.closeConnection(conn);
* 注意：对象字段不能为数据库关键字 *
本文由我司收集整编，推荐下载，如有疑问，请与我司联系
@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(); thismitTransaction(conn); } catch(Exception e){ conn.rollback(); throw new RuntimeException(e); } finally { fields = null; org.apachemons.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(); thismitTransaction(conn); } catch(Exception e){ conn.rollback(); throw new RuntimeException(e); } finally { ps.clearBatch(); fields = null; org.apachemons.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();
thismitTransaction(conn); } catch(Exception e){ conn.rollback(); throw new RuntimeException(e);
}
finally
{
fields
=
null;
return
org.apachemons.dbutils.DbUtils.closeQuietly(ps); this.closeConnection(conn);
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();