自己以前写的有意思的ibatis
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"><sqlMap namespace="Statistcs"><cacheModel id="code-CacheModel" type ="LRU" readOnly="true" serialize="false"> <flushInterval seconds="10"/> <!-- <flushOnExecute statement="getStatistcsData1"/> --> <property name="cache-size" value="1000" /> </cacheModel> <!-- 获得统计信息 --><statement id="getStatistcsDataExt" resultparametercacheModel="code-CacheModel" remapResults="true">select a.name, (select count(*) from projects t1 where t1.academy_id = a.id and t1.category_id in (select id from categories where order_doc_id = #orderDocId#)) as apply_count, (select count(*) from projects t2 where t2.academy_id = a.id and t2.apply_check_state = 3 and t2.category_id in (select id from categories where order_doc_id = #orderDocId#)) as elected_count, <iterate property="ids" conjunction=","> (select count(*) from projects t3, evaluate_result er where t3.academy_id = a.id and er.project_id = t3.id and t3.category_id in (select id from categories where order_doc_id = #orderDocId#) and er.hierarchy_id = ($ids[]$)) as '$ids[]$' </iterate> from academies a<dynamic><isNotEmpty property="academyIds">where a.id in ($academyIds$)</isNotEmpty></dynamic>order by apply_count desc, elected_count desc</statement><!-- 获得统计信息 去除缓存--><statement id="getStatistcsDataExt1" resultparametercacheModel="code-CacheModel" remapResults="true">select a.name, (select count(*) from projects t1 where t1.academy_id = a.id and t1.category_id in (select id from categories where order_doc_id = #orderDocId#)) as apply_count, (select count(*) from projects t2 where t2.academy_id = a.id and t2.apply_check_state = 3 and t2.category_id in (select id from categories where order_doc_id = #orderDocId#)) as elected_count, <iterate property="ids" conjunction=","> (select count(*) from projects t3, evaluate_result er where t3.academy_id = a.id and er.project_id = t3.idand t3.category_id in (select id from categories where order_doc_id = #orderDocId#) and er.hierarchy_id = ($ids[]$)) as '$ids[]$' </iterate> from academies a<dynamic><isNotEmpty property="academyIds">where a.id in ($academyIds$)</isNotEmpty></dynamic>order by apply_count desc, elected_count desc</statement><!-- 获得统计信息 --><statement id="getStatistcsData" resultparametercacheModel="code-CacheModel" remapResults="true">select a.name, (select count(*) from projects t1 where t1.academy_id = a.id and t1.category_id in (select id from categories where order_doc_id = #orderDocId#)) as apply_count, (select count(*) from projects t2 where t2.academy_id = a.id and t2.apply_check_state = 3 and t2.category_id in (select id from categories where order_doc_id = #orderDocId#)) as elected_count from academies a<dynamic><isNotEmpty property="academyIds">where a.id in ($academyIds$)</isNotEmpty></dynamic>order by apply_count desc, elected_count desc</statement><!-- 取得项目等级ID --><select id="getHierarchyIds" resultparameterproperty="evaluateType" compareValue="1">p.start_time = #time#</isEqual><isEqual prepend="," property="evaluateType" compareValue="2">p.mid_check_time = #time#</isEqual><isEqual prepend="," property="evaluateType" compareValue="3">p.end_time = #time#</isEqual></dynamic>where id in ($ids$)</update></sqlMap>
