SpringBoot+Mybatis这个bug估计连作为神仙的您也无法解决--》Invalid bound statement (not found): ...-摩杜云开发者社区

最近开发一个调查单的应用系统，加班加点为了解决几个bug，但是最近两天卡在一个bug上。作为一头牛，不能轻易放弃，向困难挑战是牛的精神。

1、Invalid bound问题展示

首先，我针对题型QuestionType功能,写了五个子功能:增加题型，删除题型，修改题型，查询单条题型，模糊查询多条记录；还写了问题、调查卷、答案等功能，他们都有问题。

SpringBoot+Mybatis这个bug估计连作为神仙的您也无法解决--》Invalid bound statement (not found): ..._springboot

看问题提示：

Invalid bound statement (not found): com.edison.questionnaire.dao.QuestionTypeMapper.getQuestionTypeInfo, data=null), suppressedExceptions=[], cause=(this Map), stackTrace=[Ljava.lang.StackTraceElement;@3f6da723, detailMessage=null}

意思是，“题型”映射器无法找到对应的xml中描述的SQL语句。

下面我只把题型QuestionType相关代码都贴上来，大家一起参谋参谋：

（1）QuestionTypeMapper.java (映射器）


/**
 * @author edison
 * @Description 题型映射器
 * @date 2023年04月18日
  */
public interface QuestionTypeMapper extends Mapper<QuestionType> {

    /**
     * 查询题型列表
     * @param req
     * @return
     * @throws Exception
     */
    List<ReqQuestionTypeList.RespQuestionTypeList> getQuestionTypeList(Map<String,Object> req) throws Exception;

    /**
     * 获取题型详情
     * @param req
     * @return
     * @throws Exception
     */
    ReqQuestionTypeInfo.RespQuestionTypeInfo getQuestionTypeInfo(Map<String,Object> req) throws Exception;

    /**
     * 保存题型
     * @param req
     * @return
     * @throws Exception
     */
    int saveQuestionType(Map<String,Object> req) throws Exception;

    /**
     * 新增题型
     * @param req
     * @return
     * @throws Exception
     */
     int insertQuestionType(Map<String,Object> req) throws Exception;

    /**
     * 删除题型
     * @param list
     * @return
     * @throws Exception
     */
    int removeQuestionType(List<ReqRemoveQuestionType> list) throws Exception;
}

（2）QuestionTypeMapper.xml（映射器对应的XML文件）

<?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.edison.questionnaire.dao.QuestionTypeMapper"> 
    <select id="getQuestionTypeList" parameterType="com.edison.api.questionType.protocol.ReqQuestionTypeList"
            resultType="com.edison.api.questionType.protocol.ReqQuestionTypeList$RespQuestionTypeList">
        select
        id,
        code,
        name,
        remark,
        create_time,
        update_time
        from t_question_type
        <where>
            status = 1
            <if test="name != null and name!= ''">
                and name like concat('%',#{name},'%')
            </if>
        </where>
        order by update_time desc
    </select>

    <select id="getQuestionTypeInfo" parameterType="com.edison.api.questionType.protocol.ReqQuestionTypeInfo"
            resultType="com.edison.api.questionType.protocol.ReqQuestionTypeInfo$RespQuestionTypeInfo">
        select
        id,
        code,
        name,
        remark,
        create_time,
        update_time
        from t_question_type
        <where>
            status = 1
            <if test="id != null and id != ''">
                and id = #{id}
            </if>
        </where>
    </select>

    <update id="saveQuestionType" parameterType="com.edison.api.questionType.protocol.ReqSaveQuestionType">
        update
        t_question_type
        <set>
            update_time = current_timestamp,
            <if test="id != null and id != ''">
                id = #{id},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="remark != null">
                remark = #{remark},
            </if>
            status = 1
        </set>
        <where>
            status = 1
            <if test="id != null and id != ''">
                and id = #{id}
            </if>
        </where>
    </update>

    <insert id="insertQuestionType" parameterType="com.edison.api.questionType.protocol.ReqInsertQuestionType">
        insert into t_question_type
        (
        id,
        code,
        name,
        remark,
        create_time,
        update_time
         )
        values
        (#{id},
         #{code},
         #{name},
         #{remark},
         current_timestamp,
         current_timestamp)
    </insert>

    <update id="removeQuestionType" parameterType="com.edison.api.questionType.protocol.ReqRemoveQuestionType">
        update
        t_question_type
        <set>
            status = 0
        </set>
        <where>
            id in
            <foreach collection="list" item="item" open="(" close=")" separator=",">
                #{item.id}
            </foreach>
        </where>
    </update>
</mapper>

（3）QuestionTypeService.java（服务）

/**
 * @author edison
 * @Description 题型服务
 * @date 2023年04月18日
 */
@DubboService
//@Service
public class QuestionTypeService implements IQuestionType {

    @Autowired
    private QuestionTypeMapper questionTypeMapper;

    @Override
    public RespPaging<ReqQuestionTypeList.RespQuestionTypeList> getQuestionTypeList(ReqQuestionTypeList req) throws Exception {
        RespPaging<ReqQuestionTypeList.RespQuestionTypeList> respPaging = new RespPaging<>();
        PageHelper.startPage(req.getPageNum(), req.getPageSize());

        List<ReqQuestionTypeList.RespQuestionTypeList> questionTypeList =  questionTypeMapper.getQuestionTypeList(BeanUtil.beanToMap(req));
        PageInfo<ReqQuestionTypeList.RespQuestionTypeList> respQuestionTypeListPageInfo = new PageInfo<>(questionTypeList);
        respPaging.setList(questionTypeList);
        BeanUtil.copyProperties(questionTypeList, respPaging);
        return respPaging;
    }

    @Override
    public ReqQuestionTypeInfo.RespQuestionTypeInfo getQuestionTypeInfo(ReqQuestionTypeInfo req) throws Exception {
        ReqQuestionTypeInfo.RespQuestionTypeInfo questionTypeInfo = null;
        try {
            questionTypeInfo = questionTypeMapper.getQuestionTypeInfo(BeanUtil.beanToMap(req));
        } catch (Exception e) {
            throw DaasException.of().setMsg("获取题型信息异常" + e.getMessage());
        }
        if (questionTypeInfo == null) {
            throw DaasException.of().setMsg("数据为空");
        }

        return questionTypeInfo;
    }

    @Transactional
    @Override
    public ReqSaveQuestionType.RespSaveQuestionType saveQuestionType(ReqSaveQuestionType req) throws Exception {
        ReqSaveQuestionType.RespSaveQuestionType respSaveQuestionType = new ReqSaveQuestionType.RespSaveQuestionType();
        ReqQuestionTypeInfo.RespQuestionTypeInfo questionTypeInfo = new ReqQuestionTypeInfo.RespQuestionTypeInfo();
        int updateRows = 0;
        try {
            updateRows = questionTypeMapper.saveQuestionType(BeanUtil.beanToMap(req));
        } catch (Exception e) {
            throw DaasException.of().setMsg("修改题型异常" + e.getMessage());
        }
        if (updateRows <= 0) {
            throw DaasException.of().setMsg("修改数据为空");
        } else {
            BeanUtil.copyProperties(req, respSaveQuestionType);
        }
        return respSaveQuestionType;
    }

    @Override
    public ReqSaveQuestionType.RespSaveQuestionType insertQuestionType(ReqInsertQuestionType req) throws Exception {
        ReqSaveQuestionType.RespSaveQuestionType respSaveQuestionType = new ReqSaveQuestionType.RespSaveQuestionType();
        int insertRows = 0;
        try {
            req.setId(IdUtil.fastSimpleUUID());
            insertRows = questionTypeMapper.insertQuestionType(BeanUtil.beanToMap(req));
        } catch (Exception e) {
            throw DaasException.of().setMsg("新增题型异常" + e.getMessage());
        }
        if (insertRows <= 0) {
            throw DaasException.of().setMsg("新增数据为空");
        } else {
            BeanUtil.copyProperties(req, respSaveQuestionType);
        }
        return respSaveQuestionType;
    }

    @Override
    public List<ReqRemoveQuestionType> removeQuestionType(List<ReqRemoveQuestionType> list) throws Exception {
        int removeRows = 0;
        try {
            removeRows = questionTypeMapper.removeQuestionType(list);
        } catch (Exception e) {
            throw DaasException.of().setMsg("删除题型异常" + e.getMessage());
        }
        if (removeRows <= 0) {
            throw DaasException.of().setMsg("删除数据为空");
        }
        return list;
    }
}

（4）IQuestionType.java （服务接口）

/**
 * @author edison
 * @Description 问题
 * @DateTime 2023年4月17日
 * Copyright(c) 2023. All Rights Reserved
 */

public interface IQuestionType {

    /**
     * 查询问题列表
     * @param req
     * @return
     * @throws Exception
     */
    RespPaging<ReqQuestionTypeList.RespQuestionTypeList> getQuestionTypeList(ReqQuestionTypeList req) throws Exception;

    /**
     * 获取问题详情
     * @param req
     * @return
     * @throws Exception
     */
    ReqQuestionTypeInfo.RespQuestionTypeInfo getQuestionTypeInfo(ReqQuestionTypeInfo req) throws Exception;

    /**
     * 保存问题
     * @param req
     * @return
     * @throws Exception
     */
    ReqSaveQuestionType.RespSaveQuestionType saveQuestionType(ReqSaveQuestionType req) throws Exception;

    /**
     * 新增问题
     * @param req
     * @return
     * @throws Exception
     */
    ReqSaveQuestionType.RespSaveQuestionType insertQuestionType(ReqInsertQuestionType req) throws Exception;

    /**
     * 删除问题
     * @param req
     * @return
     * @throws Exception
     */
    List<ReqRemoveQuestionType> removeQuestionType(List<ReqRemoveQuestionType> req) throws Exception;
}

（5）QuestionType.java (实体类)

/**
 * @author Edison F.
 * @Description Model: 题型 t_question_type
 * @DateTime 2023/04/18
 */
@Table(name = "t_question_type")
public class QuestionType extends QuestionTypeBase  implements Serializable {

	/**
	 * 序列化
	 */
	private static final long serialVersionUID = 1L;
}

(6) QuestionTypeBase.java (实体类基类）

/**
 * @author edison
 * @Description 题型
 * @DateTime 2023年4月17日
 * Copyright(c) 2023. All Rights Reserved
 */
@Data
@Accessors
public class QuestionTypeBase extends Base implements Serializable {
    /**
	 * 虚拟化
	 */
	private static final long serialVersionUID = 1L;

	/**
     * 记录ID
     */
    @ApiModelProperty(value = "记录ID", example = "e7e6dfb0a83911eb943f00ff71c9db07")
    private String id;

    /**
     * 题型代码（唯一）
     */
    @ApiModelProperty(value = "题型代码（唯一）",example = "decision")
    private String code;

    /**
     * 题型名称
     */
    @ApiModelProperty(value = "题型名称", example = "判断题")
    private String name;
//    /**
//     * 创建者id
//     */
//    @ApiModelProperty(value = "创建者id", example = "admin")
//    private String createUser;
//
//    /**
//     * 修改者id
//     */
//    @ApiModelProperty(value = "修改者id", example = "admin")
//    private String updateUser;
}

（7）base.java (公共属性）

/**
 * @author Edison F.
 * @Description 基类，提供三个基础字段
 * @DateTime 2021/05/14
 */
@Accessors(chain = true)
@Data
public class Base {
    /**
     * 创建时间（默认系统时间,修改新增都不需要手动插入）
     */	
	@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
	@DateTimeFormat(pattern ="yyyy-MM-dd HH:mm:ss")
	@ApiModelProperty(value = "创建时间（默认系统时间,修改新增都不需要手动插入）", example = "2021-05-01 12:30:59")
	private Date createTime;
	
    /**
     * 修改时间（默认系统时间,不需要手动插入）
     */	
	@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
	@DateTimeFormat(pattern ="yyyy-MM-dd HH:mm:ss")
	@ApiModelProperty(value = "修改时间（默认系统时间,修改新增都不需要手动插入）", example = "2021-05-01 12:30:59")
	private Date updateTime;
    /**
     * 备注
     */	
	@ApiModelProperty(value = "备注", example = "这是一个备注")
	private String remark;
}

2、这个问题常用解决办法

常用办法有几种：

（1）检查映射器xml中的方法名和DAO的映射器类方法名称是否一致；

（2）检查映射器xml文件中名称空间namespace是否正确（映射器类全限定名）；

（3）检查映射器xml文件中方法入参/出参和映射器类的方法入参/出参是否一致，ResultMap要注意type是否正确，ResultType要注意是返回结果的一条记录的类型，map要注意字段是否存在，paramtype注意路径是否正确；

（4）检查映射器xml在配置文件或者配置类中的扫描路径是否正确。

按照以上4点方法，仔细检查了至少三遍，问题依旧。

网上寻找帮助，有两个方法：

（1）在映射器XML某些地方插入空格；

（2）检查映射器XML文件是否只读。

试了若干遍，具体记不清了，反复反复，还是一样的问题，依旧提示Invalid bound statement。

3、深入调试

（1）拷贝以前项目的实体类、映射器类和XML文件到本项目对应包中，结构完全一样。结果：一切正常。

（2）动手新增测试类

新建TestMapper.java

public interface TestMapper extends Mapper<TestTable> {
    int insertTest(Map<String,Object> req) throws Exception;
    int insertQuestionType(Map<String,Object> req) throws Exception;
}

从有问题的映射器类拷贝一个，重新命名为：Test2Mapper.java

public interface Test2Mapper extends Mapper<QuestionType> {
    int insertQuestionType(Map<String,Object> req) throws Exception;
    int insertTest(Map<String,Object> req) throws Exception;
}

建表脚本：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_test
-- ----------------------------
DROP TABLE IF EXISTS `t_test`;
CREATE TABLE `t_test`  (
  `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '记录ID',
  `code` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '代码',
  `name` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '名称',
  `remark` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '备注-内部使用',
  `create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间,默认系统时间,不需要手动插入',
  `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间,默认系统时间,不需要手动插入',
  `create_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '创建者id',
  `update_user` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '修改者id',
  `status` bit(1) NOT NULL DEFAULT b'1' COMMENT '数据有效性-0无效/1有效(实体类为boolean)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '测试' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

新建TestMapper.xml：

<?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.edison.questionnaire.dao.TestMapper">
    <insert id="insertTest" >
        insert into t_test
        (id,
         code,
         name)
        values
        (#{id},
         #{code},
         #{name})
    </insert>
    <insert id="insertQuestionType">
        insert into t_test
        (id,
        code,
        name,
        remark,
        create_time,
        update_time,
        create_user,
        update_user,
        status)
        values
        (
        #{id},
        #{code},
        #{name},
        #{remark},
        #{createTime},
        #{updateTime},
        #{createUser},
        #{updateUser},
        #{status} 
		)
    </insert>
</mapper>

从有问题的映射器XML文件拷贝一个，重新命名为：Test2Mapper.xml

<?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.edison.questionnaire.dao.Test2Mapper">
    <insert id="insertTest" >
        insert into t_question_type
        (id,
         code,
         name)
        values
        (#{id},
         #{code},
         #{name})
    </insert>
    
    <insert id="insertQuestionType">
        insert into t_question_type
        (id,
        code,
        name,
        remark,
        create_time,
        update_time,
        create_user,
        update_user)
        values
        (
        #{id},
        #{code},
        #{name},
        #{remark},
        #{createTime},
        #{updateTime},
        #{createUser},
        #{updateUser}} 
		)
    </insert>
</mapper>

编写单元测试函数：

	@Test
	void questionType() {
		System.out.println("-----------------questionType----------------");
		QuestionType test = new QuestionType();
        test.setId("questionType");
        test.setCode("questionType");
        test.setName("questionType");
        try {
        	int insertRows = questionTypeMapper.insertQuestionType(BeanUtil.beanToMap(test));
        }
        catch(Exception e) {
        	System.out.println("questionType异常："+e.getLocalizedMessage());
        }
        System.out.println("-----------------questionType----------------");
	}
	
	@Test
	void testTable() {
		System.out.println("-----------------testTable----------------");
		TestTable test = new TestTable();
        test.setId("testTable");
        test.setCode("testTable");
        test.setName("testTable");
        try {
        	int insertRows = testMapper.insertTest(BeanUtil.beanToMap(test));
        }
        catch(Exception e) {
        	System.out.println("testTable异常："+e.getLocalizedMessage());
        }
        System.out.println("-----------------testTable----------------");
	}

	@Test
	void questionType2() {
		System.out.println("-----------------questionType2----------------");
		QuestionType test = new QuestionType();

        int insertRows = 0;
        try {
            test.setId("questionType2");
            test.setCode("questionType2");
            test.setName("questionType2");
        	insertRows = testMapper2.insertTest(BeanUtil.beanToMap(test));
        }
        catch(Exception e) {
        	System.out.println("questionType2-insertTest异常："+e.getLocalizedMessage());
        }
        try {
            test.setId("12345");
            test.setCode("12345");
            test.setName("12345");
//            test.setRemark("123456");
//            test.setCreateTime(Utils.getStartTime());
//            test.setUpdateTime(Utils.getStartTime());
//            test.setCreateUser("admin");
//            test.setUpdateUser("admin");
        	insertRows = testMapper2.insertQuestionType(BeanUtil.beanToMap(test));
        }
        catch(Exception e) {
        	System.out.println("questionType2-insertQuestionType异常："+e.getLocalizedMessage());
        }
        System.out.println("-----------------questionType2----------------");
	}

最初，testTable()运行时正常，questionType1()和questionType2()均不正常，后来，三个函数都运行错误：Invalid bound statement (not found)：

-----------------testTable----------------
testTable异常：Invalid bound statement (not found): com.edison.questionnaire.dao.TestMapper.insertTest
-----------------testTable----------------
-----------------questionType2----------------
questionType2-insertTest异常：Invalid bound statement (not found): com.edison.questionnaire.dao.Test2Mapper.insertTest
questionType2-insertQuestionType异常：Invalid bound statement (not found): com.edison.questionnaire.dao.Test2Mapper.insertQuestionType
-----------------questionType2----------------
-----------------questionType----------------
questionType异常：Invalid bound statement (not found): com.edison.questionnaire.dao.QuestionTypeMapper.insertQuestionType
-----------------questionType----------------

不清楚testTable()测试不通过的触发点是什么。

4、最终解决办法

不想再实验，花了太多时间，我需要快速走向正轨，我最后的解决办法是抛弃XML文件，使用注解来完成，如下：

/**
 * @author edison
 * @Description 题型映射器
 * @date 2023年04月18日
 * bug:Invalid bound statement (not found): ... 使用xml文件无法解决这个问题。改用注解@Select等。
 */
public interface QuestionTypeMapper extends Mapper<QuestionType> {

    /**
     * 查询题型列表
     * @param req
     * @return
     * @throws Exception
     */
	@Select("select id, code, name, remark, create_time, update_time "
			+ "from t_question_type where status = 1 and name like concat('%',#{name},'%') "
			+ "order by update_time desc")
    List<ReqQuestionTypeList.RespQuestionTypeList> getQuestionTypeList(Map<String,Object> req) throws Exception;

    /**
     * 获取题型详情
     * @param req
     * @return
     * @throws Exception
     */
	@Select("select id, code, name, remark, create_time, update_time "
			+ "from t_question_type where status = 1 and id = #{id} ")
    ReqQuestionTypeInfo.RespQuestionTypeInfo getQuestionTypeInfo(Map<String,Object> req) throws Exception;

    /**
     * 保存题型
     * @param req
     * @return
     * @throws Exception
     */
	@Update("<script> update t_question_type "
			+ "        <set> "
			+ "            update_time = current_timestamp, "
			+ "            <if test=\"id != null and id != ''\"> "
			+ "                id = #{id}, "
			+ "            </if> "
			+ "            <if test=\"name != null and name != ''\"> "
			+ "                name = #{name}, "
			+ "            </if> "
			+ "            <if test=\"remark != null\"> "
			+ "                remark = #{remark}, "
			+ "            </if> "
			+ "            status = 1 "
			+ "        </set> "
			+ "        <where> "
			+ "            status = 1 "
			+ "            <if test=\"id != null and id != ''\"> "
			+ "                and id = #{id} "
			+ "            </if> "
			+ "        </where> </script>")
    int saveQuestionType(Map<String,Object> req) throws Exception;

    /**
     * 新增题型
     * @param req
     * @return
     * @throws Exception
     */
    @Insert("insert into t_question_type(id, code, name) values(#{id},#{code},#{name})")
    int insertQuestionType(Map<String,Object> req) throws Exception;

    /**
     * 删除题型
     * @param list
     * @return
     * @throws Exception
     */
    @Delete("<script> update t_question_type set status = 0 where id in "
    		+ "            <foreach collection='list' item='item' open='(' close=')' separator=','> "
    		+ "                #{item.id} "
    		+ "            </foreach> "
    		+ "        </script>")
    int removeQuestionType(List<ReqRemoveQuestionType> list) throws Exception;
}

上图为证：

SpringBoot+Mybatis这个bug估计连作为神仙的您也无法解决--》Invalid bound statement (not found): ..._springcloud_02