一、查询与连接池

1、多表查询

尽量避免使用多表查询，尤其是对性能要求较高的项目。因为多表查询必然会导致性能变低。

例如：select *from ta运行需要10ms，select *from tb 运行也需要10s。但是，select *from ta left join tb on ta.xx==tb.xx 必然大于10ms，

并且数据库集群是很多项目一起使用的，当出现慢查询时，会影响整个集群，也就是会影响其他服务的速度。

在数据库上再建立一个文章表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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 CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET = 'utf8mb4';

INSERT INTO articleinfo (title, content, uid) VALUES ('Java', 'Java正文', 1);
INSERT INTO articleinfo (title, content, uid) VALUES ('Python', 'Python正文', 2);

对应Model层的实体类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

package com.example.mybatisdemo.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;
}

根据uid查询作者的名称等相关信息，进行多表查询的sql语句应该为：

1
2
3
4

SELECT ta.*, tb.username
FROM articleinfo ta
LEFT JOIN userinfo tb ON ta.uid = tb.id
WHERE ta.id = 1;

所以，我们要补充实体类，在刚刚的ArticleInfo类中加入用户相关信息，便于映射：

1
2
3
4
5
6
7
8
9
10
11
12
13

@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;
}

对应的ArticlenInfoMapper接口：

1
2
3
4
5
6
7
8

@Mapper
public interface ArticlenInfoMapper {
    //多表查询
    @Select("select ta.*,tb.username from articleinfo ta " +
            "left join userinfo tb on ta.uid = tb.id " +
            "where ta.id = #{articleId}")
    ArticleInfo selectArticlenAndUserByID(Integer articleId);
}

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

2、#{} 和 ${}

Ⅰ、区别

#{}和${}都是MyBatis框架中使用的占位符。

1
2

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

然后把#{}换成${}：

1
2

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

使用${}时，MyBatis不会自动添加引号。{}用于直接替换SQL语句中的文本，因此在某些情况下，如果替换的值是字符串，则需要手动添加引号。

#{}利用预编译SQL的方式工作，它通过在SQL语句中使用?占位符来提前编译SQL命令，并在执行时将参数值安全地绑定到这些占位符上。MyBatis会根据参数的类型自动添加必要的引号，例如字符串类型的参数会被加上引号''，以确保SQL语句的正确性和安全性。相反，${}则采用简单的字符串替换机制，它在SQL语句编译之前直接将参数值替换到SQL命令中。这意味着如果参数值是字符串，需要手动添加引号''来确保SQL语句的语法正确性。

总结：

#{}和${}在MyBatis中的区别主要体现在以下几个方面：

1. 预编译处理：
   • #{}：使用预编译语句（PreparedStatement），参数会被替换为?，并在SQL执行时绑定参数值。这种方式可以防止SQL注入，因为参数值会被数据库引擎视为数据，而不是SQL命令的一部分。
   • ${}：不使用预编译语句，参数值会直接替换到SQL语句中。这种方式不会防止SQL注入，因为参数值被视为SQL语句的一部分，如果参数值中包含SQL关键字或特殊字符，可能会改变原SQL语句的结构。
2. 参数替换方式：
   • #{}：参数替换后，MyBatis会根据参数的类型自动添加引号，例如字符串类型的参数会被加上引号''。
   • ${}：参数替换后，不会自动添加引号，如果参数是字符串类型，需要手动添加引号。
3. 使用场景：
   • #{}：适用于大部分情况，尤其是处理用户输入或不可信数据时，提供安全保障。
   • ${}：适用于需要动态指定表名、列名或其他SQL关键字的情况，但使用时需要确保参数值的安全性。
4. 性能影响：
   • #{}：通常不会对性能产生负面影响，因为预编译语句可以被数据库缓存和重用。
   • ${}：如果用于字符串替换，可能会导致数据库无法有效缓存执行计划，从而影响性能。
5. 安全性：
   • #{}：提供了更好的安全性，可以防止SQL注入攻击。
   • ${}：存在SQL注入的风险，应该尽量避免使用，或者在确保参数值安全的情况下谨慎使用。

Ⅱ、SQL注入

${}存在一个非常大的问题，那就是SQL注入。当使用${}时，MyBatis不会对替换的参数值进行任何转义或预处理。这意味着，如果参数值包含特殊字符或SQL关键字，它们将直接插入到SQL语句中。如果这些值来自于用户的输入，且没有得到适当的验证和清理，攻击者就可以利用这一点来执行恶意SQL代码。

1
2

@Select("select * from userinfo where username like '${username}'")
List<UserInfo> selectUserByName(String username);

测试代码：

1
2
3
4

@Test
void selectUserByName() {
    log.info(userInfoMap.selectUserByName("' or 1 = '1").toString());
}

SQL注⼊代码： ' or 1='1。这里可以看见，结果被正确查询出来了, 其中参数 or 被当做了SQL语句的⼀部分。由于没有对用户输⼊进行充分检查，而SQL⼜是拼接⽽成，在用户输⼊参数时，在参数中添加⼀些SQL关键字，达到改变SQL运行结果的目的，也可以完成恶意攻击。

3、排序查询

1
2
3
4

@Select("SELECT id, username, age, gender, phone, delete_flag, create_time, update_time " +
        "FROM userinfo " +
        "ORDER BY id ${sort}")
List<UserInfo> selectAllUserBySort(String sort);

这里使用 ${sort} 可以实现排序查询，而使用#{sort} 就不能实现排序查询。因为，此处 sort 参数为String类型，但是SQL语句中，排序规则是不需要加引号 '' 的，所以此时的${sort} 也不加引号。如果此时，使用 #{sort} 查询时, sort参数前后会自动给加了引号, 导致出现 sql 错误。

4、模糊查询

1
2
3

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

和前面的排序查询一样，在这个查询中，由于#{}的工作方式，MyBatis会把'%#{key}%'当作一个整体，所以 '%#{key}%' 的预期结果是，参数key被包围在两个%通配符之间。所以，当使用like查询的时候，应该使用${}，但是这样又会出现SQL注入的安全问题。

为了解决这个问题，可以使用MySQL 的CONCAT函数来动态地构造like查询的参数，像这样：

1
2
3

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

CONCAT是MySQL中的一个函数，用于将两个或多个字符串连接在一起。

基本的语法：CONCAT(string1, string2, ..., string_n)

在like查询中，你可以使用CONCAT函数来动态地构造查询参数。例如，以下查询将查找用户名包含关键词"John"的所有用户：

1	SELECT * FROM user WHERE username LIKE CONCAT('%', 'John', '%');

在这个例子中，CONCAT('%', 'John', '%')将返回字符串"%John%“，这将在任意位置匹配关键词"John”。

5、数据库连接池

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

没有使用数据库连接池的情况： 每次执行SQL语句，要先创建⼀个新的连接对象，然后执行SQL语句，SQL语句执行完，再关闭连接对象释放资源。这种重复的创建连接，销毁连接比较消耗资源。

使用数据库连接池的情况： 程序启动时, 会在数据库连接池中创建⼀定数量的Connection对象, 当客户请求数据库连接池, 会从数据库连接池中获取Connection对象，然后执行SQL， SQL语句执行完，再把Connection归还给连接池。

目前比较流行的是：Hikari，Druid

1. Hikari : SpringBoot默认使用的数据库连接池
2. Druid：阿里巴巴开源的数据库连接池

如果想把默认的数据库连接池从Hikari连接池切换为Druid连接池, 只需要在pom.xml中引入相关依赖即可

1
2
3
4
5

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

学习文档：常见问题 · alibaba/druid Wiki (github.com)

二、动态SQL

1、<if> 标签

在 MyBatis 中，<if> 标签是用来构建动态 SQL 语句的一种重要工具。它可以根据某个条件来决定是否包含某段 SQL 语句。

<if> 标签的基本语法如下：

1
2
3

<if test="condition">
    <!-- SQL 语句 -->
</if>

其中，condition 是一个表达式，它会被评估为一个布尔值。如果 condition 为 true，那么包含在 <if> 标签中的 SQL 语句将被包含在最终的 SQL 查询中。

在这个 @Insert 注解中，如果要使用使用动态 SQL，需要使用 <script> 标签包裹整个 SQL 语句。

1
2
3
4
5
6
7
8
9
10

@Mapper
public interface UserInfoMapper {
    @Insert("<script>insert into userinfo (username,password,age," +
            "<if test = 'gender != null'>gender,</if>" +
            "phone)"+
            "values(#{username},#{password},#{age}," +
            "<if test = 'gender != null'>#{gender},</if>" +
            "#{phone})</script>")
    Integer insert(UserInfo userInfo);
}

单元测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void insert() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("ww");
        userInfo.setPassword("123456");
        userInfo.setAge(20);
        userInfo.setPhone("12345678901");
        userInfo2Mapper.insert(userInfo);
    }
}

上面是注解的写法，但是注解的写法比较麻烦，还是推荐使用XML方法。

1
2
3
4
5
6
7
8

<insert id="insertByXML">
        insert into userinfo (username, password,age,
                              <if test="gender != null">gender,</if>
                              phone)
        value (#{username},#{password},#{age},
            <if test="gender != null">#{gender},</if>
            #{phone})
</insert>

2、<trim>标签

<trim>标签的作用是帮我们去除多余的字符。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

<insert id="insertByXML">
    insert into userinfo (
        <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>
    )
    values (
        <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>
    )
</insert>

如果像这样，每个属性都会检查是否为 null，因为每个 <if> 标签后面都添加了一个逗号，所以很有可能会导致生成的 SQL 语句最后多出一个逗号。

为了解决这个问题，可以使用 <trim> 标签来移除这个额外的逗号，标签中有如下属性：

• prefix：整个语句块，以prefix的值作为前缀

• suffix：整个语句块，以suffix的值作为后缀

• prefixOverrides：整个语句块要去除掉的前缀

• suffixOverrides：整个语句块要去除掉的后缀

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

<insert id="insertUserByXML">
	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>

3、<where>标签

在 MyBatis 中，<where> 标签用于构建动态的 “WHERE” 子句。

它的主要特性是：只有当至少有一个子元素的内容被包含时，才会插入 “WHERE” 关键字，并且会自动去除子句开头的 “AND” 或 “OR”。这对于避免在动态生成的 “WHERE” 子句中出现语法错误非常有用。

1
2
3
4
5
6
7
8

<select id="selectByCondition" resultType="com.example.mybatisdemo.model.UserInfo">
    SELECT * FROM userinfo
    <where>
        <if test="username != null">AND username = #{username}</if>
        <if test="age != null">AND age = #{age}</if>
        <if test="gender != null">AND gender = #{gender}</if>
    </where>
</select>

当然，上面这个需求使用 <trim> 标签也能写：

1
2
3
4
5
6
7
8
9

<select id="selectByCondition" resultType="com.example.mybatisdemo.model.UserInfo">
    SELECT * FROM userinfo
    WHERE
    <trim prefixOverrides="AND">
        <if test="username != null">AND username = #{username}</if>
        <if test="age != null">AND age = #{age}</if>
        <if test="gender != null">AND gender = #{gender}</if>
    </trim>
</select>

在 MyBatis 中，<where> 和 <trim> 标签都可以用于构建动态 SQL，它们的行为有些许差异。

1. <where> 标签：只有当它的子元素有内容时，才会插入 “WHERE” 子句。此外，它还会自动去除子句开头的 “AND” 或 “OR”。这意味着，如果所有的 <if> 测试都为 false，则不会生成 “WHERE” 子句。

2. <trim> 标签：可以通过 prefix 属性来添加前缀，例如 “WHERE”，并通过 prefixOverrides 属性来移除子句开头的 “AND” 或 “OR”。然而，与 <where> 标签不同，即使 <trim> 标签的所有子元素都没有内容，它的 prefix 属性指定的内容也会被保留。也就是说，如果所有的 <if> 测试都为 false，则会生成一个空的 “WHERE” 子句。

4、<set>标签

在 MyBatis 中，<set> 标签主要用于生成动态的 “SET” 子句，这在 “UPDATE” SQL 语句中非常有用。<set> 标签能够确保只在至少有一个子元素的内容被包含时才插入 “SET” 关键字，并且会自动去除掉多余的逗号。这对于避免在动态生成的 “UPDATE” 语句中出现语法错误非常有用。

1
2
3
4
5
6
7
8
9

<update id="updateUser" parameterType="com.example.mybatisdemo.model.UserInfo">
    UPDATE userinfo
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="age != null">age = #{age},</if>
        <if test="gender != null">gender = #{gender},</if>
    </set>
    WHERE id = #{id}
</update>

在这个例子中，<set> 标签包含了三个 <if> 标签，每个 <if> 标签都会检查一个特定的属性是否为 null。如果属性不为 null，那么对应的 “SET” 子句就会被包含在生成的 SQL 语句中，否则就会被忽略。这样，我们可以根据传入的参数动态地更新不同的字段，而不必为每一种可能的组合都写一个单独的 “UPDATE” 语句。注意，每个条件后面都有一个逗号。在生成的 SQL 语句中，<set> 标签会自动移除最后一个逗号，从而避免了语法错误。这就是 <set> 标签的一个重要特性。

5、<foreach>标签

在 MyBatis 中，<foreach> 标签允许你在 SQL 语句中使用集合，并将集合中的每个元素都作为一个独立的参数处理。

以下是 <foreach> 标签的一个例子，用于处理 in 条件查询：

1
2
3
4
5
6
7

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

<foreach> 标签的主要属性包括：

• item：集合中每个元素的别名。
• index：如果集合是 List，它是元素的索引；如果集合是 Map，它是键值。
• collection：要迭代的集合的变量名。
• open 和 close：在表达式开始和结束时插入的字符串。
• separator：用于分隔每个元素的字符串。

另一个 <foreach> 标签的常见用法是批量插入：

1
2
3
4
5
6
7

<insert id="insertUsers">
    INSERT INTO userinfo (username, age, gender)
    VALUES
    <foreach item="user" collection="userList" separator=",">
        (#{user.username}, #{user.age}, #{user.gender})
    </foreach>
</insert>

这个例子中，<foreach> 标签用于处理一个名为 “userList” 的参数，这个参数是一个用户对象的列表。对于列表中的每个用户对象，都会生成一个包含三个参数占位符的元组，这些元组被逗号分隔。这样就可以一次性插入多个用户，而不必为每个用户都写一个单独的 “INSERT” 语句。

6、<include>标签

在 MyBatis 中，<include> 标签用于引用在同一命名空间中定义的 <sql> 标签。这样就可以复用 SQL 代码，避免重复编写相同的 SQL 片段。

1
2
3
4
5
6
7
8
9

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

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

我们可以对重复的代码片段进行抽取，将其通过 <sql> 标签封装到⼀个SQL片段，然后再通过<include> 标签进行引用。

• <sql> ：定义可重用的 SQL片段

• <include> ：通过属性refid，指定包含的SQL片段