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

直接传参法

直接传参法,就是将要查询的关键字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>

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

ZhaoYingChao88

整理不易,还请多多支持,谢谢

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值