Direct transmission of reference
Direct transmission of reference , Is the keyword to be queried keyword, Put together the format of the query in the code , Such as %keyword%, And then directly pass in as a parameter mapper.xml In the mapping file of .
public void selectBykeyWord(String keyword) {
String id = "%" + keyword + "%";
String roleType = "%" + keyword + "%";
String roleName = "%" + keyword + "%";
userDao.selectBykeyWord(id,roleName,roleType);
}stay Dao Layer specifies the alias of each parameter
The code is as follows :
List<RoleEntity> selectBykeyWord(@Param(“id”) String id,@Param(“roleName”) String roleName,@Param(“roleType”) String roleType);
|
|
Executed SQL sentence ：
SELECT
*
FROM
t_role
WHERE
role_name LIKE '%why%'
OR id LIKE '%why%'
OR role_type LIKE '%why%';CONCAT（） function
MySQL Of CONCAT（） Function is used to connect multiple strings into one string , Is the most important mysql One of the functions .
CONCAT(str1,str2,...)|
|
|
|
perhaps “%”#{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>Of course , You can also use $, But you need to pay special attention to the problem of single quotation marks .
-
#{ } It’s precompiling ,MyBatis Processing #{ } when , It will be sql Medium #{ } Replace with ？, And then call PreparedStatement Of set Method to assign a value , After passing in the string , A single quotation mark will be placed around the value , Use placeholders to improve efficiency , Can prevent sql Inject .
-
${}: Indicates splicing sql strand , The content of the received parameter is spliced in… Without any decoration sql in , May trigger sql Inject .
The following methods are not recommended ：
<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 Of bind
|
|
|
|
mabatis String equality judgment Need to increase .toString()
Sample complex query ：
<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>