MyBatis笔记（一）Spring Boot整合MyBatis实现增删查改详解（入门版）

一、简介

  MyBatis也挺流行的，我们也用SpringBoot整合MyBatis做一个简单的增删查改，作为MyBatis的入门教程，让大家有个简单的了解。

二、maven依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.2</version>
        <relativePath/>
    </parent>
    <groupId>com.alian</groupId>
    <artifactId>mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis</name>
    <description>Spring Boot整合mybatis</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.5.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.5.2</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
            <scope>runtime</scope>
        </dependency>

        <!--日志输出-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

        <!--spring boot test-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
            <version>2.5.2</version>
        </dependency>

        <!--spring test-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.3.8</version>
        </dependency>

        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

三、配置文件

3.1、启动类

package com.alian.mybatis;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.alian.mybatis.mapper")
@SpringBootApplication
public class MybatisApplication {

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

}

我们需要在启动类上加上注解@MapperScan(“com.alian.mybatis.mapper”)

3.2、mybatis.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>
		<!--SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING-->
		<!--如果希望输出sql相关信息就配置logImpl，设置值为STDOUT_LOGGING-->
		<setting name="logImpl" value="STDOUT_LOGGING" />
		<setting name="cacheEnabled" value="false" />
		<setting name="lazyLoadingEnabled" value="false" />
		<setting name="mapUnderscoreToCamelCase" value="true" />
		<setting name="callSettersOnNulls" value="true" />
	</settings>

</configuration>	

  此文件我放到resource/config目录下了，提醒一下，如果是希望输出mybatis里sql相关的日志信息，可以在settings配置：

	<setting name="logImpl" value="STDOUT_LOGGING" />

3.3、application.yml

server:
  port: 8081
  servlet:
    context-path: /mybatis

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    username: test
    password: Test!@34
    url: jdbc:mysql://192.168.0.139:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=convertToNull&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
    initialSize: 5
    minIdle: 5
    maxActive: 20

mybatis:
  config-location: classpath:config/mybatis.xml
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.alian.mybatis.dto

  这里我使用的是druid作为连接池，里面有三项MyBatis配置，我们也简单介绍下

• config-location：也就是指定mybatis配置文件的地址，也就是上面我们resource/config/目录下mybatis.xml
• mapper-locations：也就是我们mapper对应的xml的路径，这里使用通配符表示classpath:mapper目录下所有的xml文件
• type-aliases-package：一般是用于mybatis结果返回的自定义pojo

3.4、数据库数据准备

CREATE TABLE `tb_inf_employee` (
   `id` varchar(10) NOT NULL DEFAULT '' COMMENT '主键',
   `emp_name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
   `age` int(2) NOT NULL DEFAULT '0' COMMENT '年龄',
   `salary` double(8,2) NOT NULL DEFAULT '0.00' COMMENT '工资',
   `department` varchar(20) NOT NULL DEFAULT '' COMMENT '部门',
   `hire_date` date NOT NULL DEFAULT '1970-07-01' COMMENT '入职时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tb_inf_employee` (`id`, `emp_name`, `age`, `salary`, `department`, `hire_date`) VALUES 
('BAT10001','梁南生',32,32000.00,'研发部','2020-05-20'),
('BAT10002','唐鹏',31,25000.00,'研发部','2015-03-01'),
('BAT10003','王林',30,22000.00,'研发部','2015-08-01'),
('BAT10004','罗考聪',35,7000.00,'测试部','2016-11-20'),
('BAT10005','包雅馨',25,7000.00,'财务部','2018-07-01'),
('BAT10006','朱健儿',26,8000.00,'人事部','2019-01-01');

四、具体使用

4.1、包装类

EmployeeDto.java

package com.alian.mybatis.dto;

import lombok.Data;

import java.io.Serializable;
import java.time.LocalDate;

@Data
public class EmployeeDto implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 员工编号
     */
    private String id;

    /**
     * 员工姓名
     */
    private String name;

    /**
     * 员工年龄
     */
    private int age;

    /**
     * 工资
     */
    private double salary;

    /**
     * 部门
     */
    private String department;

    /**
     * 入职时间
     */
    private LocalDate hireDate;

}

4.2、mapper接口

EmployeeMapper.java

package com.alian.mybatis.mapper;

import com.alian.mybatis.dto.EmployeeDto;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

    /**
     * 查询所有员工信息
     */
    List<EmployeeDto> findAll();

    /**
     * 根据id获取员工
     */
    EmployeeDto getById(@Param("id") String id);

    /**
     * 添加员工
     */
    int addEmployee(EmployeeDto employeeDto);

    /**
     * 根据员工id更新员工，可以不加@Param("emp")，这里是为了演示
     */
    int updateById(@Param("emp") EmployeeDto employeeDto);

    /**
     * 根据Id删除员工
     */
    int deleteById(@Param("id") String id);
}

  这里需要提到一个注解是@Param，就相当于给参数弄了一个别名，举个例子，如果你Mapper接口定义的是：

	EmployeeDto getById(@Param("empId") String id);

那么对应的xml配置就是

    <select id="getById" parameterType="string" resultType="EmployeeDto">
		SELECT
			e.id,e.emp_name as name,e.age,e.salary,e.department,e.hire_date as hireDate
		FROM
			tb_inf_employee e
		WHERE
			e.id= #{empId}
  	</select>

这里使用是 #{empId} 而不是 #{id} ，另外还举一个例子，针对对象别名的：

	int updateById(@Param("emp") EmployeeDto employeeDto);

那么对应的xml配置就是

	<update id="updateById" parameterType="EmployeeDto">
        update tb_inf_employee
        set emp_name=#{emp.name}, age=#{emp.age},salary=#{emp.salary},
        department=#{emp.department},hire_date=#{emp.hireDate}
        where id=#{emp.id}
    </update>

这里如果你不使用 #{emp.属性} 是会报错的，如果你没有加@Param，那么你直接引用pojo里的属性即可，更多的知识，可以参考下面映射文件里的注释。

4.3、映射文件

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接口的路径-->
<mapper namespace="com.alian.mybatis.mapper.EmployeeMapper">

    <!--property是pojo（EmployeeDto）的属性，column是数据库表（tb_inf_employee）列名，也可以是表列名的别名,也就是查询时时as后面的名称-->
    <resultMap type="com.alian.mybatis.dto.EmployeeDto" id="EmployeeResultMap">
        <id property="id" column="id"/>
        <result property="name" column="emp_name"/>
        <result property="age" column="age"/>
        <result property="salary" column="salary"/>
        <result property="department" column="department"/>
        <result property="hireDate" column="hire_date"/>
    </resultMap>

    <!--select,insert,update,delete标签里的id对应接口里方法名称-->
    <!--resultMap指的是外部的结果集，需要提前定义好，resultMap的值和定义的id的值一致-->
    <select id="findAll" resultMap="EmployeeResultMap">
        select
			id,
			emp_name,
			age,
			salary,
			department,
			hire_date
        from
        	tb_inf_employee
    </select>

    <!--查出来的值映射到结果上，如果属性不匹配可以使用as,比如：e.emp_name as name-->
    <select id="getById" parameterType="string" resultType="EmployeeDto">
		SELECT
			e.id,
			e.emp_name as name,
			e.age,
			e.salary,
			e.department,
			e.hire_date as hireDate
		FROM
			tb_inf_employee e
		WHERE
			e.id= #{id}
  	</select>

    <!--可以使用EmployeeDto，因为配置了mybatis.type-aliases-package: com.alian.mybatis.dto-->
    <!--value后面的属性都是EmployeeDto的属性，如：#{id},#{name},#{age},#{salary},#{department},#{hireDate}-->
    <!--如果想返回id,则需指定useGeneratedKeys="true" keyProperty="id"-->
    <!--此结果返回1表示是插入成功，否则失败（抛出异常），并且指定useGeneratedKeys="true" keyProperty="id"会把这个值赋值到EmployeeDto里-->
    <insert id="addEmployee" parameterType="com.alian.mybatis.dto.EmployeeDto" useGeneratedKeys="true" keyProperty="id">
        insert into tb_inf_employee(id,emp_name,age,salary,department,hire_date)
             values(#{id},#{name},#{age},#{salary},#{department},#{hireDate})
    </insert>

    <!--可以使用EmployeeDto，因为配置了mybatis.type-aliases-package: com.alian.mybatis.dto-->
    <!--此处使用了@Param("emp")指明了参数的名称进行演示，所以参数需要用emp,如果没有指定，则可以去除，使用EmployeeDto的属性名称即可-->
    <update id="updateById" parameterType="EmployeeDto">
        update tb_inf_employee set 
	        emp_name=#{emp.name}, 
	        age=#{emp.age},
	        salary=#{emp.salary},
	        department=#{emp.department},
	        hire_date=#{emp.hireDate}
        where 
        	id=#{emp.id}
    </update>

    <delete id="deleteById" parameterType="string">
        delete from tb_inf_employee where id=#{id}
    </delete>

</mapper>

这里需要注意的几点：

• namespace的值要配置成mapper接口的全路径
• select，insert，update，delete标签里的id对应接口里方法名称
• resultMap 里property是pojo的属性，column是数据库表列名，也可以是表列名的别名，也就是查询时时as后面的名称
• resultType映射结果的时候，查询的字段需要和pojo的属性一样，如果不一致，查询时可以使用as来映射成一样
• 配置了mybatis.type-aliases-package，parameterType类型是pojo，那么就可以省略全路径，直接写pojo
• 为了防止sql注入，建议都是用#{}，而不是${}

五、测试

5.1、接口

IEmployeeService.java

package com.alian.mybatis.service;

import com.alian.mybatis.dto.EmployeeDto;

import java.util.List;

public interface IEmployeeService {

    /**
     * 查询所有员工信息
     */
    List<EmployeeDto> findAll();

    /**
     * 根据id获取员工
     */
    EmployeeDto getById(String id);

    /**
     * 添加员工
     */
    int addEmployee(EmployeeDto employeeDto);

    /**
     * 根据员工id更新员工
     */
    int updateById(EmployeeDto employeeDto);

    /**
     * 根据Id删除员工
     */
    int deleteById(String id);
}

5.2、实现类

EmployeeService.java

package com.alian.mybatis.service.impl;

import com.alian.mybatis.dto.EmployeeDto;
import com.alian.mybatis.mapper.EmployeeMapper;
import com.alian.mybatis.service.IEmployeeService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;


@Slf4j
@Service
public class EmployeeService implements IEmployeeService{

    @Autowired
    private EmployeeMapper employeeMapper;

    @Override
    public List<EmployeeDto> findAll() {
        return employeeMapper.findAll();
    }

    @Override
    public EmployeeDto getById(String id) {
        return employeeMapper.getById(id);
    }

    @Override
    public int addEmployee(EmployeeDto employeeDto) {
        return employeeMapper.addEmployee(employeeDto);
    }

    @Override
    public int updateById(EmployeeDto employeeDto) {
        return employeeMapper.updateById(employeeDto);
    }

    @Override
    public int deleteById(String id) {
        return employeeMapper.deleteById(id);
    }

}

5.3、测试类

TestMybatisService.java

package com.alian.mybatis.service;

import com.alian.mybatis.dto.EmployeeDto;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.time.LocalDate;
import java.util.List;

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestMybatisService {

    @Autowired
    private IEmployeeService employeeService;

    /**
     * 测试查询所有
     */
    @Test
    public void findAll() {
        List<EmployeeDto> employeeDtoList = employeeService.findAll();
        log.info("查询所有的结果：{}", employeeDtoList.toString());
    }

    /**
     * 测试根据id查询员工
     *
     * @return
     */
    @Test
    public void getById() {
        String id = "BAT10001";
        EmployeeDto employeeDto = employeeService.getById(id);
        log.info("根据id查询员工结果：{}", employeeDto);
    }

    /**
     * 测试添加员工
     */
    @Test
    public void add() {
        EmployeeDto employeeDto = new EmployeeDto();
        employeeDto.setId("BAT10007");
        employeeDto.setName("昊天");
        employeeDto.setAge(25);
        employeeDto.setDepartment("测试部");
        employeeDto.setSalary(12000);
        employeeDto.setHireDate(LocalDate.of(2020, 5, 20));
        int result = employeeService.addEmployee(employeeDto);
        log.info("添加的结果：{}", result);
    }

    /**
     * 测试更新员工信息
     */
    @Test
    public void update() {
        String id = "BAT10007";
        EmployeeDto employeeDto = employeeService.getById(id);
        if (employeeDto == null) {
            log.error("员工信息不存在");
            return;
        }
        employeeDto.setSalary(20000);
        employeeDto.setAge(25);
        int result = employeeService.updateById(employeeDto);
        log.info("修改的结果：{}", result);
    }

    /**
     * 测试根据id删除员工信息
     */
    @Test
    public void delete() {
        String id = "BAT10007";
        EmployeeDto employeeDto = employeeService.getById(id);
        if (employeeDto == null) {
            log.error("员工信息不存在：{}", id);
            return;
        }
        int result = employeeService.deleteById(id);
        log.info("删除的结果：{}", result);
    }

}

5.4、运行结果

5.4.1、查询所有员工

==>  Preparing: select id, emp_name, age, salary, department, hire_date from tb_inf_employee
==> Parameters: 
<==    Columns: id, emp_name, age, salary, department, hire_date
<==        Row: BAT10001, 梁南生, 32, 32000.0, 研发部, 2020-05-20
<==        Row: BAT10002, 唐鹏, 31, 25000.0, 研发部, 2015-03-01
<==        Row: BAT10003, 王林, 30, 22000.0, 研发部, 2015-08-01
<==        Row: BAT10004, 罗考聪, 35, 7000.0, 测试部, 2016-11-20
<==        Row: BAT10005, 包雅馨, 25, 7000.0, 财务部, 2018-07-01
<==        Row: BAT10006, 朱健儿, 26, 8000.0, 人事部, 2019-01-01
<==      Total: 6
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7e77678c]
2021-11-03 15:25:53 223 [main] INFO findAll 28:查询所有的结果：[EmployeeDto(id=BAT10001, name=梁南生, age=32, salary=32000.0, department=研发部, hireDate=2020-05-20), EmployeeDto(id=BAT10002, name=唐鹏, age=31, salary=25000.0, department=研发部, hireDate=2015-03-01), EmployeeDto(id=BAT10003, name=王林, age=30, salary=22000.0, department=研发部, hireDate=2015-08-01), EmployeeDto(id=BAT10004, name=罗考聪, age=35, salary=7000.0, department=测试部, hireDate=2016-11-20), EmployeeDto(id=BAT10005, name=包雅馨, age=25, salary=7000.0, department=财务部, hireDate=2018-07-01), EmployeeDto(id=BAT10006, name=朱健儿, age=26, salary=8000.0, department=人事部, hireDate=2019-01-01)]

5.4.2、查询单个员工

==>  Preparing: SELECT e.id, e.emp_name as name, e.age, e.salary, e.department, e.hire_date as hireDate FROM tb_inf_employee e WHERE e.id= ?
==> Parameters: BAT10001(String)
<==    Columns: id, name, age, salary, department, hireDate
<==        Row: BAT10001, 梁南生, 32, 32000.0, 研发部, 2020-05-20
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cd7d5e1]
2021-11-03 15:26:47 709 [main] INFO getById 40:根据id查询员工结果：EmployeeDto(id=BAT10001, name=梁南生, age=32, salary=32000.0, department=研发部, hireDate=2020-05-20)

5.4.3、添加员工

==>  Preparing: insert into tb_inf_employee(id,emp_name,age,salary,department,hire_date) values(?,?,?,?,?,?)
==> Parameters: BAT10007(String), 昊天(String), 25(Integer), 12000.0(Double), 测试部(String), 2020-05-20(LocalDate)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5103eea2]
2021-11-03 15:27:35 313 [main] INFO add 56:添加的结果：1

5.4.4、修改员工

==>  Preparing: SELECT e.id, e.emp_name as name, e.age, e.salary, e.department, e.hire_date as hireDate FROM tb_inf_employee e WHERE e.id= ?
==> Parameters: BAT10007(String)
<==    Columns: id, name, age, salary, department, hireDate
<==        Row: BAT10007, 昊天, 25, 12000.0, 测试部, 2020-05-20
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cd7d5e1]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@211a9647] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@28f878a0] will not be managed by Spring
==>  Preparing: update tb_inf_employee set emp_name=?, age=?,salary=?,department=?,hire_date=? where id=?
==> Parameters: 昊天(String), 25(Integer), 20000.0(Double), 测试部(String), 2020-05-20(LocalDate), BAT10007(String)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@211a9647]
2021-11-03 15:29:34 380 [main] INFO update 73:修改的结果：1

5.4.5、删除员工

==>  Preparing: SELECT e.id, e.emp_name as name, e.age, e.salary, e.department, e.hire_date as hireDate FROM tb_inf_employee e WHERE e.id= ?
==> Parameters: BAT10007(String)
<==    Columns: id, name, age, salary, department, hireDate
<==        Row: BAT10007, 昊天, 25, 20000.0, 测试部, 2020-05-20
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@762405bf]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@17884d] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4d0753c9] will not be managed by Spring
==>  Preparing: delete from tb_inf_employee where id=?
==> Parameters: BAT10007(String)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@17884d]
2021-11-03 15:30:44 463 [main] INFO delete 88:删除的结果：1

结语

好了，非常简单的整合MyBatis，及其简单的增删查改就完成了，当然MyBatis远不止这些，后续有时间，我会发布相关博文。