transaction.java

信息发布 发布系统 动态的菜单 和 信息统计

package com.xuntian.material.data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.xuntian.material.data.idao.TransactionManager;
import com.xuntian.material.exception.ConnectPoolException;
import com.xuntian.material.util.Constants;
import com.xuntian.material.util.DBConfig;
import com.xuntian.material.util.LogUtil;

/**
 * @author lip Mar 7, 2006 copyright@xuntian 在事务中直接管理对数据库的操作 包装connection的获得和释放
 */
public class Transaction implements TransactionManager {

    private Savepoint sp = null;

    private PoolManager pm = PoolManager.getInstance();

    private Connection conn = null;
    private boolean isAutoTransaction=false;
    
    private int errorCodeUniqueKey = DBConfig.ERROR_CODE_UNIQUE_KEY;

    /**
     * 取得连接 设置自动提交为false
     */
    private Transaction() throws ConnectPoolException {
        try {
            this.conn = pm.checkOut();
            this.setAutoCommit(Constants.AUTO_COMMIT);
            this.setAutoTransaction(Constants.AUTO_TRANSACTION);
        } catch (ConnectPoolException e) {
            freeConn(this.conn);
        }
    }

    public static Transaction getInstance() throws ConnectPoolException {
        return new Transaction();
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.data.dao.TranManager#begin()
     */
    public void begin() throws ConnectPoolException {
        this.addSavePoint();
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.data.dao.TranManager#commit()
     */
    public void commit() throws ConnectPoolException {
        try {
            this.conn.commit();
        } catch (SQLException e) {
            String message = "Commit fail!";
            logAndThrowConnectPoolException(message + e.getMessage(), e);
        } 
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.data.dao.TranManager#rollback()
     */
    public void rollback() throws ConnectPoolException {
        try {
            if(this.conn==null){
                String message = "Rollbacked conn is null!";
                throw new ConnectPoolException(message);
            }else{
                this.conn.rollback(this.sp);
            }
        } catch (SQLException e) {
            String message = "Rollback fail!";
            logAndThrowConnectPoolException(message + e.getMessage(), e);
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.data.dao.TranManager#addSavePoint()
     */
    public Savepoint addSavePoint() throws ConnectPoolException {
        try {
            this.sp = this.conn.setSavepoint();
        } catch (SQLException e) {
            String message = "Add save point fail!";
            freeConn(this.conn);

            logAndThrowConnectPoolException(message + e.getMessage(), e);
        }
        return sp;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.data.dao.TranManager#rollback(java.sql.Savepoint)
     */
    public void rollback(Savepoint sp) throws ConnectPoolException {
        try {
            this.conn.rollback(sp);
        } catch (SQLException e) {
            String message = "Rollback save point fail!";
            logAndThrowConnectPoolException(message + e.getMessage(), e);
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.data.dao.TranManager#setAutoCommint(boolean)
     */
    public void setAutoCommit(boolean isAuto) throws ConnectPoolException {
        return;
        /*try {
            this.conn.setAutoCommit(isAuto);
        } catch (SQLException e) {
            String message = new StringBuffer().append("Set auto commit [")
                    .append(isAuto).append("] fail!").toString();
            logAndThrowConnectPoolException(message + e.getMessage(), e);
        }*/
    }

    public void freeConn(Connection conn) throws ConnectPoolException {
        pm.checkIn(conn);
    }

    private synchronized void logAndThrowConnectPoolException(String message,
            Exception e) throws ConnectPoolException {
        LogUtil.getLogger(this).error(message);
        throw new ConnectPoolException(message + e.getMessage(), e);
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#executeQuery(java.lang.String,
     *      java.util.List)
     */
    public ResultSet executeQuery(String sql, List<String> parameterList)
            throws ConnectPoolException {
        ResultSet rs = null;
        LogUtil.getLogger(this).info(sql+parameterList);
        try {
            if(parameterList==null){
                Statement st=this.conn.createStatement();
                rs=st.executeQuery(sql);
            }else{
            
                PreparedStatement pst = this.conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                if (parameterList != null) {
                    int i = 0;
    
                    for (String parameter : parameterList) {
                        pst.setString(++i, parameter);
                    }
                }
                rs = pst.executeQuery();
            }
            // pst.close();
        } catch (SQLException e) {
            String message = new StringBuffer().append(
                    "Query with parameters fail!\n\t").append(
                    "SQLException message:[").append("]\n\tSQL=[").append(sql)
                    .append("]\n\tparameter=").append(parameterList).toString();
            logAndThrowConnectPoolException(message, e);
        } finally {

            freeConn(this.conn);
        }
        return rs;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#executeQuery(java.lang.String)
     */
    public ResultSet executeQuery(String sql) throws ConnectPoolException {
        ResultSet rs = null;
        rs = executeQuery(sql, null);
        return rs;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#executeUpdate(java.util.List,
     *      java.util.List)
     */
    public int executeUpdate(List<String> sqlList,
            List<List<String>> parametersList) throws ConnectPoolException {
        LogUtil.getLogger(this).info(sqlList);
        LogUtil.getLogger(this).info(parametersList);
        int sqlSize = sqlList.size();
        if (parametersList != null) {
            int parametersSize = parametersList.size();
            if (sqlSize != parametersSize) {
                String message = new StringBuffer().append(
                        "SQL list size != parameter list size!\n\tSQL list=")
                        .append(sqlList).append("\n\tparameters list").append(
                                parametersList).toString();
                logAndThrowConnectPoolException(message, null);
            }
        }
        int row = 0;
        try {
            String sql;
            List<String> parameterList;
            PreparedStatement pst;
            if(isAutoTransaction()){
               begin(); 
            }
            for (int i = 0; i < sqlSize; i++) {
                sql = sqlList.get(i);
                pst = this.conn.prepareStatement(sql);
                if (parametersList != null) {
                    parameterList = parametersList.get(i);
                    if (parameterList != null) {
                        int ii = 0;
                        for (String parameter : parameterList) {
                            pst.setString(++ii, (parameter==null||parameter.equals(""))? null : parameter);
                        }
                    }
                }
                row += pst.executeUpdate();
            }
        } catch (SQLException e) {
            if(isAutoTransaction()){
                rollback();
            }
            String message = new StringBuffer().append(
                    "Batch update with parameters fail!\n\t").append(
                    "SQLException message:[").append(e.getMessage()).append(
                    "]\n\tSQL=").append(sqlList).append("\n\tparameter=")
                    .append(parametersList).toString();
            if (e.getErrorCode() == errorCodeUniqueKey) {
                LogUtil.getLogger(this).error(message);
                return -1;
            }
            logAndThrowConnectPoolException(message, e);
        }finally{
            if(isAutoTransaction()){
                commit();
            }
            freeConn(this.conn);
        }
        return row;
    }

    

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#executeUpdate(java.util.List)
     */
    public int executeUpdate(List<String> sqlList) throws ConnectPoolException {
        return executeUpdate(sqlList, null);
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#executeUpdate(java.lang.String,
     *      java.util.List)
     */
    public int executeUpdate(String sql, List<String> parameterList)
            throws ConnectPoolException {
        List<String> sqlList = new ArrayList<String>();
        sqlList.add(sql);
        List<List<String>> parametersList = null;
        if (parameterList != null) {
            parametersList = new ArrayList<List<String>>();
            parametersList.add(parameterList);
        }
        return executeUpdate(sqlList, parametersList);
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#executeUpdate(java.lang.String)
     */
    public int executeUpdate(String sql) throws ConnectPoolException {
        List<String> sqlList = new ArrayList<String>();
        sqlList.add(sql);
        return executeUpdate(sqlList, null);
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#query(java.lang.String)
     */
    public List<List<String>> query(String sql) throws ConnectPoolException {
        ResultSet rs = executeQuery(sql);
        return getResltSetToList(rs);
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.xuntian.material.data.idao.TransactionManager#query(java.lang.String,
     *      java.util.List)
     */
    public List<List<String>> query(String sql, List<String> parameterList)
            throws ConnectPoolException {

        ResultSet rs = executeQuery(sql, parameterList);
        return getResltSetToList(rs);
    }

    private List<List<String>> getResltSetToList(ResultSet rs)
            throws ConnectPoolException {
        List<String> rowResult = null;
        List<List<String>> allResult = null;
        try {
            int columCount = rs.getMetaData().getColumnCount();
            rs.last();
            int rowCount = rs.getRow();
            rs.beforeFirst();
            allResult = new ArrayList<List<String>>(rowCount);
            while (rs.next()) {
                rowResult = new ArrayList<String>(columCount);
                for (int i = 1; i <= columCount; i++) {
                    String tmp = rs.getString(i);
                    rowResult.add((tmp == null ? "" : tmp));
                }
                allResult.add(rowResult);
            }
            rs.close();
        } catch (SQLException e) {
            logAndThrowConnectPoolException("结果转化到页面table支持类型时错误！"
                    + e.getMessage(), e);
        }
        
        return allResult;
    }

    /**
     * @param clazz
     * @param rs
     * @return 判断结果集合是否有数据
     */
    public boolean isResultNull(Class clazz, ResultSet rs) {
        boolean isNull = false;
        try {
            isNull = rs.next();
        } catch (SQLException e) {
            LogUtil.getLogger(clazz).error("判断结果集合时错误" + e.getMessage());
        }
        return isNull;
    }

    public void setAutoTransaction(boolean isAutoTransaction) {
        return;
//        this.isAutoTransaction = isAutoTransaction;
    }
    private boolean isAutoTransaction() {
        return this.isAutoTransaction;
    }

    public void freeConn() throws ConnectPoolException {
        freeConn(this.conn);
    }
}