com.alibaba.druid.sql.parser.ParserException: ERROR. pos 644, line 7, column 30, token AND

最新推荐文章于 2023-11-17 11:20:47 发布

泗水六年

最新推荐文章于 2023-11-17 11:20:47 发布

阅读量1.9k

点赞数

分类专栏： java 文章标签： sql mybatis java

本文链接：https://blog.youkuaiyun.com/Jackey1314007/article/details/127818542

版权

java 专栏收录该内容

65 篇文章

订阅专栏

错误：

[iwater-wk:192.168.0.xx:4003] 2022-11-07 17:14:00.923 ERROR 27564 [] [http-nio-4003-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql : 
 SELECT COUNT(1) FROM (select
        c.id,c.transfer_code,b.resident_code,b.resident_addr,b.resident_name,b.id as residentId,c.meter_code,c.install_position,c.collector_id,c.concentrator_id,c.area_id,a.watermeter_id,c.is_manual,c.valve_id,c.meter_model_id,dmm.is_switch,c.module_code,dv.valve_code
        from data_resident_meter a INNER JOIN data_resident b
        on a.resident_id=b.id inner JOIN device_watermeter c
        on a.watermeter_id=c.id INNER JOIN data_meter_model dmm ON c.meter_model_id = dmm.id
        LEFT  JOIN device_valve dv ON c.valve_id=dv.id
         WHERE  c.area_id in
                
            

            and c.company_id=?
            and c.is_deleted=0) TOTAL 

### The error may exist in file [D:\demo\iwater-workbench\target\classes\mapper\ResidentMeterMapper.xml]
### The error may involve com.iwater.workbench.data.mapper.ResidentMeterMapper.findResidentMeters
### The error occurred while executing a query
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql : 
 SELECT COUNT(1) FROM (select
        c.id,c.transfer_code,b.resident_code,b.resident_addr,b.resident_name,b.id as residentId,c.meter_code,c.install_position,c.collector_id,c.concentrator_id,c.area_id,a.watermeter_id,c.is_manual,c.valve_id,c.meter_model_id,dmm.is_switch,c.module_code,dv.valve_code
        from data_resident_meter a INNER JOIN data_resident b
        on a.resident_id=b.id inner JOIN device_watermeter c
        on a.watermeter_id=c.id INNER JOIN data_meter_model dmm ON c.meter_model_id = dmm.id
        LEFT  JOIN device_valve dv ON c.valve_id=dv.id
         WHERE  c.area_id in
                
            

            and c.company_id=?
            and c.is_deleted=0) TOTAL 
] with root cause

com.alibaba.druid.sql.parser.ParserException: ERROR. pos 644, line 7, column 30, token AND
    at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:882)
    at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primary(MySqlExprParser.java:198)
    at com.alibaba.druid.sql.parser.SQLExprParser.inRest(SQLExprParser.java:1966)
    at com.alibaba.druid.sql.parser.SQLExprParser.exprRest(SQLExprParser.java:180)
    at com.alibaba.druid.sql.parser.SQLSelectParser.parseWhere(SQLSelectParser.java:667)
    at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:195)
    at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:362)
    at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:61)
    at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseTableSource(MySqlSelectParser.java:246)
    at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseFrom(MySqlSelectParser.java:89)
    at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:193)
    at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:362)
    at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:61)
    at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:285)
    at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:248)
    at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:182)
    at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:624)
    at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:578)
    at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:792)
    at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:258)
    at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
    at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341)
    at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:350)
    at sun.reflect.GeneratedMethodAccessor290.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
    at com.sun.proxy.$Proxy366.prepareStatement(Unknown Source)
    at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:266)
    at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:206)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    at com.sun.proxy.$Proxy365.prepare(Unknown Source)
    at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:94)
    at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:68)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at sun.reflect.GeneratedMethodAccessor474.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
    at com.sun.proxy.$Proxy177.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:173)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
    at com.sun.proxy.$Proxy184.findResidentMeters(Unknown Source)
    at com.iwater.workbench.meter.service.impl.MeterBaseServiceImpl.getMeterReadVoPageResult(MeterBaseServiceImpl.java:995)
    at com.iwater.workbench.meter.service.impl.MeterBaseServiceImpl.findMeterRead(MeterBaseServiceImpl.java:787)
    at com.iwater.workbench.meter.service.impl.MeterBaseServiceImpl$$FastClassBySpringCGLIB$$e30bb729.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
    at com.iwater.workbench.meter.service.impl.MeterBaseServiceImpl$$EnhancerBySpringCGLIB$$be5d7302.findMeterRead(<generated>)
    at com.iwater.workbench.meter.controller.MeterBaseController.meterReading(MeterBaseController.java:466)

原因：

sql语句中in条件中赋值字段areaIdByUserId为空造成。

<if test="p.areaIdByUserId!=null and p.areaIdByUserId!=''">
    and c.area_id in
    <foreach collection="p.areaIdByUserId" item="areaId" open="(" close=")" separator=",">
        #{areaId}
    </foreach>
</if>

解决：

<if test="p.areaIdByUserId!=null and p.areaIdByUserId!='' and p.areaIdByUserId.size()>0">
    and c.area_id in
    <foreach collection="p.areaIdByUserId" item="areaId" open="(" close=")" separator=",">
        #{areaId}
    </foreach>
</if>