iBATIS：一个完整的sqlmap示例

别忘记把对应每张表的sqlmap xml路径加入总sqlmap xml中

如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<!-- 模块：元数据-列-->
<sqlMap namespace="MetadataItemDao">
    <sql id="allColumn">        
        data_items.data_id, data_items.title, data_items.data_type, data_items.database_id, data_items.delay, data_items.result, data_items.status 
    </sql>      
            
    <sql id="insertColumn">     
        title, data_type, database_id, delay, result, status 
    </sql>      
            
    <sql id="whereItem">
        <isNotEmpty prepend=" AND " property="data_id"> data_items.data_id=#data_id# </isNotEmpty>
        <isNotEmpty prepend=" AND " property="title"> data_items.title=#title# </isNotEmpty>
        <isNotEmpty prepend=" AND " property="data_type"> data_items.data_type=#data_type# </isNotEmpty>
        <isNotEmpty prepend=" AND " property="database_id"> data_items.database_id=#database_id# </isNotEmpty>
        <isNotEmpty prepend=" AND " property="delay"> data_items.delay=#delay# </isNotEmpty>
        <isNotEmpty prepend=" AND " property="result"> data_items.result=#result# </isNotEmpty>
        <isNotEmpty prepend=" AND " property="status"> data_items.status=#status# </isNotEmpty>
    </sql>  
            
    <select id="getMetadataItem" parameterClass="map" resultClass="hashmap">     
        select <include refid="allColumn"/>     
        FROM data_items 
        <dynamic prepend="where">   
            <include refid="whereItem"/>
        </dynamic>  
        <isNotEmpty prepend=" order by " property="_order"> 
            $_order$
        </isNotEmpty>   
        <isNotEmpty prepend=" limit " property="_limit">    
            #_limit._pstart#,#_limit._psize#
        </isNotEmpty>   
    </select>       
            
    <select id="countMetadataItem" parameterClass="map" resultClass="int">       
        select count(0)     
        FROM data_items 
        <dynamic prepend="where">   
            <include refid="whereItem"/>
        </dynamic>  
    </select>       
            
    <insert id="addMetadataItem" parameterClass="map">       
        insert into data_items  
        ( <include refid="insertColumn"/> ) 
        values (#title#, #data_type#, #database_id#, #delay#, #result#, #status#)
        <selectKey resultClass="int">
            SELECT @@IDENTITY
        </selectKey>
    </insert>       
            
    <update id="updateMetadataItem" parameterClass="map">        
        update data_items   
        <dynamic prepend="set"> 
            <isNotNull prepend=","  property="title"> title=#title# </isNotNull>
            <isNotNull prepend=","  property="data_type"> data_type=#data_type# </isNotNull>
            <isNotNull prepend=","  property="database_id"> database_id=#database_id# </isNotNull>
            <isNotNull prepend=","  property="delay"> delay=#delay# </isNotNull>
            <isNotNull prepend=","  property="result"> result=#result# </isNotNull>
            <isNotNull prepend=","  property="status"> status=#status# </isNotNull>
        </dynamic>
        <isNotEmpty property="data_id"> where data_id=#data_id#     </isNotEmpty>
        <isNotEmpty property="data_ids"> where data_id in ($data_ids$)  </isNotEmpty>  
    </update>       
            
    <delete id="deleteMetadataItem" parameterClass="map">        
        delete from data_items  
        <dynamic prepend="where">   
            <isNotEmpty prepend=" AND " property="data_id"> data_items.data_id=#data_id# </isNotEmpty>
            <isNotEmpty prepend=" AND " property="data_ids"> data_items.data_id in ($data_ids$) </isNotEmpty>
            <isNotEmpty prepend=" AND " property="title"> data_items.title=#title# </isNotEmpty>
            <isNotEmpty prepend=" AND " property="data_type"> data_items.data_type=#data_type# </isNotEmpty>
            <isNotEmpty prepend=" AND " property="database_id"> data_items.database_id=#database_id# </isNotEmpty>
            <isNotEmpty prepend=" AND " property="delay"> data_items.delay=#delay# </isNotEmpty>
            <isNotEmpty prepend=" AND " property="result"> data_items.result=#result# </isNotEmpty>
            <isNotEmpty prepend=" AND " property="status"> data_items.status=#status# </isNotEmpty>
        </dynamic>  
    </delete>       
    
</sqlMap>