这是该方法,查询的思路是 获得符合条件(业务ASS.SERVICE_ID,店名S.NAME,S.MERGER_ID所在省,市,区id,S.ENABLED = 1店铺没有被删除,for循环的那一部分Criteria是city)的店铺有多少个
public int countMerchantByCriteriaOrderByFollowHavingPC(Integer userId,String shopName, String mergerId,String choice,Criteria... criterias){
StringBuilder jpql = new StringBuilder();
jpql.append(" SELECT COUNT(N) FROM ( ");
jpql.append(" SELECT S.ID,S.NAME,S.IMG_URL,S.ADDRESS ");
jpql.append(" FROM ATS_SHOP S ");
jpql.append(" RIGHT JOIN ATS_SHOP_SERVICE ASS ON S.ID = ASS.SHOP_ID ");
jpql.append(" WHERE 1 = 1 ");
if(StringUtils.isNotEmpty(shopName)) {
jpql.append(" AND S.NAME like :shopName ");
}
if(StringUtils.isNotEmpty(choice)&&choice.equals("5")){
jpql.append(" AND ASS.SERVICE_ID = 5 ");
}
if(StringUtils.isNotEmpty(choice)&&choice.equals("3")){
jpql.append(" AND ASS.SERVICE_ID = 3 ");
}
if(StringUtils.isNotEmpty(choice)&&choice.equals("2")){
jpql.append(" AND ASS.SERVICE_ID = 2 ");
}
if(StringUtils.isNotEmpty(choice)&&choice.equals("1")){
jpql.append(" AND ASS.SERVICE_ID = 1 ");
}
if(StringUtils.isNotEmpty(choice)&&choice.equals("4")){
jpql.append(" AND ASS.SERVICE_ID = 4 ");
}
if(StringUtils.isNotEmpty(mergerId)){
jpql.append(" AND S.MERGER_ID = :mergerId ");
}
for(final Criteria criteria : criterias){
if(null!=criteria.getValue()){
jpql.append(" AND ").append(criteria.getFiledName()).append(" ");
jpql.append(criteria.getOperation()).append(" :");
if(null==criteria.getAliasName()){
jpql.append(criteria.getFiledName());
}else{
jpql.append(criteria.getAliasName());
}
}
}
//start modify sunxin 20160319 添加已删除的店铺不被查到
jpql.append(" AND S.ENABLED = 1 ");
//end modify
jpql.append(" GROUP BY S.ID ");
jpql.append(" ) N ");
TypedQuery<Long> query = em.createQuery(jpql.toString(), Long.class);
if(StringUtils.isNotEmpty(shopName)) {
query.setParameter("shopName", "%" + shopName + "%");
}
if(StringUtils.isNotEmpty(mergerId)){
query.setParameter("mergerId",mergerId);
}
for(final Criteria criteria : criterias){
if(null!=criteria.getValue()) {
if (null == criteria.getAliasName()) {
query.setParameter(criteria.getFiledName(), criteria.getValue());
} else {
query.setParameter(criteria.getAliasName(), criteria.getValue());
}
}
}
return query.getSingleResult().intValue();
}
-----------------该语句用mysql直接查----------------------
是可以正常查出来的,但是一运行就会报错,如下。
求指出错误TAT