解决MyBatis BindingException: Invalid bound statement (not found)问题

背景

有个同学在使用MyBatis过程中如果出现org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)，很大概率是mybatis中Dao接口与Mapper配置文件在做映射绑定时出现的问题，也就是说接口与xml要么是找不到要么是找到了却无法匹配。

Spring Boot采用的是 2.7.14版本，MyBatis版本为3.5.13，因为需要和前者进行集成，所以选择的starter版本如下：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.3.1</version>
    <exclusions>
        <exclusion>
            <groupId>org.springframework.boot</groupId>
            <artifactId>*</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

Spring Boot低版本的包排除后需要添加JPA和JDBC依赖，测试数据库选择本地安装PostgreSQL 13。

环境准备

为了排查BindingException: Invalid bound statement (not found)，分析了下小同学写的TvCharacterDao和TvSeriesDao两接口：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.micboy.dao.TvCharacterDao">
    <insert id="insert" parameterType="com.micboy.pojo.TvCharacter" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO tv_character(name, tv_series_id, photo)
        VALUES (#{name}, #{tvSeriesId}, #{photo})
    </insert>

    <update id="update" parameterType="com.micboy.pojo.TvCharacter">
        UPDATE tv_character
        SET name=#{name},
            tv_series_id=#{tvSeriesId},
            photo=#{photo}
        WHERE id = #{id}
    </update>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.micboy.dao.TvSeriesDao">
    <insert id="insert" parameterType="com.micboy.pojo.TvSeries" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO tv_series(name, season_count, origin_release)
        VALUES (#{name}, #{seasonCount}, #{originRelease})
    </insert>

    <update id="update" parameterType="com.micboy.pojo.TvSeries">
        UPDATE tv_series
        SET name=#{name},
            season_count=#{seasonCount},
            origin_release=#{originRelease}
        WHERE id = #{id}
    </update>
</mapper>

问题排查

需要注意的是，低版本的MyBatis此前是不检查namespace的，本文中依赖的版本较高，如果namespace配置错误程序在启动之前就会抛出异常：

org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. The XML location is 'com/micboy/dao/TvCharacterDao.xml'. Cause: org.apache.ibatis.builder.BuilderException: Wrong namespace. Expected 'com.micboy.dao.TvCharacterDao' but found 'com.micboys.dao.TvCharacterDao'.
    at org.apache.ibatis.builder.xml.XMLMapperBuilder.configurationElement(XMLMapperBuilder.java:128) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.builder.xml.XMLMapperBuilder.parse(XMLMapperBuilder.java:100) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.builder.annotation.MapperAnnotationBuilder.loadXmlResource(MapperAnnotationBuilder.java:180) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.builder.annotation.MapperAnnotationBuilder.parse(MapperAnnotationBuilder.java:118) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.binding.MapperRegistry.addMapper(MapperRegistry.java:72) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.session.Configuration.addMapper(Configuration.java:895) ~[mybatis-3.5.13.jar:3.5.13]
    at org.mybatis.spring.mapper.MapperFactoryBean.checkDaoConfig(MapperFactoryBean.java:80) ~[mybatis-spring-2.1.1.jar:2.1.1]
    at org.springframework.dao.support.DaoSupport.afterPropertiesSet(DaoSupport.java:44) ~[spring-tx-5.3.29.jar:5.3.29]

所以大概率的异常是xml无法找到导致匹配失败，检查了下小同学在resources创建的xml文件发现文件夹名称和Dao层Interface所在的包名相同，通过GET获取数据列表没有问题。

curl -H 'Content-Type:application/json' -X GET http://127.0.0.1:8080/tvseries

[
    {
        "id":101,
        "name":"West World",
        "seasonCount":10,
        "tvCharacters":null,
        "originRelease":"2016-10-02"
    },
    Object{...},
    Object{...},
    Object{...},
    Object{...}
]

通过PUT修改数据则会出现Binding失败的提示：

curl -H 'Content-Type:application/json' -X PUT http://127.0.0.1:8080/tvseries/102 --data '{"id":102,"name":"Person of Interest","seasonCount":5,"tvCharacters":null,"originRelease":"2011-09-22"}'

{
    "timestamp":"2023-08-07",
    "status":500,
    "error":"Internal Server Error",
    "trace":"org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.micboy.dao.TvSeriesDao.update\n\tat org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>
    .......
    org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)\n\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)\n\tat java.base/java.lang.Thread.run(Thread.java:829)\n",
    "message":"Invalid bound statement (not found): com.micboy.dao.TvSeriesDao.update",
    "path":"/tvseries/102"
}

翻看代码确认没有问题，xml文件编写确认没有问题，重点放在了resource下文件夹的目录结构了，抱着试一试的态度重新创建和Dao接口相同的目录结构：

名字相同居然二者同时存在，相信明眼的你已经看出来了，小同学将xml文件所在的目录名称建的有点迷惑，如果没看出来也没关系我们从文件夹列表视图来看下：

正常的目录结构应该是按照层级关系来进行组织，但是小同学创建的文件夹只有一层，名字为“com.micboy.dao”，好家伙一步到位。问题确认后解决方法也就确认了。第一种解决方式是按照标准层级结构来组织，第二种解决方式是通过mybatis.mapper-locations参数来指定xml位置。为了验证该选项的有效性，删除刚才创建的正确的目录组织结构，通过参数指向小同学创建的xml位置：

mybatis.mapper-locations=classpath:/com.micboy.dao/*.xml

重启Spring Boot应用。

curl -H 'Content-Type:application/json' -X PUT http://127.0.0.1:8080/tvseries/102 --data '{"id":102,"name":"Person of Interest","seasonCount":5,"tvCharacters":null,"originRelease":"2011-09-22"}'

发送修改请求后，返回数据如下：

{
    "id":102,
    "name":"Person of Interest",
    "seasonCount":5,
    "tvCharacters":null,
    "originRelease":"2011-09-22"
}

可以看到update操作已经正常。当然，实际生产过程中如果使用PUT和DELETE需要做到充足的安全防范，本文举例是为了方便理解排查问题思路和常见错误分析。

转载请注明：麦童博客 » 解决MyBatis BindingException: Invalid bound statement (not found)问题