Mybatis中Mapper.xml 文件使用注释遇到的坑

Mybatis中Mapper.xml文件使用注释遇到的坑

问题复现

今天在进行根据ProductName进行查询的时候，出现了报错，报错信息如下：

Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='ProductName', mode=IN, javaType=class java.lang.String, jdbcType=VARCHAR, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: 无效的列索引
    at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.4.4.jar:3.4.4]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.4.4.jar:3.4.4]
    at com.sun.proxy.$Proxy587.query(Unknown Source) ~[na:na]
    at com.github.pagehelper.util.ExecutorUtil.executeAutoCount(ExecutorUtil.java:138) ~[pagehelper-5.1.6.jar:na]
    at com.github.pagehelper.PageInterceptor.count(PageInterceptor.java:148) ~[pagehelper-5.1.6.jar:na]
    at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:97) ~[pagehelper-5.1.6.jar:na]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.4.4.jar:3.4.4]
    at com.sun.proxy.$Proxy587.query(Unknown Source) ~[na:na]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.4.jar:3.4.4]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_302]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_302]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_302]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_302]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.2.jar:1.3.2]
    ... 117 common frames omitted
Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: 无效的列索引
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:55) ~[mybatis-3.4.4.jar:3.4.4]
    at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87) ~[mybatis-3.4.4.jar:3.4.4]
    ... 142 common frames omitted

报错原因

经过问题排查发现，问题的根本原因是在$\textcolor{RedOrange}{mapper.xml} $文件中使用了--进行了SQL语句的注释。

<if test="productName != null and productName != ''">
   --   and a.product_name = #{productName,jdbcType=VARCHAR}
    and a.product_name = #{productName,jdbcType=VARCHAR}
</if>

问题修复

mapper.xml文件中正确的注释方式为 <!--existence of query content-->

<if test="productName != null and productName != ''">
   <!--  and a.product_name = #{productName,jdbcType=VARCHAR} -->
    and a.product_name = #{productName,jdbcType=VARCHAR}
</if>

以上为个人经验，希望能给大家一个参考帮助，也希望大家多多支持。