Mybatis多表查询与动态SQL特性详解

吾爱主题阅读：195 2022-12-01 16:54:00 评论：0

1.较复杂的查询操作

1.1 参数占位符 #{} 和 ${}

#{}:预处理符，如将id=#{2}替换为id=?，然后使用2替换?。

${}:替换符，如将id=${2}替换为id=2。

两种占位符都可以正常使用的场合：传入的参数类型是数值类型

使用${}：

select * from userinfo where id=${id}
select * from userinfo where id=2

使用#{}：

select * from userinfo where id=#{id}
select * from userinfo where id=?

对于这两种参数占位符，个人建议能使用#{}就使用#{}，因为${}存在SQL注入的问题，以及如果传入的类型是字符串也会出类型。

只能使用#{}而不能使用${}的场合：传入参数类型为String

使用${}：

select * from userinfo where username=${username}
//实际执行的语句
Preparing: select * from userinfo where username=张三

但是在sql中通过字符串来查询数据，是需要加上引号的，而使用${}生成的sql并没有带引号，因此不适用于字符串参数的sql。

使用#{}：

select * from userinfo where username=#{username}
//实际执行语句
select * from userinfo where username=?

由于使用#{}是将目标参数替换为占位符，然后利用JDBC中的占位符机制实现sql语句的填充，所以使用#{}构造的sql是可以正常运行的，并且没有SQL注入的问题。

所以，#{}相比于${}，它支持所有类型的参数，包括数值类与字符串类，而${}支持数值类型，不支持字符串类型的参数，但也可以在原来sql里面为${}外面加上一对引号。

select * from userinfo where username="${username}"
//实际执行语句
Preparing: select * from userinfo where username="张三"

当传递的参数为字符串类型的时候，虽然加上一对引号，使用${}也可以做到，但是${}存在SQL注入问题，所以仍然不推荐，有关SQL注入后面我们会介绍到。

大部分场合下，使用#{}都可以解决，但还是存在一小部分只能是${}来处理的。

如当我们需要按照升序或者逆序得到数据库查询结果的时候，这种场合就只能使用${}，使用#{}会报错，我们来演示一下。

首先，我们在Mapper接口中声明一个方法：作用就是按照排序获取结果集

public List<UserInfo> getOrderList(@Param(value = "order") String order);

我们再去xml文件中去写sql语句：首先我们使用$进行演示

    <select id="getOrderList" resultType="com.example.demo.model.UserInfo">
        select * from userinfo order by createtime ${order};
    </select>

我们进行一个单元测试，单元测试代码很简单，就是调用sql，然后得到结果集：

    @Test
    void getOrderList() {
        List<UserInfo> userMappers = userMapper.getOrderList("desc");
        System.out.println(userMappers);
    }

单元测试结果：

可以正常查询，得到的结果与预期也是相同的。

我们再来试一试使用#{}来构造sql语句：

    <select id="getOrderList" resultType="com.example.demo.model.UserInfo">
        select * from userinfo order by createtime #{order};
    </select>

单元测试逻辑与代码不变，我们再来看看单元测试执行的一个结果：

我们发现程序报错了，这是因为使用了desc的字符串替换了占位符，而我们所需要的不是一个desc字符串，而是直接一个desc的关键字，所以sql也抛出了语法错误，最终执行的sql为：

select * from userinfo order by createtime ‘desc";

期望执行的sql为：

select * from userinfo order by createtime desc;

所以在传递sql关键字的时候，不能使用#{}，只能使用${}。

1.2SQL注入

SQL注入就是使用${}，使用一些特殊的语句，来达到非法获取数据的目的，如不通过正确的密码获取某账户的信息，下面我们来演示一下，就以登录的例子来演示，SQL注入可以在不知道密码的前提下登录成功，并且获取到用户的相关信息。

首先我将数据库只保留一个用户信息，目的是为了方便演示SQL注入问题：

第一步，在Mapper接口中定义方法login，返回登录成功的用户对象。

public UserInfo login(@Param("username") String username, @Param(("password")) String password);

第二步，在xml文件中编写SQL语句，我们需要演示SQL注入，所以我们使用${}来构造sql语句。

    <select id="login" resultType="com.example.demo.model.UserInfo">
        select * from userinfo where username="${username}" and password="${password}";
    </select>

第三步，编写测试类，我们在这个测试类中，传入有注入问题的SQL语句' or 1='1，使得不需要密码就能拿到相关的用户信息。

    @Test
    void login() {
        String username = "admin";
        String password = "" or 1="1";

        UserInfo userInfo = userMapper.login(username, password);
        System.out.println(userInfo);
    }

单元测试运行结果：

我们在不知道用户密码的情况下，登录成功，并拿到了用户的信息。

最终执行的一段sql语句为：

select * from userinfo where username="admin" and password="" or 1="1";

相当于它在原来条件判断的语句下，后面有加上一个或的逻辑，并且或后面的表达式为true，这样就使得原来的SQL语句中的条件判断部分一定为真，所以就在密码不知道的情况下拿到了用户的基本信息。

所以我们能不使用#{}就不使用${}，因为存在SQL注入问题，如果必须使用${}则需要验证一下传递的参数是否合法，比如上面定义排序的sql，传递的参数只能是desc或者是asc，如果不是就不能执行这条SQL，防止SQL注入的发生。

1.3like查询

在Mybatis中使用like查询比较特殊，因为直接使用#{}会报错，而使用${}，由于输入的字符串情况很多，无法做到枚举，验证比较困难，无法避免SQL注入问题。

首先，我们来演示使用#{}进行like查询，步骤我就不详细写了，就是查询的步骤。

第一步，声明方法。

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

第二步，xml写sql。

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

第三步，单元测试。

    @Test
    void getListByName() {
        String username = "a";
        List<UserInfo> list = userMapper.getListByName(username);
        for (UserInfo userInfo : list) {
            System.out.println(userInfo);
        }
    }

运行结果：报错了，因为#{}会被替换成一个字符串，而在这个%#{username}%语句中#{username}不能带上引号，带上就违背SQL语法，造成错误。

这个时候，由于#{}多出一对引号，${}无法枚举所有情况进行验证会产生SQL注入，所以不能直接使用#{}，我们需要搭配MySQL内置的字符串拼接语句concat。

我们将sql改为concat进行字符串拼接：

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

重新测试一下，发现可以正常执行了：

1.4resultType与resultMap

resultType表示数据库返回的数据映射在java程序中所对应的类型，只要定义类中的字段名与数据库中表的字段名字一致就没有任何问题，但是如果字段名存在冲突，则冲突的字段无法获取到数据库查询的结果。

比如用户名属性在数据库中的名字是username，而在java程序类中的属性名为name，此时通过mybatis将数据传递到程序中的对象时，获取到的name属性为null，就不能正确地获取到对应的属性值，为了解决这个数据库字段与类中中字段不匹配的问题，我们需要使用到resultMap。

resultMap的使用方式就是在xml文件中设置<resultMap>标签，至少需要设置两个属性，一个是id表示你这个resultMap标签的名字，还有一个是type属性，它表示映射到程序中类的类型，需包含包名。

这个标签里面需要设置至少两个子标签，一个是id标签，另外一个是result标签，前者表示主键，后者表示数据库表中普通的列，这两种标签也是至少需要设置两个属性，一个是column表示数据库表中的字段名，另外一个是property表示程序类中的字段名，如果只是在单表进行查询，只设置不同字段名的映射就可以了，但是如果是多表查询，必须将数据表中所有的字段与类中所有的字段生成映射关系。

就像下面这样，图中类与数据表字段是相同的，实际情况会存在不同的字段名：

1.4多表查询

1.4.1一对一表映射

一对一关系就是对于一个属性只与另外一个属性有关系的映射，这就是一对一的关系，举个例子，对于一篇博客，它只会对应到一个用户，则博客与用户的关系是一对一的关系，下面我们尝试在mybatis中实现一对一多表联查。

那么，首先我们先将数据库的博客表与查程序中的博客类对应起来，就是按照数据库中的博客表建立一个类：

@Data
public class Articleinfo {
    private Integer id;
    private String title;
    private String content;
    private String createtime;
    private String updatetime;
    private Integer uid;
    private Integer rcount;
    private Integer state;
    //不妨多一个属性，用户表
    private UserInfo userInfo;
}

目前文章表中只有一条数据，如下图：

第二步，创建Mapper接口和对应的xml文件。

第三步，在接口中声明方法和在xml中写sql标签与语句。

//根据文章名称获取文章对象
public Articleinfo getArticleById(@Param("id") Integer id);

    <select id="getArticleById" resultType="com.example.demo.model.Articleinfo">
        select * from articleinfo where id=#{id};
    </select>

第四步，编写测试方法，我们直接调用查询方法，然后使用日志输出对象。

    @Test
    void getArticleById() {
        Articleinfo articleinfo = articleMapper.getArticleById(1);
        log.info("文章详情：" + articleinfo);
    }

由于我们数据表与类的字段名是一致的，那些普通的属性都一一对应上了，都成功被赋值了，但是由于UserInfo类在数据表中没有，所以并没有得到UserInfo对象，如果我们想要拿到这个对象，我们得使用resultMap。

问题主要有两个，第一，数据库查询到的用户表没有映射到UserInfo对象，第二，查询的SQL语句是单表查询语句，不是多表查询语句。

所以想要实现一对一多表查询，需要设置多表查询SQL语句，我们使用左外连接进行多表查询：

    <select id="getArticleById" resultMap="BaseMap">
        select a.*, u.* from articleinfo as a left join userinfo as u on  a.uid=u.id where a.id=#{id};
    </select>

此外，我们除了设置UserInfo与Articleinfo类中每个属性与数据表的映射之外，我们还要在Articleinfo类对应的resultMap中使用association标签。最少需要设置两个属性，一个是property表示在主表Articleinfo中对应副表UserInfo映射对象的变量名，另外一个是副表UserInfo对应的resultMap。

Articleinfo类对应的resultMap：

    <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="userInfo" resultMap="com.example.demo.mapper.UserMapper.BaseMap"></association>
    </resultMap>

UserInfo类对应的resultMap：

    <resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
<!--        column 表示数据库字段名 property 表示对应对象的字段名，设置这两个值可以建立映射-->
<!--        主键约束-->
        <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>

如果UserInfo类的resultMap没有将所有的属性都与数据库的表映射，就会造成获取到的userInfo对象中的数据不完整，假设只设置了id与name的映射，那获取到的对象只有id与name有值。

将两张表的resultMap映射好后，我们运行同样的单元测试案例，运行结果如下：

但是，仍然存在一个问题，那就是我们所建的两个表存在名字相同的字段，可能会出现数据覆盖的情况，如两个表的主键都叫id，但是id在两个表的含义是不同的，在用户表它表示用户id，在文章表它表示文章的id，现在我们将获取两表的数据的id改为不相同，再来看一看单元测试运行的结果：

按理来说，由于不存在id为1的用户，所以获取到UserInfo对象应该为null才对，但是运行的结果却存在UserInfo对象，并且与文章表的重名字段都被赋值了文章表中的数据，为了解决这个问题，我们必须在文章表（主表）的resultMap中设置属性columnPrefix，它的值随便设置，作用是识别副表字段时加上一段前缀，如我们给用户表的字段加上前缀u_，此时sql中就不能使用*来一次表示所有元素了，需要一个一个单独设置，并将字段全部重命名，带上u_前缀。

association字段设置：

<association property="userInfo" columnPrefix="u_" resultMap="com.example.demo.mapper.UserMapper.BaseMap" ></association>

SQL语句需要将用户表的字段全部重命名：

    <select id="getArticleById" resultMap="BaseMap">
        select a.*, u.id as u_id,
               u.username as u_username,
               u.password as u_password,
               u.photo as u_photo,
               u.createtime as u_createtime,
               u.updatetime as u_updatetime,
               u.state as u_state
        from articleinfo as a left join userinfo as u on  a.uid=u.id where a.id=#{id};
    </select>

我们将userInfo对应的用户表的id再改回为1，让查询有关于UserInfo类的数据。

再次运行单元测试案例：

我们是能够获取到相应的数据的，所以如果两个表字段重名了，进行多表查询时，需要设置columnPrefix属性，这样才能够避免不同表同名字段数据覆盖的问题。

所以，在创建数据库的数据表时，尽量不要让表与表中的字段重名。

1.4.2一对多表映射

一对多的关系，就是对于一个属性，它对映着多个其他的属性，比如用户与博客之间的关系，一个用户可以对应多篇博客，则用户与博客之间的关系就是一对多的关系。同样的下面我们尝试使用mybatis实现多对多的多表联查。

下面我们以用户表为主，文章表为辅，来演示如何进行一对多关系的多表查询。

既然是一对多的关系，那我们可以在UserInfo类中加上一个储存ArticleInfo对象的List，来储存用户发布或所写的文章。

@Data
public class UserInfo {
    private Integer id;
    private String username;
    private String password;
    private String photo;
    private String createtime;
    private String updatetime;
    private Integer state;
    private List<Articleinfo> aList;
}

实现多表查询的大致过程如下：

在Mapper接口中声明方法，我们声明一个方法，就是通过用户id获取用户信息以及对应的文章列表。
在xml文件当中写resultMap映射关系，与一对一多表查询不同的是，我们需要设置collection标签，而不是association标签。
在xml文件的resultMap标签中至少设置resultMap名字id，对应映射的类type等属性，里面需要设置数据表与类中所有字段的映射，以及设置collection标签，需要设置property属性表示需映射的对象名，设置resultMap即副表的resultMap路径，由于你无法保证表与表之间是否存在重名字段，需要设置columnPrefix为副表的字段添加上一个前缀，防止重名数据覆盖。

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

在对应的xml文件当中写SQL标签以及语句。

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

编写单元测试代码，测试代码是否编写正确。

    @Test
    void getUserAndArticlesById() {
        Integer id = 1;
        UserInfo userInfo = userMapper.getUserAndArticlesById(id);
        log.info("用户信息：" + userInfo);
    }

运行结果：

2.动态SQL

首先来说一下什么是动态SQL，官方文档对于动态SQL的定义是：

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

前面所说的mybatis增删查改，那些传入的参数都是一定会传入的，但是在实际情况中，很多参数都是非必传参数，使用动态SQL就可以解决传入的参数是非必传参数的情况。

动态SQL可以解决多余符号的问题，如,等。

2.1if标签

if标签的作用就是判断一个参数是否有值，如果没有值就将对应的参数隐藏。

语法：

<if test="表达式">
	sql
</if>
//例如
<if test="参数!=null">
	sql部分语句
</if>

当表达式为真，则插入if标签中的sql，否则不插入。

我们以在用户表中插入一条数据为例，插入的数据中头像photo不是必传的参数：

方法声明：

    //使用动态sql插入数据
    public int addUser(UserInfo userInfo);

动态SQL语句：

其中的photo是非必传参数，我们使用if标签来判断它是否有值，没有值就不插入目标的SQL语句。

    <insert id="addUser">
        insert into userinfo(username, password
        <if test="photo!=null">
            , photo
        </if>
        ) values(#{username}, #{password}
        <if test="photo!=null">
            , #{photo}
        </if>
        )
    </insert>

单元测试代码：

    @Test
    void addUser() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("张三疯");
        userInfo.setPassword("123456");

        int res = userMapper.addUser(userInfo);
        log.info("受影响的行数为:" + res);
    }

在单元测试代码中，我没有给photo赋值，if标签会判断它为空，不会插入对应photo的SQL，因此插入数据photo为默认值。

结果：

数据库查询结果：

再来试一试给photo传值的情况，它生成的SQL有三个参数：

    @Test
    void addUser() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("张无忌");
        userInfo.setPassword("12345611");
        userInfo.setPhoto("张无忌.png");
        int res = userMapper.addUser(userInfo);
        log.info("受影响的行数为:" + res);
    }

运行结果：

最终生成的语句多了一个photo参数。

2.2trim标签

前面所说的if标签可以实现非必传参数SQL的构造，在极端情况下，有很多个非必传参数，此时如果只使用if标签构造出的SQL语句很有可能会多出一个,，因为有很多非必传参数，如果只传来一个参数，由于不确定后面是否还会有参数，因此会预留一个,，此时如果没有其他参数，就会多出一个参数。

而trim标签可以做到这一点，它可以去除SQL语句前后多余的某个字符，它需要搭配if标签使用。

<trim>标签中有如下属性：

prefix：表示整个语句块，以prefix的值作为前缀
suffix：表示整个语句块，以suffix的值作为后缀
prefixOverrides：表示整个语句块要去除掉的前缀
suffixOverrides：表示整个语句块要去除掉的后缀

语法：

<trim prefix="前缀符", suffix="后缀符", prefixOverrides="去除多余的前缀字符", suffixOverrides="去除多余的后缀字符">
	<if test="表达式">
		...
	</if>
	...
	...
</trim>

假设username password photo都是非必传参数，但是至少传递一个，我们来写插入语句的动态SQL。

    <insert id="addUser2">
        insert into userinfo
        <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
            <if test="username!=null">
                username,
            </if>
            <if test="password!=null">
                password,
            </if>
            <if test="photo!=null">
                photo
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
            <if test="username!=null">
                #{username},
            </if>
            <if test="password!=null">
                #{password},
            </if>
            <if test="photo!=null">
                #{photo}
            </if>
        </trim>
    </insert>

单元测试代码：

    @Test
    void addUser2() {
        UserInfo userInfo = new UserInfo();
        userInfo.setUsername("wangwu");
        userInfo.setPassword("12345622");
        int res = userMapper.addUser(userInfo);
        log.info("受影响的行数为:" + res);
    }

运行结果与生成的SQL语句：

我们发现逗号它自动去除了。

2.3where标签

where标签主要是实现where关键字的替换，如果SQL中没有使用到where（没有查询条件），就会隐藏，存在查询条件，就会生成含有where的查询SQL语句，并且可以去除前面的and。

我们就不以复杂的案例作为演示了，直接写一个最简单的查询，为了简单，我们删除数据库的其他数据，只留admin一条数据。

下面我们来写最简单的查询语句，就是根据id获取一个用户信息。

写动态SQL时，我故意在最前面写一个and来证明where标签是可以自动删除前面多余的and。

    <select id="getUserById" resultType="com.example.demo.model.UserInfo">
        select * from userinfo
        <where>
            <if test="id!=null">
                and id=#{id}
            </if>
        </where>
    </select>

单元测试代码：

    @Test
    void gerUserById() {
        UserInfo userInfo = userMapper.getUserById(1);
        System.out.println(userInfo);
        //Assertions.assertNotNull(userInfo);
    }

结果：

发现自动生成了where语句并删除了多余的and。

如果我们查询一个null值，就不会生成where语句。

以上<where>标签也可以使用 <trim prefix="where" prefixOverrides="and"> 替换。

2.4set标签

其实set标签与where标签很相似，只不过where用来替换查询SQL，set用于修改SQL中，用来自动生成set部分的SQL语句。

set标签还可以自动去除最后面的一个,。

比如我们写一个能够修改账户名username，密码password，头像photo的动态SQL，根据id进行修改。

方法声明：

    //使用动态SQL实现修改用户信息，包括账户名，密码，头像
    public int updateUser(UserInfo userInfo);

动态SQL：

    <update id="updateUser">
        update userinfo
        <set>
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
            <if test="photo!=null">
                photo=#{photo}
            </if>
        </set>
        where id=#{id}
    </update>

单元测试代码：

    @Test
    void updateUser() {
        UserInfo userInfo = new UserInfo();
        //修改密码为123456
        userInfo.setPassword("123456");
        userInfo.setId(1);
        int res = userMapper.updateUser(userInfo);
        log.info("受影响的行数：" + res);
    }

运行结果：

修改成功并且可以根据传入参数个数自动生成相应的修改SQL，以及可以自动去除最后的,。

以上<set>标签也可以使用 <trim prefix="set" suffixOverrides=","> 替换。

2.5foreach标签

对集合进行遍历可以使用foreach标签，常用的场景有批量删除功能。

collection：绑定方法参数中的集合，如 List，Set，Map或数组对象
item：遍历时的每一个对象
open：语句块开头的字符串
close：语句块结束的字符串
separator：每次遍历之间间隔的字符串

为了方便演示批量删除，我们随便插入几条数据到数据库：

方法声明：

    //使用动态sql批量删除元素
    public int deleteIds(List<Integer> ids);

动态SQL语句：

    <delete id="deleteIds">`在这里插入代码片`
        delete from userinfo where id in
        <foreach collection="ids" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </delete>

单元测试代码：

删除数据库中id为10 11 12的用户。

    @Test
    void deleteIds() {
        List<Integer> ids = new ArrayList<>();
        ids.add(10);
        ids.add(11);
        ids.add(12);
        
        int res = userMapper.deleteIds(ids);
        log.info("受影响的行数" + res);
    }

运行结果：