MyBatis Plus Invalid bound statement 终极解决方案

過客

已于 2023-03-01 20:09:21 修改

阅读量2.8k

点赞数 2

分类专栏： java web后台文章标签： mybatis java mysql

于 2023-03-01 18:47:38 首次发布

本文链接：https://blog.csdn.net/panyongjie2577/article/details/129287063

版权

java web后台专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MyBatis Plus Invalid bound statement 终极解决方案

一、项目

1.1 编码部分

1.1.1 实体类

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.util.Date;

@Data
public class Student {
    @TableId
    private Integer studentId;

    private String name;

    private Integer age;

    @TableField(fill = FieldFill.INSERT)
    private Date createTime;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;

    /**
     * 乐观锁实现注解
     * */
    @Version
    private int version;

    @TableField(exist = false)
    private long serializeId = 156612311187754L;

    @TableLogic(value = "0", delval = "1")
    private int deleted;
}

import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
public class PageQuery {
    private int pageIndex;
    private int pageSize;
}

1.1.2 dao层

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.batis.plus.demo.entity.Customer;

public interface CustomerDao extends BaseMapper<Customer> {
}

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.batis.plus.demo.entity.PageQuery;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface StudentDao extends BaseMapper<Student> {
    List<Student> getStudentList(PageQuery pageQuery);
    List<Student> getStudentListLessThanAge(@Param("age") Integer age);
    List<Student> getStudentListBigThanAge(@Param("age") Integer age);
    Student getStudentByCondition(Integer age, String name);
    Student getStudentByMapCondition(Map<String, Object>  queryParam);
}

1.1.3 mapper.xml

Customer.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.batis.plus.demo.dao.CustomerDao"></mapper>

Student.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.batis.plus.demo.dao.StudentDao">

    <select id="getStudentList" resultType="com.batis.plus.demo.entity.Student" parameterType="com.batis.plus.demo.entity.PageQuery">
        select * from student limit #{pageIndex}, #{pageSize};
    </select>

    <select id="getStudentListLessThanAge" resultType="com.batis.plus.demo.entity.Student" parameterType="java.lang.Integer">
        select * from student where age &lt;= #{age};
    </select>

    <select id="getStudentListBigThanAge" resultType="com.batis.plus.demo.entity.Student" parameterType="java.lang.Integer">
        select * from student where age &gt;= #{age};
    </select>

    <!-- 按照参数数据传入，超过3个参数不建议使用此方式 -->
    <select id="getStudentByCondition" resultType="com.batis.plus.demo.entity.Student">
        select * from student where age = #{param1} and name = #{param2};
    </select>

	<!-- 直接使用map中的key来作为参数 -->
    <select id="getStudentByMapCondition" resultType="com.batis.plus.demo.entity.Student">
        select * from student where age = #{age} and name = #{name};
    </select>
</mapper>

1.2 环境配置

Application上添加了Mapper扫码配置 @MapperScan("com.batis.plus.demo.dao"), 也添加了数据源配置.

@SpringBootApplication
@MapperScan("com.batis.plus.demo.dao")
public class BatisPlusDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(BatisPlusDemoApplication.class, args);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(@Autowired @Qualifier("dataSource") DataSource dataSource) throws Exception {

        MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        //获取mybatis-plus全局配置
        GlobalConfig globalConfig = GlobalConfigUtils.defaults();
        //mybatis-plus全局配置设置元数据对象处理器为自己实现的类
        globalConfig.setMetaObjectHandler(new MyMetaObjectHandler());
        mybatisSqlSessionFactoryBean.setDataSource(dataSource);
        //mybatisSqlSessionFactoryBean关联设置全局配置
        mybatisSqlSessionFactoryBean.setGlobalConfig(globalConfig);

        Interceptor[] interceptors = {paginationInterceptor()};
        mybatisSqlSessionFactoryBean.setPlugins(interceptors);

        return mybatisSqlSessionFactoryBean.getObject();
    }

    /**
     * 注册插件
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加分页插件
        PaginationInnerInterceptor pageInterceptor = new PaginationInnerInterceptor();
        // 设置请求的页面大于最大页后操作，true调回到首页，false继续请求。默认false
        pageInterceptor.setOverflow(false);
        // 单页分页条数限制，默认无限制
        pageInterceptor.setMaxLimit(100L);
        // 设置数据库类型
        pageInterceptor.setDbType(DbType.MYSQL);

        // 添加分页拦截器
        interceptor.addInnerInterceptor(pageInterceptor);

        // 添加乐观锁配置
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

yml配置文件中也手动指定了mapper.xml文件路径

#配置日志
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:mapper/*.xml

项目结构也正常

1.3 问题描述

@Test
void getStudent() {
    System.out.println("*********************************");
    studentListPrintln(studentDao.getStudentList(new PageQuery(1, 3)));

    System.out.println("*********************************");
    studentListPrintln(studentDao.getStudentListLessThanAge(30));

    System.out.println("*********************************");
    studentListPrintln(studentDao.getStudentListBigThanAge(30));

    System.out.println("*********************************");
    System.out.println(studentDao.getStudentByCondition(42, "李丽"));

    System.out.println("*********************************");
    Map<String, Object> param = new HashMap<>();
    param.put("name", "李丽");
    param.put("age", 45);
    System.out.println(studentDao.getStudentByMapCondition(param));
}

void studentListPrintln(List<Student> studentList) {
    if(null == studentList || studentList.isEmpty()) {
        println("没有查找到符合条件的用户");
        return;
    }

    for (Student student : studentList) {
        println(student.toString());
    }
}

然后运行提示org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.batis.plus.demo.dao.StudentDao.getStudentList, 按照常规的方式排查没有任何问题也没有任何效果。

二、解决方案

2.1 手动指定mapper.xml资源路径匹配规则

在sqlSessionFactory方法中添加如下代码

// 如果使用自定义xml来进行复杂的自定义sql操作，就需要手动配置xml资源文件路径地址，否则会出现"Invalid bound statement"的报错
mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().
                getResources("classpath:mapper/*.xml"));

2.2 使用mybatis自动配置

注释掉上面的SqlSessionFactory 手动配置代码，然后添加如下依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-autoconfigure</artifactId>
    <version>1.3.2</version>
</dependency>

2.3 测试效果

*********************************
Student(studentId=1, name=李丽, age=15, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:39:41 CST 2022, version=1, serializeId=156612311187754, deleted=0)
Student(studentId=2, name=于荣光, age=66, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:40:04 CST 2022, version=1, serializeId=156612311187754, deleted=0)
Student(studentId=3, name=陈, age=28, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:43:34 CST 2022, version=1, serializeId=156612311187754, deleted=0)

*********************************
Student(studentId=0, name=刘韬, age=24, createTime=Tue Dec 27 17:50:05 CST 2022, updateTime=Tue Dec 27 17:50:05 CST 2022, version=0, serializeId=156612311187754, deleted=0)
Student(studentId=1, name=李丽, age=15, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:39:41 CST 2022, version=1, serializeId=156612311187754, deleted=0)
Student(studentId=3, name=陈, age=28, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:43:34 CST 2022, version=1, serializeId=156612311187754, deleted=0)
Student(studentId=7, name=于里昂1122, age=21, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Tue Dec 20 13:42:03 CST 2022, version=3, serializeId=156612311187754, deleted=0)
Student(studentId=8, name=于里昂2022, age=29, createTime=Tue Dec 20 09:25:36 CST 2022, updateTime=Tue Dec 20 09:25:36 CST 2022, version=2, serializeId=156612311187754, deleted=0)

*********************************
Student(studentId=2, name=于荣光, age=66, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:40:04 CST 2022, version=1, serializeId=156612311187754, deleted=0)
Student(studentId=4, name=null, age=35, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:39:30 CST 2022, version=1, serializeId=156612311187754, deleted=0)
Student(studentId=5, name=李丽, age=42, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:39:31 CST 2022, version=1, serializeId=156612311187754, deleted=1)
Student(studentId=6, name=李丽, age=45, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:39:35 CST 2022, version=1, serializeId=156612311187754, deleted=1)

*********************************
Student(studentId=5, name=李丽, age=42, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:39:31 CST 2022, version=1, serializeId=156612311187754, deleted=1)

*********************************
Student(studentId=6, name=李丽, age=45, createTime=Sat Oct 01 14:26:33 CST 2022, updateTime=Mon Dec 19 18:39:35 CST 2022, version=1, serializeId=156612311187754, deleted=1)

三、附件

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `student_id` int(11) NOT NULL COMMENT '学生ID',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '最后一次更新时间',
  `deleted` int(1) NOT NULL DEFAULT 0 COMMENT '是否已删除 0 未删除 1 已删除',
  `version` int(11) NOT NULL DEFAULT 1 COMMENT '版本号',
  PRIMARY KEY (`student_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (0, '刘韬', 24, '2022-12-27 17:50:05', '2022-12-27 17:50:05', 0, 0);
INSERT INTO `student` VALUES (1, '李丽', 15, '2022-10-01 14:26:33', '2022-12-19 18:39:41', 0, 1);
INSERT INTO `student` VALUES (2, '于荣光', 66, '2022-10-01 14:26:33', '2022-12-19 18:40:04', 0, 1);
INSERT INTO `student` VALUES (3, '陈', 28, '2022-10-01 14:26:33', '2022-12-19 18:43:34', 0, 1);
INSERT INTO `student` VALUES (4, NULL, 35, '2022-10-01 14:26:33', '2022-12-19 18:39:30', 0, 1);
INSERT INTO `student` VALUES (5, '李丽', 42, '2022-10-01 14:26:33', '2022-12-19 18:39:31', 1, 1);
INSERT INTO `student` VALUES (6, '李丽', 45, '2022-10-01 14:26:33', '2022-12-19 18:39:35', 1, 1);
INSERT INTO `student` VALUES (7, '于里昂1122', 21, '2022-10-01 14:26:33', '2022-12-20 13:42:03', 0, 3);
INSERT INTO `student` VALUES (8, '于里昂2022', 29, '2022-12-20 09:25:36', '2022-12-20 09:25:36', 0, 2);

SET FOREIGN_KEY_CHECKS = 1;