前言:在上篇博客介绍了MyBatis的一些增删改查操作,接下来介绍其他查询操作
目录
1 其他查询操作
1.1 多表查询
1.1.1 准备工作
1.1.2 数据查询
1.2 #{}和${}
1.2.1 #{}和${}使用
1.2.2 #{}和${}的区别
1.3 排序功能
1.4 like查询
2 数据库连接池
2.1 介绍
2.2 使用
1 其他查询操作
1.1 多表查询
多表查询和单表查询类似,只是SQL不同
1.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);
@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;
}
1.1.2 数据查询
接口定义:
@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=1")
ArticleInfo selectArticleAndUserById(Integer id);
}
测试代码:
@Slf4j
@SpringBootTest
class ArticleInfoMapperTest {
@Autowired
private ArticleInfoMapper articleInfoMapper;
@Test
void selectArticleAndUserById() {
ArticleInfo articleInfo = articleInfoMapper.selectArticleAndUserById(1);
log.info(articleInfo.toString());
}
}
1.2 #{}和${}
MyBatis参数赋值有两种方式,前面使用了#{ }进行赋值,接下来看一下两者的区别
1.2.1 #{}和${}使用
1 Integer类型的参数
@Mapper
public interface UserInfoMapper {
@Select("select username,password,age,gender,phone from userinfo where id=#{id}")
UserInfo selectId(Integer id);
}
@Test
void selectId() {
UserInfo userInfo = userInfoMapper.selectId(1);
log.info(userInfo.toString());
}
从打印的日志可以看出,输入的参数并没有再后面进行拼接,id的值是使用?进行占位,这种SQL称之为"预编译SQL"
将#{}改为${}再观察打印日志
@Mapper
public interface UserInfoMapper {
@Select("select username,password,age,gender,phone from userinfo where id=${id}")
UserInfo selectId(Integer id);
}
可以看到,参数是直接拼接再SQL语句中了,这种SQL称之为"即时SQL"
2 String类型的参数
@Mapper
public interface UserInfoMapper {
@Select("select username,password,age,gender,phone from userinfo where username=#
{username}")
UserInfo selectId(String username);
}
@Test
void selectId() {
UserInfo userInfo = userInfoMapper.selectId("admin");
log.info(userInfo.toString());
}
可以看到结果正常返回了
将#{}改为${}再观察打印日志
@Mapper
public interface UserInfoMapper {
@Select("select username,password,age,gender,phone from userinfo where
username=${username}")
UserInfo selectId(String username);
}
从日志可以看出,这次的参数依然是直接拼接在SQL语句中了,但是字符串作为参数时,需要添加' ',使用${ }不会拼接' ',导致程序报错
修改代码如下:
@Mapper
public interface UserInfoMapper {
@Select("select username,password,age,gender,phone from userinfo where
username='${username}'")
UserInfo selectId(String username);
}
从上面两个例子可以看出:
#{ }使用的是预编译SQL, 通过?占位的方式,提前对SQL进行编译,然后把参数填充到SQL语句中,#{ }会根据参数的类型,自动拼接' '
${ }会直接进行字符串替换,仪器对SQL进行编译,如果参数为字符串,需要手动添加' '
1.2.2 #{}和${}的区别
#{ }和${ }的区别就是预编译SQL和即时SQL的区别
1)预编译SQL的性能更高
预编译SQL在编译一次之后会将编译后的SQL语句缓存起来,后面再执行这条语句时,不会再次编译,省去了解析优化等待过程,以此提高效率
2)预编译SQL更安全,不存在SQL注入的问题
SQL注入:通过操作输入的数据来修改事先定义好的SQL语句,以达到执行代码对服务器进行攻击的方法
SQL注入代码:' or 1='1
正常情况:
@Mapper
public interface UserInfoMapper {
@Select("select username,password,age,gender,phone from userinfo where
username='${username}'")
UserInfo selectId(String username);
}
@Test
void selectId() {
UserInfo userInfo = userInfoMapper.selectId("admin");
log.info(userInfo.toString());
}
SQL注入场景:
@Test
void selectId() {
UserInfo userInfo = userInfoMapper.selectId("'or 1='1");
log.info(userInfo.toString());
}
可以看出结果被查询出来了,其中参数 or 被当作SQL语句的一部分,此时查询的数据并不是想要的数据,所以用于查询的字段,尽量使用#{ }预查询的方式
SQL注入是一种非常常见的数据库攻击手段,如果发生在用户登录的场景中,密码为' or 1='1就可能完成登录
1.3 排序功能
从上面的例子可以得出:${ }会有SQL注入的风险,所以尽量使用#{ }完成查询,那么是不是${ }就没有作用了呢,因此,接下来看下${ }的使用场景
@Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
" from userinfo order by id ${sort}")
List<UserInfo> selectBySort(String sort);
@Test
void selectBySort() {
List<UserInfo> userInfoList = userInfoMapper.selectBySort("desc");
log.info(userInfoList.toString());
}
此处的sort参数类型为String类型,但是在SQL语句中,排序的规则时不需要添加' '的,此处使用的$符号,会直接把desc拼接在SQL语句中,输出的结果就是正确的
@Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
" from userinfo order by id #{sort}")
List<UserInfo> selectBySort(String sort);
将$改为#再次观察
此时可以发现,#{ }会根据参数类型判断是否要加' ',由于desc是String类型,因此加上了' ',导致SQL错误
1.4 like查询
除了排序,like使用#{ }也会报错
@Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
" from userinfo where username like '%#{username}%'")
List<UserInfo> selectByLike(String username);
@Test
void selectByLike() {
List<UserInfo> userInfoList = userInfoMapper.selectByLike("wang");
log.info(userInfoList.toString());
}
将#改为$,再次观察
@Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
" from userinfo where username like '%${username}%'")
List<UserInfo> selectByLike(String username);
此时可以正确的查出来,但是${ }存在SQL注入的问题,所以不能直接使用${ },正确的做法是使用mysql的内置函数concat()来处理
@Select("select id,username,age,gender,phone,delete_flag,create_time,update_time " +
" from userinfo where username like CONCAT('%',#{username},'%')")
List<UserInfo> selectByLike(String username);
总结:# 和 $ 的区别
# 和 $ 的区别就是预编译SQL和即时SQL的区别
1)预编译SQL性能更高
2)预编译SQL更安全,不存在SQL注入的问题
3)排序时,不能使用 # ,表明,字段名等作为参数时,也不能使用 #
4)模糊查询时,如果使用 # ,需要搭配mysql的内置函数 concat ,不能直接使用
在实际开发中能使用 # 就使用 # ,使用 $ 时,一定要考虑SQL注入的问题
2 数据库连接池
在使用MyBatis时,我们使用了数据库连接池技术,避免频繁的创建连接,销毁链接
2.1 介绍
数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个
没有使用数据库连接池的情况:每次执行SQL语句,要先创建一个新的对象,然后执行SQL语句,SQL语句执行完,再关闭连接释放对象,这种重复的创建连接,销毁比较浪费资源
使用数据库连接池的情况:程序启动时,会在数据库连接池中创建一定数量的Connection对象,当客户端请求数据库连接池,会从数据库连接池中获取Connection对象,然后执行SQL,SQL语句执行完,再把Connection归还给连接池
使用数据库连接池的优点:
1)减少了网络开销 2)资源重用 3)提升了系统的性能
2.2 使用
常见的数据库连接池:
1)C3P0 2)DBCP 3)Druid 4)Hikari
目前流行的是Hikari,Druid
Hikari:SpringBoot默认使⽤的数据库连接池
Druid
如果想把默认数据库连接池切换为Druid数据库连接池,只需要引入相关的依赖即可
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>