SSH综合查询的两种方式

方法1:直接用ORACLE方言函数进行比较

//综合查询
	public List getOpeRecsByCon1(HttpServletRequest request){
		log.debug("finding OpeRecords instances by Condition");
		try {
			
			String factoryId = "";
			String spciltyId = "";
			String dutyId = "";
			String classId = "";
			factoryId = request.getParameter("factoryId");
			spciltyId = request.getParameter("spciltyId");
			dutyId = request.getParameter("dutyId");
			classId = request.getParameter("classId");
			
			StringBuffer queryString = new StringBuffer();
			queryString.append("from OpeRecords o where 1=1");
			
			//如果选择了运行单位和专业
			if((factoryId.length()!=0)&&(spciltyId.length()!=0)){
				queryString.append(" and o.modelid in (select a.modelid from Opemodel a where a.runfactory='"+factoryId+"' and a.specialtyid='"+spciltyId+"')");
			}
			
			//如果仅仅选择了运行单位
			if((factoryId.length()!=0)&&(spciltyId.length()==0)){
				queryString.append(" and o.modelid in (select a.modelid from Opemodel a where a.runfactory='"+factoryId+"')");
			}
			
			//如果仅仅选择了专业
			if((factoryId.length()==0)&&(spciltyId.length()!=0)){
				queryString.append(" and o.modelid in (select a.modelid from Opemodel a where a.specialtyid='"+spciltyId+"')");
			}
			
			//如果选择了班次
			if(dutyId.length()!=0){
				queryString.append(" and o.dutyid='"+dutyId+"'");
			}
			
			//如果选择了值别
			if(classId.length()!=0){
				queryString.append(" and o.classid='"+classId+"'");
			}
			
			String startTimeStr = request.getParameter("startTime");
			String endTimeStr = request.getParameter("endTime");
			
			if(startTimeStr!=null&&!startTimeStr.equals("")){
				queryString.append(" and o.gettime >to_date('"+startTimeStr+"','yyyy-MM-dd HH24:mi:ss')");
			}else if(endTimeStr!=null&&!endTimeStr.equals("")){
				queryString.append(" and o.gettime <to_date('"+endTimeStr+"','yyyy-MM-dd HH24:mi:ss')");
			}else if(startTimeStr!=null&&!startTimeStr.equals("")&&endTimeStr!=null&&!endTimeStr.equals("")){
				queryString.append(" and o.gettime >to_date('"+startTimeStr+"','yyyy-MM-dd HH24:mi:ss')");
				queryString.append(" and o.gettime <to_date('"+endTimeStr+"','yyyy-MM-dd HH24:mi:ss')");
			}
			return getHibernateTemplate().find(queryString.toString());
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	

方法2:使用占位符对对象进行比较

	
//综合查询
	public List getOpeRecsByCon2(HttpServletRequest request){
		log.debug("finding OpeRecords instances by Condition");
		try {
			
			String factoryId = "";
			String spciltyId = "";
			String dutyId = "";
			String classId = "";
			factoryId = request.getParameter("factoryId");
			spciltyId = request.getParameter("spciltyId");
			dutyId = request.getParameter("dutyId");
			classId = request.getParameter("classId");
			
			StringBuffer queryString = new StringBuffer();
			queryString.append("from OpeRecords o where 1=1");
			
			//如果选择了运行单位和专业
			if((factoryId.length()!=0)&&(spciltyId.length()!=0)){
				queryString.append(" and o.modelid in (select a.modelid from Opemodel a where a.runfactory='"+factoryId+"' and a.specialtyid='"+spciltyId+"')");
			}
			
			//如果仅仅选择了运行单位
			if((factoryId.length()!=0)&&(spciltyId.length()==0)){
				queryString.append(" and o.modelid in (select a.modelid from Opemodel a where a.runfactory='"+factoryId+"')");
			}
			
			//如果仅仅选择了专业
			if((factoryId.length()==0)&&(spciltyId.length()!=0)){
				queryString.append(" and o.modelid in (select a.modelid from Opemodel a where a.specialtyid='"+spciltyId+"')");
			}
			
			//如果选择了班次
			if(dutyId.length()!=0){
				queryString.append(" and o.dutyid='"+dutyId+"'");
			}
			
			//如果选择了值别
			if(classId.length()!=0){
				queryString.append(" and o.classid='"+classId+"'");
			}
			
			String startTimeStr = request.getParameter("startTime");
			String endTimeStr = request.getParameter("endTime");
			
			if((startTimeStr!=null&&!startTimeStr.equals(""))&&(endTimeStr!=null&&endTimeStr.equals(""))){
				Date startTime = StringToDate.StrToDate4yyyyMMddHHmmss(startTimeStr);
				queryString.append(" and o.gettime >?)");
				return getHibernateTemplate().find(queryString.toString(),new Object[]{startTime});
			}else if(endTimeStr!=null&&!endTimeStr.equals("")&&(startTimeStr!=null&&startTimeStr.equals(""))){
				Date endTime = StringToDate.StrToDate4yyyyMMddHHmmss(endTimeStr);
				queryString.append(" and o.gettime <?");
				return getHibernateTemplate().find(queryString.toString(),new Object[]{endTime});
			}else{
				Date startTime = StringToDate.StrToDate4yyyyMMddHHmmss(startTimeStr);
				Date endTime = StringToDate.StrToDate4yyyyMMddHHmmss(endTimeStr);
				queryString.append(" and o.gettime between ? and ?");
				return getHibernateTemplate().find(queryString.toString(),new Object[]{startTime,endTime});
			}
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}

注:带注释符的是一种方法(使用占位符对对象进行比较),不带注释符是直接用ORACLE方言函数进行比较