兼顧效率，iBatis一些非見用法(10條)

iBatis一些非見用法，基本上解決所有棘手問題，下面總結如下：

1、動态SQL片段

通過SQL片段達到代碼複用

        <!-- 動态條件分頁查詢 --> 

        <sql id="sql_count"> 

                select count(*) 

        </sql> 

        <sql id="sql_select"> 

                select * 

        <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 prepend="and" property="area_id"> 

                                area_id = #area_id# 

                        <isNotEmpty prepend="and" property="hided"> 

                                hided = #hided# 

                </dynamic> 

                <dynamic prepend=""> 

                        <isNotNull property="_start"> 

                                <isNotNull property="_size"> 

                                        limit #_start#, #_size# 

                                </isNotNull> 

                        </isNotNull> 

        <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"/> 

        </select>

2、數字範圍查詢

所傳參數名稱是捏造所得，非資料庫字段，比如_img_size_ge、_img_size_lt字段

                        <isNotEmpty prepend="and" property="_img_size_ge"> 

                                <![CDATA[ 

                                img_size >= #_img_size_ge# 

                        ]]> 

                        <isNotEmpty prepend="and" property="_img_size_lt"> 

                                img_size < #_img_size_lt# 

多次使用一個參數也是允許的

                        <isNotEmpty prepend="and" property="_now"> 

                                            execplantime >= #_now# 

                                     ]]> 

                                            closeplantime <= #_now# 

                        </isNotEmpty>

3、時間範圍查詢

                        <isNotEmpty prepend="" property="_starttime"> 

                                <isNotEmpty prepend="and" property="_endtime"> 

                                        <![CDATA[ 

                                        createtime >= #_starttime# 

                                        and createtime < #_endtime# 

                                 ]]> 

                                </isNotEmpty> 

4、in查詢

                        <isNotEmpty prepend="and" property="_in_state"> 

                                state in ('$_in_state$') 

5、like查詢

                        <isNotEmpty prepend="and" property="chnameone"> 

                                (chnameone like '%$chnameone$%' or spellinitial like '%$chnameone$%') 

                        <isNotEmpty prepend="and" property="chnametwo"> 

                                chnametwo like '%$chnametwo$%' 

6、or條件

                        <isEqual prepend="and" property="_exeable" compareValue="N"> 

                                (t.finished='11'    or t.failure=3) 

                        </isEqual>

                        <isEqual prepend="and" property="_exeable" compareValue="Y"> 

                                t.finished in ('10','19') and t.failure<3 

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> 

        <select id="findByProgramcode" parameterClass="string" resultMap="cms_ccm_material.result"> 

                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 

9、函數的使用

        <!-- 添加 --> 

        <insert id="insert" parameterClass="RuleMaster"> 

                insert into rulemaster( 

                name, 

                createtime, 

                updatetime, 

                remark 

                ) values ( 

                #name#, 

                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>

10、map結果集

                select count(a.*) 

                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 

                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 

        <!-- 傳回沒有下載下傳的記錄總數 --> 

        <select id="getUndownFilesForCount" parameterClass="map" resultClass="int"> 

        <!-- 傳回沒有下載下傳的記錄 --> 

        <select id="getUndownFiles" parameterClass="map" resultClass="java.util.HashMap"> 

注意：在使用Map作為結果集傳回類型時候，必須這麼設定結果集類型resultClass="java.util.HashMap"，這時候，需要根據字段的名稱來取值，值類型為Object，key類型為String，這點要注意了：

定義的DAO方法實作如下：

        public List<Map<String,Object>> findUndownFiles(Map map) { 

                return getSqlMapClientTemplate().queryForList("secfiles.getUndownFiles", map); 

        }

通過DAO讀取并操作Map結果集資料：

        public void test_findUndownFiles() { 

                List<Map<String, Object>> co = ser.findUndownFiles(new HashMap()); 

                StringBuilder s = new StringBuilder(); 

                for (Map<String, Object> map : co) { 

                        System.out.println("---------------------------"); 

                        for (Map.Entry<String, Object> entry : map.entrySet()) { 

                                System.out.print(entry.getKey()+"\t"); 

                                System.out.println(entry.getValue()); 

                        } 

                } 

列印結果：

--------------------------- 

sid  1 

vfilename  200905252009235799 

url  http://d18.v.iask.com/f/1/f47817a394730dc682e660b943e84cc41006606.flv 

status  0 

filename  200905252009235799-00.flv 

imgfile  200905252009234399.jpg 

vid  1 

imgurl  http://p4.v.iask.com/95/595/1757503_1.jpg 

sid  2130 

vfilename  2009062615063867492 

url  http://lz.dhot.v.iask.com/f/1/0ee2ae8b973988f6a93c071c8045ca5217266409.mp4 

filename  2009062615063867492-00.mp4 

imgfile  2009062615063825434.jpg 

vid  93 

imgurl  http://cache.mars.sina.com.cn/nd/movievideo//thumb/2/1502_120160.jpg 

sid  2131 

vfilename  2009062615064184076 

url  http://lz5.dhot.v.iask.com/f/1/36d3dadacb8d6bda434a58e7418ad3cc19037464.flv 

filename  2009062615064184076-00.flv 

imgfile  2009062615064136733.jpg 

vid  94 

由于iBatis從2.3.4版本後就改為MyIBatis了，為了研究源碼友善，特上傳2版本的源碼和釋出包。

本文轉自 leizhimin 51CTO部落格，原文連結：http://blog.51cto.com/lavasoft/202886，如需轉載請自行聯系原作者