Mybatis--其他查询操作和数据库连接池（下下）

30 阅读 0 评论 0 点赞

序

准备工作：

mysql数据库和表的信息更新：

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 ( '十日游戏', 'snh48群像', 1 );

对应实体类model：

package com.example.zxslzw_mybaties.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;
    //用户相关的信息
    private String username;
    private Integer gender;
}

1. 多表查询

多表查询和单表查询差不多，只是SQL代码不同，下面是xml的配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.zxslzw_mybaties.mapper.ArticleInfoMapper">
    <select id="selectArticleAndUserById" resultType="com.example.zxslzw_mybaties.model.ArticleInfo">
        select ta.*, tb.username, tb.gender from articleinfo ta
        left join userinfo tb
        on ta.uid = tb.id
        where ta.id = #{id}
    </select>
</mapper>

ArticleInfoMapper接口代码如下：

package com.example.zxslzw_mybaties.mapper;

import com.example.zxslzw_mybaties.model.ArticleInfo;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ArticleInfoMapper {
    List<ArticleInfo> selectArticleAndUserById(Integer id);
}

测试类代码如下：

package com.example.zxslzw_mybaties.mapper;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest
class ArticleInfoMapperTest {
    @Autowired
    private ArticleInfoMapper articleInfoMapper;

    @Test
    void selectArticleAndUserById() {
        System.out.println(articleInfoMapper.selectArticleAndUserById(1));
    }
}

articleInfo和userinfo表如下：

运行测试类代码，结果如下：

2. #{} 和 ${}

MyBatis 参数赋值有两种⽅式，咱们前⾯使⽤了 #{} 进⾏赋值，接下来我们看下⼆者的区别。

2.1 #{} 和 ${} 的使用

1、Integer类型的参数

#{} 的使用

ArticleInfoMapper接口代码：

@Mapper
public interface UserInfoMapper {
    @Select("select * from userinfo where id = #{id}")
    List<UserInfo> selectId(Integer id);
}

测试类代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void selectId() {
        userInfoMapper.selectId(1);
    }
}

运行测试类代码，结果如下：

分析：

#{} 使用的是预编译SQL，通过 ? 占位的方式，提前对SQL进行编译，然后把参数填充到SQL语句中。这里的 #{} 会根据参数类型，自动拼接引号 ' '。

${} 的使用

UserInfoMapper接口代码如下：

@Mapper
public interface UserInfoMapper {
    @Select("select * from userinfo where id = ${id}")
    List<UserInfo> selectId(Integer id);
}

测试类代码与之前的代码不变；运行测试类，结果如下：

二者使用，结果并没有不同；但是后者的参数是直接拼接在SQL语句中了；

2、使用String类型的参数

#{} 的使用

UserInfoMapper接口代码：

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

测试类代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void selectUserName() {
        userInfoMapper.selectUserName("yuanyiqi");
    }
}

运行测试类代码，结果如下：

${} 的使用

USerInfoMapper接口代码：

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

测试类代码不变，运行测试类，查看下面结果，发现报错了，报错信息如下：

错误显示是BadSql，yuanyiqi应该是一个字符串，但拼接SQL的时候没有加引号，说明 ${} 是直接把参数传进去了，并没有添加引号 ' '，进而报错了；

总结：

1、#{} 使用的是预编译SQL，通过 ? 占位的方式，提前对SQL进行编译，然后把参数填充到SQL语句中。#{} 会根据参数类型，自动拼接引号 ' '。

2、${} 会直接进行字符替换，一起对SQL进行编译。如果参数为字符串，需要加上引号 ' '，

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

2.2 #{} 和 ${} 的区别

#{} 和 ${} 的区别就是预编译SQL和即时SQL的区别。

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

        1、解析语法和语义，校验SQL语句是否正确。

        2、优化SQL语句，指定执行计划。

        3、执行并返回结果。

（一条SQL如果走上述流程处理，我们称之为 Immediate Statements(即时SQL) ）

1、#{} 的性能更高
绝大多数情况下，某一条SQL语句可能会被反复调用执行，或者每次执行的时候只有个别的值不同（比如select 的 where 子句值不同，update 的 set 子句值不同，insert 的 values 值不同）。如果每次都需要经过上面的语法解析、SQL优化、SQL编译等，则效率就明显不行了。如下图：

预编译SQL，编译一次之后会将编译后的SQL语句缓存起来，后面再次执行这条语句时，不会再次编译（只是输入的参数不同），省去了解析优化等过程，以此来提高效率。

2、#{} 更安全 (防止SQL注入)

SQL注入：是通过操作输入的数据来修改事先定义好的SQL语句，以达到执行代码对服务器进行攻击的方法。

sql注⼊代码： ' or 1='1

USerInfoMapper接口代码：

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

测试类代码如下：（正常访问情况）

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void selectUserName() {
        System.out.println(userInfoMapper.selectUserName("zhangsan"));
    }
}

运行结果如下：

测试类代码：（SQL注入场景）

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void selectUserName() {
        System.out.println(userInfoMapper.selectUserName("' or 1='1"));
    }
}

运行结果如下：

我们发现，把表全部的信息都打印出来了，如果username是空字符串，就打印是空字符串的；如果username的赋值为真，会打印userinfo表的全部信息。所以用于查询的字段，尽量使用 #{} 预查询的方式。

2.3 登录时sql注入的案例

SQL注入是一种非常常见的数据库攻击手段，SQL注入漏洞也是网络世界中最普遍的漏洞之一。如果发生在用户登录场景中，密码输入为 ' or 1='1，就可能完成登录；

控制层：UserController

@RequestMapping("user")
@RestController
public class UserController {
    @Autowired
    private UserService userService;
    @RequestMapping("/login")
    public boolean login(String name, String password) {
        UserInfo userInfo = userService.queryUserByPassword(name, password);
        if(userInfo != null) {
            return true;
        }
        return false;
    }
}

业务层：UserService

@Service
public class UserService {
    @Autowired
    private UserInfoMapper userInfoMapper;
 
    public UserInfo queryUserByPassword(String name, String password) {
        List<UserInfo> userInfos = userInfoMapper.queryUserByPassword(name, password);
        if(userInfos != null && userInfos.size() > 0) {
            return userInfos.get(0);
        }
        return null;
    }
}

数据层：UserInfoMapper

@Mapper
public interface UserInfoMapper {
    @Select("select username, `password`, age, gender, phone from userinfo where username= '${name}' and password='${password}' ")
    List<UserInfo> queryUserByPassword(String name, String password);
}

访问浏览器，http://127.0.0.1:8080/user/login?name=shenmengyao&password=222222，

接下来访问SQL注⼊的代码：password 设置为 ' or 1='1

访问：http://127.0.0.1:8080/user/login?name=shenmengyao&password=%27%20or%201=%271结果也能访问成功；

3. 排序功能

从上面的例子中，可以得出结论：${} 会有SQL注入的风险，所以我们尽量使用 #{} 完成查询，${}也有其存在的必要性，接下来我们看 ${} 的使用场景。

UserinfoMapper接口代码如下：（使用 ${} ）

@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> queryAllUserBySort(String sort);
}

测试类代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUserBySort() {
        userInfoMapper.queryAllUserBySort("desc");
    }
}

运行测试类代码，结果如下：（按照了id逆序方式展示）

UserinfoMapper接口代码改变：（使用 #{} ）

运行测试类（不变），报错了，报错信息如下：

可以看到，#{} 传参数是通过 ? 占位，#{} 根据参数类型判断是否拼接引号 ' '，如果参数类型是String，就会加上引号。所以SQL语句传参的desc会带有引号，但是SQL语句的desc是不带引号的，所以报错了，其转换后SQL语句如下：

select id, username, age, gender, phone, delete_flag, create_time, update_time from userinfo order by id 'desc'

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

使用 ${} 有SQL注入的风险，怎样解决呢？按上面的这种情况举例子

1、让后端进行校验（在Controller这就直接进行规定参数的内容），传进来的参数必须是asc或者desc。

2、不传递参数，Mapper接口代码，@Select注解只直接使用asc或者desc，没有参数的传递，如图：

3、使用第三方工具辅助。

4. like查询

UserInfoMapper接口代码如下：

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

直接使用 #{} 会报错，把 #{} 改成 ${} 可以正确查出来，但是 ${} 存在SQL注⼊的问题，所以不能直接使⽤ ${}。
解决办法：使⽤ mysql 的内置函数 concat() 来处理，代码如下：

@Mapper
public interface UserInfoMapper {
    @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);
}

测试类代码如下：

@SpringBootTest
class UserInfoMapperTest {
    @Autowired
    private UserInfoMapper userInfoMapper;
    @Test
    void queryAllUserByLike() {
        System.out.println(userInfoMapper.queryAllUserByLike2("zhangsan"));
    }
}

结果如下：

比直接使用 ${} 好，其代码如下：下面这种写法有SQL注入的风险。

@Mapper
public interface UserInfoMapper {
    @Select("select id, username, age, gender, phone, delete_flag, create_time, update_time "
            + "from userinfo where username like '%${key}%' ")
    List<UserInfo> queryAllUserByLike(String key);
}

5. 数据库连接池

上面的Mybatis代码练习中，我们使用了数据库连接池技术，避免频繁的创建连接，销毁连接，下面我们来了解数据库连接池。

5.1 介绍

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

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

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

优点：1、减少了网络开销 2、资源重用 3、提升了系统的性能

5.2 使用

常见的数据库连接池：C3P0、DBCP、Druid、Hikari，目前比较流行的是Hikari、Druid。

SpringBoot默认使用的数据库连接池就是：Hikari，如图：

Hikari也是以追求性能极致为⽬标，如果我们想把默认的数据库连接池切换为Druid，只需要引入相关依赖即可，pom.xml代码如下：

        <!-- druid启动器的依赖  -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-3-starter</artifactId>
            <version>1.2.21</version>
        </dependency>

Druid连接池是阿里巴巴开源的数据库连接池项目。

6.总结

1、MySQL开发企业规范

（1）表名，字段名使⽤小写字⺟或数字，单词之间以下划线分割。尽量避免出现数字开头或者两个下划线中间只出现数字。数据库字段名的修改代价很大，所以字段名称需要慎重考虑。

MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名，表名，字段名都不允许出现任何大写字母，避免节外⽣枝。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name

（2）表必备三字段：id，create_time，update_time

id 为主键，类型为 bigint unsigned，单表时⾃增，步长为 1。
create_time，update_time 的类型均为 datetime 类型，create_time表示创建时间，
update_time表⽰更新时间。
有同等含义的字段即可，字段名不做强制要求。

（3）在表查询中，避免使⽤ * 作为查询的字段列表，标明需要哪些字段

1. 增加查询分析器解析成本。
2. 增减字段容易与 resultMap 配置不⼀致。
3. ⽆⽤字段增加⽹络消耗，尤其是 text 类型的字段。

2、#{} 和 ${} 区别

（1）#{}：预编译处理，${}：字符直接替换。

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

（3）但是一些场景，#{} 不能完成，比如排序功能，表名、字段名作为参数时，这些情况需要使用 ${} 。

（4）模糊查询虽然可以使用 ${} 完成，但因为存在 SQL注入的问题，所有通常使用MySQL内置函数concat 来完成。

ps：本文的内容就到这里了，如果对你有所帮助的话就请以及三连哦！！！

本站资源均来自互联网，仅供研究学习，禁止违法使用和商用，产生法律纠纷本站概不负责！如果侵犯了您的权益请与我们联系！

转载请注明出处：免费源码网-免费的源码资源网站 » Mybatis--其他查询操作和数据库连接池（下下）

点赞(0) 打赏

本文分类：文章资讯
本文标签：Mybatis--其他查询操作和数据库连接池（下下）
浏览次数：30 次浏览
本文链接：https://freeymw.com/article/24637.html

评论列表共有 0 条评论

暂无评论

Mybatis--其他查询操作和数据库连接池（下下）

序

1. 多表查询

2. #{} 和 ${}

2.1 #{} 和 ${} 的使用

2.2 #{} 和 ${} 的区别

2.3 登录时sql注入的案例

3. 排序功能

4. like查询

5. 数据库连接池

5.1 介绍

5.2 使用

6.总结

评论列表 共有 0 条评论

发表评论 取消回复

评论列表共有 0 条评论

发表评论取消回复