iBATIS 3 试用手记二

 　　接昨晚的继续，今天仔细研究了下<association>和<collection>标签，对于它们的写法比较灵活，在这儿我列举三种<resultMap>的写法，它们最终的查询结果都是一致的。

 

　　第一种：Nested(内联)写法

　　请参看我上篇文章，在此不详述了。

　　

　　第二种：将Nested的resultMap单独定义，使用<association>和<collection>的resultMap属性指定，同时查询的SQL与第一种方式相同，需要Join相关联的表。代码如下：

<!-- 第二种写法，将association/collection使用到的resultMap单独定义 -->
	<resultMap type="org.newsnotice.domain.NewsNoticeModel" id="resultMap-getNewsNotice2">
		<id column="NN_ID" property="nnId" />
		<result column="CATEGORY" property="category" />
		<result column="SUBJECT" property="subject" />
		<result column="POSTED_DATE" property="postedDate" />
		<result column="EXPIRY_DATE" property="expiryDate" />
		<result column="ALERT" property="alert" />
		<result column="EMAIL_ALERT" property="emailAlert" />
		<result column="AUDIENCE" property="audience" />
		<result column="FILTER" property="filter" />
		<result column="FILTER_VALUE" property="filterValue" />
		<result column="SUB_FILTER_VALUE" property="subFilterValue" />
		<result column="EXCLUDE_USER_ID" property="excludeUserId" />
		<result column="WF_DEPARTMENT" property="department" />
		<result column="WF_STATUS" property="status" />
		<result column="WF_NOTES" property="notes" />
		<result column="DEFUNCT_IND" property="defunctInd" />
		<result column="APPROVER" property="approver" />
		<association property="newsNoticeContent" column="CONTENT_ID" 
			javaType="org.newsnotice.domain.NewsNoticeContentModel" resultMap="resultMap-content" />
			
		<collection property="newsNoticeMsgBoxList" ofType="org.newsnotice.domain.NewsNoticeMsgBoxModel" 
				resultMap="resultMap-msgbox" />
	</resultMap>	
	<resultMap type="org.newsnotice.domain.NewsNoticeContentModel" id="resultMap-content">
		<id column="CONTENT_ID" property="contentId" />
		<result column="PARENT_NN_ID" property="parentId" />
		<result column="CONTENT" property="content" />
	</resultMap>	
	<resultMap type="org.newsnotice.domain.NewsNoticeMsgBoxModel" id="resultMap-msgbox">
		<id column="MSG_BOX_ID" property="msgBoxId"/>
		<result column="USER_ID" property="userId" />
		<result column="MSG_BOX_NN_ID" property="nnId" />
		<result column="FOLDER" property="folder" />
		<result column="READ" property="read" />			
		<result column="READ_ON" property="readOn" />
		<result column="MSG_BOX_DEFUNCT_IND" property="defunctInd" />
		<result column="MSG_BOX_PI_NO" property="piNo" />
	</resultMap>
	<select id="getNewsNotice2" parameterType="org.newsnotice.domain.NewsNoticeModel" resultMap="resultMap-getNewsNotice2" >
		SELECT 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
		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
		<if test="nnId != null">
			AND A.NN_ID = #{nnId}
		</if>
		<if test="category != null">
			AND A.CATEGORY = #{category}
		</if>
		<if test="status != null">
			AND A.WF_STATUS = #{status}
		</if>
	</select>
	<!-- End. -->

 

　　第三种方式：使用独立的select查询来获取数据，并通过<association>和<collection>的select属性来指定。这种方式需要注意的是主表查询是针对单表，并不join相关联的其它表；同时用column属性指定要传入select指定独立查询中的参数值。代码如下：

　　

<!-- 第三种写法，使用select属性来调用单独的子查询 -->
	<resultMap type="org.newsnotice.domain.NewsNoticeModel" id="resultMap-getNewsNotice3">
		<id column="NN_ID" property="nnId" />
		<result column="CATEGORY" property="category" />
		<result column="SUBJECT" property="subject" />
		<result column="POSTED_DATE" property="postedDate" />
		<result column="EXPIRY_DATE" property="expiryDate" />
		<result column="ALERT" property="alert" />
		<result column="EMAIL_ALERT" property="emailAlert" />
		<result column="AUDIENCE" property="audience" />
		<result column="FILTER" property="filter" />
		<result column="FILTER_VALUE" property="filterValue" />
		<result column="SUB_FILTER_VALUE" property="subFilterValue" />
		<result column="EXCLUDE_USER_ID" property="excludeUserId" />
		<result column="WF_DEPARTMENT" property="department" />
		<result column="WF_STATUS" property="status" />
		<result column="WF_NOTES" property="notes" />
		<result column="DEFUNCT_IND" property="defunctInd" />
		<result column="APPROVER" property="approver" />
		<!-- 通过column属性将值传入select属性所要执行的查询中 -->
		<association property="newsNoticeContent" column="PARENT_NN_ID" 
			javaType="org.newsnotice.domain.NewsNoticeContentModel" select="selectContent"/>
			
		<collection property="newsNoticeMsgBoxList" ofType="org.newsnotice.domain.NewsNoticeMsgBoxModel" 
				select="selectMsgBox" column="MSG_BOX_NN_ID"/>
			
	</resultMap>
	
	<!-- 注意此处的查询为单表查询，并未join相关联的表 -->
	<select id="getNewsNotice3" parameterType="org.newsnotice.domain.NewsNoticeModel" 
			resultMap="resultMap-getNewsNotice3">
		SELECT 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, A.NN_ID PARENT_NN_ID, A.NN_ID MSG_BOX_NN_ID
		FROM NN_MSTR A
		<where>
			<if test="nnId != null">
				AND A.NN_ID = #{nnId}
			</if>
			<if test="category != null">
				AND A.CATEGORY = #{category}
			</if>
			<if test="status != null">
				AND A.WF_STATUS = #{status}
			</if>
		</where>
	</select>	
	
	<!-- 此处的参数接收由主查询传入的值，至于属性名可以随便写，如此处的xxxxx,yyyyy -->
	<select id="selectContent" parameterType="int" 
			resultMap="resultMap-content">
		SELECT B.ID CONTENT_ID, B.PARENT_NN_ID, B.CONTENT FROM NN_CONTENT B
		WHERE B.PARENT_NN_ID = #{xxxxxx}
	</select>
	
	<select id="selectMsgBox" parameterType="int" 
			resultMap="resultMap-msgbox">
		SELECT 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
		FROM NN_MSG_BOX C
		WHERE C.NN_ID = #{yyyyyy}
	</select>
	<!-- End. -->

 

在这儿还是给出代码中涉及到的三个domain对象代码，方便大家对应看。

public class NewsNoticeModel {
	private Long nnId;
	private String category;
	private String subject;
	private Date postedDate;
	private Date expiryDate;
	private String alert;
	private String emailAlert;
	private String audience;
	private String filter;
	private String filterValue;
	private String subFilterValue;
	private String excludeUserId;
	private String department;
	private String status;
	private String notes;
	private String defunctInd;
	private String approver;
	private NewsNoticeContentModel newsNoticeContent;
	private List<NewsNoticeMsgBoxModel> newsNoticeMsgBoxList; 
	
	......//省略get/set方法

   

public class NewsNoticeMsgBoxModel {
	private long msgBoxId;
	private String userId;
	private long nnId;
	private String folder;
	private String read;
	private Date readOn;
	private String defunctInd;
	private String piNo;

......//省略get/set方法

 

public class NewsNoticeContentModel {
	private long contentId;
	private long parentId;
	private byte[] content;

......//省略get/set方法

 

哦，对了，忘记说了，源代码可以直接用eclipse导入，JUnit测试类在org.ibatis3.test包下。