ibatis​动态sql标签用法详解

KLQ 2020-07-28 10:25:16 java常见问答 6110

之前给大家介绍了一下什么是ibatis,那么下面要接着个大家讲到的就是iBatis动态SQL标签用法方面的内容,一起来看看吧。

1、动态SQL片段

通过SQL片段达到代码复用

<!-- 动态条件分页查询 -->
<sql id="sql_count">
    select count(*)
</sql>
<sql id="sql_select">
    select *
</sql>
<sql id="sql_where">
    from icp
    <dynamic prepend="where">
        <isNotEmpty prepend="and" property="name">
            name like '%$name$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="path">
            path like '%path$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="area_id">
            area_id = #area_id#
        </isNotEmpty>
        <isNotEmpty prepend="and" property="hided">
            hided = #hided#
        </isNotEmpty>
    </dynamic>
    <dynamic prepend="">
        <isNotNull property="_start">
            <isNotNull property="_size">
                limit #_start#, #_size#
            </isNotNull>
        </isNotNull>
    </dynamic>
</sql>
<select id="findByParamsForCount" parameterClass="map" resultClass="int">
    <include refid="sql_count" />
    <include refid="sql_where" />
</select>
<select id="findByParams" parameterClass="map" resultMap="icp.result_base">
    <include refid="sql_select" />
    <include refid="sql_where" />
</select>

2、数字范围查询

所传参数名称是捏造所得,非数据库字段,比如_img_size_ge、_img_size_lt字段

<isNotEmpty prepend="and" property="_img_size_ge">
    <![CDATA[ 
                                img_size >= #_img_size_ge# 
                        ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="_img_size_lt">
    <![CDATA[ 
                                img_size < #_img_size_lt# 
                        ]]>
</isNotEmpty>

多次使用一个参数也是允许的
<isNotEmpty prepend="and" property="_now">
    <![CDATA[ 
                                            execplantime >= #_now# 
                                     ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="_now">
    <![CDATA[ 
                                            closeplantime <= #_now# 
                                     ]]>
</isNotEmpty>

3、时间范围查询

<isNotEmpty prepend="" property="_starttime">
    <isNotEmpty prepend="and" property="_endtime">
        <![CDATA[ 
                                        createtime >= #_starttime# 
                                        and createtime < #_endtime# 
                                 ]]>
    </isNotEmpty>
</isNotEmpty>

4、in查询

<isNotEmpty prepend="and" property="_in_state">
    state in ('$_in_state$')
</isNotEmpty>

5、like查询

<isNotEmpty prepend="and" property="chnameone">
    (chnameone like '%$chnameone$%' or spellinitial like '%$chnameone$%')
</isNotEmpty>
<isNotEmpty prepend="and" property="chnametwo">
    chnametwo like '%$chnametwo$%'
</isNotEmpty>

6、or条件

<isEqual prepend="and" property="_exeable" compareValue="N">
    <![CDATA[ 
                                (t.finished='11'    or t.failure=3) 
                        ]]>
</isEqual>

<isEqual prepend="and" property="_exeable" compareValue="Y">
    <![CDATA[ 
                                t.finished in ('10','19') and t.failure<3 
                        ]]>
</isEqual>

7、where子查询

<isNotEmpty prepend="" property="exprogramcode">
    <isNotEmpty prepend="" property="isRational">
        <isEqual prepend="and" property="isRational" compareValue="N">
            code not in
            (select t.contentcode
            from cms_ccm_programcontent t
            where t.contenttype='MZNRLX_MA'
            and t.programcode = #exprogramcode#)
        </isEqual>
    </isNotEmpty>
</isNotEmpty>

<select id="findByProgramcode" parameterClass="string" resultMap="cms_ccm_material.result">
    select *
    from cms_ccm_material
    where code in
    (select t.contentcode
    from cms_ccm_programcontent t
    where t.contenttype = 'MZNRLX_MA'
    and programcode = #value#)
    order by updatetime desc
</select>

8、函数的使用

<!-- 添加 -->
<insert id="insert" parameterClass="RuleMaster">
    insert into rulemaster(
    name,
    createtime,
    updatetime,
    remark
    ) values (
    #name#,
    now(),
    now(),
    #remark#
    )
    <selectKey keyProperty="id" resultClass="long">
        select LAST_INSERT_ID()
    </selectKey>
</insert>
<!-- 更新 -->
<update id="update" parameterClass="RuleMaster">
    update rulemaster set
    name = #name#,
    updatetime = now(),
    remark = #remark#
    where id = #id#
</update>

9、map结果集

<!-- 动态条件分页查询 -->
<sql id="sql_count">
    select count(a.*)
</sql>
<sql id="sql_select">
    select a.id vid,
    a.img imgurl,
    a.img_s imgfile,
    b.vfilename vfilename,
    b.name name,
    c.id sid,
    c.url url,
    c.filename filename,
    c.status status
</sql>
<sql id="sql_where">
    From secfiles c, juji b, videoinfo a
    where
    a.id = b. videoid
    and b.id = c.segmentid
    and c.status = 0
    order by a.id asc,b.id asc,c.sortnum asc
    <dynamic prepend="">
        <isNotNull property="_start">
            <isNotNull property="_size">
                limit #_start#, #_size#
            </isNotNull>
        </isNotNull>
    </dynamic>
</sql>
<!-- 返回没有下载的记录总数 -->
<select id="getUndownFilesForCount" parameterClass="map" resultClass="int">
    <include refid="sql_count" />
    <include refid="sql_where" />
</select>
<!-- 返回没有下载的记录 -->
<select id="getUndownFiles" parameterClass="map" resultClass="java.util.HashMap">
    <include refid="sql_select" />
    <include refid="sql_where" />
</select>

欢迎继续关注奇Q工具网,更多java架构师基础知识可以为你分享。

推荐阅读:

ibatis原理是怎样的?原理浅析

ibatis动态拼接sql实例分享

ibatis和mybatis的区别是什么?有什么不同?