第一种:
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");