MyBatis（六） 动态SQL案例、模糊查询|where、if、trim、foreach标签使用|#{}和${}的区别

十二、动态SQL

交友网站：珍爱网、百合网 筛选心仪对象 性别 年龄 城市 …

电商平台：淘宝、京东 筛选商品（羽毛球拍） 品牌 价格 …

​ 张三的筛选：性别 女 select * from members where gender=‘女’

​ 李四的筛选：性别 女；年龄 18-23 select * from members where gender=‘女’ and age>=18 and age<=23

​ 张三的筛选：年龄；城市 select * from members where and age>=18 and age<=23 and city=’’

​ … …

​ 我仅仅有三个筛选条件，我的不同组合的SQL语句就有7种。那我如果有四个筛选条件呢，就数不清了。

​ 我不可能根据用户的每一种筛选情况，去写一种查询。有没有一种操作，只要一个查询语句，可以满足所有选择条件的查询呢？我的SQL语句，是根据你的筛选条件，来进行动态地改变。这就叫动态SQL。

筛选（不同的人筛选条件不一样，指的是条件的个数不一样，而不是条件的内容不一样）

用户的筛选条件不同，我们完成筛选执行的SQL也不一样。

我们可以通过穷举法来一一的完成不同条件的筛选，但是这种实现思路过于繁琐。

MyBatis就提供了动态SQL的配置方式来实现多条件查询。

12.1 什么是动态SQL

根据查询条件动态完成SQL的拼接

12.2 where、if标签使用案例

案例：心仪对象搜索

​ （性别、年龄范围、所在城市）

12.2.1 创建数据表

create table members(
	member_id int primary key auto_increment,
    member_nick varchar(20) not null unique,
    member_gender char(2) not null,
    member_age int not null,
    member_city varchar(30) not null
);

12.2.2 创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Member {
    private int memberId;
    private String memberNick;
    private String memberGender;
    private int memberAge;
    private String memberCity;
}

12.2.3 创建DAO接口

在DAO接口中定义一个多条件查询的方法

根据前面的学习，我们如果想写一个例如“按昵称查询”的方法，是很简单的

public interface MemberDAO {
    public Member queryMemberByNick(String nick);
}

但是现在，我对member的检索，可能只需要一个参数，也可能需要多个参数，而且多个参数的组合也是多种多样的。

public interface MemberDAO {
    public ？？？ searchMember( ？？？ );
}

在多条件查询中，如果查询条件不确定，可以直接使用HashMap作为参数

public interface MemberDAO {
    public List<Member> searchMember(HashMap<String,Object> params);
}

到此，先不写mapper配置文件，先写测试类去调用一下这个方法，直观地看一下是怎么传参的：

    @Test
    public void testSearchMember() {
        HashMap<String,Object> params = new HashMap<String,Object>();
        //我需要哪些条件就写上，不需要的不写就可以了。这里面的条件是可以任意地装的。
        //params.put("gender","女");
        params.put("minAge",18);
        //params.put("maxAge",23);
        params.put("city","郑州");
        
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMember(params);

        System.out.println(members);
    }

除了使用HashMap作为参数，也可以定义专门用于存放查询条件的实体类作为封装参数

public interface MemberDAO {
    public List<Member> searchMember(Member member);
}

如果我这样写，我传入的member里面包含哪些条件，传入的查询就查询哪些条件，可以。

但是这样写会有一个问题，比如对年龄的筛选，Member类里面只有一个age，这样的话就无法实现“年龄区间”，即minAge和maxAge的筛选了。

那么，我创建一个实体类，MemberSearchCondition，成员查询参数类，在传参的时候传递这个对象的参数，也是可以的：

@Data
public class MemberSearchCondition {
    private String gender;
    private int minAge;
    private int maxAge;
    private String city;
}

public interface MemberDAO {
    public List<Member> searchMember(MemberSearchCondition params);
}

那么使用这种方法时，测试类中该怎么调用：

    @Test
    public void testSearchMember() {
        MemberSearchCondition params2 = new MemberSearchCondition();
        params2.setGender("女");
        params2.setMinAge(18);
        params2.setMaxAge(23);
        params2.setCity("武汉");

        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMember(params2);

        System.out.println(members);
    }

这两种方法各有优缺点

使用HashMap<>

​ 优点：无需单独定义传递查询条件的类

​ 缺点：当向Map中存放参数时，key必须与动态sql保持一致（不能一个是gender，一个是gender1）

创建存放查询条件的实体类

​ 优点：设置参数时无需关注属性名，就用set方法往里面存内容即可，不会有不一致的问题

​ 缺点：需要单独定义一个类来封装参数

12.2.4 映射文件

<mapper namespace="com.wyl.dao.MemberDAO">
    <resultMap id="memberMap" type="Member">
        <id column="member_id" property="memberId"/>
        <result column="member_nick" property="memberNick"/>
        <result column="member_gender" property="memberGender"/>
        <result column="member_age" property="memberAge"/>
        <result column="member_city" property="memberCity"/>
    </resultMap>
    <select id="searchMember" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where ？？？
    </select>
</mapper>

问题来了，条件查询语句的where后面该怎么写。

我有可能只有一个gender条件，也可能有多个条件，也可能没有gender条件。

我需要从传入的param参数（无论是通过HashMap还是条件查询类）中，判断某个条件是否为空，来判断是否需要查询这个条件。但是在这个地方显然不能写java代码中的if…else…等语句。

但是没关系，我们的映射文件中还有一个标签，标签。

    <select id="searchMember" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where
        <if test="gender != null">
            member_gender=#{gender}
        </if>
    </select>

这里的gender != null中的gender，它是来自于你searchMember(param)方法传入的参数param。如果你的param是HashMap定义的，那么它就是其中的某个key；如果是通过查询条件参数类定义的，那么就是这个类中的某一个属性。

判断其不为空的话，则将标签中的语句，拼接到后面。

注意，member_gender为表中字段名，而等号右边的#{ }中，可以直接写gender，因为这个gender，要么是HashMap中的那个key，要么是条件参数类中的那个属性。所以直接#{gender}就可以获取到。

        where
        <if test="gender != null">
            member_gender = #{gender}
        </if>
        <if test="minAge != null">
            member_age >= #{minAge}
        </if>

同理，我们再加一个条件minAge。

但是出现了一个问题：如果gender不为空，那么这两个语句在拼接的时候，中间要加一个AND。

        where
        <if test="gender != null">
            member_gender = #{gender}
        </if>
        <if test="minAge != null">
            and member_age >= #{minAge}
        </if>

但是，加上and，又会出现一个问题。如果gender为空，你加了and，SQL语句就错了。

对于这个问题的解决策略：

①方法一：

在where后面先加一个恒成立的条件1=1，再在每个需要拼接的语句上都加上and

（即使后面所有条件都没有，你where 1=1的话就是查询所有呗）

        where 1=1
        <if test="gender != null">
            and member_gender = #{gender}
        </if>
        <if test="minAge != null">
            and member_age >= #{minAge}
        </if>

②方法二：

使用标签，你就不用写1=1了

        from members
        <where>
            <if test="gender != null">
                and member_gender = #{gender}
            </if>
            <if test="minAge != null">
                and member_age &gt;= #{minAge}
            </if>
        </where>

标签起到一个功能：如果你这个要拼接的语句本身就是第一个条件，则会自动的把你的and帮你去掉，and就不碍事了。

③方法三：

使用标签，给里面的要拼接的语句加上一个前缀where。

        <trim prefix="where">
            <if test="gender != null">
                and member_gender = #{gender}
            </if>
            <if test="minAge != null">
                and member_age &gt;= #{minAge}
            </if>
            <if test="maxAge != null">
                and member_age &lt;= #{maxAge}
            </if>
            <if test="city != null">
                and member_city = #{city}
            </if>
        </trim>

但是这种写法，并不会自动地把作为第一个条件的拼接语句的and自动去掉，会报错。

我们可以加上一个属性，此处的例子表示，如果你的语句作为第一个条件，它会自动把里面的and或or去掉。

<trim prefix="where" prefixOverrides="and | or">

到此，有人就会问，你这样写，还不如直接写标签。但是，它有一个好处，就是，它还可以加后缀。

<trim prefix="where" prefixOverrides="and | or" suffix="order by member_age">

当然，你在标签后面写order by member_age也是一样的效果。

对于拼接SQL语句时，小于等于（<=）和大于等于（>=）号会出现的问题：

由于<=和>=会和我们的标签中的尖括号搞混，因此可能会存有隐患，所以我们在写SQL语句的时候，一般不用尖括号的方式去表示大于、小于，而是用这两个符号：

        <if test="maxAge != null">
            and member_age <= #{maxAge}
        </if>
        <if test="minAge != null">
            and member_age >= #{minAge}
        </if>

改为：

        <if test="maxAge != null">
            and member_age &gt;= #{maxAge}	//&gt; 表示大于
        </if>
        <if test="minAge != null">
            and member_age &lt;= #{minAge}	//&lt; 表示小于
        </if>

最终版本：

    <select id="searchMember" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where 1=1
        <if test="gender != null">
            and member_gender = #{gender}
        </if>
        <if test="minAge != null">
            and member_age &gt;= #{minAge}
        </if>
        <if test="maxAge != null">
            and member_age &lt;= #{maxAge}
        </if>
        <if test="city != null">
            and member_city = #{city}
        </if>
    </select>

12.2.5 测试

先在members表中添加若干条数据：

写测试类：

    @Test
    public void testSearchMember() {
        MemberSearchCondition params = new MemberSearchCondition();
        //params.setGender("女");
        //params.setMinAge(18);
        //params.setMaxAge(23);
        //params.setCity("武汉");

        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMember(params);

        System.out.println(members);
    }

无条件筛选，即查询所有，运行结果：

[]

Process finished with exit code 0

为什么？

因为我的“条件查询参数配置类”，即MemberSearchCondition类中，

@Data
public class MemberSearchCondition {
    private String gender;
    private int minAge;
    private int maxAge;
    private String city;
}

minAge、maxAge均为int类型，它不可能为空。若不赋值，则默认为0。而不存在年龄为0的记录。

我们把int改为Integer，让它作为一个对象类型。

@Data
public class MemberSearchCondition {
    private String gender;
    private Integer minAge;
    private Integer maxAge;
    private String city;
}

当然，你如果不想改为Integer，而仍使用int的话。你把SQL语句中的minAge != null改为minAge != 0就可以了。

改过之后，正常了。

1、查询所有性别为女的

    @Test
    public void testSearchMember() {
        MemberSearchCondition params = new MemberSearchCondition();
        params.setGender("女");
        //params.setMinAge(18);
        //params.setMaxAge(23);
        //params.setCity("武汉");

        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMember(params);

        for (Member m:members){
            System.out.println(m);
        }
    }

运行结果：

Member(memberId=1, memberNick=小丽, memberGender=女, memberAge=19, memberCity=武汉)
Member(memberId=3, memberNick=王阿姨, memberGender=女, memberAge=48, memberCity=武汉)
Member(memberId=4, memberNick=翠花, memberGender=女, memberAge=22, memberCity=宜昌)
Member(memberId=5, memberNick=小花, memberGender=女, memberAge=21, memberCity=宜昌)

Process finished with exit code 0

2、性别为女，最小年龄为18的

        params.setGender("女");
        params.setMinAge(18);

运行结果：

Member(memberId=1, memberNick=小丽, memberGender=女, memberAge=19, memberCity=武汉)
Member(memberId=3, memberNick=王阿姨, memberGender=女, memberAge=48, memberCity=武汉)
Member(memberId=4, memberNick=翠花, memberGender=女, memberAge=22, memberCity=宜昌)
Member(memberId=5, memberNick=小花, memberGender=女, memberAge=21, memberCity=宜昌)

Process finished with exit code 0

3、性别为女，最小年龄为22（大于等于22）的

    params.setGender("女");
    params.setMinAge(22);

运行结果：

Member(memberId=3, memberNick=王阿姨, memberGender=女, memberAge=48, memberCity=武汉)
Member(memberId=4, memberNick=翠花, memberGender=女, memberAge=22, memberCity=宜昌)

Process finished with exit code 0

更多的就不一一测试了。

12.3 foreach标签使用案例

还是刚才的案例，但是现在的需求是：

​ 筛选城市为武汉和宜昌的，或者年龄为18和19和21的。

对数据表稍作调整吧：

12.3.1 在DAO中添加方法

public interface MemberDAO {
    //public List<Member> searchMember(HashMap<String,Object> params);
    public List<Member> searchMember(MemberSearchCondition params);
    public List<Member> searchMemberByCity(List<String> cities);	//根据城市筛选
}

12.3.2 映射文件

    <select id="searchMemberByCity" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members where member_city =
    </select>

肯定要写where member_city如何如何。但是这里能用“ = ”么，不能。因为你要筛选的城市是一个List<>，里面可能有一个武汉还有一个宜昌，也就是说它是一个集合。

所以这里应该用in，in后面跟一个集合。

如果写成固定的SQL语句的话，应该是这样写的：

    <select id="searchMemberByCity" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members where member_city in ('武汉','宜昌')
    </select>

现在的问题是，我怎么把我的集合cities拿出来，并且构造成“ (‘武汉’,‘宜昌’) ”这样的结构呢？

说白了，也就是在SQL中要遍历集合。

使用标签，其中，collection指定集合的类型（此处是list，实际上这个list是被MyBatis取过别名了，它本身是java.util.List），item表示取的是集合中每个对象的什么属性，separator表示每个元素之间用什么符号分隔开（此处要用逗号）。

此时，它会拼成这样的一个结构： 武汉，宜昌

但是武汉前面要加一个前括号，宜昌后面要加一个后括号。

所以，还要加一个open（表示以什么开头），一个close（表示以什么结尾）。

循环多次，每循环一次，取到一个item。

    <select id="searchMemberByCity" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members where member_city in
        <foreach collection="list" item="cityName" separator="," open="(" close=")">
            #{cityName}
        </foreach>
    </select>

这就表示，对集合循环取出其中每个元素的cityName，之间用逗号隔开，并且在开头补上“（”，在结尾加上“）”。

12.3.3 测试

    @Test
    public void testSearchMemberByCity() {
        List<String> cities = new ArrayList<String>();
        cities.add("厦门");
        cities.add("宜昌");
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMemberByCity(cities);
        for (Member m:members){
            System.out.println(m);
        }
    }

运行结果：

Member(memberId=2, memberNick=小强, memberGender=男, memberAge=22, memberCity=厦门)
Member(memberId=4, memberNick=翠花, memberGender=女, memberAge=22, memberCity=宜昌)
Member(memberId=5, memberNick=小花, memberGender=女, memberAge=21, memberCity=宜昌)

Process finished with exit code 0

可以看到，只查询出了厦门和宜昌的。

十三、模糊查询

根据用户昵称查询用户信息——模糊查询。例如查询“花“，会查询到小花、翠花、花仙子等。

13.1 DAO接口中定义方法

    //根据昵称查询用户信息——模糊查询
    public List<Member> searchMemberByNick(String keyWord);

13.2 映射文件

    <select id="searchMemberByNick" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where member_nick like '%花%'	//等会儿要想办法把花改为我们传入的keyWord
    </select>

SQL知识：

模糊查询用like。

‘%’ 表示任意字符（任意一个或任意多个都行）；

‘_’ 表示任意一个字符。

13.3 测试

    @Test
    public void testSearchMemberByNick(){
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);
        List<Member> members = memberDAO.searchMemberByNick("花");
        for (Member m:members){
            System.out.println(m);
        }
    }

由于我是要查询昵称中包含那个字的，所以前后都要加%。然后像我们之前取属性值那样，将#{keyWord}写在两个%%之间。

    <select id="searchMemberByNick" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where member_nick like '%#{keyWord}%'	//等会儿要想办法把花改为我们传入的keyWord
    </select>

这样会报错，不可以这样取：

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='keyWord', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
### The error may exist in mappers/MemberMapper.xml

我们换成${keyWord}试一下：

    <select id="searchMemberByNick" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where member_nick like '%${keyWord}%'
    </select>

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'keyWord' in 'class java.lang.String'
### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'keyWord' in 'class java.lang.String'

也会报错，说对于keyWord这个属性，没有get方法，也就是无法获取到keyWord这个属性。

现在我们对DAO中的方法进行一些调整：

    //根据昵称查询用户信息——模糊查询
    public List<Member> searchMemberByNick(HashMap params);

对测试类进行修改：

    @Test
    public void testSearchMemberByNick(){
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);

        HashMap<String,Object> params = new HashMap<String, Object>();
        params.put("keyWord","花");
        List<Member> members = memberDAO.searchMemberByNick(params);
        for (Member m:members){
            System.out.println(m);
        }
    }

13.4 #{}和${}的区别

${key} 表示获取参数，先获取参数的值拼接到SQL语句中，再编译执行SQL语句，可能引起SQL注入问题。

[先取到$key参数的具体的值，然后和${key}%'放到SQL语句里面执行]

#{key}表示获取参数，先完成SQL语句的编译（预编译），预编译之后再将获取的参数设置到SQL语句中，可以避免SQL注入问题。

[预编译是什么：预编译可以理解为就是JDBC里面的占位符，它先告诉你这里有一个参数，先把位置占下来，去编译，编译完成后再对每个位置赋值。这样可以避免SQL注入的问题。]

为什么模糊查询这里不能用#{}：因为’%#{keyWord}%‘这个整体是一个参数，而不是其中的#{keyWord}是一个参数。所以你用’%#{keyWord}%'去编译的时候，%#{}%编译就会出错。占位符不可能是放到引号的里面的。

而你如果直接写#{keyWord}，是可以作为一个占位符来编译通过的，但是你就不能完成模糊查询了。

大概就是，不需要用拼接，就用#{}，类似一个占位符去完成SQL就可以了，能避免SQL注入。需要用到拼接，就用${}去做拼接处理。

为什么在使用${}写SQL的情况下，DAO方法不使用HashMap params而是直接传一个String keyWord作为参数时，执行测试类会报错，说无法获取到keyWord属性（没有get方法）？（见12.4.3）

    <select id="searchMemberByNick" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where member_nick like '%${keyWord}%'
    </select>

MemberDAO.java
//根据昵称查询用户信息——模糊查询
    public List<Member> searchMemberByNick(String keyWord);

测试类
    @Test
    public void testSearchMemberByNick(){
        MemberDAO memberDAO = MyBatisUtil.getMapper(MemberDAO.class);

        List<Member> members = memberDAO.searchMemberByNick("花");
        for (Member m:members){
            System.out.println(m);
        }
    }

会报错说没有keyWord的get方法。

因为，如果你用${}去取值，它并不是直接取参数值，而是会把你传递的参数看成一个对象，从这个对象中获取这个属性。而我现在传的只是一个字符串。

此时你要去声明一个parameterType，为java.lang.String。如果你不声明，它会把你传递的参数当成一个对象，从对象里面取属性。所以，如果你只是传一个字符串，你要直接声明是字符串。

如果你本身就是一个字符串了，你直接声明我的类型就是字符串。

    <select id="searchMemberByNick" parameterType="java.lang.String" resultMap="memberMap">
        select member_id,member_nick,member_gender,member_age,member_city
        from members
        where member_nick like '%${keyWord}%'
    </select>

而且在DAO的方法中要对参数声明@Param

    //根据昵称查询用户信息——模糊查询
    public List<Member> searchMemberByNick(@Param("keyWord") String keyWord);

非String的对象参数可以不用声明@Param。