Hihernate OBJECT对象转为VO对象实例

Hihernate OBJECT对象转为VO对象实例

public List<StatChannelBean> queryChannelList2(String startDate,String endDate,Integer productId,
			Integer channelId1,Integer channelId2,Integer channelId3){
		List<StatChannelBean> list = null;
		try {
			String result = "select tscc.id,tscc.tf timeframe,tscc.channelId,tscc.productId,sum(tscc.insNum) insNum,sum(tscc.actNum) actNum,sum(tscc.regNum) regNum,sum(tscc.logNum) loginNum,sum(tscc.restNum1) restNum1,sum(tscc.restNum3) restNum3,sum(tscc.restNum7) restNum7," +
					"tscc.status,tscc.channelId1,tscc.channelId2,tscc.channelId3,tscc.productName,tscc.channelName1,tscc.channelName2,tscc.channelName3,if(sum(tsc.chargeNum) is null,0,sum(tsc.chargeNum)) chargeNum,if(sum(tsc.chargeTime) is null,0,sum(tsc.chargeTime)) chargeTime,if(sum(tsc.chargeAmount) is null,0,sum(tsc.chargeAmount)) amount ";
			
			//渠道表信息拼接SQL
			StringBuffer channelSql = new StringBuffer();
			channelSql.append("select ts.id id,date_format(ts.timeframe,'%Y-%m-%d') tf,ts.channelId channelId,ts.productId productId,ts.channelId1 channelId1,ts.channelId2 channelId2,ts.channelId3 channelId3," +
					"p.name productName, c1.nickName channelName1, c2.nickName channelName2, c3.nickName channelName3," +
					"sum(ts.loginNum) logNum,sum(ts.insNum) insNum,sum(ts.actNum) actNum,sum(ts.regNum) regNum,sum(ts.restNum1) restNum1,sum(ts.restNum3) restNum3,sum(ts.restNum7) restNum7," +
					"ts.status status ");
			channelSql.append(" from t_stat_channel_case ts, t_channel c, t_channel c1, t_channel c2, t_channel c3, t_product p  ");
			channelSql.append(" where ts.channelId = c.channelId and ts.productId = p.id and ts.channelId1 = c1.channelId and ts.channelId2 = c2.channelId and ts.channelId3 = c3.channelId and ts.status=1 ");
			if(null!=productId&&!"".equals(productId)){
				channelSql.append(" and ts.productId=").append(productId);
			}
			if(null!=channelId1&&!"".equals(channelId1)){
				channelSql.append(" and ts.channelId1=").append(channelId1);
			}
			if(null!=channelId2&&!"".equals(channelId2)){
				channelSql.append(" and ts.channelId2=").append(channelId2);
			}
			if(null!=channelId3&&!"".equals(channelId3)){
				channelSql.append(" and ts.channelId3=").append(channelId3);
			}
			
			channelSql.append(" group by tf");
			
			//充值表信息拼接SQL
			StringBuffer chargeSql = new StringBuffer();
			chargeSql.append("select timeframe,channelId,sum(chargeNum) chargeNum,sum(chargeTime) chargeTime,sum(chargeAmount) chargeAmount " +
					"from t_stat_charge where 1=1");
			if(null!=productId&&!"".equals(productId)){
				chargeSql.append(" and productId=").append(productId);
			}
			if(null!=channelId1&&!"".equals(channelId1)){
				chargeSql.append(" and channelId1=").append(channelId1);
			}
			if(null!=channelId2&&!"".equals(channelId2)){
				chargeSql.append(" and channelId2=").append(channelId2);
			}
			if(null!=channelId3&&!"".equals(channelId3)){
				chargeSql.append(" and channelId3=").append(channelId3);
			}
			chargeSql.append(" group by timeframe");
			
			String sql = " from (" + channelSql.toString() + ") tscc left join (" + chargeSql.toString() + ") tsc on tscc.tf=tsc.timeframe where 1=1";
			if(null!=startDate&&!"".equals(startDate)){
				sql += " and tscc.tf >= '" + startDate + "'";
			}
			if(null!=endDate && !"".equals(endDate)){
				sql += " and tscc.tf <= '" + endDate + "'";
			}
			sql += " group by tscc.tf order by tscc.tf desc";
			
			System.out.println(result + sql);
			
			final String fSql = result + sql;
			list = this.getHibernateTemplate().execute(new HibernateCallback<List<StatChannelBean>>(){
				@Override
				public List<StatChannelBean> doInHibernate(Session session)
						throws HibernateException, SQLException {
					return session.createSQLQuery(fSql).addScalar("timeframe",Hibernate.STRING).addScalar("productName",Hibernate.STRING)
					.addScalar("channelName1",Hibernate.STRING).addScalar("channelName2",Hibernate.STRING).addScalar("channelName3",Hibernate.STRING)
					.addScalar("insNum", Hibernate.INTEGER).addScalar("actNum",Hibernate.INTEGER)
					.addScalar("regNum",Hibernate.INTEGER).addScalar("loginNum",Hibernate.INTEGER).addScalar("restNum1",Hibernate.INTEGER)
					.addScalar("restNum3",Hibernate.INTEGER).addScalar("restNum7",Hibernate.INTEGER).addScalar("chargeNum",Hibernate.INTEGER)
					.addScalar("chargeTime",Hibernate.INTEGER).addScalar("amount",Hibernate.INTEGER)
					.setResultTransformer(Transformers.aliasToBean(StatChannelBean.class)).list();
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException(e.getMessage(),e);
		}
		return list;
	}