package com.comm.db;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import javax.sql.*;

import com.comm.*;
import com.comm.util.*;
import com.comm.vo.*;

public class DBMgr implements DBMgrInterface {
    private DataSource ds = null;
    private ThreadConnectionMap tcMap = new ThreadConnectionMap();

    public void setDatasource(DataSource ds) throws AppException {
        if (ds == null) {
            throw new AppException("null parameter");
        } else if (this.ds == null) {
            this.ds = ds;
        } else {
            throw new AppException("datasource already existed!");
        }

    }

    public void begin() throws SQLException {
        try {
            Connection con = getConnection();
            if (con == null) {
                throw new SQLException("no connection");
            }
            con.setAutoCommit(false);
        } catch (SQLException ex) {
            LoggerUtil.error(DBMgr.class, ex);
            throw ex;
        }
    }

    public void commit() throws SQLException {
        Connection conn = tcMap.getConnection();
        if (conn != null && conn.getAutoCommit() == false) {
            conn.commit();
            conn.setAutoCommit(true);
            tcMap.releaseConnection();
        }

    }

    public void rollback() throws SQLException {
        Connection conn = tcMap.getConnection();
        if (conn != null && conn.getAutoCommit() == false) {
            conn.rollback();
            conn.setAutoCommit(true);
            tcMap.releaseConnection();
        }

    }

    private Connection getConn() throws SQLException {
        Connection conn = ds.getConnection();
        conn.setAutoCommit(true);
        return conn;
    }

    public Connection getConnection() throws SQLException {
        return tcMap.getConnection();
    }

    public int execute(String sql, Vector param) throws SQLException {

        int error = 0;
        Connection conn = null;
        try {
            conn = getConnection();
            error = execute(sql, param, conn);

        } catch (SQLException ex) {
            LoggerUtil.error("Exception execute:" + sql + ":");
            LoggerUtil.error(param);
            LoggerUtil.error(DBMgr.class, ex);
            throw ex;
        } finally {
            tcMap.releaseConnection();
        }
        return error;
    }

    public int execute(String sql, Vector param
                       , Connection conn) throws SQLException {

        int error = 0;
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < param.size(); i++) {
            if (null == param.elementAt(i)) {
                pstmt.setString(i + 1, "");
            } else if (param.elementAt(i) instanceof String) {
                pstmt.setString(i + 1, (String) param.elementAt(i));
            } else if (param.elementAt(i) instanceof Timestamp) {
                pstmt.setTimestamp(i + 1, (Timestamp) param.elementAt(i));
            } else if (param.elementAt(i) instanceof Date) {
                pstmt.setTimestamp(i + 1
                                   ,
                                   new Timestamp(((Date) param.elementAt(i)).
                                                 getTime()));
            } else if (param.elementAt(i) instanceof Integer) {
                pstmt.setInt(i + 1, ((Integer) param.elementAt(i)).intValue());
            } else if (param.elementAt(i) instanceof Double) {
                pstmt.setDouble(i + 1
                                , ((Double) param.elementAt(i)).doubleValue());
            } else {
                pstmt.setString(i + 1, (String) param.elementAt(i));
            }
        }
        error = pstmt.executeUpdate();
        return error;
    }

    public int[] executeBatch(String sql, Vector param) throws SQLException {

        int error[] = null;
        Connection conn = null;
        try {
            conn = getConnection();
            error = executeBatch(sql, param, conn);
        } catch (SQLException ex) {
            LoggerUtil.error(" Exception execute:" + sql + ":");
            LoggerUtil.error(param);
            LoggerUtil.error(DBMgr.class, ex);
            throw ex;
        } catch (Exception ex) {
            LoggerUtil.error(" Exception execute:" + sql + ":");
            LoggerUtil.error(param);
            LoggerUtil.error(DBMgr.class, ex);
            throw new SQLException(ex.getMessage());
        }

        finally {
            try {
                tcMap.releaseConnection();
            } catch (SQLException ex1) {
                LoggerUtil.error(DBMgr.class, ex1);
            }
        }
        return error;
    }

    public int[] executeBatch(Vector sqls) throws SQLException {

        int error[] = null;
        Connection conn = null;
        try {
            conn = getConnection();
            error = executeBatch(sqls, conn);
        } catch (SQLException ex) {
            LoggerUtil.error(" Exception execute:");
            LoggerUtil.error(sqls);
            LoggerUtil.error(DBMgr.class, ex);
            throw ex;
        } finally {
            try {
                tcMap.releaseConnection();
            } catch (SQLException ex1) {
                LoggerUtil.error(DBMgr.class, ex1);
            }
        }
        return error;
    }

    public int[] executeBatch(Vector sqls, Connection conn) throws SQLException {

        int[] error;
        Statement stmt = conn.createStatement();
        for (int i = 0; i < sqls.size(); i++) {
            stmt.addBatch(BaseUtil.toString(sqls.elementAt(i)));
        }

        error = stmt.executeBatch();

        return error;
    }

    public int[] executeBatch(String sql, Vector params
                              , Connection conn) throws SQLException {

        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int j = 0; j < params.size(); j++) {
            if (params.elementAt(j) instanceof Vector) {

                Vector param = (Vector) params.elementAt(j);
                for (int i = 0; i < param.size(); i++) {
//                    LoggerUtil.debug("param " + i + " in Mgr :"
//                                     + param.elementAt(i));
//                    LoggerUtil.debug("sss=" + param.elementAt(i));
                    if (null == param.elementAt(i)) {
//                        LoggerUtil.debug("param.elementAt(i) is null");
                    } else {
//                        LoggerUtil.debug(param.elementAt(i).getClass().toString());
                    }
                    if (null == param.elementAt(i)) {
//                        LoggerUtil.debug("shit!!!!!!!!!!!");
                        pstmt.setString(i + 1, "");
//                        LoggerUtil.debug("shit!!!!!!!!!!!");
                    } else if (param.elementAt(i) instanceof String) {
                        pstmt.setString(i + 1, (String) param.elementAt(i));
                    } else if (param.elementAt(i) instanceof Date) {
                        pstmt.setDate(i + 1, (Date) param.elementAt(i));
                    } else if (param.elementAt(i) instanceof Integer) {
//                        LoggerUtil.debug((Integer) param.elementAt(i));
//                        LoggerUtil.debug(""
//                                         +
//                                         ((Integer) param.elementAt(i)).
//                                         intValue());
                        pstmt.setInt(i + 1
                                     , ((Integer) param.elementAt(i)).intValue());
                    } else if (param.elementAt(i) instanceof Double) {
                        pstmt.setDouble(i + 1
                                        ,
                                        ((Double) param.elementAt(i)).
                                        doubleValue());
                    } else {
                        pstmt.setString(i + 1, (String) param.elementAt(i));
                    }
                }
            } else {
                if (null == params.elementAt(j)) {
                    pstmt.setString(1, "");
                } else if (params.elementAt(j) instanceof String) {
                    pstmt.setString(1, (String) params.elementAt(j));
                } else if (params.elementAt(j) instanceof Date) {
                    pstmt.setDate(1, (Date) params.elementAt(j));
                } else if (params.elementAt(j) instanceof Integer) {
                    pstmt.setInt(1, ((Integer) params.elementAt(j)).intValue());
                } else if (params.elementAt(j) instanceof Double) {
                    pstmt.setDouble(1
                                    ,
                                    ((Double) params.elementAt(j)).doubleValue());
                } else {
                    pstmt.setString(1, (String) params.elementAt(j));
                }

            }
            pstmt.addBatch();
        }
        int[] error = pstmt.executeBatch();
        return error;
    }

    private ResultSet getResultSet(String sql, Vector param
                                   , Connection conn) throws SQLException {

        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setFetchSize(30);
        for (int i = 0; i < param.size(); i++) {