mybatis标签之——关联映射
关联关系是面向对象分析、面向对象设计最重要的知识。合理的关联映射将大大简化持久层数据的访问。关联关系大致分为以下三类：
- 一对一
- 一对多
- 多对多
1. 一对一
一对一关系推荐使用唯一主外键关联，即两张表使用外键关联关系，由于是一对一关联，因此还需要给外键列增加 unique 唯一约束。
-- 身份证表 create table tb_card { id int primary key auto_increment, code varchar(18) };
insert into tb_card (code) values('370782199502124589');
-- 个人表 create table tb_person { id int primary key auto_increment, name varchar(18), sex varchar(18), age int, card_id int unique, --card_id 唯一约束，且作为外键 foreign key (card_id) references tb_card (id) };
insert into tb_person (name,sex,age,card_id) values('jack','男',23,1);
代码中创建 Card 对象和 Person 的对象（此处省略 get/set 方法）
public class Card implements Serializable {</span><span style="color: rgba(0, 0, 255, 1)">private</span> Integer id; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 主键id</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String code; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 身份证编号</span>···
}
public class Person implements Serializable {</span><span style="color: rgba(0, 0, 255, 1)">private</span> Integer id; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 主键id</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String name; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 姓名</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String sex; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 性别</span> <span style="color: rgba(0, 0, 255, 1)">private</span> Integer age; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 年龄 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 人和身份证是一对一的关系，即一个人只有一个身份证，在Person类中定义card属性，来映射一对一关系</span> <span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Card card; ···}
映射 xml 文件如下
CardMapper.xml
<!-- namespace 指用户自定义的命名空间。 --> <mapper namespace="org.zang.mapper.CardMapper"> <!-- 根据 id 查询 Card，返回 Card 对象 --> <select id="selectCardById" parameterType="int" resultType="org.zang.domain.Card"> SELECT * from tb_card where id = #{id} </select> </mapper>
PersonMapper.xml
<mapper namespace="org.zang.mapper.PersonMapper"> <!-- 映射 Peson 对象的 resultMap --> <resultMap type="org.zang.domain.Person" id="personMapper"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="age" column="age"/><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 一对一关联映射:association </span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)"><strong>association</strong> </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="card"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="card_id"</span><span style="color: rgba(255, 0, 0, 1)"> select</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.mapper.CardMapper.selectCardById"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.Card"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">></span><!-- 根据 id 查询 Person，返回 resultMap -->
<select id="selectPersonById" parameterType="int"
resultMap="personMapper">
SELECT * from tb_person where id = #{id}
</select>
</mapper>
可以看到，personMapper 中使用 <association.../> 元素来映射一对一的关联关系。
2. 一对多
数据库中一对多关系通常使用主外键关联，外键列应该在多方，即多方维护关系。
-- 班级表 （一） create table tb_clazz( id int primary key auto_increment, code varchar(18), name varchar(18) );
insert into tb_clazz(code,name) values('j1601','java 就业班');-- 学生表 （多）
create table tb_student(
id int primary key auto_increment,
name varchar(18),
sex varchar(18),
age int,
clazz_id int, --clazz_id 作为外键参照 tb_clazz 的主键 id
foreign key (clazz_id) references tb_clazz(id)
);
insert into tb_student(name,sex,age,clazz_id) values('jack','男',23,1);
insert into tb_student(name,sex,age,clazz_id) values('rose','女',18,1);
insert into tb_student(name,sex,age,clazz_id) values('tom','男',21,1);
insert into tb_student(name,sex,age,clazz_id) values('alice','女',20,1);
代码中创建 Clazz 对象和 Student 的对象（此处省略 get/set 方法）
public class Clazz implements Serializable {</span><span style="color: rgba(0, 0, 255, 1)">private</span> Integer id; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 班级id，主键</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String code; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 班级编号</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String name; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 班级名称 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 班级和学生是一对多的关系，即一个班级可以有多个学生</span> <span style="color: rgba(0, 0, 255, 1)">private</span> List<Student><span style="color: rgba(0, 0, 0, 1)"> students;···
}
public class Student implements Serializable {</span><span style="color: rgba(0, 0, 255, 1)">private</span> Integer id; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 学生id，主键</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String name; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 姓名</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String sex; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 性别</span> <span style="color: rgba(0, 0, 255, 1)">private</span> Integer age; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 年龄 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 学生和班级是多对一的关系，即一个学生只属于一个班级</span> <span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Clazz clazz;···
}
映射文件如下
ClazzMapper.xml
<mapper namespace="org.zang.mapper.ClazzMapper"><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 映射Clazz对象的resultMap </span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">type</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.Clazz"</span><span style="color: rgba(255, 0, 0, 1)"> id</span><span style="color: rgba(0, 0, 255, 1)">="clazzResultMap"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="code"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="code"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 一对多关联映射:collection fetchType="lazy"表示懒加载 </span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">collection </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="students"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="ArrayList"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> ofType</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.Student"</span><span style="color: rgba(255, 0, 0, 1)"> select</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.mapper.StudentMapper.selectStudentByClazzId"</span><span style="color: rgba(255, 0, 0, 1)"> fetchType</span><span style="color: rgba(0, 0, 255, 1)">="lazy"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="sex"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="sex"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="age"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="age"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">collection</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 根据id查询班级信息，返回resultMap </span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="selectClazzById"</span><span style="color: rgba(255, 0, 0, 1)"> parameterType</span><span style="color: rgba(0, 0, 255, 1)">="int"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="clazzResultMap"</span><span style="color: rgba(0, 0, 255, 1)">></span><span style="color: rgba(0, 0, 0, 1)"> SELECT * FROM tb_clazz WHERE id = #{id} </span><span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">></span></mapper>
由于 student 属性是一个 List 集合，所有 clazzResultMap 中使用了 <collection.../> 元素映射一对多的关联关系。
此外还通过 fetchType="lazy" 来设置懒加载。fatch 机制更多的是为了性能考虑，如果查询班级时确认会访问班级的所有学生，则该属性应该设置为 eager；否则应该设置为 lazy；正常情况下，一对多所关联的集合对象，都应该被设置成 lazy。
使用懒加载还应该在 mybatis-config.xml 中增加如下配置
<settings> <!-- 要使延迟加载生效必须配置下面两个属性 --> <setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="false"/> </settings>
StudentMapper.xml
<mapper namespace="org.zang.mapper.StudentMapper"><!-- 映射 Student 对象的 resultMap -->
<resultMap type="org.zang.domain.Student" id="studentResultMap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<!-- 多对一关联映射:association -->
<association property="clazz" javaType="org.zang.domain.Clazz">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
</association>
</resultMap><span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 根据id查询学生信息，多表连接，返回resultMap </span><span style="color: rgba(0, 128, 0, 1)">--></span><select id="selectStudentById" parameterType="int" resultMap="studentResultMap">
SELECT * FROM tb_clazz c,tb_student s
WHERE c.id = s.clazz_id
AND s.id = #{id}
</select><!-- 根据班级 id 查询学生信息，返回 resultMap -->
<select id="selectStudentByClazzId" parameterType="int"
resultMap="studentResultMap">
SELECT * FROM tb_student WHERE clazz_id = #{id}
</select></mapper>
studentResultMap 中使用 <association.../> 元素映射多对一的关联关系。因为 <select id="selectStudentById".../> 的 sql 语句是一条多表连接，关联 tb_clazz 表的同时查询了班级数据，所以 <association.../> 只是简单的装载数据。
tips：在实际开发中，由于一对多关系通常映射为集合对象，而由于多方的数据量可能很大，所以通常使用懒加载；而多对一只是关联到一个对象，所以通常使用多表连接直接提取出数据。
3. 多对多
在一个购物系统中，一个用户可以有多个订单，这是一对多的关系；一个订单中可以有多种商品，一种商品也可以属于多个不同的订单，订单和商品就是多对多的关系。
对于数据库中多对多的关系建议使用一个中间表来维护关系，中间表中的订单 id 作为外键参照订单表的 id，商品 id 作为外键参照商品表的 id。
-- 用户表 create table tb_user( id int primary key auto_increment, username varchar(18), loginname varchar(18), password varchar(18), phone varchar(18), address varchar(18) );
insert into tb_user(username,loginname,password,phone,address) values('杰克','jack','123456','13920001616','广州');-- 商品表
create table tb_article(
id int primary key auto_increment,
name varchar(18),
price double,
remark varchar(18)
);insert into tb_article(name,price,remark)
values('疯狂 java 讲义',108.9,'李刚老师经典著作');
insert into tb_article(name,price,remark)
values('疯狂 android 讲义',99.9,'李刚老师经典著作');
insert into tb_article(name,price,remark)
values('疯狂 ios 讲义',89.9,'李刚老师经典著作');
insert into tb_article(name,price,remark)
values('springmvc+mybatis 企业开发',69.9,'肖文吉老师经典著作');-- 订单表
create table tb_order(
id int primary key auto_increment,
code varchar(32),
total double,
user_id int,
foreign key (user_id) references tb_user(id)
);insert into tb_order(code,total,user_id)
values('6aa3fa359ff14619b77fab5990940a2d',388.6,1);insert into tb_order(code,total,user_id)
values('6aa3fa359ff14619b77fab5990940b3c',217.8,1);--创建订单表
create table tb_item(
order_id int,
article_id int,
amount int,
primary key(order_id,article_id),
foreign key (order_id) references tb_order(id),
foreign key (article_id) references tb_article(id)
);insert into tb_item(order_id,article_id,amount)
values(1,1,1);
insert into tb_item(order_id,article_id,amount)
values(1,2,1);
insert into tb_item(order_id,article_id,amount)
values(1,3,2);insert into tb_item(order_id,article_id,amount)
values(2,4,2);
insert into tb_item(order_id,article_id,amount)
values(2,1,1);
tb_order 表的 user_id 作为外键参照 tb_user 表的主键 id。tb_item 表作为中间表，用来维护 tb_article 和 tb_order 的多对多关系，tb_item 表的 order_id 作为外键参照 tb_order 表的主键 id,article_id 作为外键参照 tb_article 表的主键 id。
代码中创建 User 对象、Order 对象和 Article 的对象（此处省略 get/set 方法）
public class User implements Serializable{</span><span style="color: rgba(0, 0, 255, 1)">private</span> Integer id; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 用户id，主键</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String username; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 用户名</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String loginname; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 登录名</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String password; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 密码</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String phone; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 联系电话</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String address; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 收货地址 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 用户和订单是一对多的关系，即一个用户可以有多个订单</span> <span style="color: rgba(0, 0, 255, 1)">private</span> List<Order><span style="color: rgba(0, 0, 0, 1)"> orders;···
}
public class Order implements Serializable {</span><span style="color: rgba(0, 0, 255, 1)">private</span> Integer id; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 订单id，主键</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String code; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 订单编号</span> <span style="color: rgba(0, 0, 255, 1)">private</span> Double total; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 订单总金额 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 订单和用户是多对一的关系，即一个订单只属于一个用户</span> <span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> User user; </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 订单和商品是多对多的关系，即一个订单可以包含多种商品</span> <span style="color: rgba(0, 0, 255, 1)">private</span> List<Article><span style="color: rgba(0, 0, 0, 1)"> articles;···
}
public class Article implements Serializable {</span><span style="color: rgba(0, 0, 255, 1)">private</span> Integer id; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 商品id，主键</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String name; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 商品名称</span> <span style="color: rgba(0, 0, 255, 1)">private</span> Double price; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 商品价格</span> <span style="color: rgba(0, 0, 255, 1)">private</span> String remark; <span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 商品描述 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 商品和订单是多对多的关系，即一种商品可以包含在多个订单中</span> <span style="color: rgba(0, 0, 255, 1)">private</span> List<Order><span style="color: rgba(0, 0, 0, 1)"> orders;···
}
映射文件如下
UserMapper.xml
<mapper namespace="org.zang.mapper.UserMapper"><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">type</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.User"</span><span style="color: rgba(255, 0, 0, 1)"> id</span><span style="color: rgba(0, 0, 255, 1)">="userResultMap"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="username"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="username"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="loginname"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="loginname"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="password"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="password"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="phone"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="phone"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="address"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="address"</span><span style="color: rgba(0, 0, 255, 1)">/><br></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 一对多关联映射:collection </span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">collection </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="orders"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="ArrayList" </span><span style="color: rgba(255, 0, 0, 1)">column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> ofType</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.User"</span><span style="color: rgba(255, 0, 0, 1)"> select</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.mapper.OrderMapper.selectOrderByUserId" </span><span style="color: rgba(255, 0, 0, 1)">fetchType</span><span style="color: rgba(0, 0, 255, 1)">="lazy"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="code"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="code"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="total"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="total"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">collection</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">></span><select id="selectUserById" parameterType="int" resultMap="userResultMap">
SELECT * FROM tb_user WHERE id = #{id}
</select></mapper>
OrderMapper.xml
<mapper namespace="org.zang.mapper.OrderMapper"><span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">type</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.Order"</span><span style="color: rgba(255, 0, 0, 1)"> id</span><span style="color: rgba(0, 0, 255, 1)">="orderResultMap"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="oid"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="code"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="code"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="total"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="total"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 多对一关联映射:association </span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">association </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="user"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.User"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="username"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="username"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="loginname"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="loginname"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="password"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="password"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="phone"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="phone"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="address"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="address"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">association</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 多对多映射的关键:collection </span><span style="color: rgba(0, 128, 0, 1)">--></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">collection </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="articles"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="ArrayList" </span><span style="color: rgba(255, 0, 0, 1)">column</span><span style="color: rgba(0, 0, 255, 1)">="oid"</span><span style="color: rgba(255, 0, 0, 1)"> ofType</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.domain.Article"</span><span style="color: rgba(255, 0, 0, 1)"> select</span><span style="color: rgba(0, 0, 255, 1)">="org.zang.mapper.ArticleMapper.selectArticleByOrderId" </span><span style="color: rgba(255, 0, 0, 1)">fetchType</span><span style="color: rgba(0, 0, 255, 1)">="lazy"</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="price"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="price"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"><</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="remark"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="remark"</span><span style="color: rgba(0, 0, 255, 1)">/></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">collection</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 0, 255, 1)"></</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">></span> <span style="color: rgba(0, 128, 0, 1)"><!--</span><span style="color: rgba(0, 128, 0, 1)"> 注意，如果查询出来的列同名，例如tb_user表的id和tb_order表的id都是id，同名，需要使用别名区分 </span><span style="color: rgba(0, 128, 0, 1)">--></span><select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
SELECT u.*,o.id AS oid,CODE,total,user_id
FROM tb_user u,tb_order o
WHERE u.id = o.user_id
AND o.id = #{id}
</select><!-- 根据 userid 查询订单 -->
<select id="selectOrderByUserId" parameterType="int" resultType="org.zang.domain.Order">
SELECT * FROM tb_order WHERE user_id = #{id}
</select></mapper>
ArticleMapper.xml
<mapper namespace="org.zang.mapper.ArticleMapper"><select id="selectArticleByOrderId" parameterType="int" resultType="org.zang.domain.Article">
SELECT * FROM tb_article WHERE id IN (
SELECT article_id FROM tb_item WHERE order_id = #{id}
)
</select></mapper>
相关映射的含义上面已解释，关联映射最主要的是能够用好如下两个标签：
- * 对多关联映射：collection
- * 对一关联映射：association
此外注意：一对多使用的都是 lazy（懒加载）。