/**
 * Title:
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:      CSU
 * @author       chenlong
 * @version 1.0
 */
import java.io.ByteArrayInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

/**
 * \u5728DBconnect\u7C7B\u4E2D\u4F7F\u7528\u4E86\u6570\u636E\u5E93\u63D0\u4F9B\u4E86\u4E24\u79CD\u9884\u5236SQL\uFF1A
 * \u9884\u5236\u8BED\u53E5\uFF08prepared statement\uFF09\u548C\u5DF2\u5B58\u8D2E\u8FC7\u7A0B\uFF08stored procedure\uFF09\u3002
 * \u9884\u5236SQL\u4E0E\u7B80\u5355\u7684SQL\u8BED\u53E5\u76F8\u6BD4\u6709\u5F88\u5927\u7684\u4F18\u52BF\uFF1B
 * \u5728\u505A\u5176\u4ED6\u7684\u5E94\u7528\u903B\u8F91\u65F6\uFF0C\u6570\u636E\u5E93\u53EF\u4EE5\u63D0\u524D\u53D6\u5F97SQL\u8BED\u53E5\uFF0C\u5E76\u4E3A\u5B83\u521B\u5EFA\u67E5\u8BE2\u8BA1\u5212\u3002
 * \u8FD9\u6837\u7684SQL\u8BED\u53E5\u6267\u884C\u7684\u66F4\u5FEB\uFF0C\u8FD8\u53EF\u4EE5\u4E3A\u6BCF\u4E2A\u8BED\u53E5\u5EFA\u7ACB\u4E00\u4E2A\u901A\u7528\u7684\u5F15\u7528\u3002
 */

public class DBConnect {
  /**
   * excute query
   */
  public static final int OPERATION_QUERY = 0;

  /**
   * excute insert,update,delete
   */

  public static final int OPERATION_ACTION = 1;

  /**
   * excute sql statment;
   */
  public static final int OBJECT_SQL = 3; // excute sql statment;

  /**
   * execute stored procedure;
   */

  public static final int OBJECT_PROCEDURE = 4; // execute stored procedure;
  private String userName = "";
  private String passWord = "";
  private String dbURL = "";
  private Connection connection = null;

  /**
   * OPERATION_QUERY: execute query; OPERATION_ACTION: execute update, delete, insert
   */

  private int operationType = OPERATION_QUERY; // OPERATION_QUERY: execute query; OPERATION_ACTION: execute update, delete, insert

  /**
   * OBJECT_SQL: sql statment; OBJECT_SP: store procedure;
   */
  private int objectType = OBJECT_SQL; //OBJECT_SQL: sql statment; OBJECT_SP: store procedure;
  
  
  private String errorMessage = "";
  private String defaultDatabase;
  private PoolManager poolManager;

  public DBConnect() {
    try {
      this.connect("postgresql");
    }
    catch (Exception ex) {
      System.out.println(this.getErrorMessage());
    }
  }

  public DBConnect(String server) {
    try {
      this.connect(server);
    }
    catch (Exception ex) {
      System.out.println(this.getErrorMessage());
    }
  }

  /**
   * \u4ECE\u8FDE\u63A5\u6C60\u83B7\u5F97\u6570\u636E\u5E93\u7684\u8FDE\u63A5
   *@param sever \u670D\u52A1\u5668\u540D\u79F0
   */
  public void connect(String server) throws NoConnectionException {
    try {
      poolManager = PoolManager.getInstance();
      connection = poolManager.getConnection(server);
      if (connection == null) {
        this.setErrorMessage(
            "\u5EFA\u7ACB\u6570\u636E\u5E93\u8FDE\u63A5\u5931\u8D25\uFF01");
        System.out.println(this.getErrorMessage());
        
        throw new NoConnectionException(this.getErrorMessage());
      }
    }
    catch (Exception ex) {
    	ex.printStackTrace();
      this.setErrorMessage(ex.toString());
    }
  }

  /**
   * execute a query statment to the connection
   * @param sql sql\u8BED\u53E5
   * @return the ResultSet or Number ready for use.
   */
  public Object execute(String sql) {
    return this.execute(sql, null, this.operationType, this.objectType);
    
  }

  /**
   * execute a query statment to the connection
   *
   * @param sql sql\u8BED\u53E5
   * @param parameters stored procedure para
   *
   * @return the ResultSet or Number ready for use.
   */

  public Object execute(String sql, Object[][] parameters) {
    return this.execute(sql, parameters, this.operationType, this.objectType);
  }

  /**
   * execute a query statment to the connection
   *
   * @param sql sql\u8BED\u53E5
   * @param parameters stored procedure para
   * @param objectType  OBJECT_SQL: sql statment; OBJECT_SP: store procedure;
   *
   * @return the ResultSet or Number ready for use.
   */
  public Object execute(String sql, int operationType, int objectType) {
    return this.execute(sql, null, operationType, objectType);
  }

  /**
   * execute a query statment to the connection
   *
   * @param sql sql\u8BED\u53E5
   * @param parameters stored procedure para
   * @param objectType  OBJECT_SQL: sql statment; OBJECT_SP: store procedure;
   *
   * @return the ResultSet or Number ready for use.
   */
  public Object execute(String sql, int operationType) {
    return this.execute(sql, null, operationType, this.objectType);
  	
  }
  
  

  /**
   * execute the operation specified by the user, and get the resultset or the affect rows number if success
   * or return null and set the error message if failed.
   *
   * @param sql sql\u8BED\u53E5
   * @param parameters stored procedure para
   * @param objectType  OBJECT_SQL: sql statment; OBJECT_SP: store procedure;
   *
   * @return the ResultSet or Number ready for use.
   */
  public Object execute(String sql, Object[][] parameters, int operationType,
                        int objectType) {
    if (connection == null) {
      return this.getErrorMessage();
    }
    this.setErrorMessage(""); // clear the error message
    Object o = null;
    try {

      if (objectType == DBConnect.OBJECT_PROCEDURE) {
        CallableStatement statement = null;
        if (operationType == DBConnect.OPERATION_QUERY) {
          statement = connection.prepareCall(sql, ResultSet.TYPE_FORWARD_ONLY,
                                             ResultSet.CONCUR_READ_ONLY);
        }
        else if (operationType == DBConnect.OPERATION_ACTION) {
          statement = connection.prepareCall(sql);

          if (parameters != null) {
            for (int i = 0; i < parameters.length; i++) {
              Integer col = (Integer) parameters[i][0];
              String type = (String) parameters[i][1];
              byte[] b = (byte[]) (parameters[i][2]);
              if (b == null) {
                b = new byte[1];
              }

              if (type == "text") {
                java.io.ByteArrayInputStream bi = new ByteArrayInputStream(b);
                statement.setAsciiStream(col.intValue(), bi, b.length);
              }
              else if (type == "binary") {
                java.io.ByteArrayInputStream bi = new ByteArrayInputStream(b);
                statement.setBinaryStream(col.intValue(), bi, b.length);
              }
            }
          }
        } 
        statement.execute();
        if (operationType == DBConnect.OPERATION_QUERY) {
          o = statement.getResultSet(); // get the first resultset
          if (o == null) {
            int i = 0;
            while (!statement.getMoreResults()) {
              i++;
              if (i > 1000) {
                break;
              }
            }
            o = statement.getResultSet();
          }

        }
        else if (operationType == DBConnect.OPERATION_ACTION) { // return affected rows
          o = new Integer(statement.getUpdateCount());
        }

      }
      else if (objectType == DBConnect.OBJECT_SQL) {
        if (operationType == DBConnect.OPERATION_QUERY) {
          java.sql.Statement statement = connection.createStatement(ResultSet.
              TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
          o = statement.executeQuery(sql);
        }
        else if (operationType == DBConnect.OPERATION_ACTION) {
          java.sql.PreparedStatement statement = connection.prepareStatement(
              sql);
          if (parameters != null) {
            for (int i = 0; i < parameters.length; i++) {
              Integer col = (Integer) parameters[i][0];
              String type = (String) parameters[i][1];
              byte[] b = (byte[]) (parameters[i][2]);
              System.out.println(b);
              System.out.println(type);
              if ("text".equals(type)) {
                java.io.ByteArrayInputStream bi = new ByteArrayInputStream(b);
                statement.setAsciiStream(col.intValue(), bi, b.length);
              }
              else {
                if ("binary".equals(type)) {
                  java.io.ByteArrayInputStream bi = new ByteArrayInputStream(b);
                  statement.setBinaryStream(col.intValue(), bi, b.length);
                }
              }
            }
          }
          o = new Integer(statement.executeUpdate()); // returning the affected rows number;
        }
      }
    }
    catch (java.sql.SQLException e) {
      this.setErrorMessage(e.toString());
      e.printStackTrace();
    }
    return o;
  }

  private String getUserName() {
    return userName;
  }

  public void setUserName(String userName) {
    this.userName = userName;
  }

  public void setPassWord(String passWord) {
    this.passWord = passWord;
  }

  private String getPassWord() {
    return passWord;
  }

  public void setDbURL(String dbURL) {
    this.dbURL = dbURL;
  }

  public String getDbURL() {
    return dbURL;
  }

  private void setErrorMessage(String errorMessage) {
    this.errorMessage = errorMessage;
  }

  public String getErrorMessage() {
    return errorMessage;
  }

  public int getOperationType() {
    return operationType;
  }

  public void setOperationType(int operationType) {
    this.operationType = operationType;
  }

  public int getObjectType() {
    return objectType;
  }

  public void setObjectType(int objectType) {
    this.objectType = objectType;
  }

  public void setDefaultDatabase(String defaultDatabase) {
    this.defaultDatabase = defaultDatabase;
  }

  public String getDefaultDatabase() {
    return defaultDatabase;
  }

  /**
   * free a connection to the connection pool
   */
  public void freeConnection() {
    try {
      if (connection != null) {
        //System.out.println("  Got connection!");
        poolManager.freeConnection("postgresql", connection);
      }
    }
    catch (Exception e) {
      System.out.println(e.toString());
    }
  }

  /**
   * release all connnection
   */
  public void releaseall() {
    try {
      poolManager.release();
    }
    catch (Exception e) {
      System.out.println(e.toString());
    }

  }


  //sample for Database connection
  public static void main(String[] args) {
    DBConnect cn = new DBConnect();
    try 
    {
    	//如果是查詢就用DBConnect.OPERATION_QUERY作為第二個(gè)參數(shù)，如果是insert,updata,delete等修改數(shù)據(jù)庫的操作就用DBConnect.OPERATION_ACTION
    	ResultSet rs = (ResultSet)cn.execute(" select * from d ",DBConnect.OPERATION_QUERY);
    	while (rs.next()) 
    	{
    		System.out.println(rs.getInt(1));
        }
	//很重要！！！關(guān)閉ResultSet
      	rs.close();
    }
    catch (Exception e) {
      	e.printStackTrace();
    }
    finally {
    	//很重要！！！退還聯(lián)接
      	cn.freeConnection();
    }

  }

}