Day57——SpringBoot2.x整合MyBatis

谁践踏了优雅 2023-03-04 13:29 4阅读 0赞

### 文章目录 ###

*  一. 问题背景
 *  二. 整合MyBatis
 *   *  2.1 搭建环境
     *  2.2 注解版MyBatis
     *   *  2.2.1 步骤
         *  2.2.2 配置驼峰命名
         *   *  2.2.2.1 问题背景
             *  2.2.2.2 解决方案
             *  2.2.2.3 原理
         *  2.2.3 指定扫描mapper包
     *  2.3 配置版MyBatis
     *   *  2.3.1 步骤
         *  2.3.2 报错Invalid statment xxx not found

# 一. 问题背景 #

前面学习了[Day56——SpringBoot2.x整合Druid&配置数据源监控][Day56_SpringBoot2.x_Druid]，今天来学习整合MyBatis

# 二. 整合MyBatis #

## 2.1 搭建环境 ##

首先搭建好druid数据源，这个步骤与[Day56——SpringBoot2.x整合Druid&配置数据源监控][Day56_SpringBoot2.x_Druid]一样，步骤如下：

**引入依赖：**

<dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid-spring-boot-starter</artifactId>
          <version>1.1.17</version>
      </dependency>

**配置Druid：**  
创建application.yaml，在druid属性以下的属性，都是druid-starter依赖所需要配置的。 **schema属性以及initialization-mode属性是创建表时要用到的，因此创建完表后应该将`schema`和`initialization-mode`** 注释掉如下：

spring:
      datasource:
        # 数据源基本配置
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.32.103:3306/mybatis
        type: com.alibaba.druid.pool.DruidDataSource
        #首次运行完创建表后，应该将schema和initialization-mode注释掉
    # # 指定sql脚本文件路径
    # schema:
    # - classpath:sql/department.sql
    # - classpath:sql/employee.sql
    # #设置SpringBoot启动后执行脚本文件
    # initialization-mode: always
        druid:
          initial-size: 5
          max-active: 20
          min-idle: 5
          max-wait: 60000
          pool-prepared-statements: true
          max-pool-prepared-statement-per-connection-size: 20
          validation-query: SELECT 1 FROM DUAL
          test-on-borrow: false
          test-on-return: false
          test-while-idle: true
          time-between-eviction-runs-millis: 60000
          min-evictable-idle-time-millis: 300000
          filter:
            stat:
              enabled: true
              db-type: mysql
              log-slow-sql: true
              slow-sql-millis: 2000
            wall:
              enabled: true
            slf4j:
              enabled: true
          keep-alive: true
          # 监控过滤器
          web-stat-filter:
            enabled: true
            exclusions:
              - "*.js"
              - "*.gif"
              - "*.jpg"
              - "*.png"
              - "*.css"
              - "*.ico"
              - "/druid/*"
          #exclusions可以是list格式配置，也可以是String格式
          # exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
          # druid 监控页面
          stat-view-servlet:
            enabled: true
            url-pattern: /druid/*
            reset-enable: false
            login-username: admin
            login-password: 123456
            #拒绝本机连接（自行修改自己的本机连接）
            deny: 192.168.32.103

**配置Druid数据源：**

@Configuration
    public class DruidConfig { 
    
        @Bean
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.druid")
        public DruidDataSource druidDataSource(){ 
            return DruidDataSourceBuilder.create().build();
        }
    }

**引入两个sql脚本：**  
![在这里插入图片描述][20200803213209916.jpg]  
department.sql

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for department
    -- ----------------------------
    DROP TABLE IF EXISTS `department`;
    CREATE TABLE `department` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `departmentName` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

employee.sql

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for employee
    -- ----------------------------
    DROP TABLE IF EXISTS `employee`;
    CREATE TABLE `employee` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `lastName` varchar(255) DEFAULT NULL,
      `email` varchar(255) DEFAULT NULL,
      `gender` int(2) DEFAULT NULL,
      `d_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

**首次运行完创建完表后，将yaml文件中的schema属性以及initialization-mode属性注释掉，然后创建Bean：**  
Employee.java

package com.atguigu.springboot.bean;
    
    public class Employee { 
    
        private Integer id;
        private String lastname;
        private Integer gender;
        private String email;
        private Integer dId;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getLastname() { 
            return lastname;
        }
    
        public void setLastname(String lastname) { 
            this.lastname = lastname;
        }
    
        public Integer getGender() { 
            return gender;
        }
    
        public void setGender(Integer gender) { 
            this.gender = gender;
        }
    
        public String getEmail() { 
            return email;
        }
    
        public void setEmail(String email) { 
            this.email = email;
        }
    
        public Integer getdId() { 
            return dId;
        }
    
        public void setdId(Integer dId) { 
            this.dId = dId;
        }
    }

Department.java

package com.atguigu.springboot.bean;
    
    public class Department { 
    
        private Integer id;
        private String departmentName;
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getDepartmentName() { 
            return departmentName;
        }
    
        public void setDepartmentName(String departmentName) { 
            this.departmentName = departmentName;
        }
    }

**至此搭建环境完成**

## 2.2 注解版MyBatis ##

### 2.2.1 步骤 ###

使用注解配置mybatis非常简单，只需创建interface并加上`@Mapper`注释即可，步骤如下：

**首先创建一个interface：**  
DepartmentMapper.java

//指定这是一个操作数据库的mapper
    @Mapper
    public interface DepartmentMapper { 
    
        @Select("select * from department where id = #{id}")
        public Department getDeptById(Integer id);
    
        @Delete("delete from department where id = #{id}")
        public int deleteDeptById(Integer id);
    
        @Options(useGeneratedKeys = true,keyProperty = "id")//插入后返回主键
        @Insert("insert into department(department_name) values(#{departmentName})")
        public int insertDept(Department department);
    
        @Update("update department set department_name = #{departmentName} where id = #{id}")
        public int updateDept(Department department);
    }

**解释：** 所有的增删改查只需使用`@Insert、@Delete、@Update、@Select`书写sql语句，如果插入数据后需要返回主键，只需使用`@Option(useGeneratedKeys=true, keyProperty="id")`。

为了方便讲述，这里不再创建Service这些，直接**创建Controller：**  
DeptController.java

@RestController
    public class DeptController { 
    
        @Autowired
        DepartmentMapper departmentMapper;
    
        @GetMapping("/dept/{id}")
        public Department getDepartment(@PathVariable("id") Integer id){ 
            return departmentMapper.getDeptById(id);
        }
    
        @GetMapping("/dept")
        public Department insertDept(Department department){ 
            departmentMapper.insertDept(department);
            return department;
        }
    }

至此就完成了整合MyBatis。

测试：  
![在这里插入图片描述][20200803222131445.jpg]  
![在这里插入图片描述][20200803222131441.jpg]

### 2.2.2 配置驼峰命名 ###

#### 2.2.2.1 问题背景 ####

**假如将数据库字段名改成有下划线的格式，比如将`departmentName`改成`department_name`，执行sql语句还成功吗？**  
![在这里插入图片描述][20200803222406988.png]

修改sql语句对应的数据库字段名为下划线格式，如下：

@Options(useGeneratedKeys = true,keyProperty = "id")//插入后返回主键
    @Insert("insert into department(department_name) values(#{departmentName})")
    public int insertDept(Department department);
    
    @Update("update department set department_name = #{departmentName} where id = #{id}")
    public int updateDept(Department department);

![在这里插入图片描述][20200803222406998.png]

#### 2.2.2.2 解决方案 ####

**只需创建一个配置类，并注册一个ConfigurationCustomizer到容器中，重写customize()方法，在方法里面开启驼峰命名规则**，如下：

@Configuration
    public class MyBatisConfig { 
    
        @Bean
        public ConfigurationCustomizer configurationCustomizer(){ 
            return new ConfigurationCustomizer() { 
                @Override
                public void customize(org.apache.ibatis.session.Configuration configuration) { 
                    configuration.setMapUnderscoreToCamelCase(true);//开启驼峰命名法
                }
            };
        }
    }

#### 2.2.2.3 原理 ####

按2下shift搜索并打开`MyBatisAutoConfiguration`，有一个注册`SqlSessionFactory`的方法，如下：

@Bean
        @ConditionalOnMissingBean
        public SqlSessionFactory sqlSessionFactory(DataSource dataSource) 
                                                         throws Exception { 
            ...
            this.applyConfiguration(factory);//关键方法
            ...
    }

applyConfiguration()方法非常关键，如下:

private void applyConfiguration(SqlSessionFactoryBean factory) { 
        if (configuration != null && !CollectionUtils.isEmpty(this.configurationCustomizers)) { 
                Iterator var3 = this.configurationCustomizers.iterator();
    
                while(var3.hasNext()) { 
                    //拿到的ConfigurationCustomizer并调用它的customize()方法
                    ConfigurationCustomizer customizer = (ConfigurationCustomizer)var3.next();
                    customizer.customize(configuration);
                }
            }
    }

**解释：** 可以看到，注册SqlSessionFactory的时候，会拿到的`ConfigurationCustomizer`并调用它的`customize()`方法

### 2.2.3 指定扫描mapper包 ###

**情景：** 一个一个地给每个Mapper加`@Mapper`注解很繁琐，我们可以使用`@MapperScan(basicPackage="xxx")`指定mapper包的路径。

**步骤：**  
在主配置类上加上`@MapperScan(value = "com.atguigu.springboot.mapper")`，如下：

@MapperScan(value = "com.atguigu.springboot.mapper")
    @SpringBootApplication
    public class SpringBoot06DataMybatisApplication {

## 2.3 配置版MyBatis ##

### 2.3.1 步骤 ###

**首先创建EmployeeMapper.java，如下：**

//@Mapper或者@MapperScan将接口扫描到容器中
    //@Mapper
    public interface EmployeeMapper { 
    
        public Employee getEmpById(Integer id);
    
        public void insertEmp(Employee employee);
    }

**创建EmployeeMapper.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.atguigu.springboot.mapper.EmployeeMapper">
        
        <select id="getEmpById" resultType="com.atguigu.springboot.bean.Employee">
            SELECT * FROM employee WHERE id=#{id}
        </select>
    
        <insert id="insertEmp">
            INSERT INTO employee(lastName,email,gender,d_id)
            VALUES (#{lastName},#{email},#{gender},#{dId})
        </insert>
    </mapper>

**创建mybatis-config.xml，如下：**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        
        <settings>
            <setting name= "mapUnderscoreToCamelCase" value="true"/>
        </settings>
    
    </configuration>

以上两个mybatis的xml文件模板可以去[mybatis官方文档][mybatis]查看，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 3]

**指定mybatis的xml映射文件以及全局配置文件的位置：**

mybatis:
      # 指定全局配置文件位置
      config-location: classpath:mybatis/mybatis-config.xml
      # 指定sql映射文件位置
      mapper-locations: classpath:mybatis/mapper/*.xml

创建controller，如下：

@RestController
    public class DeptController { 
    
        @Autowired
        EmployeeMapper employeeMapper;
    
        @GetMapping("/emp/{id}")
        public Employee getEmpById(@PathVariable("id") Integer id){ 
            return employeeMapper.getEmpById(id);
        }
    }

**给数据表employee添加数据：**  
![在这里插入图片描述][20200804112816920.jpg]

**测试：**  
![在这里插入图片描述][20200804112816914.jpg]

### 2.3.2 报错Invalid statment xxx not found ###

**如果所有的配置无误但就是报错，而且是Invalid statement xxx not found，那么可以看看proeprties文件是否也配置了mybatis相关的配置，详情看：[SpringBoot整合Mybatis启动报错Invalid Statement xxx not found][SpringBoot_Mybatis_Invalid Statement xxx not found]**

[Day56_SpringBoot2.x_Druid]: https://blog.csdn.net/qq_40634846/article/details/107758657
[20200803213209916.jpg]: /images/20230208/18b44850328940149d530512f6f7b471.png
[20200803222131445.jpg]: /images/20230208/04d47d46f23a4cb0a7c2d610d8198b23.png
[20200803222131441.jpg]: /images/20230208/f27f18319f9644248cd829d3be0adfef.png
[20200803222406988.png]: /images/20230208/c9fa12558b3f40fdb9b3a22ac8917a19.png
[20200803222406998.png]: /images/20230208/296a138da87e456986c79533001bc103.png
[mybatis]: http://mybatis.github.io/mybatis-3/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70]: /images/20230208/2fe3d766e29541c3a50f69f7d9688a32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 1]: /images/20230208/2818ab9b0c9f4f50a1d991a0c08df30d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 2]: /images/20230208/e148140d8a4848db9eb1ea17501a0c5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 3]: /images/20230208/b1f54891d63045ae9f0aa611d99e923e.png
[20200804112816920.jpg]: /images/20230208/85d637a48f914c9ab083ef55f4808a17.png
[20200804112816914.jpg]: /images/20230208/1a02b7dcde0e4c25981ac1fa0e0801b3.png
[SpringBoot_Mybatis_Invalid Statement xxx not found]: https://blog.csdn.net/qq_40634846/article/details/107782214