【 Spring Mybatis 复杂的查询操作 】

引言

前面我们已经学会了使用 Mybatis 进行增，删，改操作，也实现了简单的查询操作 ！下面我们就来学习更加复杂的查询操作，大体步骤与前面的操作类似 ！

一、参数占位符 #{} 和 ${}

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

预编译处理是指：MyBatis 在处理#{}时，会将 SQL 中的 #{} 替换为?号，使⽤ PreparedStatement 的set ⽅法来赋值。直接替换：是MyBatis 在处理 ${} 时，就是把 ${} 直接替换成变量的值

xml 中相应的查询语句仅修改了占位符，却达到了不同的效果：

由此得出结论，#{}适用于所有类型的参数匹配，而${}在某些场合不适合String类型参数匹配，但如果参数是一个 SQL 关键字时就不需要单引号 ，此时 ${} 直接替换关键字就起到了重要作用！

如下 ${} 重要性示例：

当你在淘宝购买东西时，你需要根据价格高低来筛选商品，此时SQL关键字 desc/asc 就起到了作用，如查询语句：select * from goodsInfo order by price desc; 就达到了根据价格从低到高查询 ！

上述你只需要将参数 desc 或 asc 传递就可以 ！如果尝试使用 #{} 来获取参数，我们看效果 ！以下为伪代码

实现 mapper 接口：

//${} 参数直接替换 场景演示:根据价格进行倒序或正序
public List<GoodsInfo> getGoodsList(@Param("order") String order);

xml 文件实现：

select * from goodsinfo order by price #{order} 

当传递参数为 desc 时，实际的效果：

select * from goodsinfo order by price ‘desc’;

我们尝试在数据库中去运行此代码，会发现它出错了！原因显而易见，此时的 desc 加上了单引号，它并不是一个SQL关键字。所以如果使用#{}预处理的话就会把 sql关键字如 desc 加上单引号，当成一个value值而不是一个sql关键字，就造成了查询失败 ！

此时 ${} 的作用就派上用场了，如下将 #{} 替换为 ${}，看效果：

select * from goodsinfo order by price desc;

以上就是一条正确的 SQL 语句，所以 ${} 直接替换成了 desc，没有加单引号 ！

---

二、SQL 注入

上面我们讲到了 ${} 的一个重要作用，当参数为 SQL 关键字时，我们需要使用 ${} 直接替换参数！但是直接替换参数又会产生严重的弊端，如果传来的参数不合法可能会对数据库中的数据造成严重的威胁，如 SQL 注入问题 ！如下：

mapper 接口实现：

//${}安全漏洞： SQL注入案例：
public UserInfo login(@Param("username") String username,@Param("password") String password);

xml 文件实现：

<!-- SQL注入案例：登录功能-->
<select id="login" resultType="com.example.demo.model.UserInfo">
     select *
     from userinfo
     where username = '${username}' #手动加上单引号
     and password = '${password}';
</select>

生成测试代码：

    @Test
    //${}安全漏洞： SQL 注入问题演示
    void login() {
        //1、能够通过正确的账号密码得到用户信息
//        String username = "admin";
//        String password = "admin";
//        UserInfo userInfo = userInfoMapper.login(username,password);
//        log.info("用户信息：" + userInfo);

        //2、输入不正确的密码，也能够得到用户信息   SQL 注入问题
        String username = "admin";
        String password = "' or 1='1";//此时 1=’1‘ 相当于一个万能钥匙，能够获取任何一个用户信息
        UserInfo userInfo = userInfoMapper.login(username,password);
        log.info("用户信息：" + userInfo);
    }

上述通过正确的账号密码能够获取到用户信息 ！ 但是我们尝试输入不正确的密码时，如 ’ or 1='1
我们观察测试结果，如下：

我们发现，虽然输入了不正确的密码，但还是得到了该用户正确的信息，这就是一个非常恐怖的事情了 ！！

这就是 SQL 注入问题，此时 1=’1‘ 相当于一个万能钥匙，能够获取任何一个用户信息 ！！ 那如何解决避免这种问题呢？第一，⽤于查询的字段，尽量使⽤ #{} 预查询的⽅式，它的安全性能更高。第二，不得不去使用${}时，就务必在业务代码中对传递的参数进行安全效验 ！！

---

三、like 模糊查询

当我们需要使用 like 进行模糊查询时，我们的查询语句如下：

select * from userinfo where username like '%冯%';

我们看查询结果：

所以 ‘%冯%’ 表示，查询表中 username包含 冯 的所有数据 ！！

那我们将 冯 作为参数传递，使用#{} 占位符来观察效果，如下

mapper 接口实现：

public List<UserInfo> getUserByName(@Param("username") String username);

xml 文件实现：

<select id="getUserByName" resultType="com.example.demo.model.UserInfo">
    select *
    from userinfo
    where username like '%#{username}%';
</select>

相应的测试代码：

    @Test
    // 特殊的 like 查询
    void getUserByName() {
        String username = "冯";
        List<UserInfo> userInfos = userInfoMapper.getUserByName(username);
        log.info("用户信息：" + userInfos);
    }

观察运行结果如下：

我们发现运行失败，所以 like模糊查询 使⽤ #{} 会报错，其具体实现的 SQL 相当于如下：

select * from userinfo where username like '%'username'%';

此时就会把 username 用单引号引起来，当成一个字符 ！而使用 ${} 在业务层的值又不能穷举，所以此时单一使用参数占位符就失效了，那我们该如何解决这个问题呢？

可以考虑使⽤ mysql 的内置函数 concat() 来处理拼接，实现代码如下：

xml 文件实现：

<select id="getUserByName" resultType="com.example.demo.model.UserInfo">
    select *
    from userinfo
    where username like concat('%', #{username}, '%');
</select>

执行上述测试类，观察运行结果：

上述代码就运行成功了，而 concat(‘%’,#{username},‘%’) 就达到了 %?% 效果，有了正确的SQL语句：select * from userinfo where username like ‘ %冯% ’;

---

四、返回类型：resultType 和 resultMap

如果是增、删、改返回的是受影响的⾏数，那么在 mapper.xml 中是可以不设置返回的类型的，如下图所示：

然⽽即使是最简单查询⽤户的名称也要设置返回的类型，否则会出现如下错误

查询不设置返回类型的错误示例演示：

controller 代码：

@RequestMapping("/getname")
public String getNameById(Integer id) {
     return userService.getNameById(id);
}

xml 文件实现：

<select id="getNameById">
      select username from userinfo where id=#{id}
</select>

使用 postman 测试如下：

显示运⾏了⼀个查询但没有找到结果映射，也就是说对于 查询标签来说⾄少需要两个属性：

1. id 属性：⽤于标识实现接⼝中的那个⽅法；
2. 结果映射属性：结果映射有两种实现标签：< resultMap > 和 < resultType >

---

4.1 返回类型：resultType

绝⼤数查询场景可以使⽤ resultType 进⾏返回，如下代码所示：

<!-- select id中的id表示要实现接口中的具体方法  resultType：表示查询返回的类型，也就是开头定义的实体类-->
<select id="getUserById" resultType="com.example.demo.model.UserInfo">
    select *
    from userinfo
    where id = #{id};
</select>

它的优点是使⽤⽅便，直接定义到某个实体类即可

4.2 返回字典映射：resultMap

resultMap 使⽤场景：

1. 字段名称和程序中的属性名不同的情况，可使⽤ resultMap 配置映射
2. ⼀对⼀和⼀对多关系可以使⽤ resultMap 映射并查询数据

字段名和属性名不同的情况：

表中的字段名为： username

程序实体类中的属性为：name

当我们使用 resultType 去查询用户时，如下：

<select id="getUserById" resultType="com.example.demo.model.UserInfo">
    select *
    from userinfo
    where id = #{id};
</select>

使用 postman 测试得：

显而易见， resultType 在这里已经失效了，这个时候就可以使⽤ resultMap 了，resultMap 的使⽤如下：

代码实现：

    <!-- resultMap id是给它取一个名字，type 表示要映射的实体类 -->
    <resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
        <!-- 主键映射 -->
        <id column="id" property="id"></id>
        <!-- 普通属性映射:column为数据库中的字段名，property为程序中的属性，下面可以解决不匹配问题-->
        <result column="username" property="name"></result>
        
        <result column="password" property="password"></result>
        <result column="photo" property="photo"></result>
        <result column="createtime" property="createtime"></result>
        <result column="updatetime" property="updatetime"></result>
        <result column="state" property="state"></result>
    </resultMap>

            <select id="getUserById" resultMap="BaseMap">
                select *
                from userinfo
                where id = #{id};
            </select>

注意：

单表查询时，resultMap里面的字段映射不写全是可以查到用户信息的，因为自己对自己已经知根知底，知道哪些字段设置了，但是多表查询时字段必须要设置完整

再次使用 postman 测试得：

即使属性名和字段名不一样的情况，上述也通过 resultMap 查询到了相应的信息 ！而 ⼀对⼀和⼀对多关系也可以使⽤ resultMap 映射并查询数据，下面我们就在多表查询中来学习 ！！

---

五、多表查询

⼀对⼀和⼀对多关系也可以使⽤ resultMap 映射并查询数据 ！

一对一的关系映射( ⼀篇⽂章只对应⼀个作者 )

我们实现 根据文章 id 来查询文章信息，里面包含了 作者信息 ！

1. 定义文章实体类 ArticleInfo，里面包含了属性 userInfo

@Data
public class ArticleInfo {
    private int id;
    private String title;
    private String content;
    private String createtime;
    private String updatetime;
    private int uid;
    private int rcount;
    private int state;
    
    private UserInfo userInfo;//一篇文章有一个作者  定义一个外键
}

2. 实现 ArticleInfoMapper 接口

@Mapper
public interface ArticleInfoMapper {
    //根据文章 id 获取文章
    public ArticleInfo getArticleById(@Param("id") Integer id);
}

3. ArticleInfoMapper.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.demo.mapper.ArticleInfoMapper">
    <resultMap id="BaseMap" type="com.example.demo.model.ArticleInfo">
        <id column="id" property="id"></id>
        <result column="title" property="title"></result>
        <result column="content" property="content"></result>
        <result column="createtime" property="createtime"></result>
        <result column="updatetime" property="updatetime"></result>
        <result column="uid" property="uid"></result>
        <result column="rcount" property="rcount"></result>
        <result column="state" property="state"></result>

        <!-- association:实现一对一查询 property设置属性是啥，resultMap设置要映射哪个实体类 columnPrefix:解决相同字段覆盖的问题-->
        <association property="userInfo" resultMap="com.example.demo.mapper.UserInfoMapper.BaseMap" columnPrefix="u_">
        </association>

    </resultMap>

    <!-- 想要获取到用户信息，还是必须要进行多表查询 -->
    <!-- 进行多表联查时，如果两个表有相同的字段，前面表的该字段会将后面表的该字段覆盖，如下代码，两个表都存在id字段，当修改userinfo中admin的id=2时，
    运行结果还是此id=1，确实被articleinfo中的id=1给覆盖 所以可以采用加前缀方法解决 -->
    <select id="getArticleById" resultMap="BaseMap">
        select a.*, u.id u_id, u.username u_username, u.password u_password
        from articleinfo a
                 left join userinfo u on a.uid = u.id
        where a.id = #{id}
    </select>
</mapper>

注意： 进行多表联查时，如果两个表有相同的字段，前面表的该字段会将后面表的该字段覆盖，如两个表都存在id字段，当修改userinfo中admin的id=2时，运行结果还是此id=1，确实被articleinfo中的id=1给覆盖 所以可以采用加前缀方法 columnPrefix=“u_” 解决

< association >标签，表示⼀对⼀的结果映射 ！文章实体类中有一个属性表示作者信息，且一篇文章对应一个作者。其中property设置属性为文章实体类中的作者用户属性，resultMap设置要映射为用户实体类 ！上述 resultMap=“BaseMap” 中的BaseMap 就是 UserInfoMapper.xml 中的设置，如下：

    <resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
        <!-- 主键映射 -->
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="photo" property="photo"></result>
        <result column="createtime" property="createtime"></result>
        <result column="updatetime" property="updatetime"></result>
        <result column="state" property="state"></result>
    </resultMap>

4. 测试代码

@SpringBootTest
@Slf4j
class ArticleInfoMapperTest {

    @Resource
    private ArticleInfoMapper articleInfoMapper;

    @Test
    // 1 对 1 场景，一篇文章对应一个作者
    void getArticleById() {
        ArticleInfo articleInfo = articleInfoMapper.getArticleById(1);
        log.info("文章信息：" + articleInfo);
    }
}

5. 运行结果
   

显而易见，我们成功通过文章 id 查询到了文章信息，且信息里面包含了作者的信息 ！！

---

⼀对多的关系映射(⼀个⽤户多篇⽂章案例)

实现根据用户 id 查询用户信息，里面包含了用户发表的所有文章信息

1. 定义用户实体类 UserInfo ，加入属性 articleInfoList

@Data
public class UserInfo {
    private Integer id;
    //private String name; 当实体类中的属性与数据库中的字段不匹配时，可以使用resultMap 来解决
    private String username;
    private String password;
    private String photo;
    private String createtime;
    private String updatetime;
    private int state;
    
    private List<ArticleInfo> articleInfoList;//一个作者对应多篇文章  1对多场景
}

2. 实现 UserInfoMapper 接口

//一对多场景：一个作者对应多篇文章  根据用户id获取用户信息和发表的所有文章
public UserInfo getUserAndArticleByUid(@Param("uid") Integer uid);

3. UserInfoMapper.xml 实现

<resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
        <!-- 主键映射 -->
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="photo" property="photo"></result>
        <result column="createtime" property="createtime"></result>
        <result column="updatetime" property="updatetime"></result>
        <result column="state" property="state"></result>

        <collection property="articleInfoList" resultMap="com.example.demo.mapper.ArticleInfoMapper.BaseMap"
                    columnPrefix="a_">
        </collection>

    </resultMap>


<select id="getUserAndArticleByUid" resultMap="BaseMap">
        select u.*,
               a.id         a_id,
               a.title      a_title,
               a.content    a_content,
               a.createtime a_createtime,
               a.updatetime a_updatetime
        from userinfo u
                 left join articleinfo a on u.id = a.uid
        where u.id = #{uid}
    </select>

⼀对多需要使⽤ < collection > 标签，⽤法和 < association > 相同，后续的验证我们就不再写了，与前面一对一相似 ！

---