sql:判断+sql+子查询+关联查询
孵化园页面：
public class MeimsServiceImpl implements MeimsService {
private ParkinfoDomain parkinfoDomain;
/** * 动态监测主控查询 * * @param parkName * 孵化园名称 * @param parkType * 孵化园类型 * @return List<Parkinfo> */
@Override
public List<ParkInfoVO> getParkinfoList(ParkInfoVO info, Pagination pagination) {
Session session = DaoHelp.getInstance().getSession();
String sql = null;
String isLost=info.getIslost2();
if ( isLost== null || "".equals(isLost)) {
sql = "select i.id,i.parkname,(select name from CODE_ALLREGICODE_fhy c where i.areacode =c.code) as areacode,(select mc from meims_dm_parktype d where i.parktype =d.dm) as parktype ,i.setupdate,i.websitelink,i.websitelogo,i.placetype,i.placearea,i.placeaddress,i.placecardpicture,"
+ "i.placepicture,i.managesystemflag,i.mentflag,i.managesystemlink,i.gsxtflag,i.entnum,i.smallentnum,i.aftersmallentnum,i.newsmallentnum,i.talkroomflag," + "i.talkroomarea,i.talkroompicture,i.recordroomflag,i.recordroomarea,i.recordroompicture,i.superviseopinion,i.supervisespotflag,i.superviseuser," + "i.supervisedate,i.superviseorgan,i.status,i.sjc,i.reportdate as applyDate,i.reportuser as applyPerson from meims_parkinfo i ";
} else if ("0".equals(isLost)){
sql = "select i.id,i.parkname,(select name from CODE_ALLREGICODE_fhy c where i.areacode =c.code) as areacode,(select mc from meims_dm_parktype d where i.parktype =d.dm) as parktype ,i.setupdate,i.websitelink,i.websitelogo,i.placetype,i.placearea,i.placeaddress,i.placecardpicture,"
+ "i.placepicture,i.managesystemflag,i.mentflag,i.managesystemlink,i.gsxtflag,i.entnum,i.smallentnum,i.aftersmallentnum,i.newsmallentnum,i.talkroomflag,"
+ "i.talkroomarea,i.talkroompicture,i.recordroomflag,i.recordroomarea,i.recordroompicture,i.superviseopinion,i.supervisespotflag,i.superviseuser,"
+ "i.supervisedate,i.superviseorgan,i.status,i.sjc,i.reportdate as applyDate,i.reportuser as applyPerson "
+ " from (SELECT * from MEIMS_PARKINFO where id not in"
+ "(select parkinfoId from " + " (SELECT MEIMS_ENTERPRISEINFO.* , a.LOSECONNFLAG , a.ACCESSDATE FROM MEIMS_ENTERPRISEINFO LEFT JOIN （select * from" + " (select t.*, row_number() over(partition by ENTERPRISEINFOID order by ACCESSDATE desc ,CREATETIME desc ) rn from MEIMS_ACCESSRECORD t ) where rn=1）a " + "ON MEIMS_ENTERPRISEINFO.ID=a.ENTERPRISEINFOID where MEIMS_ENTERPRISEINFO.STATUS='02' and MEIMS_ENTERPRISEINFO.VALID='Y'" + " ) where loseConnflag = 1 )) i ";
}else{
sql = "select i.id,i.parkname,(select name from CODE_ALLREGICODE_fhy c where i.areacode =c.code) as areacode,(select mc from meims_dm_parktype d where i.parktype =d.dm) as parktype ,i.setupdate,i.websitelink,i.websitelogo,i.placetype,i.placearea,i.placeaddress,i.placecardpicture," + "i.placepicture,i.managesystemflag,i.mentflag,i.managesystemlink,i.gsxtflag,i.entnum,i.smallentnum,i.aftersmallentnum,i.newsmallentnum,i.talkroomflag," + "i.talkroomarea,i.talkroompicture,i.recordroomflag,i.recordroomarea,i.recordroompicture,i.superviseopinion,i.supervisespotflag,i.superviseuser," + "i.supervisedate,i.superviseorgan,i.status,i.sjc,i.reportdate as applyDate,i.reportuser as applyPerson "
+ " from (SELECT * from MEIMS_PARKINFO where id in"
+ "(select parkinfoId from " + " (SELECT MEIMS_ENTERPRISEINFO.* , a.LOSECONNFLAG , a.ACCESSDATE FROM MEIMS_ENTERPRISEINFO LEFT JOIN （select * from" + " (select t.*, row_number() over(partition by ENTERPRISEINFOID order by ACCESSDATE desc ,CREATETIME desc ) rn from MEIMS_ACCESSRECORD t ) where rn=1）a " + "ON MEIMS_ENTERPRISEINFO.ID=a.ENTERPRISEINFOID where MEIMS_ENTERPRISEINFO.STATUS='02' and MEIMS_ENTERPRISEINFO.VALID='Y'" + " ) where loseConnflag = 1 )) i "; }
String whereSql = " where i.valid = 'Y' and i.status='02' and exists( select o.id from a_organ o where o.id=i.orgid start with o.id= " + info.getOrgid() + " connect by prior o.id=o.parent)"; List<Object> params = new ArrayList<Object>(); List<org.hibernate.type.Type> paramTypes = new ArrayList<org.hibernate.type.Type>(); if (info.getParkname() != null && !"".equals(info.getParkname())) { whereSql += " and i.parkname like ? "; params.add(info.getParkname()); paramTypes.add(Hibernate.STRING); } if (info.getParktype() != null && !"".equals(info.getParktype())) { whereSql += " and i.parkType = ? "; params.add(info.getParktype()); paramTypes.add(Hibernate.STRING); } if (info.getApplyDate() != null) { whereSql += " and i.reportdate >= ? "; whereSql += " and i.reportdate < ? "; params.add(info.getApplyDate()); paramTypes.add(Hibernate.TIMESTAMP); params.add(info.getEndDate()); paramTypes.add(Hibernate.TIMESTAMP); } if (info.getApplyPerson() != null && !"".equals(info.getApplyPerson())) { whereSql += " and i.reportuser like ? "; params.add(info.getApplyPerson()); paramTypes.add(Hibernate.STRING); }
String countsql = "select count(1) from ( "+ sql ;
whereSql += " order by i.sjc desc "; SQLQuery sqlQuery = session.createSQLQuery(sql + whereSql); sqlQuery.addScalar("id", Hibernate.INTEGER);
sqlQuery.addScalar("parkname", Hibernate.STRING);
sqlQuery.addScalar("parktype", Hibernate.STRING);
sqlQuery.addScalar("areacode", Hibernate.STRING);
sqlQuery.addScalar("setupdate", Hibernate.TIMESTAMP);
sqlQuery.addScalar("websitelink", Hibernate.STRING);
sqlQuery.addScalar("superviseopinion", Hibernate.STRING);
sqlQuery.addScalar("supervisespotflag", Hibernate.STRING);
sqlQuery.addScalar("superviseuser", Hibernate.STRING);
sqlQuery.addScalar("supervisedate", Hibernate.TIMESTAMP);
sqlQuery.addScalar("superviseorgan", Hibernate.STRING);
sqlQuery.addScalar("applyPerson", Hibernate.STRING);
sqlQuery.addScalar("applyDate", Hibernate.TIMESTAMP);
SQLQuery countQuery = session.createSQLQuery(countsql + whereSql+")");
BigDecimal count = (BigDecimal) countQuery.setParameters( params.toArray(), paramTypes.toArray(new Type[0])) .uniqueResult();
if (pagination != null) {
pagination .setTotalrecordCount(count == null ? 0 : count.intValue());
sqlQuery.setFirstResult(pagination.getFirstRecord());
sqlQuery.setMaxResults(pagination.getMaxRecord()); }
List<ParkInfoVO> list = sqlQuery .setResultTransformer(
Transformers.aliasToBean(ParkInfoVO.class)) .setParameters(params.toArray(),
paramTypes.toArray(new Type[0])).list();
if (list != null && !list.isEmpty()) {
return list;
}
return null;
}
/** * 得到所有的孵化园类型 * * @return List<String> */
public List<Object[]> getAllParkType() { Session session = DaoHelp.getInstance().getSession(); SQLQuery sqlquery = session .createSQLQuery("select dm, mc from meims_dm_parktype"); List<Object[]> objs = sqlquery.list(); return objs; }
/** * 录入或修改监管意见 * * @param info */ @Override
public void saveOrupadatesuperviseopinion(Parkinfo info) {
Session session = DaoHelp.getInstance().getSession();
session.saveOrUpdate(info);
}
/** * 根据id查询Parkinfo * * @param id * @return Parkinfo */ @Override
public Parkinfo getParkinfo(Integer id) { Session session = DaoHelp.getInstance().getSession(); return (Parkinfo) session.get(Parkinfo.class, id); }
/** * 根据id查询Serviceitem * * @param id * @return List<Serviceitem> */
public List<Serviceitem> getServiceItems(Integer parkId) {
Session session = DaoHelp.getInstance().getSession();
String hql = "from Serviceitem where parkinfoid =:parkId and status='02'";
Query query = session.createQuery(hql); query.setInteger("parkId", parkId);
List<Serviceitem> list = query.list();
if (list != null && !list.isEmpty()) {
return list;
} else {
return null;
}
}
}
