当前位置: 代码迷 >> 综合 >> Hibernate hql语句按时间段查询
  详细解决方案

Hibernate hql语句按时间段查询

热度:73   发布时间:2024-01-18 03:28:47.0

第一种:

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





  相关解决方案