/** * 根据查询条件分页查询出符合条件的商品订单信息 * * @author 左香勇 2014-9-9 * @update * @param conSumerOrderData 查询条件实体信息 pageRoll 分页查询对象 * @return List<ConsumerOrderGoodsinfoData> 符合查询条件商品订单信息集合 * @since vmaque 1.5 */ public List<ConsumerOrderGoodsinfoData> getConSumerOrderGoodsInfoDataList( PageRoll pageRoll, ConSumerOrderData conSumerOrderData) { // 定义查询语句 StringBuffer querySql = new StringBuffer("SELECT "); querySql.append("MCG.ID AS ID,MCG.GOODSNUM AS GOODSNUM,"); querySql.append( "MCO.ID AS CONSUMERORDERID,MCO.PRICE AS PRICE,MCO.STATE AS STATE,MCO.CREATETIME AS CREATETIME,MCO.LOGISTICSCOMPANY AS LOGISTICSCOMPANY,MCO.LOGISTICSNUMBER AS LOGISTICSNUMBER,MCO.DELIVERTIME AS DELIVERTIME,"); querySql.append("MCC.CONFIGNAME AS CONFIGNAME,"); querySql.append("MGI.GOODSNAME AS GOODSNAME,"); querySql.append( "MCU.USERNAME AS USERNAME,MCU.USERADDRESS AS USERADDRESS,MCU.USERMOBILE AS USERMOBILE "); querySql.append("FROM MINI_CONSUMERORDER_GOODSINFO MCG "); querySql.append("JOIN MINI_CONSUMERORDER MCO ON MCG.CONSUMERORDERID = MCO.ID "); querySql.append("JOIN MINI_COMMODITYCONFIG MCC ON MCG.COMMODITYCONFIGID = MCC.ID "); querySql.append("JOIN MINI_GOODSINFO MGI ON MCG.GOODSINFID = MGI.ID "); querySql.append("JOIN MINI_CONSUMERUSER MCU ON MCO.CONSUMERUSERID = MCU.ID "); querySql.append("JOIN MINI_PAGE_GOODSINFO MPG ON MPG.PAGEID = MCO.PAGEID "); querySql.append( "WHERE MCG.ISDELETE = 1 AND MCO.ISDELETE = 1 AND MCC.ISDELETE = 1 AND MGI.ISDELETE = 1 AND MCU.ISDELETE = 1 AND MCO.STATE <> 4 "); // 定义查询条件 StringBuffer where = new StringBuffer(""); List<Object> objects = new ArrayList<Object>(); // 添加查询条件 if (conSumerOrderData != null) { if (conSumerOrderData.getState() != null) { where.append(" AND MCO.STATE=?"); objects.add(conSumerOrderData.getState()); } if (conSumerOrderData.getPageId() != null && !conSumerOrderData.getPageId().equals("")) { where.append("AND MCO.PAGEID=? "); objects.add(conSumerOrderData.getPageId()); } querySql.append(where.toString()); } if (conSumerOrderData.getState() != null) { querySql.append("ORDER BY MCO.CREATETIME,MCO.PAYTYPE DESC"); } else { querySql.append("ORDER BY MCO.CREATETIME DESC"); } // 分页查询 pageRoll.setCountSQL("SELECT COUNT(1) FROM (" + querySql.toString() + ")"); pageRoll.setSearchSQL(querySql.toString()); JdbcDao jdbcDao = (JdbcDao) SpringContextHelper.getBean("jdbc"); // 获取jdbc List<ETIPResultSet> rsList = jdbcDao.search(pageRoll, objects); List<ConsumerOrderGoodsinfoData> consumerOrderGoodsinfoDatas = new ArrayList<ConsumerOrderGoodsinfoData>(); for (int i = 0; i < rsList.size(); i++) { ConsumerOrderGoodsinfoData consumerOrderGoodsinfoData = new ConsumerOrderGoodsinfoData(); ConSumerOrderData sumerOrderData = new ConSumerOrderData(); GoodsInfoData goodsInfoData = new GoodsInfoData(); CommodityConfigData commodityConfigData = new CommodityConfigData(); ConSumerUserData conSumerUserData = new ConSumerUserData(); conSumerUserData.setUserName(rsList.get(i).getString("USERNAME")); conSumerUserData.setUserAddress(rsList.get(i).getString("USERADDRESS")); conSumerUserData.setUserMobile(rsList.get(i).getString("USERMOBILE")); sumerOrderData.setConSumerUserData(conSumerUserData); sumerOrderData.setId(rsList.get(i).getString("CONSUMERORDERID")); sumerOrderData.setCreateTime(rsList.get(i).getDate("CREATETIME")); sumerOrderData.setPrice(rsList.get(i).getDouble("PRICE")); sumerOrderData.setState(rsList.get(i).getInt("STATE")); sumerOrderData.setLogisticsCompany(rsList.get(i).getString("LOGISTICSCOMPANY")); sumerOrderData.setLogisticsNumber(rsList.get(i).getString("LOGISTICSNUMBER")); sumerOrderData.setDeliverTime(rsList.get(i).getDate("DELIVERTIME")); commodityConfigData.setConfigName(rsList.get(i).getString("CONFIGNAME")); goodsInfoData.setGoodsName(rsList.get(i).getString("GOODSNAME")); consumerOrderGoodsinfoData.setCommodityConfigData(commodityConfigData); consumerOrderGoodsinfoData.setGoodsInfoData(goodsInfoData); consumerOrderGoodsinfoData.setConSumerOrderData(sumerOrderData); consumerOrderGoodsinfoData.setGoodsNum(rsList.get(i).getInt("GOODSNUM")); consumerOrderGoodsinfoData.setId(rsList.get(i).getString("ID")); consumerOrderGoodsinfoDatas.add(consumerOrderGoodsinfoData); } return consumerOrderGoodsinfoDatas; }