封装JDBC事务操作,执行存储过程测试
Oracle数据库端测试环境见:http://www.cnblogs.com/yshyee/p/4392328.html
package com.mw.utils; import java.sql.Connection; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; /** * @author y * @date 2015-4-4 13:33:02 * @version V1.0 * @desc Connection 事务管理 * 使用方式: * (1)首先从TransactionManager获取Connection * (2)开启事务 * ()进行业务处理 * (3)提交事务 * (4)异常回滚事务 * (5)关闭链接 */ public final class TransactionManager { /** * 定义局部线程变量,用于保存Connection */ private static final ThreadLocal<Connection> connThreadLocal = new ThreadLocal<Connection>(); private TransactionManager(){} /** * 采用内部类实现单例 */ private static class TransactionManagerHolder{ private static final TransactionManager instance = new TransactionManager(); } public static TransactionManager getInstance(){ return TransactionManagerHolder.instance; } /** * 1:从当前线程局部变量中获取数据库连接资源 * @return */ public Connection get(){ Connection conn = connThreadLocal.get(); if(null==conn){ conn = JdbcUtil.getConnection(); connThreadLocal.set(conn); } return conn; } /** * 2:开启事务 * @param conn */ public void beginTransaction(Connection conn){ try { if(null!=conn){ if(conn.getAutoCommit()){ conn.setAutoCommit(false); } } }catch (SQLException ex) { Logger.getLogger(TransactionManager.class.getName()).log(Level.SEVERE, null, ex); } } /** * 3:提交事务 * @param conn */ public void commitTransaction(Connection conn){ try { if(null!=conn){ if(!conn.getAutoCommit()){ conn.commit(); } } }catch (SQLException ex) { Logger.getLogger(TransactionManager.class.getName()).log(Level.SEVERE, null, ex); } } /** * 4:回滚事务 * @param conn */ public void rollbackTransaction(Connection conn){ try { if(null!=conn){ if(!conn.getAutoCommit()){ conn.rollback(); } } }catch (SQLException ex) { Logger.getLogger(TransactionManager.class.getName()).log(Level.SEVERE, null, ex); } } /** * 5:关闭链接,将数据库连接从当前线程局部变量中移除 * @param conn */ public void close(Connection conn){ JdbcUtil.release(conn); connThreadLocal.remove(); } }
测试:
package com.mw.test; import com.mw.utils.TransactionManager; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import java.sql.Types; import java.util.List; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; /** * @author y * @date 2015-4-4 12:34:05 * @version 1.0 * @desc */ public class Test { public static void main(String[] args) { QueryRunner qr = new QueryRunner(); Connection conn = TransactionManager.getInstance().get(); TransactionManager.getInstance().beginTransaction(conn); int age = 10; try { CallableStatement cs = conn.prepareCall("{call pack_user.p_user_select(?,?)}"); cs.setInt(1, age); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); String succ = cs.getString(2); if("T".equalsIgnoreCase(succ)){ String sql = "select * from tmp_yshy"; List<Map<String, Object>> list = qr.query(conn, sql, new MapListHandler()); for(Map map:list){ System.out.println("c1:"+map.get("c1")+",c2:"+map.get("c2")); } }else{ System.out.println("succ:"+succ); } TransactionManager.getInstance().commitTransaction(conn); } catch (SQLException ex) { TransactionManager.getInstance().rollbackTransaction(conn); Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex); } finally{ TransactionManager.getInstance().close(conn); } } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。