Mybatis常用语句

1 分页查询  传入id，分页的大小和页码  （附：limit的用法: https://blog.csdn.net/u013256816/article/details/103342250  ）

<select id="getCooperationInfo" resultType="coopInfo">
    SELECT *
    FROM ${tableName}
    WHERE deleted=0 AND id=#{planId}
            <if test="org != 'all'"> AND organization=#{org} </if>
            <if test="startDt != null and startDt != ''"> AND publish_dt >= #{startDt} </if>
            <if test="endDt != null and endDt != ''"> AND publish_dt <= #{endDt} </if>
    ORDER BY update_time DESC
    LIMIT ${((pageNo-1) * pageSize)}, #{pageSize}
 </select>

2选择查询，运用choose语句

注：if选项中如果传值为int则不用两个单引号即可，如果传值是string 则必须用单引号引起来
<select id="get" resultType="">
    SELECT
     max(a.crawled_time) as crawled_time, max(search_count) as search_count, min(`rank`) as `rank`,
    max(word_link) as wordLink, max(b.description) as description

    FROM aTable a
    join ${relationTable} b
    on a.list_id = b.list_id and a.site = #{site} and b.site = a.site
    WHERE b.plan_id=#{planId}
    GROUP BY a.site, title
    <choose>
            <when test="order == 'seaCount'">order by search_count desc       </when>
            <when test="order == 'craTime'">order by max(a.crawled_time) desc </when>
            <otherwise> order by `rank` asc </otherwise>
    </choose>
</select>

3，bind的用法，方便模糊查询

<select id="getEmp" resultType="User">
          <!-- bind:可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
          name是User中一个属性值
          <bind name="bName" value="'%'+name+'%'"/> 
    SELECT * FROM user
          <if test="_parameter!=null">
    where ename like #{bName}
          </if>
</select>

4，foreach和sql段落引用的用法

<select id="get" resultType="mar">
    SELECT *
            FROM ${table}
    WHERE <include refid="aa" />
            AND qipu_id=#{qipuId}
    ORDER BY create_time DESC
    LIMIT #{limit}
</select>
    
<sql id="aa">
    deleted!=1
        <if test="!admin">
    AND LOWER(creator)
            <choose>
                <when test="userType == 0">=LOWER(#{username})</when>
    <otherwise>
            IN <foreach collection="rolePeers" item="peer" open="(" separator="," close=")"> #{peer} </foreach>
                </otherwise>
            </choose>
        </if>
</sql>