Sunny Grocery
0%

MyBatis实现模糊查询的几种方式

发表于 分类于 ，
本文字数： 5k 阅读时长 ≈ 9 分钟

这篇文章主要介绍了MyBatis实现模糊查询的几种方式，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧

在学习MyBatis过程中想实现模糊查询，可惜失败了。后来上百度上查了一下，算是解决了。记录一下MyBatis实现模糊查询的几种方式。

数据库表名为test_student,初始化了几条记录，如图：

起初我在MyBatis的mapper文件中是这样写的：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
        parameterType="com.example.entity.StudentEntity">
    SELECT * FROM test_student
    <where>
        <if test="age != null and age != '' and compare != null and compare != ''">
            age
            ${compare}
            #{age}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE '%#{name}%'
        </if>
        <if test="address != null and address != ''">
            AND address LIKE '%#{address}%'
        </if>
    </where>
    ORDER BY id
</select>

写完后自我感觉良好，很开心的就去跑程序了，结果当然是报错了：

经百度得知，这么写经MyBatis转换后('%#{name}%')会变为('%?%'),而('%?%')会被看作是一个字符串，所以Java代码在执行找不到用于匹配参数的? ,然后就报错了。

解决方法

  1. 用${…}代替#{…}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
        parameterType="com.example.entity.StudentEntity">
    SELECT * FROM test_student
    <where>
        <if test="age != null and age != '' and compare != null and compare != ''">
            age
            ${compare}
            #{age}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE '%${name}%'
        </if>
        <if test="address != null and address != ''">
            AND address LIKE '%${address}%'
        </if>
    </where>
    ORDER BY id
</select>

查询结果如下图：

注：使用${…}不能有效防止SQL注入，所以这种方式虽然简单但是不推荐使用！！！

  1. 把'%#{name}%'改为"%"#{name}"%"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
        parameterType="com.example.entity.StudentEntity">
    SELECT * FROM test_student
    <where>
        <if test="age != null and age != '' and compare != null and compare != ''">
            age
            ${compare}
            #{age}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE "%"#{name}"%"
        </if>
        <if test="address != null and address != ''">
            AND address LIKE "%"#{address}"%"
        </if>
    </where>
    ORDER BY id
</select>

查询结果：

  1. 使用sql中的字符串拼接函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
        parameterType="com.example.entity.StudentEntity">
    SELECT * FROM test_student
    <where>
        <if test="age != null and age != '' and compare != null and compare != ''">
            age
            ${compare}
            #{age}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT(CONCAT('%',#{name},'%'))
        </if>
        <if test="address != null and address != ''">
            AND address LIKE CONCAT(CONCAT('%',#{address},'%'))
        </if>
    </where>
    ORDER BY id
</select>

查询结果：

  1. 使用标签
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<select id="searchStudents" resultType="com.example.entity.StudentEntity"
        parameterType="com.example.entity.StudentEntity">
    <bind name="pattern1" value="'%' + _parameter.name + '%'" />
    <bind name="pattern2" value="'%' + _parameter.address + '%'" />
    SELECT * FROM test_student
    <where>
        <if test="age != null and age != '' and compare != null and compare != ''">
            age
            ${compare}
            #{age}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE #{pattern1}
        </if>
        <if test="address != null and address != ''">
            AND address LIKE #{pattern2}
        </if>
    </where>
    ORDER BY id
</select>

查询结果：

  1. 在Java代码中拼接字符串
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
public static void main(String[] args) {
    try {
        int count = 500;

        long begin = System.currentTimeMillis();
        testString(count);
        long end = System.currentTimeMillis();
        long time = end - begin;
        System.out.println("String 方法拼接"+count+"次消耗时间：" + time + "毫秒");

        begin = System.currentTimeMillis();
        testStringBuilder(count);
        end = System.currentTimeMillis();
        time = end - begin;
        System.out.println("StringBuilder 方法拼接"+count+"次消耗时间：" + time + "毫秒");

    } catch (Exception e) {
        e.printStackTrace();
    }

}

private static String testString(int count) {
    String result = "";

    for (int i = 0; i < count; i++) {
        result += "hello ";
    }

    return result;
}

private static String testStringBuilder(int count) {
    StringBuilder sb = new StringBuilder();

    for (int i = 0; i < count; i++) {
        sb.append("hello");
    }

    return sb.toString();
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。

文章转载自 脚本之家