dbquerybean.java

SAP这个系统的一个转换器

package com.idoc.util.db;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//import javax.naming.*;
import java.util.Vector;
import com.idoc.util.db.BeanConn;
import com.idoc.util.db.Row;
import com.idoc.util.db.RowSet;
import com.idoc.util.GetProperty;
import com.scxh.eei.sql.*;

public class DBQueryBean {
  private Connection conn = null;
  private static GetProperty get = new GetProperty();
  BeanConn bconn = new BeanConn();
  static {
    String url = get.getUrl();
    String driver = get.getDriver();
    String user = get.getUser();
    String pass = get.getPassword();
    String defaultdatatsourcename = "DefaultDatasource";
    try {
      Class.forName(driver);
    }
    catch (Exception e) {
      System.out.println("DBQueryBean inilaliztion");
      e.printStackTrace();
    }
    DataSource ds = new com.scxh.eei.sql.DataSource();
    ds.setUrl(url);
    ds.setUser(user);
    ds.setPassword(pass);
    DataSourceManager.registerDefaultDatasource(defaultdatatsourcename, ds);
  }

  public DBQueryBean() {
    try {
      conn = com.scxh.eei.sql.DataSourceManager.lookup().getConnection();

    }
    catch (Exception ex) {
      System.out.println("DBQueryBean createconn : " + ex.toString());
      ex.printStackTrace();
    }
  }

  /**
   * 查询数据库
   * @param sql
   * @return 结果集
   */

  public RowSet Query(String sql) throws Exception {
    RowSet rowset = new RowSet();
    Statement stmt = null;
    ResultSet rs = null;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      rowset = new NewResultSet(rs).getRowSet();
    }
    catch (Exception e) {
      System.err.println("DBQueryBean's Query : " + sql);
      e.printStackTrace();
    }
    finally {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
        //if(conn != null) conn.close();
      return rowset;
    }
  }

  /**
   * 执行update，insert语句
   * @param sql
   * @return
   */
  public int Update(String sql) throws Exception {
    int ret = 0;
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      ret = stmt.executeUpdate(sql);
    }
    catch (Exception e) {
      ret = -1;
      System.err.println("DBQueryBean's Update : " + e.getMessage());
      e.printStackTrace();
    }
    finally {
      if (stmt != null)
        stmt.close();
        //if(conn != null) conn.close();
      return ret;
    }
  }

  /**
   * 取得部分记录，从from到to
   * @param sql
   * @param from 起始位置
   * @param to 结束位置
   * @return
   */
  public RowSet Query(String sql, int from, int to) throws Exception {
    RowSet rows = Query(sql);
    int length = rows.length();
    RowSet ret = new RowSet();
    for (int i = from; i < to; i++) {
      if (i == length)
        break;
      ret.add(rows.get(i));
    }
    return ret;
  }

  /**
   * 执行存储过程，返回记录集
   * 如果存储过程返回整型值，该函数将得不到
   * @param sql
   * @return
   */
  public Vector QueryBySP(String up_name, Object[] params) throws Exception {
    //生成执行存储过程up_name的sql语句
    String sql = "{call " + up_name + "(";
    String tmp = "";
    for (int i = 0; i < params.length; i++) {
      tmp += "?,";
    }
    if (tmp.length() > 0)
      tmp = tmp.substring(0, tmp.length() - 1);
    sql = sql + tmp + ")}";

    Vector vec = new Vector();
    CallableStatement cstmt = null;
    ResultSet rs = null;
    try {
      cstmt = conn.prepareCall(sql);
      //设置存储过程执行参数
      for (int i = 0; i < params.length; i++) {
        cstmt.setObject(i + 1, params[i]);
      }
      cstmt.execute();
      rs = cstmt.getResultSet();
      NewResultSet nrs = null;
      RowSet rowset = null;
      if (rs != null) {
        nrs = new NewResultSet(rs);
        rowset = nrs.getRowSet();
        vec.add(rowset);
      }

      while (true) {
        if ( (cstmt.getMoreResults() == false) && (cstmt.getUpdateCount() == -1)) {
          break;
        }
        rs = cstmt.getResultSet();
        if (rs != null) {
          nrs = new NewResultSet(rs);
          rowset = nrs.getRowSet();
          vec.add(rowset);
        }
      }
    }
    catch (SQLException e) {
      System.err.println("DBQueryBean's QueryBySP is Error!");
      e.printStackTrace();
      return null;
    }
    finally {
      if (rs != null)
        rs.close();
      if (cstmt != null)
        cstmt.close();
        //if(conn != null) conn.close();
      return vec;
    }
  }

  /**
   * 事务处理函数
   * @param sql
   * @return 成功：true 失败：false
   */
  public boolean doAffair(Vector sql) {
    boolean rtn = true;
    Statement st = null;
    try {
      conn.setAutoCommit(false);
      st = conn.createStatement();
      for (int i = 0; i < sql.size(); i++) {
//System.out.println(i + " ---->  " + (String)sql.get(i));
        st.executeUpdate( (String) sql.get(i));
      }
      conn.commit();
    }
    catch (Exception e) {
      try {
        conn.rollback();
      }
      catch (SQLException ex) {
        System.out.println("DBQueryBean's doAffair  is Error! ");
        ex.printStackTrace();
      }
      rtn = false;
      System.out.println("DBQueryBean's doAffair  is Error! ");
      e.printStackTrace();
    }
    finally {
      if(sql!=null)
      {sql.clear();}
      try {
        if (st!= null)
        st.close();
        conn.setAutoCommit(true);
      }
      catch (Exception ex) {
        System.out.println(ex.toString());
        ex.printStackTrace();
      }
      return rtn;
    }
  }

  public void close() {
    try {
      if (conn != null)
      {
        conn.close();}
    }
    catch (Exception ex) {
      System.out.println("DBQueryBean's close!");
      ex.printStackTrace();
    }
  }
}