前两篇手记重点说了下iBATIS 3的查询功能,因为这是它改动最大的地方。这篇手记就来说下iBATIS在Persistance方面的改进。
iBATIS 3依然提供了<insert><update><delete>标签对数据库进行持久化操作。首先来看insert操作:
<insert id="insertNewsNotice" parameterType="org.newsnotice.domain.NewsNoticeModel" flushCache="true"
statementType="PREPARED" timeout="20000">
<selectKey keyProperty="nnId" order="BEFORE" resultType="long">
SELECT SEQ_NN_MSTR_ID.NEXTVAL FROM DUAL
</selectKey>
INSERT INTO NN_MSTR(
NN_ID
<if test="category != null">,CATEGORY</if>
<if test="subject != null">,SUBJECT</if>
,POSTED_DATE
,EXPIRY_DATE
<if test="alert != null">,ALERT</if>
<if test="emailAlert != null">,EMAIL_ALERT</if>
<if test="audience != null">,AUDIENCE</if>
<if test="filter != null">,FILTER</if>
<if test="filterValue != null">,FILTER_VALUE</if>
<if test="subFilterValue != null">
,SUB_FILTER_VALUE
</if>
<if test="excludeUserId != null">
,EXCLUDE_USER_ID
</if>
<if test="department != null">,WF_DEPARTMENT</if>
<if test="status != null">,WF_STATUS</if>
<if test="notes != null">,WF_NOTES</if>
<if test="createdBy != null">,WF_CREATED_BY</if>
<if test="createdDate != null">,WF_CREATED_ON</if>
<if test="modifiedBy != null">,WF_MODIFIED_BY</if>
<if test="modifiedDate != null">
,WF_MODIFIED_ON
</if>
<if test="approvedBy != null">,WF_APPROVED_BY</if>
<if test="approvedOn != null">,WF_APPROVED_ON</if>
<if test="version != null">,WF_VERSION</if>
)VALUES(
#{nnId}
<if test="category != null">,#{category}</if>
<if test="subject != null">,#{subject}</if>
,sysdate
,sysdate + 7
<if test="alert != null">,#{alert}</if>
<if test="emailAlert != null">,#{emailAlert}</if>
<if test="audience != null">,#{audience}</if>
<if test="filter != null">,#{filter}</if>
<if test="filterValue != null">,#{filterValue}</if>
<if test="subFilterValue != null">
,#{subFilterValue}
</if>
<if test="excludeUserId != null">
,#{excludeUserId}
</if>
<if test="department != null">,#{department}</if>
<if test="status != null">,#{status}</if>
<if test="notes != null">,#{notes}</if>
<if test="createdBy != null">,#{createdBy}</if>
<if test="createdDate != null">,#{createdDate}</if>
<if test="modifiedBy != null">,#{modifiedBy}</if>
<if test="modifiedDate != null">
,#{modifiedDate}
</if>
<if test="approvedBy != null">,#{approvedBy}</if>
<if test="approvedOn != null">,#{approvedOn}</if>
)
</insert>
对于<insert>,iBATIS 3新增了以下属性:
- useGeneratedKeys:当设置为true时,它会自动从表的自增列获取主键值,像MySQL/MS SQL Server都提供了此类型的主键
- keyProperty:存储主键的属性名
- statementType:可以设置为STATEMENT,PREPARED,CALLABLE,默认为PREPARED,像在调用存储过程时就可以使用CALLABLE类型
另外一个改进是在insert时支持使用<selectKey>标签获取主键值,如上面代码所示。keyProperty指定存放主键的属性名,order指定<selectKey>的执行顺序,值可以为BEFORE或AFTER,如果是BEFORE则先执行<selectKey>中的SQL,再执行<insert>操作,AFTER则相反。
接下来是update操作,iBATIS 3提供了<set>标签来方便<update>操作,它会自动判断是否需要加上SET关键字。
<update id="updateNewsNotice" parameterType="org.newsnotice.domain.NewsNoticeModel" flushCache="true" statementType="PREPARED" timeout="20000"> UPDATE NN_MSTR <set> <if test="category != null">CATEGORY = #{category},</if> <if test="subject != null">SUBJECT = #{subject}</if> </set> WHERE NN_ID = #{nnId} </update>
至于Delete操作和iBATIS 2相同,在些就不详述了。
此外再介绍两个比较有用的标签:<sql>和<foreach>
<sql>标签主要用于定义可以重用的SQL,然后在需要用到的地方使用标签<include>将其加入即可。如下:
<sql id="newsNoticeColumns"> A.NN_ID, A.CATEGORY, A.SUBJECT, A.POSTED_DATE, A.EXPIRY_DATE, A.ALERT, A.EMAIL_ALERT, A.AUDIENCE, A.FILTER, A.FILTER_VALUE, A.SUB_FILTER_VALUE, A.EXCLUDE_USER_ID, A.WF_DEPARTMENT, A.WF_STATUS, A.WF_NOTES, A.DEFUNCT_IND, A.APPROVER, B.ID CONTENT_ID, B.PARENT_NN_ID, B.CONTENT, C.ID MSG_BOX_ID, C.USER_ID, C.NN_ID MSG_BOX_NN_ID, C.FOLDER, C.READ, C.READ_ON, C.DEFUNCT_IND MSG_BOX_DEFUNCT_IND, C.PI_NO MSG_BOX_PI_NO </sql>
<select id="getNewsNotice1" parameterType="org.newsnotice.domain.NewsNoticeModel" resultMap="resultMap-getNewsNotice1" >
SELECT <include refid="newsNoticeColumns"/>
FROM NN_MSTR A, NN_CONTENT B, NN_MSG_BOX C
WHERE A.NN_ID = B.PARENT_NN_ID
AND A.NN_ID = C.NN_ID
......
而<foreach>标签则用于迭代集合或数组,常用于在IN操作中,其操作类似JSTL中的<c:foreach>,如下:
<select id="foreachSelect" parameterType="org.newsnotice.domain.NewsNoticeModel" resultType="int"> SELECT COUNT(*) FROM NN_MSTR WHERE WF_STATUS IN <foreach collection="statusArray" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach> </select>
最终生成的SQL将是:......WF_STATUS IN (value1, value2, ....)
最后一点需要注意的是如果TransactionType设置JDBC的话,iBATIS 3默认Auto Commit为false, 所以在执行完持久化操作后,需要调用session.commit()方法来提交事务,或是首先调用session.getConnection().setAutoCommit(autoCommit)来设置Auto Commit策略。
iBATIS 3持久化的介绍就到此,下一篇手记将简要介绍下iBATIS 3提供的Annotation操作,Annotation在一些简单SQL应用方面还不非常不错的,效率高,不用专门去定义xml。