MyBatis操作数据库(3)

偏执的太偏执、 2024-04-20 07:03 70阅读 0赞

## 其它查询操作 ##

### \#\{\}和$\{\} ###

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

#### \#\{\}和$\{\}的使用 ####

1.先看Integer类型的参数:

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

我们观察一下打印的日志:

![b9264c20c36e4853ae4a438367afe52a.png][]

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

我们把\#\{\}换为$\{\}再观察打印的日志:

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

![df68088e87d8414b93d5b89fb3fc6657.png][]

可以看到, 这次的参数是直接拼接在SQL中了.

2.接下来我们再看String类型的参数:

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

观察打印的日志, 发现正常返回.

![9d3ba7c7b5304ddcb467a60c33419709.png][]

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

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

![c0e4d0d11df940bb8884fad7cccea560.png][]

可以看到, 这次的参数依然是直接拼接在SQL语句中了, 但是字符串作为参数时, 需要添加引号 ' ', 使用$\{\}而不添加引号, 会导致程序报错.

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

再次运行, 结果正常返回:

![ff8c2718a1a94a058c1d622cc6349bbe.png][]

从上面两个栗子可以看出:

> **\#\{\} 使用的是预编译SQL, 通过 ? 占位的方式, 提前对SQL进行编译, 然后把参数填充到SQL语句中. \#\{\}会根据参数的类型, 自动拼接引号 ' '.**
> 
> **$\{\} 会直接进行字符替换, 一起对SQL进行编译. 如果参数为字符串, 需要加上引号 ' '.**
> 
> 参数为数字类型时, 也可以加上, 查询结果不变, 但是可能导致索引失效, 性能下降.

#### \#\{\}和$\{\}区别 ####

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

**1.性能更高**

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

![1f43dee0a00b40dcb0aa38ad4e7e2654.png][]

预编译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}'")
     UserInfo queryByName(String name);

测试代码:

**正常访问情况:**

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

结果运行正常:

![2e3af3a1a7854ebea36232a1dbe1255f.png][] **SQL注入场景**

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

结果依然查询出来了, 其中参数or被当作了SQL语句的一部分.

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

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

### 排序功能 ###

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

当然不是.

接下来我们来看一下$\{\}的使用场景:

![6c9661cfe5ff4ea68fe2ab98c13fca5a.png][]

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);

运行结果:

![cdfb185ce1af43ba9d20a40e0c688a82.png][] 可以发现, 当使用\#\{sort\}查询时, asc前后自动给加了引号, 导致sql错误.

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

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

**其实, 这样直接使用$\{\}还是有一定风险的, 但是其实这无非就升序/降序两种情况. 我们可以直接写两个接口, 一个专门传"asc"以表示升序, 一个专门传"desc"以表示降序.**

### like查询 ###

like使用\#\{\}报错.

@Select("select * from userinfo where username like '%#{key}%'")
    List<UserInfo> queryAllUserByLike(String key);

把\#\{\}改成$\{\}可以正确查出来, 但是$\{\}存在SQL注入的问题, 所以不能直接使用$\{\}.

解决方法: 使用mysql的内置函数concat()来处理, 实现代码如下:

@Select("select * from userinfo where username like concat('%', #{key}, '%')")
    List<UserInfo> queryAllUserByLike(String key);

> 总结: \#\{\}和$\{\}区别
> 
> 1.\#\{\}: 预编译处理, $\{\}:直接字符替换
> 
> 2.\#\{\}可以防止SQL注入, $\{\}存在SQL注入的风险, 查询语句中, 可以使用\#\{\}, 推荐使用\#\{\}
> 
> 3.但是一些场景, \#\{\}不能完成, 比如排序功能, 表名, 字段名作为参数时, 这些情况需要使用$\{\}
> 
> 4.以上场景可以有更安全的方式替换$\{\}.

## 数据库连接池 ##

在上面Mybatis讲解中, 我们使用了**数据库连接池技术, 避免频繁地创建连接, 销毁连接**, 下面我们来了解一下数据库连接池:

### 介绍 ###

**数据库连接池负责分配, 管理和释放数据库连接, 它允许应用程序重复使用一个现有的数据库连接, 而不是重新建立一个**.

![edbeefa889e5426cbd693ed8ae94af7c.png][]

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

优点:

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

[b9264c20c36e4853ae4a438367afe52a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/0c8d04ddf72e4c1e99d163207197447c.png
[df68088e87d8414b93d5b89fb3fc6657.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/e94e04b060834e89b5ccb013b4828c78.png
[9d3ba7c7b5304ddcb467a60c33419709.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/4b88e78ca7184442a09b86bc4b068dfa.png
[c0e4d0d11df940bb8884fad7cccea560.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/a2a902a6477e4ba6b5bcd4ef9603c9b7.png
[ff8c2718a1a94a058c1d622cc6349bbe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/345f384d66674d22a2a8a362221bf239.png
[1f43dee0a00b40dcb0aa38ad4e7e2654.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/4dd1946c82b6461d8d2e2b9ae489e1c3.png
[2e3af3a1a7854ebea36232a1dbe1255f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/57aeb1338c5a492db53ec03966b3e9fe.png
[11733c2af4444f8eb475dc8ffa0d7706.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/ac6e22466feb4c4b8f4b779b2f2b47ae.png
[6c9661cfe5ff4ea68fe2ab98c13fca5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/ca84717857e348f29fe8e0354e31b88a.png
[cdfb185ce1af43ba9d20a40e0c688a82.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/79df0be56bb6495da3d555f72d031538.png
[edbeefa889e5426cbd693ed8ae94af7c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/9e1a0b9666fa4a91a17815d4abd0208b.png