MyBatis操作数据库(3)

其它查询操作

#{}和${}

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

#{}和${}的使用

1.先看Integer类型的参数:

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

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

 

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

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

@Select("select username, password, age, gender, phone from userinfo where id = ${id}")
UserInfo queryById2(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进行编译. 如果参数为字符串, 需要加上引号 ' '.

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

#{}和${}区别

简单回顾:

当客户发送一条SQL语句给服务器后, 大致流程如下:

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

2.优化SQL语句, 指定执行计划.

3.执行并返回结果

 一条SQL语句如果走上述流程, 我们称之为即时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}'")
 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, 就可能完成登录(不是一定会发生的场景, 需要看登录代码咋写). 

 排序功能

从上面的例子中, 可以得出结论: ${}会有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, 就会加上引号.

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

其实, 这样直接使用${}还是有一定风险的, 但是其实这无非就升序/降序两种情况. 我们可以直接写两个接口, 一个专门传"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讲解中, 我们使用了数据库连接池技术, 避免频繁地创建连接, 销毁连接, 下面我们来了解一下数据库连接池:

介绍

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

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

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

优点:

1.减小了网络开销

2.资源重用

3.提升了系统性能.