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

最新推荐文章于 2023-04-22 21:18:13 发布

原创最新推荐文章于 2023-04-22 21:18:13 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis模糊查询

mybatis 专栏收录该内容

20 篇文章

订阅专栏

本文介绍了在MyBatis中实现模糊查询的四种方法，包括使用${...}

在学习MyBatis过程中想实现模糊查询，下面列举几种方式：
1.用${…}代替#{…}

<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注入，所以这种方式虽然简单但是不推荐使用！！！

2.把’%#{name}%’改为”%”#{name}”%”

<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>

查询结果：

æ¥è¯¢ç»æ

3.使用sql中的字符串拼接函数

<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>

查询结果：

æ¥è¯¢ç»æ

4.使用标签

<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>

查询结果：

æ¥è¯¢ç»æ