devbox
从前慢现在也慢
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

数据库多表操作事务处理_conn.preparestatement(sql); 事务的处理

作者：从前慢现在也慢 | 2024-02-25 23:12:35

赞

踩

conn.preparestatement(sql); 事务的处理

一、主要思路

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

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

二、主要实现方法

  1. /**
  2. 	 * 批量插入对象-同一个表多条记录
  3. 	 * <p>
  4. 	 * 注意：对象字段不能为数据库关键字
  5. 	 * @param list
  6. 	 * @return
  7. 	 * @throws SQLException
  8. 	 * @author lims
  9. 	 * @date 2015-08-28
  10. 	 */
  11. 	public int[] insertSameTable(List<Pojo> list) throws SQLException {
  12. 		if (list == null || list.size() == 0) {
  13. 			return null;
  14. 		}
  15. 		String sql = getInsertSql(list.get(0));
  16. 		PreparedStatement ps = null;
  17. 		String[] fields = null;
  18. 		int[] result = null;
  19. 		Connection conn=getConnection();
  20. 		try {
  21. 			ps = conn.prepareStatement(sql.toString());
  22. 			this.startTransaction(conn);
  23. 			for (Pojo obj : list) {
  24. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  25. 				fields = pojo_bean.keySet().toArray(
  26. 						new String[pojo_bean.size()]);
  27. 				for (int i = 0; i < fields.length; i++) {
  28. 					ps.setObject(i + 1, pojo_bean.get(fields[i]));
  29. 				}
  30. 				ps.addBatch();
  31. 			}
  32. 			result = ps.executeBatch();
  33. 			this.commitTransaction(conn);
  34. 		} catch(Exception e){
  35. 			conn.rollback();
  36. 			throw new RuntimeException(e);
  37. 		} finally {
  38. 			fields = null;
  39. 			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
  40. 			this.closeConnection(conn);
  41. 		}
  42. 		return result;
  43. 	}
  44. 	
  45. 	/**
  46. 	 * 批量插入对象-多表插入
  47. 	 * <p>
  48. 	 * 注意：对象字段不能为数据库关键字
  49. 	 * @param list
  50. 	 * @return
  51. 	 * @throws SQLException
  52. 	 * @author lims
  53. 	 * @date 2015-08-28
  54. 	 */
  55. 	public int insertMutilTable(List<Pojo> list) throws SQLException {
  56. 		if (list == null || list.size() == 0) {
  57. 			return 0;
  58. 		}
  59. 		String[] fields;
  60. 		PreparedStatement ps = null;
  61. 		int result = 0;
  62. 		Connection conn=getConnection();
  63. 		try {
  64. 			this.startTransaction(conn);
  65. 			for (Pojo obj : list) {
  66. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  67. 				String sql = getInsertSql(obj);
  68. 				
  69. 				ps = conn.prepareStatement(sql.toString());
  70. 				
  71. 				fields = pojo_bean.keySet().toArray(
  72. 						new String[pojo_bean.size()]);
  73. 				for (int i = 0; i < fields.length; i++) {
  74. 					ps.setObject(i + 1, pojo_bean.get(fields[i]));
  75. 				}
  76. 				result = ps.executeUpdate();
  77. 			}
  78. 			this.commitTransaction(conn);
  79. 		} catch(Exception e){
  80. 			conn.rollback();
  81. 			throw new RuntimeException(e);
  82. 		} finally {
  83. 			fields = null;
  84. 			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
  85. 			this.closeConnection(conn);
  86. 		}
  87. 		return result;
  88. 	}
  89. 	
  90. 	/**
  91. 	 * 批量更新同一个表的多条记录
  92. 	 * @param list
  93. 	 * @return
  94. 	 * @throws SQLException
  95. 	 * @author lims
  96. 	 * @date 2015-08-28
  97. 	 */
  98. 	public int[] updateSameTable(List<Pojo> list) throws SQLException {
  99. 		if (list == null || list.size() == 0) {
  100. 			return null;
  101. 		}
  102. 		String[] fields;
  103. 		PreparedStatement ps = null;
  104. 		int[] result = null;
  105. 		Connection conn=getConnection();
  106. 		try {
  107. 			this.startTransaction(conn);
  108. 			for (Pojo obj : list) {
  109. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  110. 				fields = pojo_bean.keySet().toArray(
  111. 						new String[pojo_bean.size()]);
  112. 				StringBuilder sql = new StringBuilder();
  113. 				sql.append("update "+getTableName(obj.getClass())+" set ");
  114. 				for (int i = 0; i < fields.length; i++) {
  115. 					if (i > 0)
  116. 						sql.append(',');
  117. 					sql.append(fields[i]).append(" = ? ");
  118. 				}
  119. 				sql.append(" where id=?");
  120. 				ps = conn.prepareStatement(sql.toString());
  121. 				
  122. 				fields = pojo_bean.keySet().toArray(
  123. 						new String[pojo_bean.size()+1]);
  124. 				for (int i = 0; i < fields.length; i++) {
  125. 					if(i==fields.length-1) {
  126. 						ps.setObject(i + 1, obj.getId());
  127. 					}
  128. 					else {
  129. 						ps.setObject(i + 1, pojo_bean.get(fields[i]));
  130. 					}
  131. 					
  132. 				}
  133. 				ps.addBatch();
  134. 			}
  135. 			result = ps.executeBatch();
  136. 			this.commitTransaction(conn);
  137. 		} catch(Exception e){
  138. 			conn.rollback();
  139. 			throw new RuntimeException(e);
  140. 		} finally {
  141. 			ps.clearBatch();
  142. 			fields = null;
  143. 			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
  144. 			this.closeConnection(conn);
  145. 		}
  146. 		return result;
  147. 	}
  148. 	
  149. 	/**
  150. 	 * 多表更新
  151. 	 * @param list
  152. 	 * @return
  153. 	 * @throws SQLException
  154. 	 * @author lims
  155. 	 * @date 2015-08-28
  156. 	 */
  157. 	public int updateMutilTable(List<Pojo> list) throws SQLException {
  158. 		if (list == null || list.size() == 0) {
  159. 			return 0;
  160. 		}
  161. 		String[] fields;
  162. 		PreparedStatement ps = null;
  163. 		int result = 0;
  164. 		Connection conn=getConnection();
  165. 		try {
  166. 			this.startTransaction(conn);
  167. 			for (Pojo obj : list) {
  168. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  169. 				String sql = getUpdateSql(obj);
  170. 				
  171. 				ps = conn.prepareStatement(sql.toString());
  172. 				
  173. 				fields = pojo_bean.keySet().toArray(
  174. 						new String[pojo_bean.size()+1]);
  175. 				for (int i = 0; i < fields.length; i++) {
  176. 					if(i==fields.length-1) {
  177. 						ps.setObject(i + 1, obj.getId());
  178. 					}
  179. 					else {
  180. 						ps.setObject(i + 1, pojo_bean.get(fields[i]));
  181. 					}
  182. 					
  183. 				}
  184. 				result = ps.executeUpdate();
  185. 			}
  186. 			this.commitTransaction(conn);
  187. 		} catch(Exception e){
  188. 			conn.rollback();
  189. 			throw new RuntimeException(e);
  190. 		} finally {
  191. 			fields = null;
  192. 			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
  193. 			this.closeConnection(conn);
  194. 		}
  195. 		return result;
  196. 	}
  197. 	
  198. 	/**
  199. 	 * 插入数据和更新多表数据
  200. 	 * @param list
  201. 	 * @return
  202. 	 * @throws SQLException
  203. 	 * @author lims
  204. 	 * @date 2015-08-28
  205. 	 */
  206. 	public int insertAndUpdateMutilTable(List<Pojo> saveList,List<Pojo> updateList) throws SQLException {
  207. 		if (saveList == null || saveList.size() == 0 || updateList == null || updateList.size() == 0) {
  208. 			return 0;
  209. 		}
  210. 		String[] fields;
  211. 		PreparedStatement ps = null;
  212. 		int result = 0;
  213. 		Connection conn=getConnection();
  214. 		try {
  215. 			this.startTransaction(conn);
  216. 			for (Pojo obj : saveList) {//插入操作
  217. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  218. 				String sql = getInsertSql(obj);
  219. 				
  220. 				ps = conn.prepareStatement(sql.toString());
  221. 				
  222. 				fields = pojo_bean.keySet().toArray(
  223. 						new String[pojo_bean.size()]);
  224. 				for (int i = 0; i < fields.length; i++) {
  225. 					ps.setObject(i + 1, pojo_bean.get(fields[i]));
  226. 				}
  227. 				result = ps.executeUpdate();
  228. 			}
  229. 			for (Pojo obj : updateList) {//更新操作
  230. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  231. 				String sql = getUpdateSql(obj);
  232. 				
  233. 				ps = conn.prepareStatement(sql.toString());
  234. 				
  235. 				fields = pojo_bean.keySet().toArray(
  236. 						new String[pojo_bean.size()+1]);
  237. 				for (int i = 0; i < fields.length; i++) {
  238. 					if(i==fields.length-1) {
  239. 						ps.setObject(i + 1, obj.getId());
  240. 					}
  241. 					else {
  242. 						ps.setObject(i + 1, pojo_bean.get(fields[i]));
  243. 					}
  244. 					
  245. 				}
  246. 				result = ps.executeUpdate();
  247. 			}
  248. 			this.commitTransaction(conn);
  249. 		} catch(Exception e){
  250. 			conn.rollback();
  251. 			throw new RuntimeException(e);
  252. 		} finally {
  253. 			fields = null;
  254. 			org.apache.commons.dbutils.DbUtils.closeQuietly(ps);
  255. 			this.closeConnection(conn);
  256. 		}
  257. 		return result;
  258. 	}

三、相关调用方法 

  1. protected abstract Connection getConnection() throws SQLException;
  2.  
  3. 	protected abstract void closeConnection(Connection conn);
  4.  
  5. 	private void startTransaction(Connection conn) {
  6. 		try {
  7. 			if (conn != null) {
  8. 				conn.setAutoCommit(false);
  9. 			}
  10. 		} catch (Exception e) {
  11. 			throw new RuntimeException(e);
  12. 		}
  13. 	}
  14.  
  15. 	private void commitTransaction(Connection conn) {
  16. 		try {
  17. 			if (conn != null) {
  18. 				conn.commit();
  19. 			}
  20. 		} catch (Exception e) {
  21. 			throw new RuntimeException(e);
  22. 		}
  23. 	}
  1. /**
  2. 	 * 获取Pojo对应的数据库表名
  3. 	 * 
  4. 	 * @param c
  5. 	 * @return
  6. 	 */
  7. 	private static <T extends Pojo> String getTableName(Class<T> c) {
  8. 		try {
  9. 			String tn = tableNameCache.get(c.getSimpleName());
  10. 			if (tn == null) {
  11. 				tn = c.newInstance().tableName();
  12. 				tableNameCache.put(c.getSimpleName(), tn);
  13. 			}
  14. 			return tn;
  15. 		} catch (Exception e) {
  16. 			log.error("Get " + c.getSimpleName() + " name exception.");
  17. 			return null;
  18. 		}
  19. 	}
  20.  
  21. 	/**
  22. 	 * 获取Pojo的插入sql语句
  23. 	 * 
  24. 	 * @param obj
  25. 	 * @return
  26. 	 */
  27. 	private static String getInsertSql(Pojo obj) {
  28. 		try {
  29. 			String insertSql = insertSqlCache.get(obj.getClass().getName());
  30. 			if (insertSql == null) {
  31. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  32. 				String[] fields = pojo_bean.keySet().toArray(
  33. 						new String[pojo_bean.size()]);			
  34. 				StringBuilder sql = new StringBuilder("INSERT INTO ");
  35. 				sql.append(obj.tableName());
  36. 				sql.append('(');
  37. 				for (int i = 0; i < fields.length; i++) {
  38. 					if (i > 0)
  39. 						sql.append(',');
  40. 					sql.append(fields[i]);
  41. 				}
  42. 				sql.append(") VALUES(");
  43. 				for (int i = 0; i < fields.length; i++) {
  44. 					if (i > 0)
  45. 						sql.append(',');
  46. 					sql.append('?');
  47. 				}
  48. 				sql.append(')');
  49. 				insertSql = sql.toString();
  50. 				sql = null;
  51. 				insertSqlCache.put(obj.getClass().getName(), insertSql);
  52. 			}
  53. 			return insertSql;
  54. 		} catch (Exception e) {
  55. 			log.error("Get " + obj.getClass().getSimpleName()
  56. 					+ " insertSql exception.");
  57. 			return null;
  58. 		}
  59. 	}
  60. 	
  61. 	/**
  62. 	 * 获取Pojo的更新sql语句
  63. 	 * 
  64. 	 * @param obj
  65. 	 * @return
  66. 	 * @author lims
  67. 	 * @date 2015-08-23
  68. 	 */
  69. 	private static String getUpdateSql(Pojo obj) {
  70. 		try {
  71. 			String updateSql = updateSqlCache.get(obj.getClass().getName());
  72. 			if (updateSql == null) {
  73. 				Map<String, String> pojo_bean = obj.listInsertableFields();
  74. 				String[] fields = pojo_bean.keySet().toArray(
  75. 						new String[pojo_bean.size()]);
  76. 				StringBuilder sql = new StringBuilder();
  77. 				sql.append("update "+getTableName(obj.getClass())+" set ");
  78. 				for (int i = 0; i < fields.length; i++) {
  79. 					if (i > 0)
  80. 						sql.append(',');
  81. 					sql.append(fields[i]).append(" = ? ");
  82. 				}
  83. 				sql.append(" where id=?");
  84. 				
  85. 				updateSql = sql.toString();
  86. 				sql = null;
  87. 				updateSqlCache.put(obj.getClass().getName(), updateSql);
  88. 			}
  89. 			return updateSql;
  90. 		} catch (Exception e) {
  91. 			log.error("Get " + obj.getClass().getSimpleName()
  92. 					+ " updateSql exception.");
  93. 			return null;
  94. 		}
  95. 	}


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

  1. import org.apache.commons.beanutils.BeanUtils;
  2.  
  3. import java.io.Serializable;
  4. import java.util.Map;
  5.  
  6. /**
  7.  * 持久化基类
  8.  */
  9. public abstract class Pojo implements Serializable {
  10. 	private static final long serialVersionUID = 1L;
  11. 	protected int id;
  12.  
  13. 	public int getId() {
  14. 		return id;
  15. 	}
  16.  
  17. 	public void setId(int id) {
  18. 		this.id = id;
  19. 	}
  20.  
  21. 	public String tableName() {
  22. 		String tn=getClass().getSimpleName() ;
  23. 		if(tn.endsWith("Bean")) {
  24. 			tn=tn.substring(0,tn.length()-4);
  25. 		}
  26. 		tn = tn.toLowerCase();
  27. 		return tn;
  28. 	}
  29.  
  30. 	protected String cacheRegion() {
  31. 		return this.getClass().getSimpleName();
  32. 	}
  33. 	
  34. 	/**
  35. 	 * 列出要插入到数据库的字段集合，子类可以按照实际需求覆盖
  36. 	 * @return
  37. 	 */
  38. 	public Map<String, String> listInsertableFields() {
  39. 		try {
  40. 			Map<String, String> props = BeanUtils.describe(this);
  41. 			props.remove("id");
  42. 			props.remove("class");
  43. 			for(String s:props.keySet()){
  44. 				if(s.endsWith("_"))props.remove(s);
  45. 			}
  46. 			return props;
  47. 		} catch (Exception e) {
  48. 			throw new RuntimeException("Exception when Fetching fields of "
  49. 					+ this);
  50. 		}
  51. 	}
  52.  
  53. 	@Override
  54. 	public boolean equals(Object obj) {
  55. 		if (obj == null)
  56. 			return false;
  57. 		if (obj == this)
  58. 			return true;
  59. 		// 不同的子类尽管ID是相同也是不相等的
  60. 		if (!getClass().equals(obj.getClass()))
  61. 			return false;
  62. 		Pojo wb = (Pojo) obj;
  63. 		return wb.getId() == getId();
  64. 	}
  65.  
  66. }



声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/142119
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号