「MyBatis」数据库相关操作2

🎇个人主页

🎇所属专栏：Spring

🎇欢迎点赞收藏加关注哦！

---

#{} 和 ${}

我们前面都是采用 #{} 对参数进行赋值，实际上也可以用 ${}

客户端发送⼀条 SQL 给服务器后，大致流程如下：

1. 解析语法和语义, 校验SQL语句是否正确

2. 优化SQL语句, 制定执⾏计划

3. 执行并返回结果

一条 SQL如果走上述流程处理, 我们称为即时 SQL

#{} 用的是预编译SQL，通过 ? 占位的方式提前编译 SQL（类似 C 语言的 printf 的占位符），然后把参数填充到 SQL 语句中，它还 会根据参数类型自动拼接引号

而 ${} 则是即时 SQL，它直接替换字符，⼀起编译 SQL。如果参数为字符串，需要加上引号

@Mapper
public interface UserInfoMapper {
    @Select("select password from userinfo where username = #{name}")
    UserInfo queryByName1(String name);

    @Select("select password from userinfo where username = '${name}'")
    UserInfo queryByName2(String name);
}

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;

    @Test
    void queryByName1() {
        userInfoMapper.queryByName1("lisi");
    }

    @Test
    void queryByName2() {
        userInfoMapper.queryByName2("wangwu");
    }
}

运行结果：

探讨二者的区别其实就是在讨论预编译 SQL 和即时 SQL 的区别

1. 预编译 SQL 性能更高

大多数情况下某条 SQL 语句可能会被反复调用执行，或者每次执行时只有个别值不同（比如 select 的 where 子句值不同、update 的 set 子句值不同、insert 的 values 值不同)。如果每次都需要经过上面的语法解析、SQL 优化、SQL 编译这些步骤，也就是即时 SQL，则效率显然不行

而预编译 SQL 编译一次之后就会将编译后的 SQL 语句 缓存起来 。后面再次执行这条语句时，不会再次编译（只是输入的参数不同），这样就省去解析优化等过程，提高了效率（这个原理和常量池类似）

---

2. 预编译 SQL 可以防止 SQL 注入，更安全

SQL 注入是一种通过操作输入的数据来修改事先定义好的 SQL 语句，执行代码后对服务器进行攻击的技术

${} 会有 SQL 注入的风险，所以尽量使用 #{} 完成查询

下面演示一下 SQL 注入

@Mapper
public interface UserInfoMapper {
    @Select("select password from userinfo where username = '${name}'")
    List<UserInfo> queryByName2(String name);
}

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;

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

可以看到把所有的 password 都查询出来了，这显然是不合理的

---

3. 有一些场景 #{} 实现不了，需要用 ${}，比如排序

@Mapper
public interface UserInfoMapper {
    @Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
            "from userinfo order by id #{sort}")
    List<UserInfo> queryAllUserBySort1(String sort); //传入排序方式:排升序 or 降序

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

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;

    @Test
    void queryAllUserBySort1() {
        List<UserInfo> list = userInfoMapper.queryAllUserBySort1("asc");
        System.out.println(list);
    }

    @Test
    void queryAllUserBySort2() {
        List<UserInfo> list = userInfoMapper.queryAllUserBySort2("desc");
        System.out.println(list);
    }
}

#{sort} 查询结果如下：

使用  #{sort} 查询时, asc 前后会自动加上引号，导致 sql 错误；而我们不想它加引号，所以只能用 ${sort}，因为这个的引号一定要我们自己手动加上去

按照 id 排降序：

---

模糊匹配（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);

like 查询使用 #{} 会报错，需要用 ${}，但它存在 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);

    @Test
    void queryAllUserByLike() {
        List<UserInfo> list = userInfoMapper.queryAllUserByLike("S");
        System.out.println(list);
    }

---

数据库连接池

数据库连接池顾名思义，就是存放数据库连接的池子，它负责分配、管理和释放数据库连接，允许应用程序重复使用一个现有的数据库连接， 而不是再重新建立一个连接

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

而如果使用数据库连接池，那么在程序启动时，会在数据库连接池中创建⼀定数量的 Connection 对象, 当客户端需要访问数据库时，会从数据库连接池中获取 Connection 对象, 然后执行 SQL, 执行完再把 Connection 放回连接池

优点：

1. 减少了网络开销

2. 资源重用

3. 提升系统的性能

常见的数据库连接池有：Hikari、Druid、C3P0、DBCP。其中 SpringBoot 默认使用的数据库连接池是 Hikari

我们也可以把默认的数据库连接池切换为 Druid，只需引入下述的依赖：

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

---

总结

1. 命名规则：表名和字段名使用 小写字母或数字 ，单词之间以下划线分割，尽量避免出现数字开头、两个下划线、中间只出现数字这些情况

2. 表中一定有这三个字段：id、create_time、update_time。其中 id 必为主键，它的类型为 bigint unsigned，单表时自增；create_time 和 update_time 的类型均为 datetime

3. 在表查询中, 应避免使用 * 查询

4. #{} 和 ${} 区别

1. #{}：预编译处理, ${}：字符直接替换

2. #{} 可以防止 SQL 注入，${} 存在SQL注入的风险。查询语句中推荐使用 #{}

3. 但是⼀些场景 #{} 没法完成要求，比如排序功能，表名, 字段名作为参数时，需要使用 ${}

4. 模糊查询虽然 ${} 可以完成，但由于存在 SQL 注入的问题，所以通常使用 mysql 内置函数concat 来完成