mybatis中的${} 与 #{}实际使用-模糊查询的几种实现方式

最新推荐文章于 2024-01-18 17:05:37 发布

ZhaoYingChao88

最新推荐文章于 2024-01-18 17:05:37 发布

阅读量1.2k

点赞数

分类专栏： mysql 文章标签： mysql

本文为博主原创文章，未经博主允许不得转载。如果对你有帮助，请记得点赞支持，谢谢！

本文链接：https://blog.csdn.net/ZYC88888/article/details/109653108

版权

mysql 专栏收录该内容

14 篇文章 3 订阅

订阅专栏

直接传参法

直接传参法，就是将要查询的关键字keyword,在代码中拼接好要查询的格式，如%keyword%,然后直接作为参数传入mapper.xml的映射文件中。

public void selectBykeyWord(String keyword) {

   String id = "%" + keyword + "%";

   String roleType = "%" + keyword + "%";

   String roleName = "%" + keyword + "%";

   userDao.selectBykeyWord(id,roleName,roleType);

 }

在Dao层指定各个参数的别名

代码如下:

List<RoleEntity> selectBykeyWord(@Param("id") String id,@Param("roleName") String roleName,@Param("roleType") String roleType);

<select id="selectBykeyWord" parameterType="string" resultType="com.why.mybatis.entity.RoleEntity">

SELECT

*

FROM

t_role

WHERE

role_name LIKE #{roleName}

OR id LIKE #{id}

OR role_type LIKE #{roleType}

</select>

执行出来的SQL语句：

SELECT

  *

FROM

  t_role

WHERE

  role_name LIKE '%why%'

OR id LIKE '%why%'

OR role_type LIKE '%why%';

CONCAT（）函数

MySQL的 CONCAT（）函数用于将多个字符串连接成一个字符串，是最重要的mysql函数之一。

CONCAT(str1,str2,...)

List<RoleEntity> selectBykeyWord(@Param("keyword") String keyword);

<select id="selectBykeyWord" parameterType="string" resultType="com.why.mybatis.entity.RoleEntity">

SELECT

*

FROM

t_role

WHERE

role_name LIKE CONCAT('%',#{keyword},'%')

OR

id LIKE CONCAT('%',#{keyword},'%')

OR

role_type LIKE CONCAT('%',#{keyword},'%')

</select>

或者 "%"#{keyword}"%"

<select id="selectBykeyWord" parameterType="string" resultType="com.why.mybatis.entity.RoleEntity">

  SELECT

    *

  FROM

    t_role

  WHERE

    role_name LIKE "%"#{keyword}"%"

  OR

    id LIKE "%"#{keyword}"%"

  OR

    role_type LIKE "%"#{keyword}"%"

</select>

当然，也可以使用$，不过需要特别留意单引号的问题。

#{ }是预编译处理，MyBatis在处理#{ }时，它会将sql中的#{ }替换为？，然后调用PreparedStatement的set方法来赋值，传入字符串后，会在值两边加上单引号，使用占位符的方式提高效率，可以防止sql注入。
${}:表示拼接sql串，将接收到参数的内容不加任何修饰拼接在sql中，可能引发sql注入。

不推荐如下方式：

<select id="selectBykeyWord" parameterType="string" resultType="com.why.mybatis.entity.RoleEntity">

  SELECT

    *

  FROM

    t_role

  WHERE

    role_name LIKE CONCAT('%',${keyword},'%')

  OR

    id LIKE CONCAT('%',${keyword},'%')

  OR

    role_type LIKE CONCAT('%',${keyword},'%')

</select>

<select id="selectBykeyWord" parameterType="string" resultType="com.why.mybatis.entity.RoleEntity">

  SELECT

    *

  FROM

    t_role

  WHERE

    role_name LIKE '%${keyword}%'

  OR

    id LIKE '%${keyword}%'

  OR

    role_type LIKE '%${keyword}%'

</select>

Mybatis的bind

List<RoleEntity> selectBykeyWord(@Param("keyword") String keyword);

<select id="selectBykeyWord" parameterType="string" resultType="com.why.mybatis.entity.RoleEntity">

<bind name="pattern" value="'%' + keyword + '%'" />

SELECT

*

FROM

t_role

WHERE

role_name LIKE #{pattern}

OR

id LIKE #{pattern}

OR

role_type like #{pattern}

</select>

mabatis字符串相等判断需增加 .toString()

复杂查询样例：

<select id="regionQuery" resultMap="resultMap"
            parameterType="com.cy.param.queryParam">
        SELECT * from (
        SELECT region.*,
        GROUP_CONCAT(distinct region.type, '_', region.count) type_count_list,
        GROUP_CONCAT(distinct person.obj_type, '_', person.obj_value) obj_type_value_list,
        person.longitude longitude,
        person.latitude latitude,
        person.address address,
        person.wifi_name wifi_name,
        person.wifi_mac wifi_mac,
        person.gps_mac gps_mac,
        person.gps_imei gps_imei,
        person.gather_flag gather_flag
        FROM (
        SELECT agg.*,
        dict.type AS type,
        dict.`value` AS count,
        dict.privilege
        FROM monitor_region_agg agg
        LEFT JOIN monitor_dict dict ON agg.region_id = dict.region_id
        AND dict.type IN ('attation', 'readStatus')
        <if test="userId != null and userId != ''">
            and dict.gmt_creator = #{userId}
        </if>
        <if test="regionIdList != null and regionIdList.size > 0">
            and agg.region_id in
            <foreach collection="regionIdList" item="code" index="index" open="(" close=")" separator=",">
                #{code}
            </foreach>
        </if>
        <if test="regionIdList != null and regionIdList.size > 0">
            and dict.region_id in
            <foreach collection="regionIdList" item="code" index="index" open="(" close=")" separator=",">
                #{code}
            </foreach>
        </if>
        ) region
        LEFT JOIN monitor_region_person_relation person ON region.region_id = person.region_id
        WHERE region.`is_delete` = '0'
        AND person.`is_delete` = '0'
        AND person.gather_flag = '0'
        <if test="regionTypeList != null and regionTypeList.size > 0">
            and region.region_type in
            <foreach collection="regionTypeList" item="code" index="index" open="(" close=")" separator=",">
                #{code}
            </foreach>
        </if>
        <if test="areaCodeList != null and areaCodeList.size > 0">
            AND ( region.region_province_code in
            <foreach collection="areaCodeList" item="code" index="index" open="(" close=")" separator=",">
                #{code}
            </foreach>
            or region.region_city_code in
            <foreach collection="areaCodeList" item="code" index="index" open="(" close=")" separator=",">
                #{code}
            </foreach>
            or region.region_district_code in
            <foreach collection="areaCodeList" item="code" index="index" open="(" close=")" separator=",">
                #{code}
            </foreach>
            )
        </if>
        <if test="tenantCode != null and tenantCode != ''">
            and region.tenant_code = #{tenantCode}
        </if>
        <if test="businessCode != null and businessCode != ''">
            and region.business_code = #{businessCode}
        </if>
        <if test="appCode != null and appCode != ''">
            and region.app_code = #{appCode}
        </if>
        <if test="startDate != null and startDate != ''">
            <![CDATA[
            AND region.risk_date >= #{startDate}
            ]]>
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[
            AND region.risk_date <= #{endDate}
            ]]>
        </if>
        <if test="searchValue != null and searchValue != ''">
            AND (region.region_name LIKE concat('%', #{searchValue}, '%') OR
            person.obj_value LIKE concat('%', #{searchValue}, '%'))
        </if>
        <if test="regionTypeList != null and regionTypeList.size > 0">
            and region.region_type in
            <foreach collection="regionTypeList" item="code" index="index" open="(" close=")" separator=",">
                #{code}
            </foreach>
        </if>
        ) result
        WHERE 1 = 1
        <if test="attentionSign != null and attentionSign == '1'.toString()">
            AND type_count_list LIKE '%attation%'
        </if>
        <if test="attentionSign != null and attentionSign == '0'.toString()">
            AND type_count_list NOT LIKE '%attation%'
        </if>
        <if test="readSign != null and readSign == '1'.toString()">
            AND type_count_list LIKE '%readStatus%'
        </if>
        <if test="readSign != null and readSign == '0'.toString()">
            AND type_count_list NOT LIKE '%readStatus%'
        </if>
        GROUP BY region_id
        <if test="orderField != null and orderField != ''">
            ORDER BY #{orderField}
            <if test="order != null and order != ''">
                #{order}
            </if>
        </if>
    </select>