荆棘从死亡中来，死亡又包含着新生。
      •  0 回帖  •  860 浏览  •  5 年前
   

mybatis 中 like 语句的使用

sqlserver

<select id="getPersonnelBySiteAndName" resultMap="get-getPersonnelBySiteAndName">
  SELECT
  p.personnelId as p_personnelId, p.voip as p_voip, p.mobile as p_mobile, p.name as p_name,
  p.photo as p_photo,p.majorType as p_majorType,si.siteName as s_siteName,p.siteId as p_siteId,
  p.gender as p_gender,p.isExpert as p_isExpert,p.appRoleId as p_appRoleId,p.compId as p_compId
  FROM
  Personnel p,sites si
  WHERE
  1=1
  <if test="siteId != null">
    AND
    si.siteId=#{siteId}
  </if>
  <if test="name != null">
    AND
    p.name like '%'+#{name}+'%'
  </if>
  AND
  si.siteId=p.siteId
  AND
  p.operation != 'del'
  ORDER BY p.name
  COLLATE chinese_prc_cs_as_ks_ws
</select>

mysql

<select id="getPersonnelBySiteAndName" resultMap="get-getPersonnelBySiteAndName">
  SELECT
  p.personnelId as p_personnelId, p.voip as p_voip, p.mobile as p_mobile, p.name as p_name,
  p.photo as p_photo,p.majorType as p_majorType,si.siteName as s_siteName,p.siteId as p_siteId,
  p.gender as p_gender,p.isExpert as p_isExpert,p.appRoleId as p_appRoleId,p.compId as p_compId
  FROM
  Personnel p,sites si
  WHERE
  1=1
  <if test="siteId != null">
    AND
    si.siteId=#{siteId}
  </if>
  <if test="name != null">
    AND
    p.name like CONCAT("%",#{name},"%") 
  </if>
  AND
  si.siteId=p.siteId
  AND
  p.operation != 'del'
  ORDER BY p.name
  COLLATE chinese_prc_cs_as_ks_ws
</select>

oracle

<select id="getPersonnelBySiteAndName" resultMap="get-getPersonnelBySiteAndName">
  SELECT
  p.personnelId as p_personnelId, p.voip as p_voip, p.mobile as p_mobile, p.name as p_name,
  p.photo as p_photo,p.majorType as p_majorType,si.siteName as s_siteName,p.siteId as p_siteId,
  p.gender as p_gender,p.isExpert as p_isExpert,p.appRoleId as p_appRoleId,p.compId as p_compId
  FROM
  Personnel p,sites si
  WHERE
  1=1
  <if test="siteId != null">
    AND
    si.siteId=#{siteId}
  </if>
  <if test="name != null">
    AND
    p.name like '%'||#{name}||'%'
  </if>
  AND
  si.siteId=p.siteId
  AND
  p.operation != 'del'
  ORDER BY p.name
  COLLATE chinese_prc_cs_as_ks_ws
</select>