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

淩亂°似流年 2022-12-20 12:59 89阅读 0赞

**直接传参法**

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

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>&lt;</code><code>select</code> <code>id</code><code>=</code><code>"selectBykeyWord"</code> <code>parameterType</code><code>=</code><code>"string"</code> <code>resultType</code><code>=</code><code>"com.why.mybatis.entity.RoleEntity"</code><code>&gt;</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SELECT</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>*</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>FROM</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>t_role</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>WHERE</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>role_name LIKE #{roleName}</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>OR id LIKE #{id}</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>OR role_type LIKE #{roleType}</code></p> <p><code>&nbsp;&nbsp;</code><code>&lt;/</code><code>select</code><code>&gt;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

执行出来的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,...)

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>List&lt;RoleEntity&gt; selectBykeyWord(</code><code>@Param</code><code>(</code><code>"keyword"</code><code>) String keyword);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>&lt;</code><code>select</code> <code>id</code><code>=</code><code>"selectBykeyWord"</code> <code>parameterType</code><code>=</code><code>"string"</code> <code>resultType</code><code>=</code><code>"com.why.mybatis.entity.RoleEntity"</code><code>&gt;</code></p> <p><code>&nbsp;&nbsp;</code><code>SELECT</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>*</code></p> <p><code>&nbsp;&nbsp;</code><code>FROM</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>t_role</code></p> <p><code>&nbsp;&nbsp;</code><code>WHERE</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>role_name LIKE CONCAT('%',#{keyword},'%')</code></p> <p><code>&nbsp;&nbsp;</code><code>OR</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id LIKE CONCAT('%',#{keyword},'%')</code></p> <p><code>&nbsp;&nbsp;</code><code>OR</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>role_type LIKE CONCAT('%',#{keyword},'%')</code></p> <p><code>&lt;/</code><code>select</code><code>&gt;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

或者** "%"\#\{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**

<table> 
 <tbody> 
  <tr> 
   <td> <p><code>&lt;</code><code>select</code> <code>id</code><code>=</code><code>"selectBykeyWord"</code> <code>parameterType</code><code>=</code><code>"string"</code> <code>resultType</code><code>=</code><code>"com.why.mybatis.entity.RoleEntity"</code><code>&gt;</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>&lt;</code><code>bind</code> <code>name</code><code>=</code><code>"pattern"</code> <code>value</code><code>=</code><code>"'%' + keyword + '%'"</code> <code>/&gt;</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>SELECT</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>*</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>FROM</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>t_role</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>WHERE</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>role_name LIKE #{pattern}</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>OR</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>id LIKE #{pattern}</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>OR</code></p> <p><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>role_type like #{pattern}</code></p> <p><code>&nbsp;&nbsp;</code><code>&lt;/</code><code>select</code><code>&gt;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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>