/**
* 按条件查询多条数据
*
* @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;
}