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 事务管理

 * 使用方式：

 * （１）首先从TransactionManager获取Connection

 * （２）开启事务

 * （）进行业务处理

 * （３）提交事务

 * （４）异常回滚事务

 * （５）关闭链接

 */

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);

        }

    }

}