13、Spring Boot：使用MyBatis操作数据库_同一张表可以对应多张mapper吗-优快云博客

本文链接：https://blog.youkuaiyun.com/qtdywp/article/details/85329243

目标：使用MyBatis对数据库中的Employee表增删改查。

环境：MySQL数据库，版本8.0.13，库名叫test1

1、启动MySQL数据库，建表：

CREATE TABLE
    Employee
    (
        ID bigint NOT NULL AUTO_INCREMENT,
        user_name VARCHAR(100),
        pass_word VARCHAR(100),
        login_time DATETIME,
        remark VARCHAR(500),
        PRIMARY KEY (ID)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、pom.xml中添加相关依赖

    <dependencies>
         <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-web</artifactId>
         </dependency>
         
         <dependency>
             <groupId>org.mybatis.spring.boot</groupId>
             <artifactId>mybatis-spring-boot-starter</artifactId>
             <version>1.3.2</version>
         </dependency>

         <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-devtools</artifactId>
             <scope>runtime</scope>
         </dependency>
         
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <scope>runtime</scope>
         </dependency>
         
         <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-test</artifactId>
             <scope>test</scope>
         </dependency>
     </dependencies>

3、application.properties中添加配置

spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/test1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = 12345678

logging.level.com.example.MyBatisDemo.mapper=debug

这里需要注意：

从MySQL5.X以后的版本（不包括5.X）开始，数据库连接驱动使用com.mysql.cj.jdbc.Driver
旧版本的数据库连接驱动为com.mysql.jdbc.Driver
使用新的数据库驱动时要加上时区serverTimezone=Asia/Shanghai，如果不加则默认为格林威治标准时间。
打印日志配置分为logging.level.加mapper包的位置两部分组成。

4、编写与数据库表对应的Entity类

package com.example.MyBatisDemo.entity;

import java.io.Serializable;
import java.util.Date;

public class EmployeeEntity implements Serializable
{
     private static final long serialVersionUID = 1L;

     private Long id;
     private String userName;
     private String password;
     private Date loginTime;
     private String remark;
     
     public Long getId()
     {
         return id;
     }
     public void setId(Long id)
     {
         this.id = id;
     }
     public String getUserName()
     {
         return userName;
     }
     public void setUserName(String userName)
     {
         this.userName = userName;
     }
     public String getPassword()
     {
         return password;
     }
     public void setPassword(String password)
     {
         this.password = password;
     }
     public Date getLoginTime()
     {
         return loginTime;
     }
     public void setLoginTime(Date loginTime)
     {
         this.loginTime = loginTime;
     }
     public String getRemark()
     {
         return remark;
     }
     public void setRemark(String remark)
     {
         this.remark = remark;
     }
}

5、编写Mapper（交由Spring托管的Java接口，增删改查操作都写到这里）

一般来说，一个数据库表的操作，对应一个Mapper。但把多张表的操作都写到一个Mapper里也不会报错，只是不够规范。

package com.example.MyBatisDemo.mapper;

import java.util.LinkedHashMap;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.example.MyBatisDemo.entity.EmployeeEntity;

public interface EmployeeMapper
{
     // #{}中的名称要和Entity类中的属性名一致
     @Insert("INSERT INTO Employee(user_name, pass_word, login_time, remark) VALUES(#{userName}, #{password}, #{loginTime}, #{remark})")
     void insert(EmployeeEntity employeeEntity);
     
     // 由于数据库字段名和Entity中的属性不完全一致，因此这里需要编写映射字段。
     @Select("SELECT * FROM Employee WHERE user_name = '#{userName}'")
     @Results(id="EmployeeResults", value={@Result(column="user_name", property="userName"),
           @Result(column="pass_word", property="password"),
           @Result(column="login_time", property="loginTime")}) // remark的映射就不用写了，因为数据库列名称和Entity类字段完全一样。
     EmployeeEntity getEmployeeByUserName(String userName);
     
     // 直接使用定义好的@Results
     @Select("SELECT * FROM Employee")
     @ResultMap("EmployeeResults")
     List<EmployeeEntity> getAll();
     
     // 不用Entity类接收返回值(省略写@Results的麻烦)
     @Select("SELECT * FROM Employee")
     List<LinkedHashMap<String,Object>> getAll_NoEntity();
     
     // 注意这里用$号(相当于拼接字符串)
     @Update("UPDATE Employee SET remark='${remark}' WHERE user_name = '${userName}'")
     void update(@Param("userName")String userName, @Param("remark")String remark);
     
     // 注意这里用#号（相当于？号传参）
     @Delete("DELETE FROM Employee WHERE ID=#{id}")
     void delete(Long id);
     
     @Delete("DELETE FROM Employee WHERE user_name = '${userName}'")
     void deleteByUserName(@Param("userName")String userName);
}

6、新建配置类，加入对自定义Mapper的扫描注解

package com.example.MyBatisDemo.config;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.example.MyBatisDemo.mapper")// 这个也可以直接写到Spring Boot的启动类上
public class AppConfig
{
}

到此所有的配置工作都完成了，开始测试。

测试类：

package com.example.MyBatisDemo;

import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

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 com.example.MyBatisDemo.entity.EmployeeEntity;
import com.example.MyBatisDemo.mapper.EmployeeMapper;

@RunWith(SpringRunner.class)
@SpringBootTest
public class MyBatisTest
{
     @Autowired
     EmployeeMapper employeeMapper;
     
     @Test
     public void TestInsert()
     {
         EmployeeEntity employeeEntity = new EmployeeEntity();
         employeeEntity.setUserName("wang");
         employeeEntity.setPassword("123456");
         employeeEntity.setLoginTime(new Date());
         employeeEntity.setRemark("备注XXX");
         employeeMapper.insert(employeeEntity);
         
         EmployeeEntity employeeEntity2 = new EmployeeEntity();
         employeeEntity2.setUserName("wang2");
         employeeEntity2.setPassword("888888");
         employeeEntity2.setLoginTime(new Date());
         employeeEntity2.setRemark("备注YYY");
         employeeMapper.insert(employeeEntity2);
     }
     
     @Test
     public void testSelect()
     {
         List<EmployeeEntity> employeeEntitys = employeeMapper.getAll();
         for (EmployeeEntity employeeEntity : employeeEntitys)
         {
             System.out.println(employeeEntity.getUserName());
             System.out.println(employeeEntity.getPassword());
             System.out.println(employeeEntity.getLoginTime());
             System.out.println(employeeEntity.getRemark());
             System.out.println("--------------");
         }
     }
     
     @Test
     public void testSelectNoEntity()
     {
         List<LinkedHashMap<String,Object>> employeeEntitys = employeeMapper.getAll_NoEntity();
         for (LinkedHashMap<String, Object> hashMap : employeeEntitys)
         {
             for (String key : hashMap.keySet())
             {
                 System.out.println(key + ":" + hashMap.get(key));
             }
             System.out.println("--------------");
         }
     }
     
     @Test
     public void testUpdate() throws Exception 
     {
         employeeMapper.update("wang2", "KKKKKKKKK");
     }
     
     @Test
     public void testDelete() throws Exception 
     {
         employeeMapper.deleteByUserName("wang");
     }
     
}

补充1：

如果数据库字段是下划线分隔，Entity类中的字段是驼峰命名的导致无法匹配，则可以开启MyBatis的驼峰命名转换。例如：

数据库字段	Entity类
user_name	userName
pass_word	password
login_time	loginTime

开启方法：

在application.properties中添加以下配置

mybatis.configuration.map-underscore-to-camel-case=true

补充2：

为什么MySQL从5.7版本一下子变成8版本了？

The first question people are likely to ask: If the most recent version of MySQL was a 5.x release, why's this one 8.0? For one, version 6.0 was canned as part of the transition to "more frequent and timely releases of the MySQL Server." Version 7 was reserved for the clustering version of MySQL. That leaves 8.x as the next major version number for the common use-case version of MySQL.