mysql - 带连接的计数查询中的 Sql 异常

Question

我在使用以下代码时遇到问题

public long findDataCount(EntityUiBean entityUIBean){

        long count = 0;
        StringBuffer sql = new StringBuffer();
        sql.append("select count(a.id) from")
                .append(" entity_listing a left outer join relation_master d on d.entity_id=a.id, entity_location b left outer join  "
                        + "city_master c on c.id=b.city_id ")
                .append(" with b.entity_id=a.id ");

        if (!entityUIBean.getMasterCompanyId().equals("0") || entityUIBean.getMasterCompanyId().equals("")) {
            sql.append("and d.other_entity_id=" + entityUIBean.getMasterCompanyId());
        }
        boolean b = true;
        if (entityUIBean.isDateStatus()) {
            if (b)
                sql.append(" and ");
            sql.append(" a.inserted_date between '").append(entityUIBean.getYearF()).append("-")
                    .append(entityUIBean.getMonthF() + 1).append("-").append(entityUIBean.getDayF()).append("' and '")
                    .append(entityUIBean.getYearT()).append("-").append(entityUIBean.getMonthT() + 1).append("-")
                    .append(entityUIBean.getDayT()).append(" 23:59:59'");
        }
        if (entityUIBean.getLocation() != null && !entityUIBean.getLocation().equals("")) {
            if (b)
                sql.append(" and ");
            sql.append("c.cityname like '%").append(entityUIBean.getLocation().trim()).append("%'");
            b = true;
        }
        if (entityUIBean.getEntityIdComma() != null && !entityUIBean.getEntityIdComma().equals("")) {
            if (b)
                sql.append(" and ");
            sql.append("a.id in(").append(entityUIBean.getEntityIdComma().trim()).append(")");
            b = true;
        }
        if (entityUIBean.getName() != null && !entityUIBean.getName().equals("")) {
            if (b)
                sql.append(" and ");
            sql.append("a.entity_name  like '%").append(entityUIBean.getName().trim()).append("%'");
            b = true;
        }
        if (entityUIBean.getMobile() != null && !entityUIBean.getMobile().equals("")) {
            if (b)
                sql.append(" and ");
            sql.append("(b.mobile_number1  like '%").append(entityUIBean.getMobile().trim())
                    .append("%' or b.mobile_number2  like '%").append(entityUIBean.getMobile()).append("%')");
            b = true;
        }
        if (entityUIBean.getEmail() != null && !entityUIBean.getEmail().equals("")) {
            if (b)
                sql.append(" and ");
            sql.append("b.email  like '%").append(entityUIBean.getEmail().trim()).append("%'");
            b = true;
        }
        if (entityUIBean.getStatus() > -1) {
            if (b)
                sql.append(" and ");
            sql.append("a.entity_status =").append(entityUIBean.getStatus());
            b = true;
        }
        if (entityUIBean.getEntityType() != null && !entityUIBean.getEntityType().equals("")) {
            if (b)
                sql.append(" and ");
            {
                if (entityUIBean.getEntityType().equals("corporate") || entityUIBean.getEntityType().equals("vendor"))
                    sql.append("a.entity_type ='").append(entityUIBean.getEntityType()).append("'");
                else
                    sql.append("a.entity_type !='corporate' and a.entity_type !='vendor'");
            }

            b = true;
        } else
            sql.append(" and a.entity_type !='corporate' and a.entity_type !='vendor'");
        MY_LOGGER.info("SQL Query is : "+sql.toString());

        try {
            count =(Long) getHibernateTemplate().find(sql.toString()).get(0);
        } catch (Exception re) {
            MY_LOGGER.error("SERVICE:AuLoginDao.checkLoginStatus Fail sql=" + sql, re, 1);
            throw re;
        }
        return count;
    }

堆栈跟踪是:

 type Exception report    
 message Request processing failed; nested exception is org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: on near line 1, column 76 [select count(a.id) from entity_listing a left outer join relation_master d on d.entity_id=a.id, entity_location b left outer join city_master c on c.id=b.city_id with b.entity_id=a.id and a.entity_type !='corporate' and a.entity_type !='vendor']; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 76 [select count(a.id) from entity_listing a left outer join relation_master d on d.entity_id=a.id, entity_location b left outer join city_master c on c.id=b.city_id with b.entity_id=a.id and a.entity_type !='corporate' and a.entity_type !='vendor']

    description The server encountered an internal error that prevented it from fulfilling this request.

    exception

    org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: on near line 1, column 76 [select count(a.id) from entity_listing a left outer join relation_master d on d.entity_id=a.id, entity_location b left outer join  city_master c on c.id=b.city_id  with b.entity_id=a.id  and a.entity_type !='corporate' and a.entity_type !='vendor']; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 76 [select count(a.id) from entity_listing a left outer join relation_master d on d.entity_id=a.id, entity_location b left outer join  city_master c on c.id=b.city_id  with b.entity_id=a.id  and a.entity_type !='corporate' and a.entity_type !='vendor']
        org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:980)
        org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:870)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
        org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:844)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
        org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        com.til.et.sme.listing.web.controller.CustomFilter.doFilter(CustomFilter.java:75)
    root cause

    org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: on near line 1, column 76 [select count(a.id) from entity_listing a left outer join relation_master d on d.entity_id=a.id, entity_location b left outer join  city_master c on c.id=b.city_id  with b.entity_id=a.id  and a.entity_type !='corporate' and a.entity_type !='vendor']; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 76 [select count(a.id) from entity_listing a left outer join relation_master d on d.entity_id=a.id, entity_location b left outer join  city_master c on c.id=b.city_id  with b.entity_id=a.id  and a.entity_type !='corporate' and a.entity_type !='vendor']
        org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:660)
        org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
        org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
        org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
        org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912)
        org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:904)
        com.til.et.sme.listing.db.dao.impl.EntityMasterDaoImpl.findDataCount(EntityMasterDaoImpl.java:338)
        sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
        org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
        com.til.et.sme.listing.core.utils.SimpleProfiler.profile(SimpleProfiler.java:30)
        sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:620)
        org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:609)
        org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
        com.sun.proxy.$Proxy101.findDataCount(Unknown Source)
        com.til.et.sme.listing.db.dao.impl.MasterDaoImpl.getCountForEntityQuery(MasterDaoImpl.java:405)
        sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
        org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
        com.til.et.sme.listing.core.utils.SimpleProfiler.profile(SimpleProfiler.java:30)
        sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:620)
        org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:609)
        org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
        com.sun.proxy.$Proxy111.getCountForEntityQuery(Unknown Source)
        com.til.et.sme.listing.web.controller.MasterController.entityManage(MasterController.java:340)
        sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:178)
        org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:444)
        org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:432)
        org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
        org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
        org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:968)
        org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:870)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
        org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:844)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
        org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        com.til.et.sme.listing.web.controller.CustomFilter.doFilter(CustomFilter.java:75)

我似乎不明白问题的原因。查询在 MySQL 中成功执行，但通过代码抛出异常。请帮我查询一下。

Answer 1

您使用 WITH 关键字代替 AND(我从您的堆栈跟踪中获取了查询)

SELECT Count(a.id)
FROM   entity_listing a
       LEFT OUTER JOIN relation_master d
                    ON d.entity_id = a.id,
       entity_location b
       LEFT OUTER JOIN city_master c
                    ON c.id = b.city_id
                       WITH --Here
                       b.entity_id = a.id
                       AND a.entity_type != 'corporate'
                       AND a.entity_type != 'vendor' 

不确定您想要实现什么，但 AND 是预期的(应该立即编译)

SELECT Count(a.id)
FROM   entity_listing a
       LEFT OUTER JOIN relation_master d
                    ON d.entity_id = a.id,
       entity_location b
       LEFT OUTER JOIN city_master c
                    ON c.id = b.city_id
                       AND b.entity_id = a.id
                       AND a.entity_type NOT IN ( 'corporate', 'vendor' ) 

注意:您可以使用NOT IN运算符代替多个!=条件

更新:

您已将显式 JOIN 语法与逗号分隔的旧式 Join 语法组合在一起，这可能是问题所在。试试这个

SELECT Count(a.id)
FROM   entity_listing a
       LEFT OUTER JOIN relation_master d
                    ON d.entity_id = a.id
       JOIN entity_location b
         ON b.entity_id = a.id
       LEFT OUTER JOIN city_master c
                    ON c.id = b.city_id
                       AND a.entity_type NOT IN ( 'corporate', 'vendor' )