Hibernate hql语句按时间段查询

来源：互联网发布：农村淘宝店要多少钱编辑：程序博客网时间：2024/04/29 23:13

第一种：

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");

0 0