sqlobject.java

连接数据库

package jbtmailproxy;

import java.util.Vector;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Types;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.io.FileInputStream;
import java.util.Properties;
import java.io.IOException;
import java.io.FileOutputStream;
import java.io.File;

public class SQLObject {
  private String dbCfg = "jbtmail";
  private String result = "";
  private String javaEnc = "GB2312";
  private String dbEnc = "GB2312";
  private String sql = "";
  private String[][] recSet = null;
  private Vector parameters = new Vector();
  public SQLObject() {
  }
  private Connection connectDBMS(String propFile) throws Exception {
    if (propFile == null) {
      propFile = "sql";
    }
    if (propFile.indexOf('.') < 0) {
      File jdbcDir = new File("C:/jdbc");
      if (!jdbcDir.exists()){
        jdbcDir.mkdir();
      }
      propFile = "C:/jdbc/" + propFile + ".properties";
    }
    File pf = new File(propFile);
    if (!pf.exists()){
      pf.createNewFile();
      FileOutputStream fos = new FileOutputStream(pf);
      fos.write("driver=sun.jdbc.odbc.JdbcOdbcDriver\r\n".getBytes());
      fos.write("url=sqlo\r\n".getBytes());
      fos.write("logid=sa\r\n".getBytes());
      fos.write("logpass=INFOX1EIES2SPS3WAS4\r\n".getBytes());
      fos.close();
    }
    String driver = "";
    String url = "";
    String user = "";
    String password = "";
    Properties props = new Properties();
    try {
      props.load(new FileInputStream(propFile));
    }
    catch (IOException e) {
      throw new Exception("配置文件(" + propFile + ")无法打开，或找不到。");
    }
    driver = props.getProperty("driver");
    // com.mysql.jdbc.Driver
    // sun.jdbc.odbc.JdbcOdbcDriver
    url = props.getProperty("url");
    user = props.getProperty("logid");
    password = props.getProperty("logpass");
    dbEnc = props.getProperty("dbEnc");
    if (dbEnc == null || dbEnc.length() <= 0){
      dbEnc = javaEnc;
    }
    if (driver == null || driver.length() <= 0){
      throw new Exception("配置文件(" + propFile + ")不正确，没有driver条目。");
    }
    if (url == null || url.length() <= 0) {
      throw new Exception("配置文件(" + propFile + ")不正确，没有url条目。");
    }
    try {
      Class.forName(driver);
    }
    catch (ClassNotFoundException e) {
      throw new Exception("JDBC驱动 - " + driver + "没有找到。");
    }
    return java.sql.DriverManager.getConnection(url, user, password);
  }
  private String EncodeSQL(String str) {
    if (str == null) {
      return str;
    }
    if (javaEnc.equalsIgnoreCase(dbEnc)){
      return str;
    }
    else {
      try {
        str = new String(str.getBytes(javaEnc), dbEnc);
      }
      catch (Exception ex) {
        ex.printStackTrace();
      }
    }
    return str;
  }
  private String DecodeResult(String str) {
    if (str == null) {
      str = "";
    }
    if (javaEnc.equalsIgnoreCase(dbEnc)){
      return str;
    }
    else {
      try {
        str = new String(str.getBytes(dbEnc), javaEnc);
      }
      catch (Exception ex) {
        ex.printStackTrace();
      }
    }
    return str;
  }
  public String getSql() {
    return sql;
  }
  public void setSql(String sql) {
    if (parameters.size() > 0){
      parameters.clear();
    }
    this.sql = sql;
  }
  public String[][] getRecSet() {
    return recSet;
  }
  public String getResult() {
    return result;
  }
  public void fillParam(String param) {
    if (param != null){
      param = param.replaceAll("[\\\\]", "/");
      if (param.length() <= 0){
        //param = null;
      }
    }
    parameters.add(param);
    return;
  }
  public void fillParam(long param) {
    parameters.add("" + param + "");
  }
  public void fillParam(double param) {
    parameters.add("" + param + "");
  }
  public void fillParam(java.util.Date param) {
    parameters.add(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(param));
  }
  public void fillParam(java.sql.Date param) {
    parameters.add(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(param));
  }
  public String[][] executeQuery() {
    try {
      Connection conn = connectDBMS(dbCfg);
      //Statement stmt = conn.createStatement();
      System.out.println(sql);

      PreparedStatement stmt = conn.prepareStatement(EncodeSQL(sql));
      for (int i = 0; i < parameters.size(); i ++){
        stmt.setString(i + 1, EncodeSQL((String)parameters.get(i)));
      }
      //ResultSet rs = stmt.executeQuery(EncodeSQL(sql));
      ResultSet rs = stmt.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      int colcount = rsmd.getColumnCount();

      Vector vrs = new Vector();
      while (rs.next()) {
        String[] cols = new String[colcount];
        for (int col = 0; col < colcount; col++) {
          cols[col] = DecodeResult(rs.getString(col + 1));
        }
        vrs.add(cols);
      }
      int rowcount = vrs.size();
      recSet = new String[rowcount][colcount];
      for (int row = 0; row < rowcount; row++) {
        recSet[row] = (String[])vrs.get(row);
      }
      stmt.close();
      conn.close();
      result = "";
    }
    catch (SQLException e) {
      result = e.getMessage();
      return null;
    }
    catch (Exception e) {
      result = e.getMessage();
      return null;
    }
    return recSet;
  }

  public boolean executeUpdate() {
    try {
      Connection conn = connectDBMS(dbCfg);
      //Statement stmt = conn.createStatement();
      PreparedStatement stmt = conn.prepareStatement(EncodeSQL(sql));
      for (int i = 0; i < parameters.size(); i ++){
        try {
          String var = (String)parameters.get(i);
          stmt.setString(i + 1, EncodeSQL(var));
        }
        catch(Exception ex){
          System.out.println(ex.getMessage());
        }
      }
      //stmt.executeUpdate(EncodeSQL(sql));
      System.out.println(stmt.toString());
      stmt.executeUpdate();
      //String[] sqls = sql.split("\r\n;\r\n");
      //StringBuffer error = new StringBuffer("");
      //for (int i = 0; i < sqls.length; i ++){
      //  try {
      //  }
      //  catch (SQLException e) {
      //    error.append("执行第" + i +"句出错（SQLSTATE：" + e.getSQLState() + "） - SQL Error " + e.getErrorCode() + ":" + e.getMessage());
      //  }
      //}
      stmt.close();
      conn.close();
      result = "";
      //if (error.length() > 0){
        //result = error.toString();
        //return result;
      //}
    }
    catch (SQLException e) {
      result = "执行语句出错（SQLSTATE：" + e.getSQLState() + "） - SQL Error " + e.getErrorCode() + ":" + e.getMessage();
      return false;
    }
    catch (Exception e) {
      result = e.getMessage();
      return false;
    }
    return true;
  }
  // sql 的样式： "{call <存贮过程名称> (?,...,?)}")
  public String[][] callProcedure() {
    try {
      int askCnt = 0;
      char[] aSql = sql.toCharArray();
      for (int i = 0; i < aSql.length; i ++){
        if (aSql[i] == '?'){
          askCnt ++;
        }
      }
      if (askCnt < parameters.size()){
        throw new Exception("参数个数不一致！");
      }
      Connection conn = connectDBMS(dbCfg);
      CallableStatement cstmt = conn.prepareCall(sql);
      int i = 0;
      for (; i < parameters.size(); i ++){
        cstmt.setString(i + 1, EncodeSQL((String)parameters.get(i)));
      }
      for (; i < askCnt; i ++){
        cstmt.registerOutParameter(i + 1, Types.CHAR);
      }
      try {
        cstmt.execute();
        if (askCnt > parameters.size()){
          recSet = new String[1][askCnt - parameters.size()];
        }
        for (i = parameters.size(); i < askCnt; i ++){
          recSet[0][i - parameters.size()] = DecodeResult(cstmt.getString(i + 1));
        }
        result = "";
      }
      catch (ArrayIndexOutOfBoundsException ex){
        result = "调用的语句有错误，参数等有关问题";
      }
      cstmt.close();
      conn.close();
    }
    catch (SQLException e) {
      result = e.getMessage();
      recSet = null;
    }
    catch (Exception e) {
      result = e.getMessage();
      recSet = null;
    }
    return recSet;
  }
}