在使用DB2数据库开发时,写了一段SQL运行报错,但是百度上找了很多都没找到解决办法,mybatis:
报错如下:
### Cause: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0418] Use of parameter marker not valid.
; bad SQL grammar []; nested exception is com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0418] Use of parameter marker not valid.
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:95) ~[spring-jdbc-4.0.2.RELEASE.jar:4.0.2.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.0.2.RELEASE.jar:4.0.2.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.0.2.RELEASE.jar:4.0.2.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.2.2.jar:1.2.2]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) ~[mybatis-spring-1.2.2.jar:1.2.2]
at com.sun.proxy.$Proxy13.selectList(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198) ~[mybatis-spring-1.2.2.jar:1.2.2]
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:114) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43) ~[mybatis-3.2.5.jar:3.2.5]
在谷歌搜索后,总算在 Db2400 Use of parameter marker not valid 这篇文章中找到了答案,修改后SQL:
原因是:
The reason is that db2 cannot guess the parameter's type. The parameters should be cast to the columns parameter.
大致是说,传入的类型需要转成该列的参数类型,目前发现有 LIKE 语句会这个问题,加上CAST后就不报错了。