mybatis几个不容易觉察的错误-优快云博客

本文介绍了在使用Mybatis时可能会遇到的两种不易察觉的错误：一是SQL语句书写错误但未报错，导致查询异常，解决方法是仔细检查SQL语法；二是多个参数和数组参数混用时，出现Parameter找不到的问题，解决关键在于正确设置collection属性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

（1）SQL语句写错但是没有报错该语句

报错：

2019-07-12 14:41:24.248 [http-nio-7002-exec-1] DEBUG c.j.c.m.M.delMessageSendStatusBatch - ==>  Preparing: delete message_send where send_id in ( ? , ? ) 
2019-07-12 14:41:24.264 [http-nio-7002-exec-1] DEBUG c.j.c.m.M.delMessageSendStatusBatch - ==> Parameters: b48bb427-05c9-48b6-8bb7-d7d6c244c161(String), b48bb427-05c9-48b6-8bb7-d7d6c244c16d(String)
2019-07-12 14:41:24.311 [http-nio-7002-exec-1] ERROR o.a.c.c.C.[.[.[/console].[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [/console] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: 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 'where send_id in
         (  
            'b48bb427-05c9-48b6-8bb7-d7d6c244c161'' at line 1
### The error may exist in file [D:\JwtProjectHub\jwt-capability-platform\capability-manager\capability-project\target\classes\mybatis\service\MessageSendMapper.xml]
### The error may involve com.jwt.capability.mapper.MessageSendMapper.delMessageSendStatusBatch-Inline
### The error occurred while setting parameters
### SQL: delete message_send where send_id in          (               ?          ,              ?          )
### Cause: java.sql.SQLSyntaxErrorException: 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 'where send_id in
         (  
            'b48bb427-05c9-48b6-8bb7-d7d6c244c161'' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 'where send_id in
         (  
            'b48bb427-05c9-48b6-8bb7-d7d6c244c161'' at line 1] with root cause
java.sql.SQLSyntaxErrorException: 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 'where send_id in
         (  
            'b48bb427-05c9-48b6-8bb7-d7d6c244c161'' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy85.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)

根据报错，检查很久参数也没发现错误，不知道为啥无缘无故多了一个引号

mapper文件

    <delete id="delMessageSendStatusBatch" parameterType="String">
        delete from message_send where send_id in
        <foreach collection="array" index="index" item="sendIds" open="(" separator="," close=")">
            #{sendIds}
        </foreach>
    </delete>

经过将语句在数据库执行和请教，才发现是sql语句写错了

正确是

delete from message_send where send_id in()

下次遇到错误，先检查自己的sql 是否正确，避免浪费不必要的时间。谨记！

（2）Parameter 'Array' not found. Available parameters are [0, 1, 2, param3, param1, param2]

    <delete id="deleteMessageSendBatch" parameterType="String">
        delete from t_message_send where send_id in
        user_id=#{user_id} and
        <foreach collection="send_ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

当多个参数和数组参数一起传递时候，会这样报错！

上面是正确的写法

collection="send_ids" 不要写成collection="array"

当只有一个参数的时候，用collection="array"