iBATIS中的sql map参考

1. <?xml version="1.0" encoding="UTF-8" ?>  
2.   
3. <!DOCTYPE sqlMap         
4.     PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"         
5.     "http://ibatis.apache.org/dtd/sql-map-2.dtd">  
6.   
7. <sqlMap namespace="Category">  
8.   
9.     <typeAlias alias="subject" type="com.lhq.prj.bms.po.Subject" />  
10.     <typeAlias alias="category" type="com.lhq.prj.bms.po.Category" />  
11.     <typeAlias alias="page" type="com.lhq.prj.bms.core.Page" />  
12.   
13.     <parameterMap class="category" id="pm_category_without_id">  
14.         <parameter property="categoryName" javaType="string" jdbcType="VARCHAR" />  
15.         <parameter property="subjectId" javaType="integer" jdbcType="NUMBER" />  
16.         <parameter property="subjectName" javaType="string" jdbcType="VARCHAR" />  
17.         <parameter property="remark" javaType="string" jdbcType="VARCHAR" />  
18.     </parameterMap>  
19.     <sql id="byCategoryIdCondition">  
20.         <![CDATA[  
21.            categoryId = #categoryId:NUMBER#  
22.         ]]>  
23.     </sql>  
24.   
25.     <insert id="save" parameterMap="pm_category_without_id">  
26.         <![CDATA[  
27.             INSERT INTO t_category   
28.                         (categoryName,subjectId,subjectName,remark)   
29.                  VALUES (?,?,?,?)  
30.         ]]>  
31.         <selectKey resultClass="int">  
32.             <![CDATA[  
33.                 SELECT @@IDENTITY AS ID  
34.             ]]>  
35.         </selectKey>  
36.     </insert>  
37.   
38.     <delete id="deleteById" parameterClass="integer">  
39.         <![CDATA[  
40.             delete t_category  
41.         ]]>  
42.         <dynamic prepend="WHERE">  
43.             <include refid="byCategoryIdCondition" />  
44.         </dynamic>  
45.     </delete>  
46.   
47.     <select id="findAll" resultClass="category">  
48.         <![CDATA[  
49.             SELECT * FROM t_category  
50.         ]]>  
51.     </select>  
52.   
53.     <update id="update" parameterClass="category">  
54.         <![CDATA[  
55.             UPDATE t_category  
56.         ]]>  
57.         <dynamic prepend="SET">  
58.             <isNotNull property="categoryName" prepend=",">  
59.                 <![CDATA[  
60.                     categoryName = #categoryName:VARCHAR#  
61.                 ]]>  
62.             </isNotNull>  
63.             <isNotNull property="subjectId" prepend=",">  
64.                 <![CDATA[  
65.                     subjectId = #subjectId:NUMBER#  
66.                 ]]>  
67.             </isNotNull>  
68.             <isNotNull property="subjectName" prepend=",">  
69.                 <![CDATA[  
70.                     subjectName = #subjectName:VARCHAR#  
71.                 ]]>  
72.             </isNotNull>  
73.             <isNotNull property="remark" prepend=",">  
74.                 <![CDATA[  
75.                     remark = #remark:VARCHAR#  
76.                 ]]>  
77.             </isNotNull>  
78.         </dynamic>  
79.         <dynamic prepend="WHERE">  
80.             <include refid="byCategoryIdCondition" />  
81.         </dynamic>  
82.     </update>  
83.   
84.     <sql id="findCategoryByPageCondition">  
85.         <isNotEmpty property="conditions">  
86.             <iterate property="conditions" open="(" close=")" conjunction="OR">  
87.                 <![CDATA[  
88.                     upper(categoryName) LIKE '%' + upper(#conditions[]:VARCHAR#) + '%'  
89.                     OR upper(subjectName) LIKE '%' + upper(#conditions[]:VARCHAR#) + '%'  
90.                     OR upper(remark) LIKE '%' + upper(#conditions[]:VARCHAR#) + '%'   
91.                 ]]>  
92.             </iterate>  
93.         </isNotEmpty>  
94.     </sql>  
95.   
96.     <select id="findByPage" parameterClass="page" resultClass="category">  
97.         <![CDATA[  
98.             SELECT TOP $limit$ * FROM t_category   
99.                     WHERE (categoryId >= (SELECT MAX(categoryId) FROM (SELECT TOP $start$ categoryId FROM t_category   
100.         ]]>  
101.         <dynamic prepend="WHERE">  
102.             <include refid="findCategoryByPageCondition" />  
103.         </dynamic>  
104.         <![CDATA[  
105.             ORDER BY categoryId ) AS T))   
106.         ]]>  
107.         <dynamic prepend="AND">  
108.             <include refid="findCategoryByPageCondition" />  
109.         </dynamic>  
110.         <![CDATA[  
111.             ORDER BY categoryId  
112.         ]]>  
113.     </select>  
114.   
115.     <select id="findByCount" parameterClass="page" resultClass="int">  
116.         <![CDATA[  
117.             SELECT COUNT(*) FROM t_category   
118.         ]]>  
119.         <dynamic prepend="WHERE">  
120.             <include refid="findCategoryByPageCondition" />  
121.         </dynamic>  
122.     </select>  
123.   
124.     <select id="findCategoryBySubject" parameterClass="subject" resultClass="category">  
125.         <![CDATA[  
126.             SELECT * FROM t_category   
127.         ]]>  
128.         <dynamic prepend="WHERE">  
129.             <isNotNull property="subjectId" prepend="OR">  
130.                 <![CDATA[  
131.                     subjectId = #subjectId:NUMBER#  
132.                 ]]>  
133.             </isNotNull>  
134.             <isNotNull property="subjectName" prepend="OR">  
135.                 <![CDATA[  
136.                     subjectName like '%' +  #subjectName:VARCHAR# + '%'  
137.                 ]]>  
138.             </isNotNull>  
139.         </dynamic>  
140.     </select>  
141. </sqlMap>