/**
 * 數據庫訪問公用
 */
package com.db;

import java.sql.*;
import java.util.*;
import java.io.*;

public class DBLib {

  private static final String CFG_RES_NAME = "DBLib"; //DBLib.class.getName()

  private static String DRIVER_NAME;
  private static String USER_NAME;
  private static String PASSWORD;
  private static String CONNECTION_URL;

  static boolean loadDriverOK = true;
  static int connCount = 0;
  static Connection pooledConn = null; // 只用這一個連接

  static {
    try {
      initConfig();
      Class.forName(DRIVER_NAME);
    }
    catch (Exception e) {
      loadDriverOK = false;
    }
  }

  /**
   * initConfig 根據配置文件初始化
   */
  private static void initConfig() throws NullPointerException, MissingResourceException{
    ResourceBundle rb = ResourceBundle.getBundle(CFG_RES_NAME);
    if (rb != null){
      DRIVER_NAME = rb.getString("DRIVER_NAME");
      PASSWORD = rb.getString("PASSWORD");
      CONNECTION_URL = rb.getString("CONNECTION_URL");
      USER_NAME = rb.getString("USER_NAME");
    }
  }

  /**
   * genConnection 產生一個連接
   * @return
   * @throws SQLException
   */
  static Connection genConnection() throws SQLException{
    return loadDriverOK ? DriverManager.getConnection(CONNECTION_URL, USER_NAME, PASSWORD) : null;
  }

  /**
   * getConnection 獲取一個連接
   * @return
   * @throws SQLException
   */
  public static Connection getConnection() throws SQLException{
    if (pooledConn != null && pooledConn.isClosed()){ connCount = 0; pooledConn = null; }

    if (pooledConn == null){
      pooledConn = genConnection();
    }
    connCount++;
    return pooledConn;
  }

  /**
   * freeConnection 釋放連接(pooledConn專用，如果連接不是pooledConn則不處理)
   * @param conn
   * @throws SQLException
   */
  public static void freeConnection(Connection conn) throws SQLException{
    if (conn != null && conn == pooledConn && --connCount == 0){
      pooledConn.close();
    }
  }

  /**
   * exec 在一個事務里面執行statements語句組
   * @param sql
   * @return
   */
  public static boolean exec(String[] statements) {
    if (!loadDriverOK) return false;

    boolean is_ok = true;

    Connection conn = null;
    Statement stmt = null;

    try {
      conn = genConnection(); //getConnection();

      conn.setAutoCommit(false);
      stmt = conn.createStatement();

      for (int i=0; i < statements.length; i++)
        stmt.executeUpdate(statements[i]);

      conn.commit();
    }
    catch (SQLException e) {

      is_ok = false;
      if (conn != null) {
        try {
          conn.rollback();
        }
        catch (Exception ex) {}
      }
      e.printStackTrace();
    }
    finally {
      try {
        conn.close(); conn = null;//freeConnection(conn);
      }
      catch (Exception e) {}
    }

    return is_ok;
  }

  /**
   * exec 執行不返回查詢結果的SQL語句
   * @param sql
   * @return 執行成功返回true，否則false
   */
  public static boolean exec(String sql) {
    String[] statements = {sql};
    return exec(statements);
  }

  /**
   * getIndexRecords 返回的Vector的元素是String[]，每個String[]元素是一條紀錄
   * @param query
   * @return
   */
  public static IndexRecords getIndexRecords(String query) {
    if (!loadDriverOK) return null;

    IndexRecords result = new IndexRecords();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rest = null;

    try {
      conn = getConnection();

      stmt = conn.createStatement();
      rest = stmt.executeQuery(query);
      int cols = rest.getMetaData().getColumnCount();

      while (rest.next()) {
        String[] temp = new String[cols];
        for (int i = 0; i < cols; i++) {
          temp[i] = rest.getString(i + 1);
        }
        result.addElement(temp);
      }
    }
    catch (SQLException e) {
      result = null;
      e.printStackTrace();
    }
    finally {

      try {
        if (rest != null) {
          rest.close();
        }
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          freeConnection(conn);
        }
      }
      catch (SQLException e) {}
    }

    return result;
  }

  /**
   * getNameRecords 返回的Vector的元素是HashMap，每個HasMap對象是一條以列名為索引的紀錄
   * @param query
   * @return
   */
  public static NameRecords getNameRecords(String query){
    if (!loadDriverOK) return null;

    NameRecords result = new NameRecords();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rest = null;

    try {
      conn = getConnection();
      stmt = conn.createStatement();
      rest = stmt.executeQuery(query);

      ResultSetMetaData rsMetaData = rest.getMetaData();
      int cols = rsMetaData.getColumnCount();
      NameRecord nameRecord = null;

      // 獲取列名
      String[] colLabels = new String[cols];
      for (int i = 0; i < cols; i++){
        colLabels[i] = rsMetaData.getColumnName(i+1);
      }

      while (rest.next()) {
        nameRecord = new NameRecord();
        for (int i = 0; i < cols; i++) {
          nameRecord.put(colLabels[i], rest.getString(i + 1));
        }
        result.addElement(nameRecord);
      }

    }
    catch (SQLException e) {
      result = null;
      e.printStackTrace();
    }
    finally {

      try {
        if (rest != null) {
          rest.close();
        }
        if (stmt != null) {
          stmt.close();
        }
        freeConnection(conn);
      }
      catch (SQLException e) {}
    }

    return result;
  }

  public static void main(String[] args) throws Exception{
    /** getRecordsByIndex test
    Vector vec = getRecordsByIndex("select count(*) from BUS_SER_COM_DEAL");
    String[] result = (String[]) vec.get(0);
    System.out.println(result[0]); */

    /** getRecordsByName test
    NameRecords results = getNameRecords("select SEQ_NC_USER.nextval as seq from dual");
    System.out.println(results.getRecord(0).getFieldValue("SEQ"));
    /**/

    /** pooledConn test
    Connection[] conns = new Connection[50];
    for (int i = 0; i < 50; i++){
      conns[i] = getConnection();
    }
    for (int i = 0; i < 50; i++){
      freeConnection(conns[i]);
    }*/

    NameRecord nr = new NameRecord();
    nr.put("A", "A");
    System.out.println(nr.getFieldValue("A"));
    nr.put("A", "B");
    System.out.println(nr.getFieldValue("A"));
  }
}