Hibernate hql语句按时间段查询

SQL查询优化与条件应用

最新推荐文章于 2018-09-15 10:10:38 发布

原创最新推荐文章于 2018-09-15 10:10:38 发布 · 2.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #hql

mysql 同时被 2 个专栏收录

23 篇文章

订阅专栏

Hibernate

3 篇文章

订阅专栏

第一种：

StringBuffer jpql = new StringBuffer();
			jpql.append("select new Company(c.id,c.name,c.businessLicence,c.legalPerson,c.categorys,c.businessUserId,c.addedTime,c.status,r.name)");
			jpql.append(" from Company c,Region r where c.regionId=r.id ");
		    String sql=SqlGenerateTool.generateWhere(company, "c");
		    jpql.append(sql);
		    if(company.getQueryParameters().getEndTime()!=null && !company.getQueryParameters().getEndTime().equals("")){
		    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String parmtime=null;
				try {
					parmtime=(sdf.format(new Date(sdf.parse(company.getQueryParameters().getEndTime()).getTime()+ (24 * 60 * 60 * 1000))));
				} catch (ParseException e) {
				}
				jpql.append(" and c.addedTime < str_to_date('").append(parmtime).append("','%Y-%m-%d')");
		    }

第二种：

StringBuffer jpql=new StringBuffer();
		jpql.append("select new ByBusinessesWanted(bw.id,bw.buyCode,bw.itemName,bw.intentionPrice,bw.createTime,bw.effectiveTime,bw.status)");
		jpql.append(" from ByBusinessesWanted bw");
		jpql.append(" where 1=1");
		jpql.append(" and bw.userId="+byBusinessesWanted.getUserId());
		if(byBusinessesWanted!=null){
			if(byBusinessesWanted.getBuyCode()!=null && !byBusinessesWanted.getBuyCode().equals("")){
				jpql.append(" and bw.buyCode='"+byBusinessesWanted.getBuyCode()+"'");
			}
			if(byBusinessesWanted.getStatus()!=null && !byBusinessesWanted.getStatus().equals("")){
				jpql.append(" and bw.status="+byBusinessesWanted.getStatus());
			}
			if(byBusinessesWanted.getItemName()!=null && !byBusinessesWanted.getItemName().equals("")){
				jpql.append(" and bw.itemName like '%").append(byBusinessesWanted.getItemName()).append("%'");
			}
			if(byBusinessesWanted.getQueryParameters()!=null){
				if(byBusinessesWanted.getQueryParameters().getStartTime()!=null&&!"".equals(byBusinessesWanted.getQueryParameters().getStartTime())){
					jpql.append(" and bw.createTime >=");
					jpql.append(" DATE_FORMAT('"+byBusinessesWanted.getQueryParameters().getStartTime()+" 00:00:00','%Y-%m-%d %H:%i:%s')");
				}
				if(byBusinessesWanted.getQueryParameters().getEndTime()!=null&&!"".equals(byBusinessesWanted.getQueryParameters().getEndTime())){
					jpql.append(" and bw.createTime <=");
					jpql.append(" DATE_FORMAT('"+byBusinessesWanted.getQueryParameters().getEndTime()+" 23:59:59','%Y-%m-%d %H:%i:%s')");
				}
			}
		}
		jpql.append(" order by bw.createTime desc");