mybatis foreach的一个bug

LittleLeeZi 2014-08-14 03:35:48


如上图,在传入的list中有数据的情况时,没有错误,数据也查出了;
但如果list中有0个数据的话,最终生成的sql是in(),报错了

org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
and ad.ads_is_verify=2
order by rand() limit 0,3' at line 7
...全文
2196 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhumengdetianshi 2015-06-26
  • 打赏
  • 举报
回复
感谢,之前忘了回复了,好人一生平安~
zhumengdetianshi 2014-12-02
  • 打赏
  • 举报
回复
引用 9 楼 shixitong 的回复:
[quote=引用 7 楼 lkl713 的回复:] 解决了,没想到动态sql支持这种list.size()的写法 但是我还是不明白我在5L说的问题,为啥百度上都没有类似的问题,网上的教程也都没有加这样的判断
网上很多都是他抄你,你抄他,不一定准确,还有别人当时的情景和你遇到的也不一定一样,人家那个list也许肯定有值呢,对吧? 最好先看下官方的API FAQ这些,如果没有找到,再到网上搜,不过现在的百度,搜到的东西比google差多了[/quote] 楼主,我参考你的 @Param("pIds") 注入参数的方法,哎呀,没想到一直报错都没能解决,请问你是样写的捏?网上其实都没有怎么介绍这个东西,找不到资料呀 代码如下 public void updateDelFlagOfcrMaterialByPIds(@Param("pIds")List<String> pIds); <delete id="updateDelFlagOfcrMaterialByPIds"> DELETE FROM gg_coupon_report_project <where> projectID IN <foreach item="item" index="index" collection="pIds" open="(" separator="," close=")">#{item}</foreach> </where> </delete> 11:42:24,725 DEBUG JakartaCommonsLoggingImpl : ==> Executing: SELECT * FROM gg_project_merge WHERE 1=1 AND mergeToID=? 11:42:24,726 DEBUG JakartaCommonsLoggingImpl : ==> Parameters: 93(Integer) 11:42:24,736 DEBUG JakartaCommonsLoggingImpl : ==> Executing: UPDATE gg_project SET delFlag=1 11:42:24,737 DEBUG JakartaCommonsLoggingImpl : ==> Parameters: 11:42:25,374 DEBUG JakartaCommonsLoggingImpl : ==> Executing: UPDATE gg_material_ivtrecord SET delFlag=1 11:42:25,375 DEBUG JakartaCommonsLoggingImpl : ==> Parameters: 11:42:27,337 DEBUG JakartaCommonsLoggingImpl : ==> Executing: UPDATE gg_material_manage_checkout SET delFlag=1 11:42:27,338 DEBUG JakartaCommonsLoggingImpl : ==> Parameters: 11:42:36,428 DEBUG CommonsLogger : Forwarding to location /error.jsp 11:42:36,430 DEBUG CommonsLogger : Entering nullPropertyValue [target=[com.opensymphony.xwork2.interceptor.ExceptionHolder@11f523a3, custom.project.action.ProjectAction@41964274, com.opensymphony.xwork2.DefaultTextProvider@59c87031], property=org] org.apache.ibatis.exceptions.IbatisException: ### Error updating database. Cause: org.apache.ibatis.mapping.SqlMapperException: The expression 'pIds' evaluated to a null value. ### The error may exist in custom/couponReport/dao/ICouponReportProjectDao.xml ### The error may involve custom.couponReport.dao.ICouponReportProjectDao.updateDelFlagOfcrMaterialByPIds ### The error occurred while executing an update ### Cause: org.apache.ibatis.mapping.SqlMapperException: The expression 'pIds' evaluated to a null value. at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:95) at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:104) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:55) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:18) at com.sun.proxy.$Proxy37.updateDelFlagOfcrMaterialByPIds(Unknown Source) at custom.couponReport.service.ICouponReportServiceImpl.deleteProjectCouponReportByPIds(ICouponReportServiceImpl.java:163) at custom.couponReport.service.ICouponReportServiceImpl.cleanProjectDataByPIds(ICouponReportServiceImpl.java:170) at custom.project.service.IProjectImpl.deleteProjectRelateDataByPId(IProjectImpl.java:579) at custom.project.service.IProjectImpl.clearProject(IProjectImpl.java:608) at custom.project.action.ProjectAction.delProject(ProjectAction.java:172) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440) at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242) at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235) at com.google.inject.struts2.GuiceObjectFactory$ProvidedInterceptor.intercept(GuiceObjectFactory.java:224) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236) at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87) at com.google.inject.struts2.GuiceObjectFactory$ProvidedInterceptor.intercept(GuiceObjectFactory.java:224) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236) at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249) at
LittleLeeZi 2014-12-02
  • 打赏
  • 举报
回复
引用 11 楼 zhumengdetianshi 的回复:
[quote=引用 9 楼 shixitong 的回复:]
[quote=引用 7 楼 lkl713 的回复:]
解决了,没想到动态sql支持这种list.size()的写法


但是我还是不明白我在5L说的问题,为啥百度上都没有类似的问题,网上的教程也都没有加这样的判断


网上很多都是他抄你,你抄他,不一定准确,还有别人当时的情景和你遇到的也不一定一样,人家那个list也许肯定有值呢,对吧?
最好先看下官方的API FAQ这些,如果没有找到,再到网上搜,不过现在的百度,搜到的东西比google差多了[/quote]

楼主,我参考你的 @Param("pIds") 注入参数的方法,哎呀,没想到一直报错都没能解决,请问你是样写的捏?网上其实都没有怎么介绍这个东西,找不到资料呀
代码如下
public void updateDelFlagOfcrMaterialByPIds(@Param("pIds")List<String> pIds);

<delete id="updateDelFlagOfcrMaterialByPIds">
DELETE FROM gg_coupon_report_project
<where>
projectID IN <foreach item="item" index="index" collection="pIds" open="(" separator="," close=")">#{item}</foreach>
</where>
</delete>

11:42:24,725 DEBUG JakartaCommonsLoggingImpl : ==> Executing: SELECT * FROM gg_project_merge WHERE 1=1 AND mergeToID=?
11:42:24,726 DEBUG JakartaCommonsLoggingImpl : ==> Parameters: 93(Integer)
11:42:24,736 DEBUG JakartaCommonsLoggingImpl : ==> Executing: UPDATE gg_project SET delFlag=1
11:42:24,737 DEBUG JakartaCommonsLoggingImpl : ==> Parameters:
11:42:25,374 DEBUG JakartaCommonsLoggingImpl : ==> Executing: UPDATE gg_material_ivtrecord SET delFlag=1
11:42:25,375 DEBUG JakartaCommonsLoggingImpl : ==> Parameters:
11:42:27,337 DEBUG JakartaCommonsLoggingImpl : ==> Executing: UPDATE gg_material_manage_checkout SET delFlag=1
11:42:27,338 DEBUG JakartaCommonsLoggingImpl : ==> Parameters:
11:42:36,428 DEBUG CommonsLogger : Forwarding to location /error.jsp
11:42:36,430 DEBUG CommonsLogger : Entering nullPropertyValue [target=[com.opensymphony.xwork2.interceptor.ExceptionHolder@11f523a3, custom.project.action.ProjectAction@41964274, com.opensymphony.xwork2.DefaultTextProvider@59c87031], property=org]
org.apache.ibatis.exceptions.IbatisException:
### Error updating database. Cause: org.apache.ibatis.mapping.SqlMapperException: The expression 'pIds' evaluated to a null value.
### The error may exist in custom/couponReport/dao/ICouponReportProjectDao.xml
### The error may involve custom.couponReport.dao.ICouponReportProjectDao.updateDelFlagOfcrMaterialByPIds
### The error occurred while executing an update
### Cause: org.apache.ibatis.mapping.SqlMapperException: The expression 'pIds' evaluated to a null value.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:95)
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:104)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:55)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:18)
at com.sun.proxy.$Proxy37.updateDelFlagOfcrMaterialByPIds(Unknown Source)
at custom.couponReport.service.ICouponReportServiceImpl.deleteProjectCouponReportByPIds(ICouponReportServiceImpl.java:163)
at custom.couponReport.service.ICouponReportServiceImpl.cleanProjectDataByPIds(ICouponReportServiceImpl.java:170)
at custom.project.service.IProjectImpl.deleteProjectRelateDataByPId(IProjectImpl.java:579)
at custom.project.service.IProjectImpl.clearProject(IProjectImpl.java:608)
at custom.project.action.ProjectAction.delProject(ProjectAction.java:172)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)
at com.google.inject.struts2.GuiceObjectFactory$ProvidedInterceptor.intercept(GuiceObjectFactory.java:224)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
at com.google.inject.struts2.GuiceObjectFactory$ProvidedInterceptor.intercept(GuiceObjectFactory.java:224)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
at [/quote]
你也加上对集合为null的判断试试,你的错误信息里好像是说集合为null了
浮云若水 2014-08-15
  • 打赏
  • 举报
回复
这应该不算是BUG吧 可以预知的东西 自己加条件判断 BUG是无法预知的东西 抛出一些找不着原因的异常
shixitong 2014-08-14
  • 打赏
  • 举报
回复
引用 7 楼 lkl713 的回复:
解决了,没想到动态sql支持这种list.size()的写法 但是我还是不明白我在5L说的问题,为啥百度上都没有类似的问题,网上的教程也都没有加这样的判断
网上很多都是他抄你,你抄他,不一定准确,还有别人当时的情景和你遇到的也不一定一样,人家那个list也许肯定有值呢,对吧? 最好先看下官方的API FAQ这些,如果没有找到,再到网上搜,不过现在的百度,搜到的东西比google差多了
yl784248831 2014-08-14
  • 打赏
  • 举报
回复
mybatis好像是 <if test="dealStatus != null and dealStatus !=‘’''> AND DEAL_STATUS =#{dealStatus} </if>
LittleLeeZi 2014-08-14
  • 打赏
  • 举报
回复
解决了,没想到动态sql支持这种list.size()的写法


但是我还是不明白我在5L说的问题,为啥百度上都没有类似的问题,网上的教程也都没有加这样的判断
shixitong 2014-08-14
  • 打赏
  • 举报
回复
LittleLeeZi 2014-08-14
  • 打赏
  • 举报
回复
我看网上的教程都没有做判断,难道他们就没遇见这个问题吗?我觉得还是我的配置有问题?
LittleLeeZi 2014-08-14
  • 打赏
  • 举报
回复
引用 2 楼 yl784248831 的回复:
可以先判断是否为空,我用的是ibaits ,<isNotEmpty property="communityId" prepend="AND"> gu.communityId=#communityId# </isNotEmpty>
有这个标签吗?写在xml里吗?哪个位置
LittleLeeZi 2014-08-14
  • 打赏
  • 举报
回复
引用 1 楼 shixitong 的回复:
这个时候要加条件判断了
判断集合长度吗?动态sql里支持list.size()==0的写法吗?
yl784248831 2014-08-14
  • 打赏
  • 举报
回复
可以先判断是否为空,我用的是ibaits ,<isNotEmpty property="communityId" prepend="AND"> gu.communityId=#communityId# </isNotEmpty>
shixitong 2014-08-14
  • 打赏
  • 举报
回复
这个时候要加条件判断了

81,122

社区成员

发帖
与我相关
我的任务
社区描述
Java Web 开发
社区管理员
  • Web 开发社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧