DBO

package jsswt.sendsms;

/**
 * <p>Title: </p>
 * <p>Description: 数据库操作类
 * 支持动态配置文件dbconfig.txt
 * 支持Struts连接池和其他连接池（需要外部连接池支持）
 * 支持数据分页显示（与laosun.servlet.Pagelet配合使用可以实现不用写一行代码实现分页JSP）
 * 支持类反射机制自动完成任何标准FormBean的数据赋值（用于Struts）
 * </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: </p>
 * @author SunHui
 * @version 1.01
 */

import java.io.*;
import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import javax.servlet.http.*;
import javax.sql.*;

import laosun.string.*;

public class DBO {

  private boolean connected = false; // 是否连接上了
  private Connection conn = null; // 数据库连接
  private File cfgFile = null; // 数据库配置信息文件，主要用于试验数据库连接，不要用于商用
  private String driverClass = null; // 数据库驱动类
  private String url = null; // 数据库url
  private String user = null; // 数据库连接用户
  private String password = null; // 数据库连接密码
  private Vector columnNames = new Vector(); // 列名
  private Vector vContent = new Vector(); // 表数据
  private int totalRec = 0; // 总记录数，用于分页
  private int totalPage = 1; // 总页数
  private long aa;
  private int totalR=0;
  public DBO() {
  }

  // 用于在Struts环境下自动获得连接
  public DBO(HttpServlet servlet, String dataSourceName) throws
      Exception {
    if (servlet != null && dataSourceName != null) {
      DataSource ds = (DataSource) servlet.getServletContext().getAttribute(
          dataSourceName);
      setConn(ds.getConnection());
    }
    else {
      throw new Exception("Servlet or dataSourceName is null !");
    }
  }

  public DBO(DataSource dataSource) {
    try {
      setConn(dataSource.getConnection());
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  /**
   * determine connect state
   * @return boolean
   */
  public boolean isConnected() {
    return connected;
  }

  /**
   * load database config file
   * @param filename String
   * @return boolean
   * @deprecated 使用该连接方式可能导致数据库性能不佳
   */
  public boolean loadCfgFile(String filename) {
    Properties p = new Properties();
    try {
      cfgFile = new File(filename);
      p.load(new FileInputStream(cfgFile));
      driverClass = p.getProperty("driverClass");
      url = p.getProperty("url");
      user = p.getProperty("user");
      password = p.getProperty("password");
      return true;
    }
    catch (Exception ex) {
      System.out.println("Error in DBOperator.loadCfgFile(filename)" + ex);
      return false;
    }
  }

  /**
   * 打开数据库连接
   * @return boolean
   */
  public boolean openDB() {
    if (connected) {
      return true;
    }
    if (!connected && cfgFile == null) {
      System.out.println(
          "Error operation! Please load database config file first!");
      System.out.println("Example of database config file:");
      System.out.println("----------------------------/n"
                         + "sqlserver2000 example/n"
                         +
          "driverClass=com.microsoft.jdbc.sqlserver.SQLServerDriver/n"
          + "url=jdbc:microsoft:sqlserver://localhost:1433/n"
          + "user=shy/n"
          + "password=shy/n"
          + "----------------------------/n"
          + "oracle example/n"
          + "driverClass=oracle.jdbc.driver.OracleDriver/n"
          + "url=jdbc:oracle:thin:@localhost:1521:sid/n"
          + "user=shy/n"
          + "password=shy/n"
          + "----------------------------/n"
          + "odbc example/n"
          + "driverClass=sun.jdbc.odbc.JdbcOdbcDriver/n"
          + "url=jdbc:odbc:odbcname or jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=xxx.mdb/n"
          + "user=shy/n"
          + "password=shy/n"
          + "----------------------------/n"
          + "mysql example/n"
          + "driverClass=com.mysql.jdbc.Driver/n"
          + "url=jdbc:mysql://localhost:3306/database/n"
          + "user=shy/n"
          + "password=shy/n"
          );
      connected = false;
    }
    try {
      Class.forName(driverClass).newInstance();
      conn =
          DriverManager.getConnection(url, user, password);
    }
    catch (Exception e) {
      close(null, null, conn);
      connected = false;
      System.out.println("Error in DBOperator.openDB()" + e);
      connected = false;
    }
    connected = true;
    System.out.println("Connect to " + url + " successful");
    return connected;
  }

  /**
   * 取表列名集合
   * @return Vector
   */
  public Vector getColumnNames() {
    if (columnNames.isEmpty()) {
      System.out.println("Column names empty!");
    }
    return columnNames;
  }

  /**
   * 运用类反射机制生成数据对象的集合，用于Struts环境
   * @param sql String 查询语句
   * @param className String 类名
   * @param params Vector 参数集合
   * @return v 数据对象的集合
   */
  public Vector reflect(String sql, String className, Vector params) {
    Vector v = new Vector();
    openDB();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      if (!connected) {
        return v;
      }
      pstmt = conn.prepareStatement(sql);
      if (params != null && !params.isEmpty()) {
        if (StrUtil.count(sql, "?") != params.size()) {
          System.out.println("参数个数不匹配");
        }
        else {
          for (int i = 0; i < params.size(); i++) {
            pstmt.setObject(i + 1, params.get(i));
          }
        }
      }
      rs = pstmt.executeQuery();
      Object recordValue; // 数据库字段值
      Object formBean; // 封装表的bean
      Object oTmp = new Object(); // 对象模板
      ResultSetMetaData rsmd = rs.getMetaData();
      int columnCount = rsmd.getColumnCount();
      while (rs.next()) {
        formBean = Class.forName(className).newInstance();
        for (int i = 1; i <= columnCount; i++) {
          recordValue = (rs.getObject(i) != null) ? rs.getObject(i) : "null";
          // 列名首字母大写，其余小写
          String prop = Character.toUpperCase(rsmd.getColumnName(i).charAt(0)) +
              rsmd.getColumnName(i).toLowerCase().substring(1);
          String mName = "set" + prop;
          // 默认所有formBean方法的参数都为Object型，这样可以简化操作
          Method m = formBean.getClass().getMethod(mName,
              new Class[] {oTmp.getClass()});
          m.invoke(formBean, new Object[] {recordValue});
        }
        v.add(formBean);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    finally {
      close(pstmt, rs, conn);
      return v;
    }
  }

  /**
   * 运用类反射机制生成数据对象的集合，用于Struts环境
   * @param sql String 查询语句
   * @param className String 类名
   * @param pageNum int 页码
   * @param pageSize int 每页最多记录数
   * @return vBean 数据对象的集合
   */
  public Vector reflect(int totalRec,String sql, String className, long pageNum,
                        int pageSize) {
    Vector vBean = new Vector();
    Vector vTemp = new Vector();
    try {
      vTemp = queryPage(totalRec,sql, pageNum, pageSize);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    if (vTemp.size() > 0) {
      for (int i = 0; i < vTemp.size(); i++) {
        Object recordValue; // 数据库字段值
        Object formBean = null; // 封装表的bean
        Object oTmp = new Object(); // 对象模板
        HashMap hm = (HashMap) vTemp.get(i);
        try {
          formBean = Class.forName(className).newInstance();
          for (int j = 0; j < columnNames.size(); j++) {
            recordValue = hm.get(columnNames.get(j).toString());
            // 列名首字母大写，其余小写
            String prop = Character.toUpperCase(columnNames.get(j).toString().
                                                charAt(0)) +
                columnNames.get(j).toString().toLowerCase().substring(1);
            String mName = "set" + prop;
            // 默认所有formBean方法的参数都为Object型，这样可以简化操作
            Method m = formBean.getClass().getMethod(mName,
                new Class[] {oTmp.getClass()});
            m.invoke(formBean, new Object[] {recordValue});
          }
          vBean.add(formBean);
        }
        catch (Exception ex) {
          ex.printStackTrace();
        }
      }
    }
    return vBean;
  }

  /**
   * 根据sql语句生成ByteArray的集合，用于图像显示
   * @param sql String
   * @return vByteArray
   */
  public Vector createByteArrays(String sql) {
    Vector vByteArray = new Vector();
    openDB();
    if (!connected) {
      return vByteArray;
    }
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Object o = rs.getObject(1);
        if (o != null) {
          if (o instanceof byte[]) {
            byte[] b = (byte[]) o;
            vByteArray.addElement(b);
          }
        }
        else {
          vByteArray.addElement(null);
        }
      }
    }
    catch (Exception e) {
      System.out.println("Error in DBOperator.createByteArray(sql)" + e);
    }
    finally {
      close(pstmt, rs, conn);
      return vByteArray;
    }
  }

  /**
   * 根据sql语句生成ByteArray的集合，用于图像显示
   * @param sql String
   * @param offset int
   * @return vByteArray
   */
  public Vector createByteArrays(String sql, int offset) {
    Vector vByteArray = new Vector();
    openDB();
    if (!connected) {
      return vByteArray;
    }
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Object o = rs.getObject(offset);
        if (o != null) {
          if (o instanceof byte[]) {
            byte[] b = (byte[]) o;
            vByteArray.addElement(b);
          }
        }
        else {
          vByteArray.addElement(null);
        }
      }
    }
    catch (Exception e) {
      System.out.println("Error in DBOperator.createByteArray(sql)" + e);
    }
    finally {
      close(pstmt, rs, conn);
      return vByteArray;
    }
  }

  /**
   * 在没有执行查询的情况下释放连接
   */
  public void releaseConn() {
    close(null, null, conn);
  }

  /**
   * 关闭所有连接
   * @param resultSet ResultSet
   * @param statement Statement
   * @param connection Connection
   */
  private void close(Statement statement, ResultSet resultSet,
                     Connection connection) {
    try {
      if (statement != null) {
        statement.close();
      }
      if (resultSet != null) {
        resultSet.close();
      }
      if (connection != null) {
        connection.close();
      }
    }
    catch (Exception e) {
      System.out.println("Error in DBOperator.close()" + e);
    }
    finally {
      connected = false;
    }
  }

  /**
   * 取总页数
   * @return int
   */
  public int getTotalPage() {
    return totalPage;
  }

  /**
   * 取总记录数
   * @return int
   */
  public int getTotalRec() {
    return totalRec;
  }

//***********追加  康庆（08.24）根据单一表取得数据总数***********start
//取得总纪录条数
  public int queryTotalRec(String sql){
    clear();
    openDB();
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      rs = pstmt.executeQuery();
      rs.next();
      totalRec=Integer.parseInt(rs.getString(1));
      return totalRec;
      }
      catch (Exception e)
      {
            System.out.print("查找企业总数发生异常~~~~~~~");
            return 0;
      }
  }

//***********追加  郭宏亮（9.4）根据单一表取得数据总数***********end
public int queryTotal(String sql){
    clear();
    openDB();
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      rs = pstmt.executeQuery();
      if(rs.next())
        rs.last();
      totalR=rs.getRow();
      return totalR;
      }
      catch (Exception e)
      {
            System.out.print("查找企业总数发生异常~~~~~~~");
            return 0;
      }
  }

  /**
   * 分页显示，用于各种数据库分页显示
   * @param sql String
   * @param pageNum int 页号
   * @param pageSize int 每页个数
   * @return Vector
   * @throws java.lang.Exception
   */
  public Vector queryPage(int totalRec,String sql, long pageNum, int pageSize) throws
      Exception {
    aa = pageNum*12-12;
    sql=sql + " limit " + aa + " , 12";
    System.out.println("sql======"+sql);
    clear();
    if (pageNum < 1 || pageSize < 1) {
      throw new Exception("PageNum or pageSize error !");
    }
    if (!connected) {
      System.err.println("Not connected !");
      return new Vector();
    }
    openDB();
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      rs = pstmt.executeQuery();
      // 获得总记录数
//      System.out.println("in the dbo totalrec" + totalRec);
//      if (totalRec==0){
//        System.out.println("dsffsdafsadfsdfsfffffffffffffffffffffffffffffffffff");
//        if (rs.next()) {
//          rs.last();
//          System.out.println("44444444444="+rs.getString(6));
//          totalRec = rs.getRow();
//          System.out.println("totalRec1111111111111111111111111=" + totalRec);
//
//        }
//      }
      // 总记录数小于每页个数
      if (totalRec < pageSize) {
        rs.beforeFirst();
      }
      else {
        // 得到总页数
        totalPage = (totalRec % pageSize == 0) ? (totalRec / pageSize) :
            (totalRec / pageSize) + 1;
        totalPage = (totalPage == 0) ? 1 : totalPage;
        // 如果定位页码大于总页数，就默认取第一页
        if (pageNum > totalPage) {
          pageNum = 1;
        }
        // 定位到指定页
//        long pointer = (pageNum - 1) * pageSize;
        // 定位到首页
//        if (pointer == 0) {
//          rs.beforeFirst();
//        }
//        else {
//          rs.absolute( (int) pointer);
//        }
      }
      // 构建数据
      buildDataVector(rs, pageSize);
    }
    catch (Exception e) {
      System.out.println("Error int DBOperator.getPage(sql, pageNum, pageSize)" +
                         e);
    }
    finally {
      close(pstmt, rs, conn);
      return vContent;
    }
  }

  /**
   * 不分页取出所有数据
   * @param sql String
   * @return Vector
   */
  public Vector doQuery(String sql) {
    openDB();
    clear();
    if (!connected) {
      return new Vector();
    }
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      rs = pstmt.executeQuery();
      buildDataVector(rs, -1);
    }
    catch (Exception e) {
      System.out.println("Error int DBOperator.doQuery()" + e);
    }
    finally {
      close(pstmt, rs, conn);
      return vContent;
    }
  }

  /**
   * 不分页取出所有数据
   * @param sql String
   * @param params Vector
   * @return Vector
   * @throws Exception
   */
  public Vector doQuery(String sql, Vector params) {
    openDB();
    clear();
    if (!connected) {
      return new Vector();
    }
    if (StrUtil.count(sql, "?") != params.size()) {
      System.out.println("参数个数不匹配");
    }
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql,
                                    ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
      for (int i = 0; i < params.size(); i++) {
        pstmt.setObject(i + 1, params.get(i));
      }
      rs = pstmt.executeQuery();
      buildDataVector(rs, -1);
    }
    catch (Exception e) {
      throw e;
    }
    finally {
      close(pstmt, rs, conn);
      return vContent;
    }
  }

  /**
   * 清除所有历史记录
   */
  private void clear() {
    vContent.clear();
    columnNames.clear();
  }

  /**
   * 从结果集得到数据，并将数据处理成两种类型，一种是Vector容纳HashMap（放置Object），
   * 另一种是Vector容纳Vector（放置String，用于构建TableModel）
   * @param rs ResultSet
   * @param pageSize int
   * @throws Exception
   */
  private void buildDataVector(ResultSet rs, int pageSize) throws
      Exception {
    //get column name
    int lvColumnNu = rs.getMetaData().getColumnCount();
    String[] lvColumnNames = new String[lvColumnNu];
    for (int lvLoop = 0; lvLoop < lvColumnNu; lvLoop++) {
      lvColumnNames[lvLoop] = rs.getMetaData().getColumnName(lvLoop + 1).
          toLowerCase();
      columnNames.add(lvColumnNames[lvLoop]);
    }
    // 不分页取出所有数据
    if (pageSize == -1) {
      while (rs.next()) {
        HashMap hm = new HashMap();
        Vector vTemp = new Vector();
        for (int lvLoop = 0; lvLoop < lvColumnNames.length; lvLoop++) {
          Object o = rs.getObject(lvColumnNames[lvLoop]);
          if (o == null) {
            o = "";
          }
          hm.put(lvColumnNames[lvLoop], o);
        }
        vContent.addElement(hm);
      }
    }
    // 分页取出所需页记录
    else {
      for (int i = 0; i < pageSize; i++) {
        while(rs.next()){
          HashMap hm = new HashMap();
          Vector vTemp = new Vector();
          for (int lvLoop = 0; lvLoop < lvColumnNames.length; lvLoop++) {
            Object o = rs.getObject(lvColumnNames[lvLoop]);
            vTemp.add(o);
            if (o == null) {
              o = "";
            }
            hm.put(lvColumnNames[lvLoop], o);
          }
          vContent.addElement(hm);
        }
      }
    }
  }

  /**
   * 从外部获得一个连接
   * @param c Connection
   */
  public void setConn(Connection c) {
    if (c != null) {
      this.conn = c;
      this.connected = true;
    }
    else {
      this.conn = null;
      this.connected = false;
    }
  }

  public Connection getConn() {
    return conn;
  }

  /**
   * 执行批量操作
   * @param sqlVector Vector
   * @return boolean
   */
  public boolean doBatch(Vector sqlVector) {

    openDB();
    boolean success = false;
    if (!connected || sqlVector.isEmpty()) {
      System.out.println("Database not connected or SQL vector empty!");
      return success;
    }
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      for (Iterator i = sqlVector.iterator(); i.hasNext(); ) {
        String sql = (String) i.next();
        stmt.addBatch(sql);
      }
      stmt.executeBatch();
      success = true;
    }
    catch (Exception e) {
      success = false;
      System.out.println("Error in DBOperator.doBatch(sqlVector)" + e);
    }
    finally {
      close(stmt, null, conn);
      return success;
    }
  }

  /**
   * 执行单条更新操作
   * @param sql String
   * @param params Vector
   * @return boolean
   */
  public boolean doUpdate(String sql, Vector params) {
    openDB();
    boolean success = false;
    if (!connected) {
      return success;
    }
    // 如果参数个数不匹配
    if (StrUtil.count(sql, "?") != params.size()) {
      System.out.println("参数个数不匹配");
      return success;
    }
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql);
      for (int i = 0; i < params.size(); i++) {
        pstmt.setObject(i + 1, params.get(i));
      }
      pstmt.executeUpdate();
    }
    catch (Exception e) {
      System.out.println("Error in DBOperator.doUpdate(String, Vector)" + e);
    }
    finally {
      close(pstmt, null, conn);
      return success;
    }
  }

  /**
   * 执行单条更新操作
   * @param sql String
   * @return boolean
   */
  public boolean doUpdate(String sql) {
    openDB();
    boolean success = false;
    if (!connected) {
      return success;
    }
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      stmt.executeUpdate(sql);
      success=true;
    }
    catch (Exception e) {
      System.out.println("Error in DBOperator.doUpdate(sql)" + e);
    }
    finally {
      close(stmt, null, conn);
      return success;
    }
  }
}