【MyBatis】MyBatis 操作数据库

---

本篇文章，主要包含以下几部分：

1. 使用MyBatis完成简单的增删改查操作, 参数传递.
2. 掌握MyBatis的两种写法: 注解和XML方式
3. 掌握MyBatis相关的日志配置

前言

在应用分层学习时, 我们了解到web应用程序⼀般分为三层，即：Controller、Service、Dao . 之前的案例中，请求流程如下: 浏览器发起请求, 先请求Controller, Controller接收到请求之后, 调用Service进行业务逻辑处理, Service再调用Dao, 但是Dao层的数据是Mock的, 真实的数据应该从数据库中读取. 我们学习MySQL数据库时，已经学习了JDBC来操作数据库, 但是JDBC操作太复杂了

JDBC 操作示例回顾

1. 创建数据库连接池 DataSource
2. 通过 DataSource 获取数据库连接 Connection
3. 编写要执行带 ? 占位符的 SQL 语句
4. 通过 Connection 及 SQL 创建操作命令对象 Statement
5. 替换占位符：指定要替换的数据库字段类型，占位符索引及要替换的值
6. 使⽤ Statement 执行 SQL 语句
7. 查询操作：返回结果集 ResultSet，更新操作：返回更新的数量
8. 处理结果集
9. 释放资源

下⾯的⼀个完整案例，展示了通过 JDBC 的 API 向数据库中添加⼀条记录，修改⼀条记录，查询⼀条记录的操作。

-- 创建数据库
create database if not exists library default character set utf8mb4;
-- 使⽤数据库
use library;
-- 创建表
create table if not exists soft_bookrack (
 book_name varchar(32) NOT NULL,
 book_author varchar(32) NOT NULL,
 book_isbn varchar(32) NOT NULL primary key
);

以下是 JDBC 操作的具体实现代码：

package com.example.demo.mapper;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

    public class SimpleJdbcOperation {
        private final DataSource dataSource;

        public SimpleJdbcOperation(DataSource dataSource) {
            this.dataSource = dataSource;
        }

        /**
         * 添加⼀本书
         */
        public void addBook() {
            Connection connection = null;
            PreparedStatement stmt = null;
            try {
//获取数据库连接
                connection = dataSource.getConnection();
                //创建语句
                stmt = connection.prepareStatement(
                        "insert into soft_bookrack (book_name, book_author, 
                        book_isbn) values( ?,?,?);
                "
 );
                //参数绑定
                stmt.setString(1, "Spring in Action");
                stmt.setString(2, "Craig Walls");
                stmt.setString(3, "9787115417305");
                //执⾏语句
                stmt.execute();
            } catch (SQLException e) {
                //处理异常信息
            } finally {
                //清理资源
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    //
                }
            }
        }

        /**
         * 更新⼀本书
         */
        public void updateBook() {
            Connection connection = null;
            PreparedStatement stmt = null;
            try {
			//获取数据库连接 connection = dataSource.getConnection();
			//创建语句 stmt = connection.prepareStatement(
                "update soft_bookrack set book_author=? where book_isbn=?;"
);
				//参数绑定 stmt.setString(1, "张卫滨");
                stmt.setString(2, "9787115417305");
				//执⾏语句 stmt.execute(); } catch (SQLException e) {
				//处理异常信息 } finally {
				//清理资源
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    //
                }
            }
        }

        /**
         * 查询⼀本书
         */
        public void queryBook() {
            Connection connection = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            Book book = null;
            try {
                //获取数据库连接
                connection = dataSource.getConnection();
                //创建语句
                stmt = connection.prepareStatement(
                        "select book_name, book_author, book_isbn from 
                        soft_bookrack where book_isbn = ? "
 );
                //参数绑定
                stmt.setString(1, "9787115417305");
                //执⾏语句
                rs = stmt.executeQuery();
                if (rs.next()) {
                    book = new Book();
                    book.setName(rs.getString("book_name"));
                    book.setAuthor(rs.getString("book_author"));
                    book.setIsbn(rs.getString("book_isbn"));
                }
                System.out.println(book);
            } catch (SQLException e) {
                //处理异常信息
            } finally {
                //清理资源
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                	//处理异常信息
                }
            }
        }

        public static class Book {
            private String name;
            private String author;
            private String isbn;
            //省略 setter getter ⽅法
        }
    }

从上述代码和操作流程可以看出，对于 JDBC 来说，整个操作⾮常的繁琐，我们不但要拼接每⼀个参 数，⽽且还要按照模板代码的⽅式，一步步的操作数据库，并且在每次操作完，还要⼿动关闭连接 等，而所有的这些操作步骤都需要在每个⽅法中重复书写. 那有没有⼀种⽅法，可以更简单、更⽅便的 操作数据库呢？ 答案是肯定的，这就是我们要学习 MyBatis 的真正原因，它可以帮助我们更方便、更快速的操作数据库.

一、什么是MyBatis？

• MyBatis是⼀款优秀的 持久层框架，用于简化JDBC的开发。
• MyBatis本是 Apache的⼀个开源项目iBatis，2010年这个项目由apache迁移到了google code，并且改名为MyBatis 。2013年11月迁移到Github。

在上⾯我们提到⼀个词：持久层

• 持久层：指的就是持久化操作的层, 通常指数据访问层(dao), 是用来操作数据库的.

简单来说 MyBatis 是更简单完成程序和数据库交互的框架，也就是更简单的操作和读取数据库工具接下来，我们就通过⼀个入门程序，让大家感受⼀下通过Mybatis如何来操作数据库

二、MyBatis入门

Mybatis操作数据库的步骤：

1. 准备工作(创建springboot工程、数据库表准备、实体类)
2. 引入Mybatis的相关依赖，配置Mybatis(数据库连接信息)
3. 编写SQL语句(注解/XML)
4. 测试

2.1、准备工作

2.1.1 创建工程

创建springboot工程，并导入mybatis的起步依赖、mysql的驱动包

Mybatis 是⼀个持久层框架, 具体的数据存储和数据操作还是在MySQL中操作的, 所以需要添加MySQL驱动

项目工程创建完成后，自动在pom.xml⽂件中，导⼊Mybatis依赖和MySQL驱动依赖

<!--Mybatis 依赖包-->
<dependency>
 <groupId>org.mybatis.spring.boot</groupId>
 <artifactId>mybatis-spring-boot-starter</artifactId>
 <version>2.3.1</version>
</dependency>
<!--mysql驱动包-->
<dependency>
 <groupId>com.mysql</groupId>
 <artifactId>mysql-connector-j</artifactId>
 <scope>runtime</scope>
</dependency>

2.1.2、数据准备

-- 创建数据库
DROP DATABASE IF EXISTS mybatis_test;
CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
-- 使⽤数据数据
USE mybatis_test;
-- 创建表[⽤⼾表]
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `username` VARCHAR ( 127 ) NOT NULL,
 `password` VARCHAR ( 127 ) NOT NULL,
 `age` TINYINT ( 4 ) NOT NULL, `gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-⼥ 0-默认',
 `phone` VARCHAR ( 15 ) DEFAULT NULL,
 `delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
 `create_time` DATETIME DEFAULT now(),
 `update_time` DATETIME DEFAULT now(),
 PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; 
-- 添加⽤⼾信息
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );

创建对应的实体类 UserInfo

实体类的属性名与表中的字段名⼀⼀对应

import lombok.Data;
import java.util.Date;
@Data
public class UserInfo {
 private Integer id;
 private String username;
 private String password;
 private Integer age;
 private Integer gender;
 private String phone;
 private Integer deleteFlag;
 private Date createTime;
 private Date updateTime;
}

2.2、配置数据库连接字符串

Mybatis中要连接数据库，需要数据库相关参数配置

• MySQL驱动类
• 登录名
• 密码
• 数据库连接字符串

如果是application.yml⽂件, 配置内容如下

# 数据库连接配置
spring:
 datasource:
 url: jdbc:mysql://127.0.0.1:3306/mybatis_test?
characterEncoding=utf8&useSSL=false
 username: root
 password: root
 driver-class-name: com.mysql.cj.jdbc.Driver

注意事项： 如果使用MySQL 是 5.x 之前的使用的是"com.mysql.jdbc.Driver"，如果是⼤于 5.x 使用的是“com.mysql.cj.jdbc.Driver”.

如果是application.properties文件, 配置内容如下:

#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis_test?
characterEncoding=utf8&useSSL=false
#连接数据库的⽤⼾名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=root

2.3、写持久层代码

在项目中, 创建持久层接口UserInfoMapper

在UserInfo接口中编写以下代码

import com.example.demo.model.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface UserInfoMapper {
 //查询所有⽤⼾
 @Select("select username, `password`, age, gender, phone from userinfo")
 public List<UserInfo> queryAllUser();
}

Mybatis的持久层接口规范⼀般都叫 XxxMapper
@Mapper注解：表示是MyBatis中的Mapper接口
• 程序运行时, 框架会自动生成接口的实现类对象(代理对象)，并给交Spring的IOC容器管理
• @Select注解：代表的就是select查询，也就是注解对应方法的具体实现内容

2.4 单元测试

在创建出来的SpringBoot⼯程中，在src下的test目录下，已经目动帮我们创建好了测试类 ，我们可以直接使用这个测试类来进行测试

import com.example.demo.mapper.UserInfoMapper;
import com.example.demo.model.UserInfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;

    @SpringBootTest
    class DemoApplicationTests {
        @Autowired
        private UserInfoMapper userInfoMapper;

        @Test
        void contextLoads() {
            List<UserInfo> userInfoList = userInfoMapper.queryAllUser();
            System.out.println(userInfoList);
        }
    }

测试类上添加了注解 @SpringBootTest，该测试类在运⾏时，就会⾃动加载Spring的运行环境. 我们通过@Autowired这个注解, 注入我们要测试的类, 就可以开始进行测试了

运行结果如下:

返回结果中, 可以看到, 只有SQL语句中查询的列对应的属性才有赋值

三、MyBatis的基础操作

上⾯我们学习了Mybatis的查询操作, 接下来我们学习MyBatis的增, 删, 改操作
在学习这些操作之前, 我们先来学习MyBatis日志打印

3.1 打印日志

在Mybatis当中我们可以借助日志, 查看到sql语句的执行、执行传递的参数以及执行结果 在配置文件中进行配置即可

mybatis: 
  configuration: # 配置打印 MyBatis⽇志
	  log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

如果是application.properties, 配置内容如下:

#指定mybatis输出⽇志的位置, 输出控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

注意: 后续配置项, 默认只提供⼀种, 请自行进行配置项转换

重新运行程序, 可以看到SQL执行内容, 以及传递参数和执行结果

①: 查询语句
②: 传递参数及类型
③: SQL执行结果

3.2、参数传递

需求: 查找id=4的用户,对应的SQL就是: select * from userinfo where id=4

@Select("select username, `password`, age, gender, phone from userinfo where id= 4 ")
UserInfo queryById();

但是这样的话, 只能查找id=4 的数据, 所以SQL语句中的id值不能写成固定数值，需要变为动态的数值解决方案：在queryById方法中添加⼀个参数(id)，将⽅法中的参数，传给SQL语句 使用 #{} 的方式获取方法中的参数

@Select("select username, `password`, age, gender, phone from userinfo where id= #{id} ")
UserInfo queryById(Integer id);

如果mapper接口方法形参只有⼀个普通类型的参数，#{…} ⾥⾯的属性名可以随便写，如：#{id}、#{value}。建议和参数名保持一致

添加测试用例

@Test
void queryById() {
 UserInfo userInfo = userInfoMapper.queryById(4);
 System.out.println(userInfo);
}

运行结果：

也可以通过 @Param , 设置参数的别名, 如果使用@Param 设置别名, #{…}里面的属性名必须和@Param 设置的⼀样

@Select("select username, `password`, age, gender, phone from userinfo where id= #{userid} ")
UserInfo queryById(@Param("userid") Integer id);

3.3、增(Insert)

SQL 语句:

insert into userinfo (username, `password`, age, gender, phone) values("zhaoliu","zhaoliu",19,1,"18700001234")

把SQL中的常量替换为动态的参数
Mapper接口：

@Insert("insert into userinfo (username, `password`, age, gender, phone) values (#{username},#{password},#{age},#{gender},#{phone})")
Integer insert(UserInfo userInfo);

直接使用UserInfo对象的属性名来获取参数

测试代码：

@Test
void insert() {
 UserInfo userInfo = new UserInfo();
 userInfo.setUsername("zhaoliu");
 userInfo.setPassword("zhaoliu");
 userInfo.setGender(2);
 userInfo.setAge(21);
 userInfo.setPhone("18612340005");
 userInfoMapper.insert(userInfo);
}

运行后, 观察数据库执行结果

如果设置了 @Param 属性, #{…} 需要使用参数属性来获取

@Insert("insert into userinfo (username, `password`, age, gender, phone) values (#{userinfo.username},#{userinfo.password},#{userinfo.age},#{userinfo.gender},#{userinfo.phone})")
Integer insert(@Param("userinfo") UserInfo userInfo);

返回主键
Insert 语句默认返回的是受影响的行数，但有些情况下, 数据插入之后, 还需要有后续的关联操作, 需要获取到新插入数据的id

⽐如订单系统
当我们下完订单之后, 需要通知物流系统, 库存系统, 结算系统等, 这时候就需要拿到订单ID

如果想要拿到自增id, 需要在Mapper接⼝的⽅法上添加⼀个Options的注解

@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into userinfo (username, age, gender, phone) values (#{userinfo.username},#{userinfo.age},#{userinfo.gender},#{userinfo.phone})")
Integer insert(@Param("userinfo") UserInfo userInfo);

• useGeneratedKeys：这会令MyBatis使用JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的主键（比如：像 MySQL 和 SQL Server 这样的关系型数据库管理系统的自动递增字段），默认值：false.
• keyProperty：指定能够唯⼀识别对象的属性，MyBatis 会使用getGeneratedKeys 的返回值或insert 语句的 selectKey 子元素设置它的值，默认值：未设置（unset）

测试数据：

  @Test
    void insert() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("zhaoliu");
        userInfo.setPassword("zhaoliu");
        userInfo.setGender(2);
        userInfo.setAge(21);
        userInfo.setPhone("18612340005");
        Integer count = userInfoMapper.insert(userInfo);
        System.out.println("添加数据条数:" + count + ", 数据ID:" + userInfo.getId());
    }

运行结果:

注意: 设置 useGeneratedKeys=true 之后, 方法返回值依然是受影响的行数, 自增id 会设置在上述 keyProperty 指定的属性中.

3.4、 删(Delete)

SQL语句：

delete from userinfo where id=6

把SQL中的常量替换为动态的参数
Mapper接口：

@Delete("delete from userinfo where id = #{id}")
void delete(Integer id);

3.5、改(Update)

SQL 语句:

update userinfo set username="zhaoliu" where id=5

把SQL中的常量替换为动态的参数
Mapper接口：

@Update("update userinfo set username=#{username} where id=#{id}") 
void update(UserInfo userInfo);

3.6、查(Select)

我们在上⾯查询时发现, 有几个字段是没有赋值的, 只有Java对象属性和数据库字段⼀模⼀样时, 才会进行赋值 接下来我们多查询⼀些数据

@Select("select id, username, `password`, age, gender, phone, delete_flag, create_time, update_time from userinfo")
List<UserInfo> queryAllUser();

查询结果：

从运行结果上可以看到, 我们SQL语句中, 查询了delete_flag, create_time, update_time, 但是这几个属性却没有赋值

MyBatis 会根据方法的返回结果进行赋值.
方法用对象 UserInfo接收返回结果, MySQL 查询出来数据为⼀条, 就会自动赋值给对象.
方法用List接收返回结果, MySQL 查询出来数据为⼀条或多条时, 也会自动赋值给List.
但如果MySQL 查询返回多条, 但是方法使⽤UserInfo接收, MyBatis执行就会报错

原因分析:
当自动映射查询结果时，MyBatis 会获取结果中返回的列名并在 Java 类中查找相同名字的属性（忽略大小写）。这意味着如果发现了 ID 列和 id 属性，MyBatis 会将列 ID 的值赋给 id 属性

解决办法:

1. 起别名
2. 结果映射
3. 开启驼峰命名

3.6.1、起别名

在SQL语句中，给列名起别名，保持别名和实体类属性名⼀样

@Select("select id, username, `password`, age, gender, phone, delete_flag as deleteFlag,create_time as createTime, update_time as updateTime from userinfo")
public List<UserInfo> queryAllUser();

3.6.2 结果映射

   @Select("select id, username, `password`, age, gender, phone, delete_flag, 
            create_time, update_time from userinfo")
            @Results({
            @Result(column = "delete_flag", property = "deleteFlag"),
    @Result(column = "create_time", property = "createTime"),
    @Result(column = "update_time", property = "updateTime")
})
        List<UserInfo> queryAllUser();

如果其他SQL, 也希望可以复用这个映射关系, 可以给这个Results定义⼀个名称

@Select("select id, username, `password`, age, gender, phone, delete_flag, 
create_time, update_time from userinfo")
@Results(id = "resultMap",value = {
 @Result(column = "delete_flag",property = "deleteFlag"),
 @Result(column = "create_time",property = "createTime"),
 @Result(column = "update_time",property = "updateTime")
})
List<UserInfo> queryAllUser();
@Select("select id, username, `password`, age, gender, phone, delete_flag, create_time, update_time " +
 "from userinfo where id= #{userid} ")
@ResultMap(value = "resultMap")
UserInfo queryById(@Param("userid") Integer id);

3.6.3 开启驼峰命名

通常数据库列使用蛇形命名法进行命名(下划线分割各个单词), 而Java 属性⼀般遵循驼峰命名法约定.为了在这两种命名方式之间启用自动映射，需要将 mapUnderscoreToCamelCase 设置为 true。

mybatis:
 configuration:
  map-underscore-to-camel-case: true #配置驼峰⾃动转换

驼峰命名规则： abc_xyz => abcXyz
• 表中字段名：abc_xyz
• 类中属性名：abcXyz

Java 代码不做任何处理

@Select("select id, username, `password`, age, gender, phone, delete_flag as deleteFlag, create_time as createTime, update_time as updateTime from userinfo")
public List<UserInfo> queryAllUser();

添加上述配置, 运行代码：

四、MaBatis XML配置文件

Mybatis的开发有两种方式：

1. 注解
2. XML

上面学习了注解的方式, 接下来我们学习XML的方式

使用Mybatis的注解方式，主要是来完成⼀些简单的增删改查功能. 如果需要实现复杂的SQL功能，建议使用XML来配置映射语句，也就是将SQL语句写在XML配置文件中.

MyBatis XML的方式需要以下两步:

1. 配置数据库连接字符串和MyBatis
2. 写持久层代码

4.1、配置连接字符串和MyBatis

此步骤需要进行两项设置，数据库连接字符串设置和 MyBatis 的XML文件配置。

如果是application.yml文件, 配置内容如下:

# 数据库连接配置
spring:
 datasource:
  url: jdbc:mysql://127.0.0.1:3306/mybatis_test?
characterEncoding=utf8&useSSL=false
 username: root
 password: root
 driver-class-name: com.mysql.cj.jdbc.Driver
# 配置 mybatis xml 的⽂件路径，在 resources/mapper 创建所有表的 xml ⽂件
mybatis:
 mapper-locations: classpath:mapper/**Mapper.xml

如果是application.properties⽂件, 配置内容如下:

#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis_test?
characterEncoding=utf8&useSSL=false
#连接数据库的⽤⼾名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=root
# 配置 mybatis xml 的⽂件路径，在 resources/mapper 创建所有表的 xml ⽂件
mybatis.mapper-locations=classpath:mapper/**Mapper.xml

4.2、写持久层代码

持久层代码分两部分

1. ⽅法定义 Interface
2. ⽅法实现: XXX.xml

4.2.1、添加 mapper 接口

数据持久层的接口定义：

import com.example.demo.model.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserInfoXMlMapper {
 List<UserInfo> queryAllUser();
}

4.2.1、添加 UserInfoXMLMapper.xml

数据持久成的实现，MyBatis 的固定 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.example.demo.mapper.UserInfoMapper">
 
</mapper>

创建UserInfoXMLMapper.xml, 路径参考yml中的配置

查询所有用户的具体实现 ：

<?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.example.demo.mapper.UserInfoXMlMapper">
<select id="queryAllUser" resultType="com.example.demo.model.UserInfo">
        select username,`password`,age,gender,phone from userinfo
</select>
</mapper>

以下是对以上标签的说明：

• 标签：需要指定 namespace 属性，表示命名空间，值为 mapper 接⼝的全限定名，包括全包名.类名。
• 查询标签：是用来执行数据库的查询操作的：
◦ id ：是和 Interface （接口）中定义的方法名称⼀样的，表示对接口的具体实现方法。
◦ resultType ：是返回的数据类型，也就是开头我们定义的实体类

4.2.3、单元测试

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUser() {
        List<UserInfo> userInfoList = userInfoMapper.queryAllUser();
        System.out.println(userInfoList);
    }

}

运行结果如下:

4.3 增删改查操作

接下来，我们来实现⼀下用户的增加、删除和修改的操作.

4.3.1、增(Insert)

UserInfoMapper接口:

Integer insertUser(UserInfo userInfo);

UserInfoMapper.xml实现:

<insert id="insertUser">
 insert into userinfo (username, `password`, age, gender, phone) values (#
 {username}, #{password}, #{age},#{gender},#{phone})
</insert>

如果使用@Param设置参数名称的话, 使用方法和注解类似

UserInfoMapper接口:

Integer insertUser(@Param("userinfo") UserInfo userInfo)

UserInfoMapper.xml实现:

<insert id="insertUser">
 insert into userinfo (username, `password`, age, gender, phone) values(#{userinfo.username},#{userinfo.password},#{userinfo.age},
 #{userinfo.gender},#{userinfo.phone})
</insert>

返回自增 id

接口定义不变, Mapper.xml 实现 设置useGeneratedKeys 和keyProperty属性

<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
 insert into userinfo (username, `password`, age, gender, phone) values
 (#{userinfo.username},#{userinfo.password},#{userinfo.age},#
{userinfo.gender},#{userinfo.phone})
</insert>

4.3.2 删(Delete)

UserInfoMapper接口:

Integer deleteUser(Integer id);

UserInfoMapper.xml实现:

<delete id="deleteUser">
 delete from userinfo where id = #{id}
</delete>

4.3.3 改(Update)

UserInfoMapper接口:

Integer updateUser(UserInfo userInfo);

UserInfoMapper.xml实现:

<update id="updateUser"> 
 update userinfo set username=#{username} where id=#{id}
</update>

4.3.4 查(Select)

同样的, 使用XML 的方式进行查询, 也存在数据封装的问题 我们把SQL语句进行简单修改, 查询更多的字段内容

<select id="queryAllUser" resultType="com.example.demo.model.UserInfo">
 select id, username,`password`, age, gender, phone, delete_flag, 
create_time, update_time from userinfo
</select>

运行结果:

结果显示: deleteFlag, createTime, updateTime 也没有进行赋值
解决办法和注解类似:
. 起别名
. 结果映射
. 开启驼峰命名
其中1,3的解决办法和注解⼀样,不再多说, 接下来看下xml如果来写结果映射：

Mapper.xml：

<resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
<id column="id" property="id"></id>
<result column="delete_flag" property="deleteFlag"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</resultMap><select id="queryAllUser" resultMap="BaseMap">
        select id,username,`password`,age,gender,phone,delete_flag,
        create_time,update_time from userinfo
</select>

五、其他查询操作

5.1 多表查询

多表查询和单表查询类似, 只是SQL不同而已

5.1.1、准备工作

上面建了⼀张用户表, 我们再来建⼀张文章表, 进行多表关联查询. 文章表的uid, 对应用户表的id.

数据准备：

-- 创建⽂章表
DROP TABLE IF EXISTS articleinfo;
 
CREATE TABLE articleinfo (
 id INT PRIMARY KEY auto_increment,
 title VARCHAR ( 100 ) NOT NULL,
 content TEXT NOT NULL,
 uid INT NOT NULL,
 delete_flag TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
 create_time DATETIME DEFAULT now(),
 update_time DATETIME DEFAULT now() 
) DEFAULT charset 'utf8mb4';
-- 插⼊测试数据
INSERT INTO articleinfo ( title, content, uid ) VALUES ( 'Java', 'Java正⽂', 1
);

对应Model:

import lombok.Data;
import java.util.Date;
@Data
public class ArticleInfo {
 private Integer id;
 private String title;
 private String content;
 private Integer uid;
 private Integer deleteFlag;
 private Date createTime;
 private Date updateTime;
}

5.1.2、数据查询

需求: 根据uid查询作者的名称等相关信息
SQL:

SELECT
 ta.id,
 ta.title,
 ta.content,
 ta.uid,
 tb.username,
 tb.age,
 tb.gender 
FROM
 articleinfo ta
 LEFT JOIN userinfo tb ON ta.uid = tb.id 
WHERE
 ta.id =1

补充实体类:

@Data
public class ArticleInfo {
    private Integer id;
    private String title;
    private String content;
    private Integer uid;
    private Integer deleteFlag;
    private Date createTime;
    private Date updateTime;
    //⽤⼾相关信息
    private String username;
    private Integer age;
    private Integer gender;
}

接口定义:

import com.example.demo.model.ArticleInfo;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface ArticleInfoMapper {
    @Select("SELECT 
            ta.id, ta.title, ta.content, ta.uid, tb.username, tb.age, tb.gender" +
            "FROM articleinfo ta LEFT JOIN userinfo tb ON ta.uid = tb.id "+
            "WHERE ta.id = #{id}")
    ArticleInfo queryUserByUid(Integer id);
}

如果名称不⼀致的, 采用ResultMap, 或者别名的⽅式解决, 和单表查询⼀样Mybatis 不分单表还是多表, 主要就是三部分: SQL, 映射关系和实体类
通过映射关系, 把SQL运行结果和实体类关联起来.

5.2、#{} 和 ${}

MyBatis 参数赋值有两种方式, 咱们前⾯使用了 #{} 进行赋值, 接下来我们看下二者的区别

5.2.1 #{} 和${} 使用

1. 先看Interger类型的参数

@Select("select username, `password`, age, gender, phone from userinfo where id= #{id} ")
UserInfo queryById(Integer id);

观察我们打印的日志

发现我们输出的SQL语句：

select username, `password`, age, gender, phone from userinfo where id= ?

我们输⼊的参数并没有在后面拼接，id的值是使用 ? 进行占位. 这种SQL 我们称之为"预编译SQL"

MySQL 课程 JDBC编程使用的就是预编译SQL, 此处不再多说.

我们把 #{} 改成 ${} 再观察打印的日志:

@Select("select username, `password`, age, gender, phone from userinfo where id= ${id} ")
UserInfo queryById(Integer id);

可以看到, 这次的参数是直接拼接在SQL语句中了.
2. 接下来我们再看String类型的参数

@Select("select username, `password`, age, gender, phone from userinfo where username= #{name} ")
UserInfo queryByName(String name);

观察我们打印的日志, 结果正常返回：

我们把 #{} 改成 ${} 再观察打印的日志:

@Select("select username, `password`, age, gender, phone from userinfo where username= ${name} ")
UserInfo queryByName(String name);

可以看到, 这次的参数依然是直接拼接在SQL语句中了, 但是字符串作为参数时, 需要添加引号 ‘’ , 使 ⽤ ${} 不会拼接引号 ‘’ , 导致程序报错.
修改代码如下：

@Select("select username, `password`, age, gender, phone from userinfo where username= '${name}' ")
UserInfo queryByName(String name);

再次运行, 结果正常返回

从上面两个例子可以看出:
#{} 使用的是预编译SQL, 通过 ? 占位的方式, 提前对SQL进行编译, 然后把参数填充到SQL语句 中. #{} 会根据参数类型, 自动拼接引号 ‘’ .
${} 会直接进行字符替换, ⼀起对SQL进行编译. 如果参数为字符串, 需要加上引号 ‘’

参数为数字类型时, 也可以加上, 查询结果不变, 但是可能会导致索引失效, 性能下降

5.2.2、#{} 和 ${}区别

#{} 和 ${} 的区别就是预编译SQL和即时SQL的区别

简单回顾:
当客户发送⼀条SQL语句给服务器后, 大致流程如下:
1.解析语法和语义, 校验SQL语句是否正确
2.优化SQL语句, 制定执行计划
3.执行并返回结果 、
⼀条 SQL如果走上述流程处理, 我们称之为 Immediate Statements(即时 SQL)

1. 性能更高
   绝⼤多数情况下, 某⼀条 SQL 语句可能会被反复调用执行, 或者每次执行的时候只有个别的值不同(比如 select 的 where ⼦句值不同, update 的 set 子句值不同, insert 的 values 值不同). 如果每次都需要 经过上面的语法解析, SQL优化、SQL编译等，则效率就明显不行了

预编译SQL，编译⼀次之后会将编译后的SQL语句缓存起来，后⾯再次执行这条语句时，不会再次编译 (只是输入的参数不同), 省去了解析优化等过程, 以此来提高效率

2. 更安全(防止SQL注入)
   SQL注入：是通过操作输入的数据来修改事先定义好的SQL语句，以达到执行代码对服务器进行攻击的方法。

由于没有对用户输⼊进行充分检查，而SQL⼜是拼接而成，在用户输入参数时，在参数中添加⼀些SQL关键字，达到改变SQL运行结果的目的，也可以完成恶意攻击

sql 注⼊代码： ’ or 1='1
先来看看SQL注入的例子：

@Select("select username, `password`, age, gender, phone from userinfo where username= '${name}' ")
List<UserInfo> queryByName(String name);

测试代码:
正常访问情况：

@Test
void queryByName() {
 List<UserInfo> userInfos = userInfoMapper.queryByName("admin");
 System.out.println(userInfos);
}

结果运行正常

SQL注入场景:

@Test
void queryByName() {
 	List<UserInfo> userInfos = userInfoMapper.queryByName("' or 1='1");
 	System.out.println(userInfos);
}

结果依然被正确查询出来了, 其中参数 or被当做了SQL语句的⼀部分

可以看出来, 查询的数据并不是自己想要的数据. 所以用于查询的字段，尽量使用 #{} 预查询的方式

SQL注⼊是⼀种⾮常常见的数据库攻击手段, SQL注入漏洞也是网络世界中最普遍的漏洞之⼀. 如果发生在用户登录的场景中, 密码输人为 ’ or 1='1 , 就可能完成登录(不是⼀定会发生的场景, 需要看登录代码如何写)

5.3、排序功能

从上面的例子中, 可以得出结论: ${} 会有SQL注⼊的风险, 所以我们尽量使用#{}完成查询
既然如此, 是不是 ${} 就没有存在的必要性了呢?
当然不是

接下来我们看下${}的使⽤场景

Mapper实现

@Select("select id, username, age, gender, phone, delete_flag, create_time, 
update_time " +
 "from userinfo order by id ${sort} ")
List<UserInfo> queryAllUserBySort(String sort);

使用 ${sort} 可以实现排序查询, 而使用 #{sort} 就不能实现排序查询了.

注意: 此处 sort 参数为String类型, 但是SQL语句中, 排序规则是不需要加引号 ‘’ 的, 所以此时的${sort} 也不加引号

我们把 ${} 改成 #{}

@Select("select id, username, age, gender, phone, delete_flag, create_time, 
update_time " +
 "from userinfo order by id #{sort} ")
List<UserInfo> queryAllUserBySort(String sort);

运行结果:
可以发现, 当使用 #{sort} 查询时, asc 前后自动给加了引号, 导致 sql 错误

#{} 会根据参数类型判断是否拼接引号 ‘’
如果参数类型为String, 就会加上引号

除此之外, 还有表名作为参数时, 也只能使用 ${}

5.4、like查询

like 使用 #{} 报错

@Select("select id, username, age, gender, phone, delete_flag, create_time, 
update_time " +
 "from userinfo where username like '%#{key}%' ")
List<UserInfo> queryAllUserByLike(String key);

把 #{} 改成 $ {} 可以正确查出来, 但是${}存在SQL注入的问题, 所以不能直接使用 ${}.
解决办法: 使用 mysql 的内置函数 concat() 来处理，实现代码如下：

@Select("select id, username, age, gender, phone, delete_flag, create_time, 
update_time " +
 "from userinfo where username like concat('%',#{key},'%')")
List<UserInfo> queryAllUserByLike(String key);

六、数据库连接池

在上⾯Mybatis的讲解中, 我们使⽤了数据库连接池技术, 避免频繁的创建连接, 销毁连接 ，下面我们来了解下数据库连接池

6.1、介绍

数据库连接池负责分配、管理和释放数据库连接，它允许应⽤程序重复使⽤⼀个现有的数据库连接，而不是再重新建立⼀个.

没有使用数据库连接池的情况: 每次执行SQL语句, 要先创建⼀个新的连接对象, 然后执行SQL语句, SQL语句执行完, 再关闭连接对象释放资源. 这种重复的创建连接, 销毁连接比较消耗资源
使用数据库连接池的情况: 程序启动时, 会在数据库连接池中创建⼀定数量的Connection对象, 当客户请求数据库连接池, 会从数据库连接池中获取Connection对象, 然后执行SQL, SQL语句执行完, 再把Connection归还给连接池

优点:

1. 减少了网络开销
2. 资源重用
3. 提升了系统的性能

6.2 使用

常见的数据库连接池：
• C3P0
• DBCP
• Druid
• Hikari
目前比较流行的是 Hikari, Druid

1. Hikari : SpringBoot默认使用的数据库连接池
   

2. Druid
   如果我们想把默认的数据库连接池切换为Druid数据库连接池, 只需要引⼊相关依赖即可

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

运行结果:

• Druid连接池是阿里巴巴开源的数据库连接池项目
• 功能强大，性能优秀，是Java语言最好的数据库连接池之⼀

七、动态SQL

动态 SQL 是Mybatis的强大特性之⼀，能够完成不同条件下不同的 sql 拼接。

7.1、< if >标签

在注册用户的时候，可能会有这样⼀个问题，如下图所示：

注册分为两种字段：必填字段和非必填字段，那如果在添加用户的时候有不确定的字段传入，程序应该如何实现呢？ 这个时候就需要使用动态标签来判断了，比如添加的时候性别 gender 为⾮必填字段，具体实现如下：

接口定义:

Integer insertUserByCondition(UserInfo userInfo);

Mapper.xml实现：

<insert id="insertUserByCondition">
	 INSERT INTO userinfo (
	 username,
	 `password`,
	 age,
	 <if test="gender != null">
		 gender,
		 </if>
	 phone)
	 VALUES (
	 #{username},
	 #{age},
	 <if test="gender != null">
		 #{gender},
	 </if>
 	#{phone})
</insert>

注意 test 中的 gender，是传⼊对象中的属性，不是数据库字段
Q: 可不可以不进行判断, 直接把字段设置为null呢?
A: 不可以, 这种情况下, 如果gender字段有默认值, 就会设置为默认值

7.2 、< trim >标签

之前的插入用户功能，只是有⼀个 gender 字段可能是选填项，如果有多个字段，⼀般考虑使用标签结合标签，对多个字段都采取动态生成的方式。

标签中有如下属性：
• prefix：表示整个语句块，以prefix的值作为前缀
• suffix：表示整个语句块，以suffix的值作为后缀
• prefixOverrides：表示整个语句块要去除掉的前缀
• suffixOverrides：表示整个语句块要去除掉的后缀

调整 Mapper.xml 的插入语句为：

<insert id="insertUserByCondition">
	 INSERT INTO userinfo
	 <trim prefix="(" suffix=")" suffixOverrides=",">
 	<if test="username !=null">
		 username,
 	</if>
	 <if test="password !=null">
		 `password`,
 	</if>
 	<if test="age != null">
		 age,
	 </if>
 	<if test="gender != null">
		 gender,
	 </if>
	 <if test="phone != null">
		 phone,
	 </if>
 </trim>
 VALUES
 <trim prefix="(" suffix=")" suffixOverrides=",">
	 <if test="username !=null">
		 #{username},
	</if>
	 <if test="password !=null">
		 #{password},
 	</if>
	 <if test="age != null">
		 #{age},
	 </if>
	 <if test="gender != null">
		 #{gender},
 	</if>
	 <if test="phone != null">
		 #{phone}
	 </if>
 </trim>
</insert>

在以上 sql 动态解析时，会将第⼀个部分做如下处理：
• 基于 prefix 配置，开始部分加上 (
• 基于 suffix 配置，结束部分加上 )
• 多个组织的语句都以 , 结尾，在最后拼接好的字符串还会以 , 结尾，会基于
suffixOverrides 配置去掉最后⼀个 ,
• 注意 < if test=“username !=null” > 中的 username 是传⼊对象的属性

7.3、< where >标签

看下面这个场景, 系统会根据我们的筛选条件, 动态组装where 条件

这种如何实现呢?
接下来我们看代码实现:
需求: 传入的用户对象，根据属性做where条件查询，用户对象中属性不为 null 的，都为查询条件. 如username 为 “a”，则查询条件为 where username=“a”

原有SQL

SELECT
 * 
FROM
 userinfo 
WHERE
 age = 18
 AND gender = 1
 AND delete_flag =0

接口定义:

List<UserInfo> queryByCondition();

Mapper.xml实现：

  <select id="queryByCondition" resultType="com.example.demo.model.UserInfo">
        select id, username, age, gender, phone, delete_flag, create_time,
        update_time
        from userinfo
        <where>
            <if test="age != null">
                and age = #{age}
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="deleteFlag != null">
                and delete_flag = #{deleteFlag}
            </if>
        </where>
    </select>

< where > 只会在子元素有内容的情况下才插⼊where⼦句，而且会自动去除子句的开头的AND或OR
以上标签也可以使⽤ < trim prefix=“where” prefixOverrides=“and” > 替换, 但是此种 情况下, 当子元素都没有内容时, where关键字也会保留

1.4 、< set >标签

需求: 根据传入的用户对象属性来更新用户数据，可以使用标签来指定动态内容. 接口定义: 根据传⼊的用户id 属性，修改其他不为 null 的属性

Integer updateUserByCondition(UserInfo userInfo);

Mapper.xml

  <update id="updateUserByCondition">
        update userinfo
        <set>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="age != null">
                age = #{age},
            </if>
            <if test="deleteFlag != null">
                delete_flag = #{deleteFlag},
            </if>
        </set>
        where id = #{id}
    </update>

：动态的在SQL语句中插⼊set关键字，并会删掉额外的逗号. (用于update语句中) 以上标签也可以使用 < trim prefix=“set” suffixOverrides=“,” > 替换。

7.5 < foreach>标签

对集合进行遍历时可以使用该标签。标签有如下属性：
• collection：绑定方法参数中的集合，如 List，Set，Map或数组对象
• item：遍历时的每⼀个对象
• open：语句块开头的字符串
• close：语句块结束的字符串
• separator：每次遍历之间间隔的字符串需求: 根据多个userid, 删除用户数据

接口方法:

void deleteByIds(List<Integer> ids);

ArticleMapper.xml 中新增删除 sql：

 <delete id="deleteByIds">
        delete from userinfo
        where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

7.6 < include >标签

问题分析： • 在xml映射文件中配置的SQL，有时可能会存在很多重复的片段，此时就会存在很多冗余的代码

我们可以对重复的代码片段进行抽取，将其通过 < sql > 标签封装到⼀个SQL片段，然后再通过
< include > 标签进行引用。
• < sql> ：定义可重用的SQL片段
• < include> ：通过属性refid，指定包含的SQL片段

<sql id="allColumn">
 	id, username, age, gender, phone, delete_flag, create_time, update_time
</sql

通过 < include > 标签在原来抽取的地方进行引用。操作如下：

 	<select id="queryAllUser" resultMap="BaseMap">
        select
        <include refid="allColumn"></include>
        from userinfo
    </select>
    <select id="queryById" resultType="com.example.demo.model.UserInfo">
        select
        <include refid="allColumn"></include>
        from userinfo where id= #{id}
    </select>

八、 总结

7.1 MySQL 开发企业规范

1. 表名, 字段名使用小写字目或数字, 单词之间以下划线分割. 尽量避免出现数字开头或者两个下划线 中间只出现数字. 数据库字段名的修改代价很大, 所以字段名称需要慎重考虑。

MySQL 在 Windows 下不区分大小写, 但在 Linux 下默认是区分大小写. 因此, 数据库名, 表名, 字段名都不允许出现任何大写字母, 避免节外生枝
正例： aliyun_admin， rdc_config， level3_name
反例： AliyunAdmin， rdcConfig， level_3_name

2. 表必备三字段: id, create_time, update_time

id 必为主键, 类型为 bigint unsigned, 单表时⾃增, 步⻓为 1
create_time, update_time 的类型均为 datetime 类型, create_time表⽰创建时间,
update_time表⽰更新时间 有同等含义的字段即可, 字段名不做强制要求

3. 在表查询中, 避免使⽤ * 作为查询的字段列表, 标明需要哪些字段

1. 增加查询分析器解析成本
   2.增减字段容易与 resultMap 配置不⼀致
   3.无用字段增加网络络消耗, 尤其是 text 类型的字段

7.2 #{} 和${} 区别

1. #{}：预编译处理, ${}：字符直接替换
2. #{} 可以防止SQL注入, ${}存在SQL注⼊的风险, 查询语句中, 可以使用 #{} ,推荐使用 #{}
3. 但是⼀些场景, #{} 不能完成, 比如 排序功能, 表名, 字段名作为参数时, 这些情况需要使⽤${}
4. 模糊查询虽然${}可以完成, 但因为存在SQL注入的问题,所以通常使用mysql内置函数concat来完成

以上就是本文全部内容，感谢各位能够看到最后，希望大家可以有所收获！创作不易，希望大家多多支持！

最后，大家再见！祝好！我们下期见！