ibatis学习笔记（三） 数据关联

我们来讨论一下关联数据的情况

1.一对多关联

首先，我们有两张表，商品和评论，表的结构如下：

create table t_product (    id int(11) not null auto_increment,    name varchar(50) default null,    description varchar(1000) default null,    url varchar(100) default null,    price float(20,2) default null,    primary key (id));create table t_remark (    id int(11) not null auto_increment,    prod_id int(11),    user_name varchar(50) default NULL,    content varchar(50) default NULL,    primary key (id));

一个商品对应多个评论

我们在配置Product.xml的定义如下：

<sqlMap namespace="Product"><typeAlias alias="product" type="com.commerce.model.Product" /><typeAlias alias="remark" type="com.commerce.model.Remark" /><resultMap id="get-prod-result" class="product"><result property="id" column="id"/><result property="name" column="name"/><result property="remarks" column="id" select="getRemarkByProdId"/></resultMap><select id="getProducts" parameterClass="java.lang.Integer"resultMap="get-prod-result"><![CDATA[select * from t_product]]></select><select id="getRemarkByProdId" parameterClass="int"resultClass="remark"><![CDATA[select * from t_remark where prod_id=#value#]]></select></sqlMap>

然后给表插入数据后，调用如下代码：

List l = sqlMap.queryForList("getProducts", null);Remark re1 = ((Product)l.get(0)).getRemarks().get(0);Remark re2 = ((Product)l.get(1)).getRemarks().get(0);System.out.println(re1.getContent());System.out.println(re2.getContent());

然后我们来看一下日志：

[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [Created connection 792735552.] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{conn-100000} Connection] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100001} PreparedStatement:        select * from t_product     ] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100001} Parameters: []] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100001} Types: []] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100002} ResultSet] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100002} Header: [id, name, id]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100002} Result: [1, ?? T400 ???, 1]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100002} Result: [2, T410, 2]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [Returned connection 792735552 to pool.] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [Checked out connection 792735552 from pool.] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{conn-100003} Connection] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100004} PreparedStatement:        select * from t_remark where prod_id=?     ] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100004} Parameters: [1]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100004} Types: [java.lang.Integer]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100005} ResultSet] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100005} Header: [id, prod_id, user_name, content]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100005} Result: [1, 1, huiming, good]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [Returned connection 792735552 to pool.] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [Checked out connection 792735552 from pool.] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{conn-100006} Connection] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100007} PreparedStatement:        select * from t_remark where prod_id=?     ] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100007} Parameters: [2]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{pstm-100007} Types: [java.lang.Integer]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100008} ResultSet] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100008} Header: [id, prod_id, user_name, content]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [{rset-100008} Result: [2, 2, huiming, bad]] |[DEBUG] [2012-03-21 11:19:15]  [Thread = main | Class = com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl | Method = debug | Line = 23 ] | [Returned connection 792735552 to pool.] |goodbad