package cmd.db;


import java.sql.*;
import java.util.*;
import javax.sql.*;

import org.apache.commons.pool.*;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;

public class ConnDBnew {
  public static final int STONE_CONN = 0;
  public static final int POOL_CONN = 1;
  public static final int sybase_CONN = 2;
  public static final boolean AUTO_COMMIT = true;
  public static final boolean NOT_AUTO_COMMIT = false;
  private static Hashtable hconnDBPara = new Hashtable();
  public String sDBDriver = null;
  public String sConnStr = null;
  public String sUser = null;
  public String sPwd = null;
  public String sJndi = null;
  public String sJndiDB = null;
  public String sMOServerPost = null;

  public String sDBDriverOther = null;
  public String sConnStrOther = null;
  public String sUserOther = null;
  public String sPwdOther = null;


  public int iConnMethod = STONE_CONN;
  public boolean bAutoCommit = NOT_AUTO_COMMIT;

  public Connection conn = null;
  public ResultSet rs = null;
  public Statement stmt = null;
  public PreparedStatement pstmt = null;
  public String sErrMsg = null;


  public void setConnMethod(int iConndbMethod) {
    iConnMethod = iConndbMethod;
  }

  public void setAutoCommit(boolean bCommit) {
    bAutoCommit = bCommit;
  }


  public boolean loadProperty() {
    try {
      ConnDBPara connDBPara = (ConnDBPara) hconnDBPara.get("Default");
      if (connDBPara == null) {
        connDBPara = new ConnDBPara("");
        hconnDBPara.put("Default", connDBPara);
      }

      sUser = connDBPara.sUserName;
      sPwd = connDBPara.sPassword;
      sDBDriver = connDBPara.sDBDriver;
      sConnStr = connDBPara.sConnStr;

      sUserOther = connDBPara.sUserNameOther;
      sPwdOther = connDBPara.sPasswordOther;
      sDBDriverOther = connDBPara.sDBDriverOther;
      sConnStrOther = connDBPara.sConnStrOther;

      sMOServerPost = connDBPara.sMOServerPost;
      //System.out.println(sConnStrOther);

      //Class.forName(sDBDriver);
    } catch (Exception e) {
      System.out.println("Read XML ERR: " + e);
    }

    return true;
  }

  public Connection getConn() {
    try {
      if (conn == null) {
        if (loadProperty()) {
          try {
            Class.forName(sDBDriverOther).newInstance();
            String url = sConnStrOther;
            Properties SysProps = System.getProperties();
            SysProps.put("user", sUserOther);
            SysProps.put("password", sPwdOther);
            //MSSQL連接方法
            //DataSource dataSource = setupDataSource(url+";user="+sUserOther+";Password="+sPwdOther);
            //MySQL連接方法
            //String sUrl = url+"?user="+sUser+"&Password="+sPwd;
            System.out.println(url);
            DataSource dataSource = setupDataSource(url + "?user=" + sUserOther +"&password=" + sPwdOther);
            conn = dataSource.getConnection();
          } catch (Exception e) {
            System.out.println(e);
          }
          conn.setAutoCommit(bAutoCommit);
        }
      }
    } catch (SQLException e) {
      conn = null;
      System.out.println("ConnDB::getConn Error " + e.toString());
    }
    return conn;
  }

  public boolean closeStmt() {
    try {
      if (rs != null) {
        rs.close();
      }
      if (stmt != null) {
        stmt.close();
      }
      if (pstmt != null) {
        pstmt.close();
      }
    } catch (SQLException e) {
      System.out.println("ConnDB::closeStmt Error " + e.toString());
      return false;
    }
    rs = null;
    stmt = null;
    pstmt = null;
    return true;
  }

  public PreparedStatement createPstmt(String sSQL) {
    try {
      closeStmt();
      if (conn == null) {
        conn = getConn();
      }
      if (conn != null) {
        pstmt = conn.prepareStatement(sSQL);
      }
    } catch (SQLException e) {
      System.out.println("ConnDB::createPstmt Error " + e.toString());
      return null;
    }
    return pstmt;
  }

  public ResultSet executeQuery() {
    try {
      rs = pstmt.executeQuery();
    } catch (SQLException e) {
      rs = null;
      System.out.println("ConnDB::executeQuery Error " + e.toString());
    }
    return rs;
  }

  public int executeUpdate() {
    int retval = 0;
    try {
      retval = pstmt.executeUpdate();
    } catch (SQLException e) {
      retval = 0;
      System.out.println("ConnDB::executeUpdate Error " + e.toString());
    }
    return retval;
  }

  public void commit() {
    try {
      conn.commit();
    } catch (SQLException e) {
      System.out.println("ConnDB::commit Error " + e.toString());
    }
  }

  public void rollback() {
    try {
      conn.rollback();
    } catch (SQLException e) {
      System.out.println("ConnDB::rollback Error " + e.toString());
    }
  }

  public void close() {
    try {
      closeStmt();
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException e) {
      System.out.println("ConnDB::close Error " + e.toString());
    }

  }

  public static DataSource setupDataSource(String connectURI) {
    //
    // First, we'll need a ObjectPool that serves as the
    // actual pool of connections.
    //
    // We'll use a GenericObjectPool instance, although
    // any ObjectPool implementation will suffice.
    //
    ObjectPool connectionPool = new GenericObjectPool(null);

    //
    // Next, we'll create a ConnectionFactory that the
    // pool will use to create Connections.
    // We'll use the DriverManagerConnectionFactory,
    // using the connect string passed in the command line
    // arguments.
    //
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
        connectURI, null);

    //
    // Now we'll create the PoolableConnectionFactory, which wraps
    // the "real" Connections created by the ConnectionFactory with
    // the classes that implement the pooling functionality.
    //
    PoolableConnectionFactory poolableConnectionFactory = new
        PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);

    //
    // Finally, we create the PoolingDriver itself,
    // passing in the object pool we created.
    //
    PoolingDataSource dataSource = new PoolingDataSource(connectionPool);

    return dataSource;
  }


  public static void main(String args[]) {
    ConnDBnew conndb = new ConnDBnew();
    conndb.setConnMethod(ConnDBnew.STONE_CONN);
    conndb.setAutoCommit(ConnDBnew.AUTO_COMMIT);
    CodeChange code = new CodeChange();
    //String sSQL = "SELECT * FROM Mobile_user_Record WHERE SmsId <= ?";
    String sSQL = "SELECT * FROM delivertask";
    System.out.println(sSQL);
    PreparedStatement pstmt = conndb.createPstmt(sSQL);
    if (pstmt != null) {
      try {
        //pstmt.setInt(1, 5);
        ResultSet rs = conndb.executeQuery();
        while (rs != null && rs.next()) {
          //System.out.print(rs.getString(2));
          System.out.println("The DB SQL: " + code.toChinese(rs.getString("msgconten")));
        }
      }
      catch (SQLException e) {
        System.out.println("ConnDB::main Error " + e.toString());
      }
      conndb.close();

    }

  }
}