iBatis小结(转)

# <?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="Account">     
#   <typeAlias alias="Account" type="test.Account"/>     
#   <!--列表查询,返回Account Object的List-->        
#   <resultMap id="AccountResult" class="Account">     
#     <result property="id" column="ID"/>     
#     <result property="firstName" column="FIRST_NAME"/>     
#     <result property="lastName" column="LAST_NAME"/>     
#     <result property="emailAddress" column="EMAIL"/>     
#   </resultMap>       
#   <select id="selectAllAccounts" resultMap="AccountResult">     
#     select * from ACCOUNT     
#   </select>     
#        
#   <!--列表查询,返回HashMap的List -->      
#   <!--resultMap无需再通过JDBC ResultSetMetaData 来动态获取字段信息，性能有提高-->     
#     
#   <resultMap id="mapResult" class="java.util.HashMap">     
#     <result property="roleid" column="ROLEID"/>     
#     <result property="rolename" column="ROLENAME"/>     
#     <result property="id" column="ID"/>     
#     <result property="firstName" column="FIRST_NAME"/>     
#     <result property="lastName" column="LAST_NAME"/>     
#     <result property="emailAddress" column="EMAIL"/>     
#     <result property="dt" column="DT"/>     
#   </resultMap>     
#   <select id="selectAllAccountsWithMap" resultMap="mapResult">     
#         select B.ROLEID, B.ROLENAME, A.ID, A.FIRST_NAME,A.LAST_NAME,A.EMAIL,A.DT     
#         from ACCOUNT A left outer join ROLE B   on A.ROLEID = B.ROLEID     
#         ORDER BY A.ID DESC     
#   </select>     
#        
# <!--动态构造查询条件-->     
# <select id="getUsers" parameterClass="user" resultMap="get-user-result">     
#     Select id,name,sex from t_user     
#     <dynamic prepend="WHERE">     
#         <isNotEmpty prepend="AND" property="name">     
#             (name like #name#)     
#         </isNotEmpty>     
#         <isNotEmpty prepend="AND" property="address">     
#             (address like #address#)     
#         </isNotEmpty>     
#     </dynamic>     
# </select>     
# <isNotEmpty prepend="AND" property="name">     
#     ( name=#name#     
#         <isNotEmpty prepend="AND" property="address">     
#             address=#address#     
#         </isNotEmpty>     
#     )     
# </isNotEmpty>     
# <select id="dynamicGetAccountList" resultMap="account-result" >     
#     select * from ACCOUNT     
#     <dynamic prepend="WHERE">     
#         <isNotNull prepend="AND" property="firstName" open=”(“ close=”)”>     
#             ACC_FIRST_NAME = #firstName#     
#             <isNotNull prepend="OR" property="lastName">     
#                 ACC_LAST_NAME = #lastName#     
#             </isNotNull>     
#         </isNotNull>     
#         <isNotNull prepend="AND" property="emailAddress">     
#             ACC_EMAIL like #emailAddress#     
#         </isNotNull>     
#         <isGreaterThan prepend="AND" property="id" compareValue="0">     
#             ACC_ID = #id#     
#         </isGreaterThan>     
#     </dynamic>     
#     order by ACC_LAST_NAME     
# </select>     
#     
# <isParameterPresent> <isNotParameterPresent> <isNull> <isNotNull> <isEmpty> <isNotEmpty>     
# <isEqual> <isNotEqual> <isGreaterThan> <isGreaterEqual> <isLessThan> <isLessEqual>     
#     
# <!-- Sql片段的是用-->     
# <sql id="selectItem_fragment">     
#     FROM items WHERE parentid = 6    
# </sql>     
# <select id="selectItemCount" resultClass="int">     
#     SELECT COUNT(*) AS total     
#     <include refid="selectItem_fragment"/>     
# </select>     
# <select id="selectItems" resultClass="Item">     
#     SELECT id, name     
#     <include refid="selectItem_fragment"/>     
# </select>     
#     
# <!--缓存-->     
# <cacheModel id="product-cache" type="LRU">     
#     <flushInterval hours="24"/>     
#     <flushOnExecute statement="insertProduct"/>     
#     <flushOnExecute statement="updateProduct"/>     
#     <flushOnExecute statement="deleteProduct"/>     
#     <property name=”size” value=”1000” />     
# </cacheModel>     
# <select id=”getProductList” parameterClass=”int” cacheModel=”product-cache”>     
#     select * from PRODUCT where PRD_CAT_ID = #value#     
# </select>     
#     
# <!—对XML支持 -->     
# <select id="getPerson" parameterClass=”int” resultClass="xml" xmlResultName=”person”>     
#     SELECT     
#     PER_ID as id,     
#     PER_FIRST_NAME as firstName,     
#     PER_LAST_NAME as lastName,     
#     PER_BIRTH_DATE as birthDate,     
#     PER_WEIGHT_KG as weightInKilograms,     
#     PER_HEIGHT_M as heightInMeters     
#     FROM PERSON     
#     WHERE PER_ID = #value#     
# </select>     
# <person>     
#     <id>1</id>     
#     <firstName>Clinton</firstName>     
#     <lastName>Begin</lastName>     
#     <birthDate>1900-01-01</birthDate>     
#     <weightInKilograms>89</weightInKilograms>     
#     <heightInMeters>1.77</heightInMeters>     
# </person>     
#     
# <!—字符串替换     
# select * from $tableName$     
# Important Note 1: This support will only substitute Strings, so it is not appropriate for complex data types like Date or Timestamp.     
# Important Note 2: If you use this support to alter a table name, or a column list, in an SQL select statement,then you should always specify remapResults=“true”     
# -->.     
#     
# <!—关联查询方式，有1/N问题-->     
# <sqlMap namespace="User">     
#     <typeAlias alias="user" type="com.ibatis.sample.User"/>     
#     <typeAlias alias="address" type="com.ibatis.sample.Address"/>     
#     <resultMap id="get-user-result" class="user">     
#         <result property="id" column="id"/>     
#         <result property="name" column="name"/>     
#         <result property="sex" column="sex"/>     
#         <result property="addresses" column="id" select="User.getAddressByUserId"/>     
#     </resultMap>     
#     <select id="getUsers"    parameterClass="java.lang.String" resultMap="get-user-result">     
#         <![CDATA[Select id,name,sex from t_user where id = #id#]]>     
#     </select>     
#     <select id="getAddressByUserId"  parameterClass="int" resultClass="address">     
#         <![CDATA[select address,zipcode from t_address where user_id = #userid# ]]>     
#     </select>     
# </sqlMap>     
# <resultMap id="get-user-result" class="user">     
#     <result property="id" column="id"/>     
#     <result property="name" column="name"/>     
#     <result property="sex" column="sex"/>     
#     <result property="address" column="t_address.address"/>     
#     <result property="zipCode" column="t_address.zipcode"/>     
# </resultMap>     
# <select id="getUsers" parameterClass="java.lang.String" resultMap="get-user-result">     
#     <![CDATA[select* from t_user,t_address where t_user.id=t_address.user_id]]>     
# </select>     
# 保证User 类中包含address和zipCode两个String型属性。     
#     
# <!—关联查询，无1/N问题-->     
#   <resultMap id="AccountResultWithRole" class="Account" groupBy="id">     
#     <result property="id" column="ID"/>     
#     <result property="firstName" column="FIRST_NAME"/>     
#     <result property="lastName" column="LAST_NAME"/>     
#     <result property="emailAddress" column="EMAIL"/>         
#     <result property="role" resultMap="Account.roleResult"/>     
#   </resultMap>     
#   <resultMap id="roleResult" class="test.Role">     
#     <result property="roleid" column="ROLEID"/>     
#     <result property="rolename" column="ROLENAME"/>     
#   </resultMap>     
#   <select id="selectAccountByIdWithRole" parameterClass="int" resultMap="AccountResultWithRole">     
#         select B.ROLEID, B.ROLENAME, A.ID, A.FIRST_NAME,A.LAST_NAME,A.EMAIL from ACCOUNT A left outer join ROLE B on A.ROLEID = B.ROLEID where A.ID = #id#     
#   </select>     
#        
#   <!--查询-->        
#   <select id="selectAccountById" parameterClass="int" resultClass="Account">     
#     select ID as id,FIRST_NAME as firstName,LAST_NAME as lastName,       EMAIL as emailAddress from ACCOUNT where ID = #id#     
#   </select>     
#        
#   <!--新增-->          
#   <insert id="insertAccount" parameterClass="Account">     
#     insert into ACCOUNT (FIRST_NAME,LAST_NAME,EMAIL,PID,DT)       
#     values (#firstName:VARCHAR#, #lastName:VARCHAR#, #emailAddress:VARCHAR#,#pid:INTEGER:0#,#dt:TIME#)     
#     <selectKey resultClass="int" type="post" keyProperty="id">     
#         SELECT @@IDENTITY AS ID     
#     </selectKey>         
#   </insert>     
#        
#   <!--更新-->        
#   <update id="updateAccount" parameterClass="Account">     
#     update ACCOUNT set     
#       FIRST_NAME = #firstName:VARCHAR#,     
#       LAST_NAME = #lastName:VARCHAR#,     
#       EMAIL = #emailAddress:VARCHAR#     
#     where     
#       ID = #id#     
#   </update>     
#     
#   <!--删除-->     
#   <delete id="deleteAccountById" parameterClass="int">     
#     delete from ACCOUNT where ID = #id#     
#   </delete>     
#        
#   <!--存储过程，如果没有返回列表，procTest的resultMap可以省略-->      
#   <parameterMap id="procParamMap" class="java.util.HashMap" >     
#     <parameter property="id" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>     
#     <parameter property="outid" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>     
#     <parameter property="errMsg" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>     
#   </parameterMap>     
#   <resultMap id="procResultMap" class="java.util.HashMap" >     
#     <result property="a" column="AAA"/>     
#     <result property="b" column="BBB"/>     
#     <result property="c" column="CCC"/>         
#   </resultMap>       
#   <procedure id="procTest" parameterMap="procParamMap" resultMap="procResultMap">     
#     {call test_sp_1 (?,?,?)}     
#   </procedure>     
# </sqlMap>