Hql语句查询

返回一个值 如最大值，最小值

 

	/**
	 * 获取最早激活时间
	 */
	public String getStartDate() {
		// TODO Auto-generated method stub
		
		Session session=this.getSessionFactory().openSession();

		String hql="select min(activ_time) from  r_device_active " ;
		String list=new String();
		Object results=session.createSQLQuery(hql).uniqueResult();
		
		this.closeSession(session);
		//没有记录
		if(results == null || "".equals(results)){
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			return sdf.format(new Date());
 
		}
		return results.toString();
	}

查询一个字段 ，返回一个list

/**
	 * 获取某条件下的激活时间
	 */
	public List<String> getActiveDate(String tradeognm,String custnm,String productdutyempnm,String productnm,String startDate,String endsDate ) {
		// TODO Auto-generated method stub
		Session session=this.getSessionFactory().openSession();
		String Hhql="";
		if(startDate != null && !"".equals(startDate) && !"null".equals(startDate)){
			Hhql += " and c.activ_time >='" + startDate + "'";
		}
		
		if(Hhql != null && !"".equals(endsDate) && !"null".equals(endsDate)){
				Hhql += " and c.activ_time <='" + endsDate + "'";
		}
		String hql=" select c.activ_time  from r_patternoutlist a" +
		" join r_product_info b on a.barcode=b.serial_no" +
		" join r_device_active c on c.mac=b.mac  where a .tradeognm='"+tradeognm +"'and a.custnm='"+custnm+"' and a.productdutyempnm ='"+productdutyempnm+"'and a.productnm ='"+productnm+"'"+Hhql;
		
		
		
		List<String> list=new ArrayList<String>();
		//results 定义一个list Object
		List<Object> results=session.createSQLQuery(hql).list();
		try{

			for(Object obj:results){
				int i=0;
				list.add(SimpleTools.CNull(obj).toString());
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		this.closeSession(session);
		return list;
	}




查询多个字段 ，返回一个list
   

    /**
     * 获取工贸-门店-产品代表-型号
     */
	public List<ManchineOpenedJson> getManchineOpenedJsonList(Page reportPage,HashMap<String, Object> param) {
		// TODO Auto-generated method stub
		Session session=this.getSessionFactory().openSession();
		
		
		//工贸
		String trade = (String)param.get("trade");
		//门店
		String store = (String)param.get("store");
		//产品代表
		String productDuty = (String)param.get("productDuty");
		//产品型号
		String product = (String)param.get("product");
		
		//开始时间
		String startDate = (String)param.get("startDate");
		//结束时间
		String endDate = (String)param.get("endDate");
		
		String queryHql = "";
		//查询条件
		if(trade != null && !"".equals(trade) && !"null".equals(trade)){
			queryHql += "and tradeognm='" + trade + "'";
		}
		
		if(store != null && !"".equals(store) && !"null".equals(store)){
			 
				queryHql += " and custnm='" + store + "'";
		 
		}
		if(productDuty != null && !"".equals(productDuty) && !"null".equals(productDuty)){
			 
				queryHql += " and productdutyempnm='" + productDuty + "'";
			 
		}
		if(product != null && !"".equals(product) && !"null".equals(product)){
			 
				queryHql+= " and productnm='" + product + "'";
			 
		}
		
		if(startDate != null && !"".equals(startDate) && !"null".equals(startDate)){
			queryHql += " and c.activ_time >='" + startDate + "'";
		}
		
		if(endDate != null && !"".equals(endDate) && !"null".equals(endDate)){
				queryHql += " and c.activ_time <='" + endDate + "'";
		}
		
		String hql="select tradeognm, custnm,productdutyempnm, productnm,count(barcode) " +
				"from r_patternoutlist a " +
				" join r_product_info b on a.barcode=b.serial_no "+
				"join r_device_active c on c.mac=b.mac "+
				"where 1=1 " +queryHql+
				" group by(tradeognm, custnm,productdutyempnm, productnm) order by tradeognm asc";
		List<ManchineOpenedJson> list=new ArrayList<ManchineOpenedJson>();
		//results 定义一个list Object[]
		List<Object[]> results=session.createSQLQuery(hql).list();
		
		
		//设置总记录数
		reportPage.setRecordCount(results.size());
		//需要分页
		if(reportPage.isIfPage()){
			int start = (reportPage.getCurrentPage() - 1) * reportPage.getPageSize();
			int end   = (reportPage.getCurrentPage()) * reportPage.getPageSize(); 
			if(end > results.size()){
				end = results.size();
			}
			try{
				results = results.subList(start, end);
			}catch(Exception e){
				
			}
		}

		ManchineOpenedJson m;
		
		for(Object[] obj:results){
			m=new ManchineOpenedJson();
			int i=0;
			//将查询字段 放到javaBean中
			m.setTrade(obj[i].toString());
			m.setStore(SimpleTools.CNull(obj[++i]));
			m.setProductDuty(SimpleTools.CNull(obj[++i]));
			m.setProduct(SimpleTools.CNull(obj[++i]));
			m.setActiveSum(SimpleTools.CNull(obj[++i]));
			list.add(m);
		}

		this.closeSession(session);
		return list;
	}