jdbcconnection.java

好像是一个SQL执行器的设计实现

              variants[ordinal].setBigDecimal(getColBigDecimal(lrs_data, columnNo + 1));
              break;

            case Variant.DATE:
              variants[ordinal].setDate(getColDate(lrs_data, columnNo + 1));
              break;
            case Variant.TIME:
              variants[ordinal].setTime(getColTime(lrs_data, columnNo + 1));
              break;
            case Variant.TIMESTAMP:
              variants[ordinal].setTimestamp(getColTimestamp(lrs_data, columnNo + 1));
              break;

            default:
              variants[ordinal].setString(getColString(lrs_data, columnNo + 1));
              break;

          } //end of switch
        }

        // 将缓冲区中的一行记录装入数据集中
        lo_dataset.loadRow();

      } // end of while
      System.out.println("recnum1:" + lo_dataset.rowCount() + " ilrecnum:" + ll_rownum);

      // 结束装入,关闭装入缓冲区
      lo_dataset.endLoading();

      return lo_dataset;
    }
    catch (Exception ex)
    {
      System.out.println("err:" + ex + " rownum:" + ll_rownum + "errcode:" +
                         ( (SQLException) ex).getErrorCode());
      errProcess(ex);
      return null;
    }
    finally
    {
      if (lps_stmt != null)
      {
        try
        {
          lps_stmt.close();
          lps_stmt = null;
          lrs_data.close();
          lrs_data = null; //数据集
          lrmd_data = null;
        }
        catch (Exception ex)
        {}
        ;
      }
    }
  }

  /**
    功能:取得查询结果数据集数据
    @param ps_sql sql语句
    @return  成功 查询结果 失败 null
   */
  private ExecInfo doGetDataSetData(String ps_sql)
  {
    TableDataSet lo_dataset = null;
    ExecInfo lo_ret = new ExecInfo();
    try
    {

      lo_dataset = getTableDataSet2(ps_sql);
      Util.logInfo("getDataSetData:" + ps_sql + " rownum:" + lo_dataset.getRowCount());
      if (lo_dataset == null)
      {
        Util.logError("getDataSetData:error1! sql:" + ps_sql);
        lo_ret.setErrMsg("getDataSetData:error! sql:" + ps_sql);
        lo_ret.setSqlCode( -1);
        return lo_ret;
      }
      lo_ret.setColNum(lo_dataset.getColumnCount()); //字段数
      lo_ret.setRecNum(lo_dataset.rowCount()); //记录数


      DataSetData lo_data = DataSetData.extractDataSet(lo_dataset);
      if (lo_data == null)
      {
        lo_ret.setErrMsg("getDataSetData:error2! sql:" + ps_sql);
        lo_ret.setSqlCode( -1);
        return lo_ret;
      }
      lo_ret.setDataSetData(lo_data);
      return lo_ret;
    }
    catch (Exception ex)
    {
      errProcess(ex);
      lo_ret.setErrMsg("getDataSetData:error3! sql:" + ps_sql + " ex:" + ex);
      lo_ret.setSqlCode( -1);
      return lo_ret;
    }
    finally
    {
      if (lo_dataset != null)
      {
        lo_dataset.close();
        lo_dataset = null;
      }
    }
  }

  /**
    功能:执行sql语句
    @param ps_sql  sql语句
    @return  成功  查询结果  失败  null
   */
  private ExecInfo doExecSql(String ps_sql)
  {
    int i = 0;
    int li_colNum;
    ExecInfo lo_ret = new ExecInfo();
    ResultSet lrs_data = null; //数据集
    ResultSetMetaData lrmd_data = null;
    PreparedStatement lps_stmt = null; //sql语句类
    try
    {
      lps_stmt = conn.prepareStatement(ps_sql);
      Util.logInfo("execsql sql:" + ps_sql);
      if (Util.isSelectSql(ps_sql)) //属于select语句
      {
        return doGetDataSetData(ps_sql);
      }
      else
      {
        lo_ret.setRecNum(lps_stmt.executeUpdate());
      }
      return lo_ret;
    }
    catch (Exception ex)
    {
      errProcess(ex);
      lo_ret.setSqlCode( -1);
      return lo_ret;
    }
    finally
    {
      if (lps_stmt != null)
      {
        try
        {
          lps_stmt.close();
        }
        catch (Exception ex)
        {}
        ;
        lps_stmt = null;
      }
      if (lrs_data != null)
      {
        try
        {
          lrs_data.close();
        }
        catch (Exception ex)
        {}
        lrs_data = null; //数据集
      }
      lrmd_data = null;
    }
  }

  /**
   功能:  取得查询的数据集
   @param pi_page 页号
   @param pi_pageRec 每页记录数
   @param ps_sql sql语句
   @return 成功 查询的数据集 失败 null
   */
  private TableDataSet getTableDataSet2(int pi_page, int pi_pageRec, String ps_sql)
  {
    TableDataSet lo_dataset = new TableDataSet();
    int li_colType, li_colScale;
    int li_colCount, i;
    int li_row = 0, li_begin_row, li_end_row;
    ResultSet lrs_data = null; //数据集
    ResultSetMetaData lrmd_data = null;
    PreparedStatement lps_stmt = null; //sql语句类
    long ll_rownum = 0;
    try
    {
      lps_stmt = conn.prepareStatement(ps_sql);
      lrs_data = lps_stmt.executeQuery();
      if (lrs_data == null)
      {
        return null;
      }

      lrmd_data = lrs_data.getMetaData();
      //字段数
      li_colCount = lrmd_data.getColumnCount();

      lo_dataset.open();

      //定义数据集的字段信息
      Column[] columns = new Column[li_colCount];
      for (i = 0; i < li_colCount; i++)
      {
        li_colType = lrmd_data.getColumnType(i + 1);
        li_colScale = lrmd_data.getScale(i + 1);

        columns[i] = new Column(lrmd_data.getColumnName(i + 1),
                                lrmd_data.getColumnName(i + 1),
                                getDataType(li_colType, li_colScale));
      }

      //创建数据集字段信息
      int[] columnMap = ProviderHelp.initData(lo_dataset, columns, true, false, true);

      // Optionally the rowId could be set here.
      // The ResolverBean is the only consumer of this information.
      //设置记录的唯一标识字字段
      //dataSet.setAllRowIds(false);
      //Column rowid = dataSet.getColumn(ROWID_NAME);
      //rowid.setRowId(true);

      //设置该字段不显示
      //rowid.setHidden(true);

      //清空数据记录
      lo_dataset.empty();

      // 开始装入处理,形成装入数据缓冲区variants
      Variant[] variants = lo_dataset.startLoading(new LoadCancel()
      {
        public void cancelLoad()
        {}
      }

      , RowStatus.LOADED, false);

      li_begin_row = (pi_page - 1) * pi_pageRec + 1;
      li_end_row = pi_page * pi_pageRec;
      ll_rownum = 0;
      while (lrs_data.next())
      {
        li_row++;
        if (li_row < li_begin_row)
        {
          continue;
        }
        if (li_row > li_end_row)
        {
          break;
        }

        ll_rownum++;
        if (ll_rownum > MAX_FETCH_ROW_NUM)
        {
          Util.logError("查询记录数超过最大值:" + MAX_FETCH_ROW_NUM);
          return null;
        }

        for (int columnNo = 0; columnNo < columnMap.length; columnNo++)
        {
          // Get the ordinal of the column in the dataSet:
          int ordinal = columnMap[columnNo];

          //设置字段值到缓冲区中
          switch (variants[ordinal].getSetType())
          {
            case Variant.STRING:
              variants[ordinal].setString(getColString(lrs_data, columnNo + 1));

              break;
            case Variant.INT:
              variants[ordinal].setInt(getColInt(lrs_data, columnNo + 1));
              break;
            case Variant.BIGDECIMAL:
              variants[ordinal].setBigDecimal(getColBigDecimal(lrs_data, columnNo + 1));
              break;

            case Variant.DATE:
              variants[ordinal].setDate(getColDate(lrs_data, columnNo + 1));
              break;
            case Variant.TIME:
              variants[ordinal].setTime(getColTime(lrs_data, columnNo + 1));
              break;
            case Variant.TIMESTAMP:
              variants[ordinal].setTimestamp(getColTimestamp(lrs_data, columnNo + 1));
              break;

            default:
              variants[ordinal].setString(getColString(lrs_data, columnNo + 1));
              break;

          } //end of switch
        }

        // 将缓冲区中的一行记录装入数据集中
        lo_dataset.loadRow();

      } // end of while

      System.out.println("recnum1:" + lo_dataset.rowCount() + " ll_recnum:" + ll_rownum);

      // 结束装入,关闭装入缓冲区
      lo_dataset.endLoading();

      return lo_dataset;
    }
    catch (Exception ex)
    {
      Util.logError("getTableDataSet2: error!ex:" + ex);
      errProcess(ex);
      return null;
    }
    finally
    {
      if (lps_stmt != null)
      {
        try
        {
          lps_stmt.close();
          lps_stmt = null;
          lrs_data.close();
          lrs_data = null; //数据集
          lrmd_data = null;
        }
        catch (Exception ex)
        {}
        ;
      }
    }
  }

  /**
    功能:取得查询结果数据集数据
    @param pi_page 页号
    @param pi_pageRec 每页记录数
    @param ps_sql  sql语句
    @return  成功 查询结果 失败 null
   */
  private ExecInfo doGetDataSetData(int pi_page, int pi_pageRec, String ps_sql)
  {
    TableDataSet lo_dataset = null;
    ExecInfo lo_ret = new ExecInfo();
    try
    {
      lo_dataset = getTableDataSet2(pi_page, pi_pageRec, ps_sql);
      if (lo_dataset == null)
      {
        System.out.println("getDataSetData:error1! sql:" + ps_sql);
        lo_ret.setErrMsg("getDataSetData:error1! sql:" + ps_sql);
        lo_ret.setSqlCode( -1);
        return lo_ret;
      }
      DataSetData lo_data = DataSetData.extractDataSet(lo_dataset);
      if (lo_data == null)
      {
        lo_ret.setErrMsg("getDataSetData:error2! sql:" + ps_sql);
        lo_ret.setSqlCode( -1);
        return lo_ret;
      }
      lo_ret.setDataSetData(lo_data);
      return lo_ret;
    }
    catch (Exception ex)
    {
      errProcess(ex);
      lo_ret.setErrMsg("getDataSetData:error3! sql:" + ps_sql + " ex:" + ex);
      lo_ret.setSqlCode( -1);
      return lo_ret;
    }
    finally
    {
      if (lo_dataset != null)
      {
        lo_dataset.close();
        lo_dataset = null;
      }
    }
  }

  /**
   功能:执行sql语句
   @param ps_sql  sql语句
   @return  成功  查询结果  失败  null
   */
  public ExecInfo execSql(String ps_sql)
  {
    boolean lb_isSelectSql = false;

    if (conn == null)
    {
      Util.logError("execSql:数据库尚未建立连接!");
      return null;
    }
    try
    {
      lb_isSelectSql = Util.isSelectSql(ps_sql);
      ExecInfo lo_ret = doExecSql(ps_sql);

      if (lo_ret.getSqlCode() != 0)
      {
        return null;
      }
      if (lb_isSelectSql)
      {
        //产生查询数据集
        if (!lo_ret.generateDataSet())
        {
          return null;
        }
      }
      return lo_ret;
    }
    catch (Exception ex)
    {
      Util.logError("execSql:执行语句:" + ps_sql + "失败:" + ex);
      return null;
    }
  }

  /**
    功能:取得查询结果数据集数据
    @param 无
    @return  成功 查询结果 失败 null
   */
  public DataSetData getDataSetData(String ps_sql)
  {
    ExecInfo lo_ret = null;
    try
    {
      if (conn == null)
      {
        Util.logError("getDataSetData:尚未连接数据库!");
        return null;
      }
      Util.logInfo("getDataSetData sql:" + ps_sql);
      lo_ret = doGetDataSetData(ps_sql);
      if (lo_ret == null)
      {
        Util.logError("getDataSetData:执行语句:" + ps_sql + "失败");
        return null;
      }
      if (lo_ret.getSqlCode() != 0)
      {
        return null;
      }
      return lo_ret.getDataSetData();
    }
    catch (Exception ex)
    {
      Util.logError("getDataSetData:执行语句:" + ps_sql + "失败:" + ex);
      return null;
    }
    finally
    {
      if (lo_ret != null)
      {
        lo_ret.close();
      }
    }
  }

  /**
    功能:取得查询结果数据集数据
    @param pi_page 页号
    @param pi_pageRec 每页记录数
    @param ps_sql  sql语句
    @return  成功 查询结果 失败 null
   */
  public DataSetData getDataSetData(int pi_page, int pi_pageRec, String ps_sql)
  {
    ExecInfo lo_ret = null;
    try
    {
      if (conn == null)
      {
        Util.logError("getDataSetData:尚未连接数据库!");
        return null;
      }
      lo_ret = doGetDataSetData(pi_page, pi_pageRec, ps_sql);
      if (lo_ret.getSqlCode() != 0)
      {
        return null;
      }
      DataSetData lo_data = lo_ret.getDataSetData();
      return lo_data;
    }
    catch (Exception ex)
    {
      Util.logError("getDataSetData:执行语句:" + ps_sql + "失败:" + ex);
      return null;
    }
    finally
    {
      if (lo_ret != null)
      {
        lo_ret.close();
      }
    }
  }

  /**
   功能:保存数据窗口变动的数据
   @param  ps_sql 查询sql语句
   @param  po_changes  变动的数据集数据
   @return  可能产生错误的数据集
   */
  public DataSetData saveDataChanges(String ps_sql, DataSetData po_changes) throws
      DataSetException
  {
    return null;
  }

  /**
    功能:提交数据
    @param 无
    @return  1 成功 0 失败
   */
  public int commit()
  {
    try
    {
      if (conn == null)
      {
        Util.logError("commit:尚未连接数据库!");
        return 0;
      }
      conn.commit();
      return 1;
    }
    catch (Exception ex)
    {
      Util.logError("commit:" + ex.toString());
      return 0;
    }
  }

  /**
    功能:回滚事务,同时关闭数据库连接
    @param 无
    @return  1 成功  0 失败
   */
  public int rollback()
  {
    try
    {
      if (conn == null)
      {
        Util.logError("rollback:尚未连接数据库!");
        return 0;
      }
      conn.rollback();
      return 1;
    }
    catch (Exception ex)
    {
      Util.logError("rollback:" + ex.toString());
      return 0;
    }
  }

  /**
   功能:关闭数据库连接
   @param 无
   @return 无
   */
  public void close()
  {
    try
    {
      conn.close();
    }
    catch (Exception ex)
    {
      Util.error("close:" + ex);
    }
  }

}