1、命名空间:
<selectKey resultClass="long" keyProperty="sctId">
SELECT SEQ_TEST.NEXTVAL FROM DUAL
</selectKey>
<selectKey resultClass="long" keyProperty="sctId">
SELECT LAST_INSERT_ID()
select * from customer where id = #value#
</select>
<result property="id" column="ACC_ID"/>
<result property="firstName" column="ACC_FIRST_NAME"/>
<result property="lastName" column="ACC_LAST_NAME"/>
<result property="emailAddress" column="ACC_EMAIL"/>
</resultMap>
<isNotEmpty prepend="and" property="$$$$$">
$name like '%'|| #$name# ||'%'
</isNotEmpty>
<isGreaterThan prepend="and" property="$$$$$" compareValue="$$$number">
$code like '%'|| #$code# ||'%'
</isGreaterThan>
</dynamic>
<resultMap id="base" class="Account">
<result property="id" column="ACC_ID"/>
<result property="firstName" column="ACC_FIRST_NAME"/>
<result property="lastName" column="ACC_LAST_NAME"/>
</resultMap>
<resultMap id="AccountResult" class="Account" extends="Account.base">
<result property="emailAddress" column="ACC_EMAIL"/>
</resultMap>
这样,就很容易扩展了一个映射策略。
private Long id;
private String name;
private String address;
private String postcode;
private String sex;
private List<Orders> orderlist = new ArrayList<Orders>();
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="address" column="address"/>
<result property="postcode" column="postcode"/>
<result property="sex" column="sex"/>
<result property="orderlist" column="id" select="orders.findByCustomerId"/>
</resultMap>
select * from orders where customerId = #value#
</select>
的存储过程。
<parameterMap id="swapParameters" class="map">
<parameter property="email1" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
<parameter property="email2" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
</parameterMap>
<procedure id="swapEmailAddresses" parameterMap="swapParameters">
{call swap_email_address (?, ?)}
</procedure>
调用上面的存储过程将同时互换两个字段(数据库表)和参数对象(Map)中的两个 email地址。如果参数的 mode 属性设为 INOUT 或 OUT,则参数对象的值被修改。否则保持不变。
注意!要确保始终只使用 JDBC 标准的存储过程语法。参考 JDBC 的 CallableStatement
文档以获得更详细的信息。
19、返回map
<resultMap id="CustomerRM" class="Customer">
<result property="id" column="c_id"/>
<result property="name" column="c_name"/>
<result property="address" column="c_address"/>
</resultMap>
<select id="queryCustomer" resultMap="CustomerRM" >
select * from customer
</select>
sqlMapClient.queryForMap("queryCustomer", "param"," id","name"); //map中以id属性为key,以name属性为value
20、一对一,一对多,多对多
<resultMap type="com.zjy.ibatis.model.Customer" id="customerResultMap">
<id property="id" column="id"/>
<result property="username" column="user_name"/>
<result property="password" column="user_pass"/>
<association property="account" select="com.zjy.ibatis.IAccountDao.findAccountByCustomerId" column="id"></association>
<collection property="orders" javaType="ArrayList" ofType="Order"
select="com.zjy.ibatis.IOrderDao.findOrderByCustomerId" column="id"/>
<collection property="activitys" javaType="ArrayList" ofType="Activity"
select="com.zjy.ibatis.IActivityDao.findActivitysByCustomerId" column="id"/>
</resultMap>
21、iBATIS 3改动最多的地方
- <resultMap type="org.newsnotice.domain.NewsNoticeModel" id="resultMap-getNewsNotice1">
- <id column="NN_ID" property="id" />
- <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">
- <id column="CONTENT_ID" property="id" />
- <result column="PARENT_NN_ID" property="parentId" />
- <result column="CONTENT" property="content" />
- </association>
- <collection property="newsNoticeMsgBoxList" ofType="org.newsnotice.domain.NewsNoticeMsgBoxModel" >
- <id column="MSG_BOX_ID" property="id"/>
- <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" />
- </collection>
- </resultMap>
- <select id="getNewsNotice" parameterType="org.newsnotice.domain.NewsNoticeModel" resultMap="resultMap-getNewsNotice1" >
- 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="id != null">
- AND A.NN_ID = #{id}
- </if>
- <if test="category != null">
- AND A.CATEGORY = #{category}
- </if>
- <if test="status != null">
- AND A.WF_STATUS = #{status}
- </if>
- </select>
此外根元素<mapper>的属性namespace在iBATIS 3中是required,而不像iBATIS 2中是可选的,可要可不要。
下面来说说新增元素<association>和<collection>,<association>对应于Java中的Has A模型,也可以理解为数据库中一对一关系,拿上述例子来说,每条消息的概要信息与消息内容是分别存放在两张Table中的,可以通过上述方法一次性将其取出来,而不需要执行多次查询。而<collection>有点类型主从表关系,即one-to-many模型。
查询标签<select>也有所改变,首先是属性名称,由原来的parameterClass改为了parameterType,resultClass与变为了resultType,此外需要注意的是如果传入的参数类型为复杂对象,如Bean,则需要在参数后面加上jdbcType属性来指定对应数据库表列的类型,如#{userName, jdbcType=VARCHAR},如果传入的是基本类型,像int,long之类的,则不需要指定。
另外的变化就是执行方法上的变化,使用select, selectOne, selectList等替代了原来的方法。