DBI.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBI {
 private static DBI dbi = new DBI();

 private static DataSource ds = null;

 public static DBI getInstance() {
  return dbi;
 }

 private Connection getConnection() throws SQLException {
  // String datasource = "java:/Oracleds";
  String datasource = "java:comp/env/mysql_ds";/* t��tomcat */

  Connection conn = null;
  try {

   if (ds == null) {
    InitialContext ctx = new InitialContext();
    ds = (DataSource) ctx.lookup(datasource);
   }
  } catch (NamingException ne) {
   throw new SQLException("Lookup Datasource fail." + ne.getMessage());
  }
  conn = ds.getConnection();

  return conn;
 }

 public ArrayList query(String sql) throws Exception {
  ArrayList alRS = new ArrayList();
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try {
   conn = getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   ResultSetMetaData rsmd = rs.getMetaData();
   int cols = rsmd.getColumnCount();
   int i = 0;
   while (rs.next() != false) {
    ArrayList alRow = new ArrayList();
    for (i = 1; i <= cols; ++i) {
     if (rs.getString(i) == null) {
      alRow.add("");
     } else {
      alRow.add(rs.getString(i));
     }
    }
    alRS.add(alRow);
   }
  } catch (SQLException sqle) {
   throw new SQLException("Select Data SQL exception: "
     + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return alRS;
 }

 public ArrayList queryByPageOfMySql(String sql, int intRecordsPerPage,
   int intCurrentPage) throws Exception {
  /**
   * intRecordsPerPageÿҳ������ intCurrentPage��ǰҳ��
   * **/
  ArrayList alRS = new ArrayList();
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try {
   conn = getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql + " LIMIT " + intRecordsPerPage
     * intCurrentPage + "," + intCurrentPage);
   ResultSetMetaData rsmd = rs.getMetaData();
   int cols = rsmd.getColumnCount();
   int i = 0;
   while (rs.next() != false) {
    ArrayList alRow = new ArrayList();
    for (i = 1; i <= cols; ++i) {
     if (rs.getString(i) == null) {
      alRow.add("");
     } else {
      alRow.add(rs.getString(i));
     }
    }
    alRS.add(alRow);
   }
  } catch (SQLException sqle) {
   throw new SQLException("Select Data SQL exception: "
     + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return alRS;
 }

 public ArrayList query(String sql, List params) throws Exception {
  ArrayList alRS = new ArrayList();
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  try {
   conn = getConnection();
   pstmt = conn.prepareStatement(sql);
   setParameters(pstmt, params);
   rs = pstmt.executeQuery();
   ResultSetMetaData rsmd = rs.getMetaData();
   int cols = rsmd.getColumnCount();
   int i = 0;
   while (rs.next()) {
    ArrayList alRow = new ArrayList();
    for (i = 1; i <= cols; ++i) {
     if (rs.getString(i) == null) {
      alRow.add("");
     } else {
      alRow.add(rs.getString(i));
     }
    }
    alRS.add(alRow);
   }
  } catch (SQLException sqle) {
   throw new SQLException("Select Data SQL exception: "
     + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return alRS;
 }

 public ArrayList queryTopN(String sql, int topn) throws Exception {
  ArrayList alRS = new ArrayList();
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try {
   conn = getConnection();
   stmt = conn.createStatement();
   String sql_topn = "select rownum as myrownum,a.* from (" + sql
     + ") a where rownum<=" + topn + ")";

   rs = stmt.executeQuery(sql_topn);
   ResultSetMetaData rsmd = rs.getMetaData();
   int cols = rsmd.getColumnCount();
   int i = 0;
   int j = 0;
   while (rs.next()) {
    ArrayList alRow = new ArrayList();
    for (i = 1; i <= cols; ++i) {
     if (rs.getString(i) == null) {
      alRow.add("");
     } else {
      alRow.add(rs.getString(i));
     }
    }
    alRS.add(alRow);
    j++;
   }
  } catch (SQLException sqle) {
   throw new SQLException("Select Data SQL exception: "
     + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return alRS;
 }

 public String queryColumn(String colName, String sql) throws Exception {
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  String colData = null;
  try {

   conn = getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   colData = rs.next() ? rs.getString(colName) : null;
   rs.close();
   stmt.close();
  } catch (SQLException sqle) {
   throw new SQLException("SQL exception: " + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return colData;
 }

 public double queryMax(String column, String sqlstr) throws Exception {
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  double max = 0;

  String sql = "select max(" + column + ") maxID from (" + sqlstr + ")";
  try {
   conn = getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   max = rs.next() ? rs.getDouble("maxID") : 0;

   rs.close();
   stmt.close();
  } catch (SQLException sqle) {
   throw new SQLException("SQL exception: " + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return max;
 }

 public int queryCount(String sqlstr) throws Exception {
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  int i_count = 0;
  System.out.println(sqlstr);
  String sql = "select count(*) Count from " + sqlstr;
  System.out.println(sql);
  try {
   conn = getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   i_count = rs.next() ? rs.getInt("Count") : 0;
   rs.close();
   stmt.close();
  } catch (SQLException sqle) {
   throw new SQLException("GetCount SQL exception: "
     + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return i_count;
 }

 public int queryCount2(String sql) throws Exception {
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  int i_count = 0;
  System.out.println(sql);
  try {
   conn = getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   if (rs.next()) {
    i_count = rs.getInt("Count");
   }
  } catch (SQLException sqle) {
   throw new SQLException("GetCount SQL exception: "
     + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("System e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("rs close exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("stmt close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return i_count;
 }

 public int update(String sql) throws Exception {
  Connection conn = null;
  Statement stmt = null;
  int numRows = 0;
  try {

   conn = getConnection();

   stmt = conn.createStatement();

   numRows = stmt.executeUpdate(sql);

  } catch (SQLException sqle) {
   throw new Exception("Update/Delete/Insert_Data exception: "
     + sqle.getMessage());
  } finally {
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("ps Close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return numRows;
 }

 public int[] updateBatch(String[] sql) throws Exception {
  Connection conn = null;
  Statement stmt = null;
  int[] numRows = new int[0];
  try {

   conn = getConnection();

   stmt = conn.createStatement();

   conn.setAutoCommit(false);
   for (int i = 0; i < sql.length; i++) {
    System.out.println("***** " + sql[i]);
    stmt.addBatch(sql[i]);
   }

   numRows = stmt.executeBatch();
   conn.commit();
   conn.setAutoCommit(true);

  } catch (SQLException sqle) {
   throw new Exception("Update/Delete/Insert_Data exception: "
     + sqle.getMessage());
  } finally {
   if (!conn.getAutoCommit())
    conn.setAutoCommit(true);
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("ps Close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return numRows;
 }

 // todo
 // need test
 public int[] updateTransaction(String[] sql) throws Exception {
  Connection conn = null;
  Statement stmt = null;
  int[] numRows = new int[0];
  try {

   conn = getConnection();

   stmt = conn.createStatement();

   conn.setAutoCommit(false);

   for (int i = 0; i < sql.length; i++) {
    System.out.println("***** " + sql[i]);
    numRows[i] = stmt.executeUpdate(sql[i]);
   }

   conn.commit();
   conn.setAutoCommit(true);

  } catch (SQLException sqle) {
   try {
    conn.rollback();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   throw new Exception("Update/Delete/Insert_Data exception: "
     + sqle.getMessage());
  } finally {
   if (!conn.getAutoCommit())
    conn.setAutoCommit(true);
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("ps Close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return numRows;
 }

 public int update(String sql, List params) throws Exception {
  Connection conn = null;
  PreparedStatement pstmt = null;
  int numRows = 0;
  try {
   conn = getConnection();
   pstmt = conn.prepareStatement(sql);
   setParameters(pstmt, params);
   numRows = pstmt.executeUpdate();
  } catch (SQLException sqle) {
   throw new Exception("Update/Delete/Insert_Data exception: "
     + sqle.getMessage());
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
    }
   } catch (Exception e) {
    throw new Exception("ps Close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return numRows;
 }

 public int updateSP(String sql, List params) throws Exception {
  Connection conn = null;
  CallableStatement cstmt = null;
  int numRows = 0;
  try {
   conn = getConnection();
   cstmt = conn.prepareCall(sql);
   setParameters(cstmt, params);
   numRows = cstmt.executeUpdate();
  } catch (SQLException sqle) {
   throw new Exception("SP Update/Delete/Insert_Data exception: "
     + sqle.getMessage());
  } finally {
   try {
    if (cstmt != null) {
     cstmt.close();
    }
   } catch (Exception e) {
    throw new Exception("ps Close exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return numRows;
 }

 public ArrayList queryByPage(int intRecordsPerPage, int intCurrentPage,
   String sqlstr) throws Exception {
  ArrayList alPage = new ArrayList();
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  String sql = "select * from ((select rownum as myrownum,a.* from ("
    + sqlstr + ") a where rownum<=" + intCurrentPage
    * intRecordsPerPage + "))  where myrownum>"
    + (intCurrentPage - 1) * intRecordsPerPage;

  try {

   conn = getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   ResultSetMetaData rsmd = rs.getMetaData();

   int cols = rsmd.getColumnCount();

   int i = 0;

   while (rs.next()) {
    ArrayList alRow = new ArrayList();
    for (i = 1; i <= cols; ++i) {
     if (rs.getString(i) == null) {
      alRow.add("null");
     } else {
      alRow.add(rs.getString(i));
     }
    }
    alPage.add(alRow);
   }
  } catch (SQLException sqle) {
   throw new Exception("PageControl SQL exception: "
     + sqle.getMessage());
  } catch (Exception e) {
   throw new Exception("PageControl e exception: " + e.getMessage());
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
   } catch (Exception e) {
    throw new Exception("PageControl exception: " + e.getMessage());
   }
   try {
    if (stmt != null) {
     stmt.close();
    }
   } catch (Exception e) {
    throw new Exception("PageControl exception: " + e.getMessage());
   }
   try {
    if (conn != null) {
     conn.close();
    }
   } catch (Exception e) {
    throw new Exception("DB Connection Close exception: "
      + e.getMessage());
   }
  }
  return alPage;
 }

 private void setParameters(PreparedStatement pstmt, List params)
   throws SQLException {
  for (int i = 0; i < params.size(); i++) {
   pstmt.setObject(i + 1, params.get(i));
  }
 }

}