/**
  * 查询满足模糊查询条件的记录数
  *
  * @param conditions conditions
  * @return 满足模糊查询条件的记录数
  * @throws Exception
  */
 public int getCount(String conditions) throws Exception {
   int count = -1;
   StringBuffer buffer = new StringBuffer(100);
   buffer.append("SELECT count(*) FROM LwWholeSaleSummary WHERE ");
   buffer.append(conditions);
   if (logger.isDebugEnabled()) {
     logger.debug(buffer.toString());
   }
   ResultSet resultSet = dbManager.executeQuery(buffer.toString());
   resultSet.next();
   count = dbManager.getInt(resultSet, 1);
   resultSet.close();
   return count;
 }
  /**
   * 按主键查找一条数据
   *
   * @param saleArea 趸售区域
   * @param voltageBegin 起始电压
   * @return LwWholeSalePriceDto
   * @throws Exception
   */
  public LwWholeSalePriceDto findByPrimaryKey(String saleArea, double voltageBegin)
      throws Exception {
    StringBuffer buffer = new StringBuffer(200);
    // 拼SQL语句
    buffer.append("SELECT ");
    buffer.append("PowerClass,");
    buffer.append("SaleArea,");
    buffer.append("VoltageBegin,");
    buffer.append("VoltageEnd,");
    buffer.append("Price,");
    buffer.append("ValidStatus,");
    buffer.append("Flag,");
    buffer.append("Remark ");
    buffer.append("FROM LwWholeSalePrice ");
    if (logger.isDebugEnabled()) {
      StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
      debugBuffer.append(buffer.toString());
      debugBuffer.append("WHERE ");
      debugBuffer.append("SaleArea=").append("'").append(saleArea).append("' AND ");
      debugBuffer.append("VoltageBegin=").append("").append(voltageBegin).append("");
      logger.debug(debugBuffer.toString());
    }

    buffer.append("WHERE ");
    buffer.append("SaleArea = ? And ");
    buffer.append("VoltageBegin = ?");

    dbManager.prepareStatement(buffer.toString());
    // 设置条件字段;
    dbManager.setString(1, saleArea);
    dbManager.setDouble(2, voltageBegin);
    ResultSet resultSet = dbManager.executePreparedQuery();
    LwWholeSalePriceDto lwWholeSalePriceDto = null;
    if (resultSet.next()) {
      lwWholeSalePriceDto = new LwWholeSalePriceDto();
      lwWholeSalePriceDto.setPowerClass(dbManager.getString(resultSet, 1));
      lwWholeSalePriceDto.setSaleArea(dbManager.getString(resultSet, 2));
      lwWholeSalePriceDto.setVoltageBegin(dbManager.getDouble(resultSet, 3));
      lwWholeSalePriceDto.setVoltageEnd(dbManager.getDouble(resultSet, 4));
      lwWholeSalePriceDto.setPrice(dbManager.getDouble(resultSet, 5));
      lwWholeSalePriceDto.setValidStatus(dbManager.getString(resultSet, 6));
      lwWholeSalePriceDto.setFlag(dbManager.getString(resultSet, 7));
      lwWholeSalePriceDto.setRemark(dbManager.getString(resultSet, 8));
    }
    resultSet.close();
    return lwWholeSalePriceDto;
  }
示例#3
0
  /**
   * 按主键查找一条数据
   *
   * @param lineCode 线路名称
   * @return LineLossDto
   * @throws Exception
   */
  public LineLossDto findByPrimaryKey(String lineCode) throws Exception {
    StringBuffer buffer = new StringBuffer(200);
    // 拼SQL语句
    buffer.append("SELECT ");
    buffer.append("LineCode,");
    buffer.append("R,");
    buffer.append("LineLong,");
    buffer.append("Volt,");
    buffer.append("T,");
    buffer.append("ValidStatus,");
    buffer.append("Flag,");
    buffer.append("Remark ");
    buffer.append("FROM LineLoss ");
    if (logger.isDebugEnabled()) {
      StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
      debugBuffer.append(buffer.toString());
      debugBuffer.append("WHERE ");
      debugBuffer.append("LineCode=").append("'").append(lineCode).append("'");
      logger.debug(debugBuffer.toString());
    }

    buffer.append("WHERE ");
    buffer.append("LineCode = ?");

    dbManager.prepareStatement(buffer.toString());
    // 设置条件字段;
    dbManager.setString(1, lineCode);
    ResultSet resultSet = dbManager.executePreparedQuery();
    LineLossDto lineLossDto = null;
    if (resultSet.next()) {
      lineLossDto = new LineLossDto();
      lineLossDto.setLineCode(dbManager.getString(resultSet, 1));
      lineLossDto.setR(dbManager.getDouble(resultSet, 2));
      lineLossDto.setLineLong(dbManager.getDouble(resultSet, 3));
      lineLossDto.setVolt(dbManager.getDouble(resultSet, 4));
      lineLossDto.setT(dbManager.getDouble(resultSet, 5));
      lineLossDto.setValidStatus(dbManager.getString(resultSet, 6));
      lineLossDto.setFlag(dbManager.getString(resultSet, 7));
      lineLossDto.setRemark(dbManager.getString(resultSet, 8));
    }
    resultSet.close();
    return lineLossDto;
  }
  /**
   * 按条件查询多条数据
   *
   * @param conditions 查询条件
   * @param pageNo 页号
   * @param rowsPerPage 每页的行数
   * @return Collection
   * @throws Exception
   */
  public Collection findByConditions(String conditions, int pageNo, int rowsPerPage)
      throws Exception {
    StringBuffer buffer = new StringBuffer(200);
    // 拼SQL语句
    buffer.append("SELECT ");
    buffer.append("LineCode,");
    buffer.append("StatMonth,");
    buffer.append("PowerClass,");
    buffer.append("ElectricQuantity,");
    buffer.append("PointerQuantity,");
    buffer.append("SanXiaFee,");
    buffer.append("Surcharge,");
    buffer.append("SumFee,");
    buffer.append("ValidStatus,");
    buffer.append("Flag,");
    buffer.append("Remark,");
    buffer.append("TransLoss,");
    buffer.append("LineLoss,");
    buffer.append("UnPointerQuantity,");
    buffer.append("RateCode,");
    buffer.append("AdjustRate,");
    buffer.append("FarmUseScale,");
    buffer.append("FarmUsePrice,");
    buffer.append("FarmUseQuantity,");
    buffer.append("FarmUseFee,");
    buffer.append("ProductScale,");
    buffer.append("ProductPrice,");
    buffer.append("ProductQuantity,");
    buffer.append("ProductFee,");
    buffer.append("DenizenScale,");
    buffer.append("DenizenPrice,");
    buffer.append("DenizenQuantity,");
    buffer.append("DenizenFee,");
    buffer.append("UnDenizenScale,");
    buffer.append("UnDenizenPrice,");
    buffer.append("UnDenizenQuantity,");
    buffer.append("UnDenizenFee,");
    buffer.append("IndustryScale,");
    buffer.append("IndustryPrice,");
    buffer.append("IndustryQuantity,");
    buffer.append("IndustryFee,");
    buffer.append("BizScale,");
    buffer.append("BizPrice,");
    buffer.append("BizQuantity,");
    buffer.append("BizFee,");
    buffer.append("PowerRateFee,");
    buffer.append("UpCompany,");
    buffer.append("PowerFee,");
    buffer.append("InputDate,");
    buffer.append("Kv,");
    buffer.append("Wholesaletype,");
    buffer.append("WorkNum,");
    buffer.append("UnWorkNum,");
    buffer.append("OtherSurcharge,");
    buffer.append("DifferenceQuantity,");
    buffer.append("UnTransLoss,");
    buffer.append("UnLineLoss ");
    buffer.append("FROM LwWholeSaleSummary WHERE ");
    buffer.append(conditions);
    boolean supportPaging = false; // 数据库是否支持分页
    if (pageNo > 0) {
      // 对Oracle优化
      if (dbManager
          .getConnection()
          .getMetaData()
          .getDatabaseProductName()
          .equalsIgnoreCase("Oracle")) {
        buffer.insert(0, "SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (");
        buffer.append(
            ") row_ WHERE rownum <= "
                + rowsPerPage * pageNo
                + ") WHERE rownum_ > "
                + rowsPerPage * (pageNo - 1));
        supportPaging = true;
      } else if (dbManager
          .getConnection()
          .getMetaData()
          .getDatabaseProductName()
          .equalsIgnoreCase("DB2")) {
        String sql = buffer.toString();
        buffer.setLength(0);
        buffer.append("select * from ( select rownumber() over(");
        int orderByIndex = sql.toLowerCase().indexOf("order by");
        if (orderByIndex > 0) {
          buffer.append(sql.substring(orderByIndex));
        }
        buffer.append(") as rownumber_,");
        buffer.append(sql.substring(6));
        buffer.append(" ) as temp_ where rownumber_");
        buffer.append(
            " between " + (rowsPerPage * (pageNo - 1) + 1) + " and " + rowsPerPage * pageNo);
        supportPaging = true;
      }
    }
    if (logger.isDebugEnabled()) {
      logger.debug(buffer.toString());
    }
    ResultSet resultSet = dbManager.executeQuery(buffer.toString());
    int count = 0;
    if (supportPaging == false && pageNo > 1) {
      dbManager.locate(resultSet, rowsPerPage * (pageNo - 1));
    }

    // 定义返回结果集合
    Collection collection = new ArrayList(rowsPerPage);
    LwWholeSaleSummaryDto lwWholeSaleSummaryDto = null;
    while (resultSet.next()) {
      if (supportPaging == false && pageNo > 0) {
        count++;
        if (count > rowsPerPage) {
          break;
        }
      }

      lwWholeSaleSummaryDto = new LwWholeSaleSummaryDto();
      lwWholeSaleSummaryDto.setLineCode(dbManager.getString(resultSet, "LineCode"));
      lwWholeSaleSummaryDto.setStatMonth(dbManager.getString(resultSet, "StatMonth"));
      lwWholeSaleSummaryDto.setPowerClass(dbManager.getString(resultSet, "PowerClass"));
      lwWholeSaleSummaryDto.setElectricQuantity(dbManager.getDouble(resultSet, "ElectricQuantity"));
      lwWholeSaleSummaryDto.setPointerQuantity(dbManager.getDouble(resultSet, "PointerQuantity"));
      lwWholeSaleSummaryDto.setSanXiaFee(dbManager.getDouble(resultSet, "SanXiaFee"));
      lwWholeSaleSummaryDto.setSurcharge(dbManager.getDouble(resultSet, "Surcharge"));
      lwWholeSaleSummaryDto.setSumFee(dbManager.getDouble(resultSet, "SumFee"));
      lwWholeSaleSummaryDto.setValidStatus(dbManager.getString(resultSet, "ValidStatus"));
      lwWholeSaleSummaryDto.setFlag(dbManager.getString(resultSet, "Flag"));
      lwWholeSaleSummaryDto.setRemark(dbManager.getString(resultSet, "Remark"));
      lwWholeSaleSummaryDto.setTransLoss(dbManager.getDouble(resultSet, "TransLoss"));
      lwWholeSaleSummaryDto.setLineLoss(dbManager.getDouble(resultSet, "LineLoss"));
      lwWholeSaleSummaryDto.setUnPointerQuantity(
          dbManager.getDouble(resultSet, "UnPointerQuantity"));
      lwWholeSaleSummaryDto.setRateCode(dbManager.getDouble(resultSet, "RateCode"));
      lwWholeSaleSummaryDto.setAdjustRate(dbManager.getDouble(resultSet, "AdjustRate"));
      lwWholeSaleSummaryDto.setFarmUseScale(dbManager.getDouble(resultSet, "FarmUseScale"));
      lwWholeSaleSummaryDto.setFarmUsePrice(dbManager.getDouble(resultSet, "FarmUsePrice"));
      lwWholeSaleSummaryDto.setFarmUseQuantity(dbManager.getDouble(resultSet, "FarmUseQuantity"));
      lwWholeSaleSummaryDto.setFarmUseFee(dbManager.getDouble(resultSet, "FarmUseFee"));
      lwWholeSaleSummaryDto.setProductScale(dbManager.getDouble(resultSet, "ProductScale"));
      lwWholeSaleSummaryDto.setProductPrice(dbManager.getDouble(resultSet, "ProductPrice"));
      lwWholeSaleSummaryDto.setProductQuantity(dbManager.getDouble(resultSet, "ProductQuantity"));
      lwWholeSaleSummaryDto.setProductFee(dbManager.getDouble(resultSet, "ProductFee"));
      lwWholeSaleSummaryDto.setDenizenScale(dbManager.getDouble(resultSet, "DenizenScale"));
      lwWholeSaleSummaryDto.setDenizenPrice(dbManager.getDouble(resultSet, "DenizenPrice"));
      lwWholeSaleSummaryDto.setDenizenQuantity(dbManager.getDouble(resultSet, "DenizenQuantity"));
      lwWholeSaleSummaryDto.setDenizenFee(dbManager.getDouble(resultSet, "DenizenFee"));
      lwWholeSaleSummaryDto.setUnDenizenScale(dbManager.getDouble(resultSet, "UnDenizenScale"));
      lwWholeSaleSummaryDto.setUnDenizenPrice(dbManager.getDouble(resultSet, "UnDenizenPrice"));
      lwWholeSaleSummaryDto.setUnDenizenQuantity(
          dbManager.getDouble(resultSet, "UnDenizenQuantity"));
      lwWholeSaleSummaryDto.setUnDenizenFee(dbManager.getDouble(resultSet, "UnDenizenFee"));
      lwWholeSaleSummaryDto.setIndustryScale(dbManager.getDouble(resultSet, "IndustryScale"));
      lwWholeSaleSummaryDto.setIndustryPrice(dbManager.getDouble(resultSet, "IndustryPrice"));
      lwWholeSaleSummaryDto.setIndustryQuantity(dbManager.getDouble(resultSet, "IndustryQuantity"));
      lwWholeSaleSummaryDto.setIndustryFee(dbManager.getDouble(resultSet, "IndustryFee"));
      lwWholeSaleSummaryDto.setBizScale(dbManager.getDouble(resultSet, "BizScale"));
      lwWholeSaleSummaryDto.setBizPrice(dbManager.getDouble(resultSet, "BizPrice"));
      lwWholeSaleSummaryDto.setBizQuantity(dbManager.getDouble(resultSet, "BizQuantity"));
      lwWholeSaleSummaryDto.setBizFee(dbManager.getDouble(resultSet, "BizFee"));
      lwWholeSaleSummaryDto.setPowerRateFee(dbManager.getDouble(resultSet, "PowerRateFee"));
      lwWholeSaleSummaryDto.setUpCompany(dbManager.getString(resultSet, "UpCompany"));
      lwWholeSaleSummaryDto.setPowerFee(dbManager.getDouble(resultSet, "PowerFee"));
      lwWholeSaleSummaryDto.setInputDate(dbManager.getString(resultSet, "InputDate"));
      lwWholeSaleSummaryDto.setKv(dbManager.getString(resultSet, "Kv"));
      lwWholeSaleSummaryDto.setWholesaletype(dbManager.getString(resultSet, "Wholesaletype"));
      lwWholeSaleSummaryDto.setWorkNum(dbManager.getDouble(resultSet, "WorkNum"));
      lwWholeSaleSummaryDto.setUnWorkNum(dbManager.getDouble(resultSet, "UnWorkNum"));
      lwWholeSaleSummaryDto.setOtherSurcharge(dbManager.getDouble(resultSet, "OtherSurcharge"));
      lwWholeSaleSummaryDto.setDifferenceQuantity(
          dbManager.getString(resultSet, "DifferenceQuantity"));
      lwWholeSaleSummaryDto.setUnTransLoss(dbManager.getDouble(resultSet, "UnTransLoss"));
      lwWholeSaleSummaryDto.setUnLineLoss(dbManager.getDouble(resultSet, "UnLineLoss"));
      collection.add(lwWholeSaleSummaryDto);
    }
    resultSet.close();
    return collection;
  }
  /**
   * 按主键查找一条数据
   *
   * @param lineCode 线路代码
   * @param statMonth 统计年月
   * @return LwWholeSaleSummaryDto
   * @throws Exception
   */
  public LwWholeSaleSummaryDto findByPrimaryKey(String lineCode, String statMonth)
      throws Exception {
    StringBuffer buffer = new StringBuffer(200);
    // 拼SQL语句
    buffer.append("SELECT ");
    buffer.append("LineCode,");
    buffer.append("StatMonth,");
    buffer.append("PowerClass,");
    buffer.append("ElectricQuantity,");
    buffer.append("PointerQuantity,");
    buffer.append("SanXiaFee,");
    buffer.append("Surcharge,");
    buffer.append("SumFee,");
    buffer.append("ValidStatus,");
    buffer.append("Flag,");
    buffer.append("Remark,");
    buffer.append("TransLoss,");
    buffer.append("LineLoss,");
    buffer.append("UnPointerQuantity,");
    buffer.append("RateCode,");
    buffer.append("AdjustRate,");
    buffer.append("FarmUseScale,");
    buffer.append("FarmUsePrice,");
    buffer.append("FarmUseQuantity,");
    buffer.append("FarmUseFee,");
    buffer.append("ProductScale,");
    buffer.append("ProductPrice,");
    buffer.append("ProductQuantity,");
    buffer.append("ProductFee,");
    buffer.append("DenizenScale,");
    buffer.append("DenizenPrice,");
    buffer.append("DenizenQuantity,");
    buffer.append("DenizenFee,");
    buffer.append("UnDenizenScale,");
    buffer.append("UnDenizenPrice,");
    buffer.append("UnDenizenQuantity,");
    buffer.append("UnDenizenFee,");
    buffer.append("IndustryScale,");
    buffer.append("IndustryPrice,");
    buffer.append("IndustryQuantity,");
    buffer.append("IndustryFee,");
    buffer.append("BizScale,");
    buffer.append("BizPrice,");
    buffer.append("BizQuantity,");
    buffer.append("BizFee,");
    buffer.append("PowerRateFee,");
    buffer.append("UpCompany,");
    buffer.append("PowerFee,");
    buffer.append("InputDate,");
    buffer.append("Kv,");
    buffer.append("Wholesaletype,");
    buffer.append("WorkNum,");
    buffer.append("UnWorkNum,");
    buffer.append("OtherSurcharge,");
    buffer.append("DifferenceQuantity,");
    buffer.append("UnTransLoss,");
    buffer.append("UnLineLoss ");
    buffer.append("FROM LwWholeSaleSummary ");
    if (logger.isDebugEnabled()) {
      StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
      debugBuffer.append(buffer.toString());
      debugBuffer.append("WHERE ");
      debugBuffer.append("LineCode=").append("'").append(lineCode).append("' AND ");
      debugBuffer.append("StatMonth=").append("'").append(statMonth).append("'");
      logger.debug(debugBuffer.toString());
    }

    buffer.append("WHERE ");
    buffer.append("LineCode = ? And ");
    buffer.append("StatMonth = ?");

    dbManager.prepareStatement(buffer.toString());
    // 设置条件字段;
    dbManager.setString(1, lineCode);
    dbManager.setString(2, statMonth);
    ResultSet resultSet = dbManager.executePreparedQuery();
    LwWholeSaleSummaryDto lwWholeSaleSummaryDto = null;
    if (resultSet.next()) {
      lwWholeSaleSummaryDto = new LwWholeSaleSummaryDto();
      lwWholeSaleSummaryDto.setLineCode(dbManager.getString(resultSet, 1));
      lwWholeSaleSummaryDto.setStatMonth(dbManager.getString(resultSet, 2));
      lwWholeSaleSummaryDto.setPowerClass(dbManager.getString(resultSet, 3));
      lwWholeSaleSummaryDto.setElectricQuantity(dbManager.getDouble(resultSet, 4));
      lwWholeSaleSummaryDto.setPointerQuantity(dbManager.getDouble(resultSet, 5));
      lwWholeSaleSummaryDto.setSanXiaFee(dbManager.getDouble(resultSet, 6));
      lwWholeSaleSummaryDto.setSurcharge(dbManager.getDouble(resultSet, 7));
      lwWholeSaleSummaryDto.setSumFee(dbManager.getDouble(resultSet, 8));
      lwWholeSaleSummaryDto.setValidStatus(dbManager.getString(resultSet, 9));
      lwWholeSaleSummaryDto.setFlag(dbManager.getString(resultSet, 10));
      lwWholeSaleSummaryDto.setRemark(dbManager.getString(resultSet, 11));
      lwWholeSaleSummaryDto.setTransLoss(dbManager.getDouble(resultSet, 12));
      lwWholeSaleSummaryDto.setLineLoss(dbManager.getDouble(resultSet, 13));
      lwWholeSaleSummaryDto.setUnPointerQuantity(dbManager.getDouble(resultSet, 14));
      lwWholeSaleSummaryDto.setRateCode(dbManager.getDouble(resultSet, 15));
      lwWholeSaleSummaryDto.setAdjustRate(dbManager.getDouble(resultSet, 16));
      lwWholeSaleSummaryDto.setFarmUseScale(dbManager.getDouble(resultSet, 17));
      lwWholeSaleSummaryDto.setFarmUsePrice(dbManager.getDouble(resultSet, 18));
      lwWholeSaleSummaryDto.setFarmUseQuantity(dbManager.getDouble(resultSet, 19));
      lwWholeSaleSummaryDto.setFarmUseFee(dbManager.getDouble(resultSet, 20));
      lwWholeSaleSummaryDto.setProductScale(dbManager.getDouble(resultSet, 21));
      lwWholeSaleSummaryDto.setProductPrice(dbManager.getDouble(resultSet, 22));
      lwWholeSaleSummaryDto.setProductQuantity(dbManager.getDouble(resultSet, 23));
      lwWholeSaleSummaryDto.setProductFee(dbManager.getDouble(resultSet, 24));
      lwWholeSaleSummaryDto.setDenizenScale(dbManager.getDouble(resultSet, 25));
      lwWholeSaleSummaryDto.setDenizenPrice(dbManager.getDouble(resultSet, 26));
      lwWholeSaleSummaryDto.setDenizenQuantity(dbManager.getDouble(resultSet, 27));
      lwWholeSaleSummaryDto.setDenizenFee(dbManager.getDouble(resultSet, 28));
      lwWholeSaleSummaryDto.setUnDenizenScale(dbManager.getDouble(resultSet, 29));
      lwWholeSaleSummaryDto.setUnDenizenPrice(dbManager.getDouble(resultSet, 30));
      lwWholeSaleSummaryDto.setUnDenizenQuantity(dbManager.getDouble(resultSet, 31));
      lwWholeSaleSummaryDto.setUnDenizenFee(dbManager.getDouble(resultSet, 32));
      lwWholeSaleSummaryDto.setIndustryScale(dbManager.getDouble(resultSet, 33));
      lwWholeSaleSummaryDto.setIndustryPrice(dbManager.getDouble(resultSet, 34));
      lwWholeSaleSummaryDto.setIndustryQuantity(dbManager.getDouble(resultSet, 35));
      lwWholeSaleSummaryDto.setIndustryFee(dbManager.getDouble(resultSet, 36));
      lwWholeSaleSummaryDto.setBizScale(dbManager.getDouble(resultSet, 37));
      lwWholeSaleSummaryDto.setBizPrice(dbManager.getDouble(resultSet, 38));
      lwWholeSaleSummaryDto.setBizQuantity(dbManager.getDouble(resultSet, 39));
      lwWholeSaleSummaryDto.setBizFee(dbManager.getDouble(resultSet, 40));
      lwWholeSaleSummaryDto.setPowerRateFee(dbManager.getDouble(resultSet, 41));
      lwWholeSaleSummaryDto.setUpCompany(dbManager.getString(resultSet, 42));
      lwWholeSaleSummaryDto.setPowerFee(dbManager.getDouble(resultSet, 43));
      lwWholeSaleSummaryDto.setInputDate(dbManager.getString(resultSet, 44));
      lwWholeSaleSummaryDto.setKv(dbManager.getString(resultSet, 45));
      lwWholeSaleSummaryDto.setWholesaletype(dbManager.getString(resultSet, 46));
      lwWholeSaleSummaryDto.setWorkNum(dbManager.getDouble(resultSet, 47));
      lwWholeSaleSummaryDto.setUnWorkNum(dbManager.getDouble(resultSet, 48));
      lwWholeSaleSummaryDto.setOtherSurcharge(dbManager.getDouble(resultSet, 49));
      lwWholeSaleSummaryDto.setDifferenceQuantity(dbManager.getString(resultSet, 50));
      lwWholeSaleSummaryDto.setUnTransLoss(dbManager.getDouble(resultSet, 51));
      lwWholeSaleSummaryDto.setUnLineLoss(dbManager.getDouble(resultSet, 52));
    }
    resultSet.close();
    return lwWholeSaleSummaryDto;
  }
示例#6
0
  /**
   * 按条件查询多条数据
   *
   * @param conditions 查询条件
   * @param pageNo 页号
   * @param rowsPerPage 每页的行数
   * @return Collection
   * @throws Exception
   */
  public Collection findByConditions(String conditions, int pageNo, int rowsPerPage)
      throws Exception {
    StringBuffer buffer = new StringBuffer(200);
    // 拼SQL语句
    buffer.append("SELECT ");
    buffer.append("LineCode,");
    buffer.append("R,");
    buffer.append("LineLong,");
    buffer.append("Volt,");
    buffer.append("T,");
    buffer.append("ValidStatus,");
    buffer.append("Flag,");
    buffer.append("Remark ");
    buffer.append("FROM LineLoss WHERE ");
    buffer.append(conditions);
    boolean supportPaging = false; // 数据库是否支持分页
    if (pageNo > 0) {
      // 对Oracle优化
      if (dbManager
          .getConnection()
          .getMetaData()
          .getDatabaseProductName()
          .equalsIgnoreCase("Oracle")) {
        buffer.insert(0, "SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (");
        buffer.append(
            ") row_ WHERE rownum <= "
                + rowsPerPage * pageNo
                + ") WHERE rownum_ > "
                + rowsPerPage * (pageNo - 1));
        supportPaging = true;
      } else if (dbManager
          .getConnection()
          .getMetaData()
          .getDatabaseProductName()
          .equalsIgnoreCase("DB2")) {
        String sql = buffer.toString();
        buffer.setLength(0);
        buffer.append("select * from ( select rownumber() over(");
        int orderByIndex = sql.toLowerCase().indexOf("order by");
        if (orderByIndex > 0) {
          buffer.append(sql.substring(orderByIndex));
        }
        buffer.append(") as rownumber_,");
        buffer.append(sql.substring(6));
        buffer.append(" ) as temp_ where rownumber_");
        buffer.append(
            " between " + (rowsPerPage * (pageNo - 1) + 1) + " and " + rowsPerPage * pageNo);
        supportPaging = true;
      }
    }
    if (logger.isDebugEnabled()) {
      logger.debug(buffer.toString());
    }
    ResultSet resultSet = dbManager.executeQuery(buffer.toString());
    int count = 0;
    if (supportPaging == false && pageNo > 1) {
      dbManager.locate(resultSet, rowsPerPage * (pageNo - 1));
    }

    // 定义返回结果集合
    Collection collection = new ArrayList(rowsPerPage);
    LineLossDto lineLossDto = null;
    while (resultSet.next()) {
      if (supportPaging == false && pageNo > 0) {
        count++;
        if (count > rowsPerPage) {
          break;
        }
      }

      lineLossDto = new LineLossDto();
      lineLossDto.setLineCode(dbManager.getString(resultSet, "LineCode"));
      lineLossDto.setR(dbManager.getDouble(resultSet, "R"));
      lineLossDto.setLineLong(dbManager.getDouble(resultSet, "LineLong"));
      lineLossDto.setVolt(dbManager.getDouble(resultSet, "Volt"));
      lineLossDto.setT(dbManager.getDouble(resultSet, "T"));
      lineLossDto.setValidStatus(dbManager.getString(resultSet, "ValidStatus"));
      lineLossDto.setFlag(dbManager.getString(resultSet, "Flag"));
      lineLossDto.setRemark(dbManager.getString(resultSet, "Remark"));
      collection.add(lineLossDto);
    }
    resultSet.close();
    return collection;
  }
  /**
   * 按条件查询多条数据
   *
   * @param conditions 查询条件
   * @param pageNo 页号
   * @param rowsPerPage 每页的行数
   * @return Collection
   * @throws Exception
   */
  public Collection findByConditions(String conditions, int pageNo, int rowsPerPage)
      throws Exception {
    StringBuffer buffer = new StringBuffer(200);
    // 拼SQL语句
    buffer.append("SELECT ");
    buffer.append("UserNo,");
    buffer.append("UserName,");
    buffer.append("Address,");
    buffer.append("ReadDate,");
    buffer.append("StatMonth,");
    buffer.append("ThisWorkNum,");
    buffer.append("MidWorkNum,");
    buffer.append("LastWorkNum,");
    buffer.append("Rate,");
    buffer.append("ReadQuantity,");
    buffer.append("ExcepQuantity,");
    buffer.append("ChgAmmeterQuantity,");
    buffer.append("CompensateQuantity,");
    buffer.append("AppendCalQuantity,");
    buffer.append("TranferLossQuantity,");
    buffer.append("PeoplePrice,");
    buffer.append("NotPeoplePrice,");
    buffer.append("FarmPrice,");
    buffer.append("ProducePrice,");
    buffer.append("BusinessPrice,");
    buffer.append("Voltlevel,");
    buffer.append("IndustryPrice,");
    buffer.append("ValidStatus,");
    buffer.append("Flag,");
    buffer.append("Remark,");
    buffer.append("InputDate ");
    buffer.append("FROM LwTownIndicator WHERE ");
    buffer.append(conditions);
    boolean supportPaging = false; // 数据库是否支持分页
    if (pageNo > 0) {
      // 对Oracle优化
      if (dbManager
          .getConnection()
          .getMetaData()
          .getDatabaseProductName()
          .equalsIgnoreCase("Oracle")) {
        buffer.insert(0, "SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (");
        buffer.append(
            ") row_ WHERE rownum <= "
                + rowsPerPage * pageNo
                + ") WHERE rownum_ > "
                + rowsPerPage * (pageNo - 1));
        supportPaging = true;
      } else if (dbManager
          .getConnection()
          .getMetaData()
          .getDatabaseProductName()
          .equalsIgnoreCase("DB2")) {
        String sql = buffer.toString();
        buffer.setLength(0);
        buffer.append("select * from ( select rownumber() over(");
        int orderByIndex = sql.toLowerCase().indexOf("order by");
        if (orderByIndex > 0) {
          buffer.append(sql.substring(orderByIndex));
        }
        buffer.append(") as rownumber_,");
        buffer.append(sql.substring(6));
        buffer.append(" ) as temp_ where rownumber_");
        buffer.append(
            " between " + (rowsPerPage * (pageNo - 1) + 1) + " and " + rowsPerPage * pageNo);
        supportPaging = true;
      }
    }
    if (true) {
      logger.debug(buffer.toString());
    }
    ResultSet resultSet = dbManager.executeQuery(buffer.toString());
    int count = 0;
    if (supportPaging == false && pageNo > 1) {
      dbManager.locate(resultSet, rowsPerPage * (pageNo - 1));
    }

    // 定义返回结果集合
    Collection collection = new ArrayList(rowsPerPage);
    LwTownIndicatorDto lwTownIndicatorDto = null;
    while (resultSet.next()) {
      if (supportPaging == false && pageNo > 0) {
        count++;
        if (count > rowsPerPage) {
          break;
        }
      }

      lwTownIndicatorDto = new LwTownIndicatorDto();
      lwTownIndicatorDto.setUserNo(dbManager.getString(resultSet, "UserNo"));
      lwTownIndicatorDto.setUserName(dbManager.getString(resultSet, "UserName"));
      lwTownIndicatorDto.setAddress(dbManager.getString(resultSet, "Address"));
      lwTownIndicatorDto.setReadDate(dbManager.getString(resultSet, "ReadDate"));
      lwTownIndicatorDto.setStatMonth(dbManager.getString(resultSet, "StatMonth"));
      lwTownIndicatorDto.setThisWorkNum(dbManager.getDouble(resultSet, "ThisWorkNum"));
      lwTownIndicatorDto.setMidWorkNum(dbManager.getDouble(resultSet, "MidWorkNum"));
      lwTownIndicatorDto.setLastWorkNum(dbManager.getDouble(resultSet, "LastWorkNum"));
      lwTownIndicatorDto.setRate(dbManager.getDouble(resultSet, "Rate"));
      lwTownIndicatorDto.setReadQuantity(dbManager.getDouble(resultSet, "ReadQuantity"));
      lwTownIndicatorDto.setExcepQuantity(dbManager.getDouble(resultSet, "ExcepQuantity"));
      lwTownIndicatorDto.setChgAmmeterQuantity(
          dbManager.getDouble(resultSet, "ChgAmmeterQuantity"));
      lwTownIndicatorDto.setCompensateQuantity(
          dbManager.getDouble(resultSet, "CompensateQuantity"));
      lwTownIndicatorDto.setAppendCalQuantity(dbManager.getLong(resultSet, "AppendCalQuantity"));
      lwTownIndicatorDto.setTranferLossQuantity(
          dbManager.getLong(resultSet, "TranferLossQuantity"));
      lwTownIndicatorDto.setPeoplePrice(dbManager.getDouble(resultSet, "PeoplePrice"));
      lwTownIndicatorDto.setNotPeoplePrice(dbManager.getDouble(resultSet, "NotPeoplePrice"));
      lwTownIndicatorDto.setFarmPrice(dbManager.getDouble(resultSet, "FarmPrice"));
      lwTownIndicatorDto.setProducePrice(dbManager.getDouble(resultSet, "ProducePrice"));
      lwTownIndicatorDto.setBusinessPrice(dbManager.getDouble(resultSet, "BusinessPrice"));
      lwTownIndicatorDto.setVoltlevel(dbManager.getInt(resultSet, "Voltlevel"));
      lwTownIndicatorDto.setIndustryPrice(dbManager.getDouble(resultSet, "IndustryPrice"));
      lwTownIndicatorDto.setValidStatus(dbManager.getString(resultSet, "ValidStatus"));
      lwTownIndicatorDto.setFlag(dbManager.getString(resultSet, "Flag"));
      lwTownIndicatorDto.setRemark(dbManager.getString(resultSet, "Remark"));
      lwTownIndicatorDto.setInputDate(dbManager.getString(resultSet, "InputDate"));
      collection.add(lwTownIndicatorDto);
    }
    resultSet.close();
    return collection;
  }
  /**
   * 按主键查找一条数据
   *
   * @param userNo 户号
   * @param statMonth 账期
   * @return LwTownIndicatorDto
   * @throws Exception
   */
  public LwTownIndicatorDto findByPrimaryKey(String userNo, String statMonth) throws Exception {
    StringBuffer buffer = new StringBuffer(200);
    // 拼SQL语句
    buffer.append("SELECT ");
    buffer.append("UserNo,");
    buffer.append("UserName,");
    buffer.append("Address,");
    buffer.append("ReadDate,");
    buffer.append("StatMonth,");
    buffer.append("ThisWorkNum,");
    buffer.append("MidWorkNum,");
    buffer.append("LastWorkNum,");
    buffer.append("Rate,");
    buffer.append("ReadQuantity,");
    buffer.append("ExcepQuantity,");
    buffer.append("ChgAmmeterQuantity,");
    buffer.append("CompensateQuantity,");
    buffer.append("AppendCalQuantity,");
    buffer.append("TranferLossQuantity,");
    buffer.append("PeoplePrice,");
    buffer.append("NotPeoplePrice,");
    buffer.append("FarmPrice,");
    buffer.append("ProducePrice,");
    buffer.append("BusinessPrice,");
    buffer.append("Voltlevel,");
    buffer.append("IndustryPrice,");
    buffer.append("ValidStatus,");
    buffer.append("Flag,");
    buffer.append("Remark,");
    buffer.append("InputDate ");
    buffer.append("FROM LwTownIndicator ");
    if (logger.isDebugEnabled()) {
      StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
      debugBuffer.append(buffer.toString());
      debugBuffer.append("WHERE ");
      debugBuffer.append("UserNo=").append("'").append(userNo).append("' AND ");
      debugBuffer.append("StatMonth=").append("'").append(statMonth).append("'");
      logger.debug(debugBuffer.toString());
    }

    buffer.append("WHERE ");
    buffer.append("UserNo = ? And ");
    buffer.append("StatMonth = ?");

    dbManager.prepareStatement(buffer.toString());
    // 设置条件字段;
    dbManager.setString(1, userNo);
    dbManager.setString(2, statMonth);
    ResultSet resultSet = dbManager.executePreparedQuery();
    LwTownIndicatorDto lwTownIndicatorDto = null;
    if (resultSet.next()) {
      lwTownIndicatorDto = new LwTownIndicatorDto();
      lwTownIndicatorDto.setUserNo(dbManager.getString(resultSet, 1));
      lwTownIndicatorDto.setUserName(dbManager.getString(resultSet, 2));
      lwTownIndicatorDto.setAddress(dbManager.getString(resultSet, 3));
      lwTownIndicatorDto.setReadDate(dbManager.getString(resultSet, 4));
      lwTownIndicatorDto.setStatMonth(dbManager.getString(resultSet, 5));
      lwTownIndicatorDto.setThisWorkNum(dbManager.getDouble(resultSet, 6));
      lwTownIndicatorDto.setMidWorkNum(dbManager.getDouble(resultSet, 7));
      lwTownIndicatorDto.setLastWorkNum(dbManager.getDouble(resultSet, 8));
      lwTownIndicatorDto.setRate(dbManager.getDouble(resultSet, 9));
      lwTownIndicatorDto.setReadQuantity(dbManager.getDouble(resultSet, 10));
      lwTownIndicatorDto.setExcepQuantity(dbManager.getDouble(resultSet, 11));
      lwTownIndicatorDto.setChgAmmeterQuantity(dbManager.getDouble(resultSet, 12));
      lwTownIndicatorDto.setCompensateQuantity(dbManager.getDouble(resultSet, 13));
      lwTownIndicatorDto.setAppendCalQuantity(dbManager.getLong(resultSet, 14));
      lwTownIndicatorDto.setTranferLossQuantity(dbManager.getLong(resultSet, 15));
      lwTownIndicatorDto.setPeoplePrice(dbManager.getDouble(resultSet, 16));
      lwTownIndicatorDto.setNotPeoplePrice(dbManager.getDouble(resultSet, 17));
      lwTownIndicatorDto.setFarmPrice(dbManager.getDouble(resultSet, 18));
      lwTownIndicatorDto.setProducePrice(dbManager.getDouble(resultSet, 19));
      lwTownIndicatorDto.setBusinessPrice(dbManager.getDouble(resultSet, 20));
      lwTownIndicatorDto.setVoltlevel(dbManager.getInt(resultSet, 21));
      lwTownIndicatorDto.setIndustryPrice(dbManager.getDouble(resultSet, 22));
      lwTownIndicatorDto.setValidStatus(dbManager.getString(resultSet, 23));
      lwTownIndicatorDto.setFlag(dbManager.getString(resultSet, 24));
      lwTownIndicatorDto.setRemark(dbManager.getString(resultSet, 25));
      lwTownIndicatorDto.setInputDate(dbManager.getString(resultSet, 26));
    }
    resultSet.close();
    return lwTownIndicatorDto;
  }