首页 > 代码库 > sql:判断+sql+子查询+关联查询

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;   

}  

}

}

sql:判断+sql+子查询+关联查询