MyBatis日记（五）：一对一关系，一对多关系
一对一关系：
首先在数据库中新增地址表（t_address）用于存储人员的地址信息：
1 CREATE TABLE `t_address` (
2 `id` int(11) NOT NULL AUTO_INCREMENT,
3 `country` varchar(100) DEFAULT NULL,
4 `city` varchar(100) DEFAULT NULL,
5 `district` varchar(100) DEFAULT NULL,
6 PRIMARY KEY (`id`)
7 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
然后在人员信息表（t_person）中增加addressId字段：
1 ALTER TABLE t_person ADD addressId int(11);
在com.Aiden.domain中新增地址信息的实体类（Address.java）：
1 package com.Aiden.domain;
2
3 public class Address {
4
5 private Integer id;
6 private String country;
7 private String city;
8 private String district;
9 public Address() {
10 super();
11 }
12 public Address(String country, String city, String district) {
13 super();
14 this.country = country;
15 this.city = city;
16 this.district = district;
17 }
18 public Address(Integer id, String country, String city, String district) {
19 super();
20 this.id = id;
21 this.country = country;
22 this.city = city;
23 this.district = district;
24 }
25 public Integer getId() {
26 return id;
27 }
28 public void setId(Integer id) {
29 this.id = id;
30 }
31 public String getCountry() {
32 return country;
33 }
34 public void setCountry(String country) {
35 this.country = country;
36 }
37 public String getCity() {
38 return city;
39 }
40 public void setCity(String city) {
41 this.city = city;
42 }
43 public String getDistrict() {
44 return district;
45 }
46 public void setDistrict(String district) {
47 this.district = district;
48 }
49 @Override
50 public String toString() {
51 return "Address [id=" + id + ", country=" + country + ", city=" + city + ", district=" + district + "]";
52 }
53 }
在com.Aiden.dao中新增地址信息的接口（addressMapper.java）：
package com.Aiden.dao;
public interface addressMapper {}
在com.Aiden.dao中新增地址信息的映射文件（addressMapper.xml）：
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="com.Aiden.dao.addressMapper">
6 </mapper>
在Person实体类中增加address属性并添加get、set方法：
1 private Address address;
2
3 public Address getAddress() {
4 return address;
5 }
6 public void setAddress(Address address) {
7 this.address = address;
8 }
在com.Aiden.service包中创建新的Junit测试类（MyBatisDemo02.Java），并添加测试方法：
1 package com.Aiden.service;
2
3 import static org.junit.Assert.*;
4
5 import java.util.List;
6
7 import org.apache.ibatis.session.SqlSession;
8 import org.apache.log4j.Logger;
9 import org.junit.After;
10 import org.junit.Before;
11 import org.junit.Test;
12
13 import com.Aiden.dao.personMapper;
14 import com.Aiden.domain.Person;
15 import com.Aiden.util.SqlSessionFactoryUtil;
16 /**
17 * 一对一、一对多关系映射
18 * @author 郭祥跃
19 *
20 */
21 public class MybatisDemo02 {
22 private static Logger logger=Logger.getLogger(MybatisDemo02.class);
23 private static SqlSession sqlSession=null;
24 private static personMapper personMapper=null;
25
26 @Before
27 public void setUp() throws Exception {
28 sqlSession=SqlSessionFactoryUtil.openSession();
29 System.out.println(sqlSession);
30 personMapper=sqlSession.getMapper(personMapper.class);
31 }
32
33 @After
34 public void tearDown() throws Exception {
35 sqlSession.close();
36 }
37
38 @Test
39 public void testFindPersonWithAddressById() {
40 logger.info("一对多关系映射");
41 List<Person> person=personMapper.findPersonWithAddressById(3);
42 System.out.println(person);
43 }
44
45 }
在personMapper.java文件中添加新的接口（findPersonWithAddressById）：
1 /**
2 * 根据ID查询人员及地址信息
3 * @param id
4 * @return
5 */
6 public List<Person> findPersonWithAddressById(Integer id);
在personMapper.xml文件中添加select查询：
1 <select id="findPersonWithAddressById" parameterType="Integer" resultMap="resultPersonWithAddress">
2 select * from t_person tp,t_address ta where tp.addressId=ta.id and tp.id=#{id}
3 </select>
添加resultMap返回结果：此处resultMap标签有几种不同的实现，记之如下：
第一种：
1 <resultMap type="Person" id="resultPersonWithAddress">
2 <id property="id" column="id"/>
3 <result property="name" column="name"/>
4 <result property="age" column="age"/>
5
6 <result property="address.id" column="id"/>
7 <result property="address.country" column="country"/>
8 <result property="address.city" column="city"/>
9 <result property="address.district" column="district"/>
10 </resultMap>
查询结果：
第二种：
1 <resultMap type="Person" id="resultPersonWithAddress">
2 <id property="id" column="id"/>
3 <result property="name" column="name"/>
4 <result property="age" column="age"/>
5
6 <association property="address" resultMap="resultAddress"/>
7 </resultMap>
8 <resultMap type="Address" id="resultAddress">
9 <id property="id" column="id"/>
10 <result property="country" column="country"/>
11 <result property="city" column="city"/>
12 <result property="district" column="district"/>
13 </resultMap>
查询结果：
第三种：
1 <resultMap type="Person" id="resultPersonWithAddress">
2 <id property="id" column="id" />
3 <result property="name" column="name" />
4 <result property="age" column="age" />
5
6 <association property="address" javaType="Address">
7 <id property="id" column="id" />
8 <result property="country" column="country" />
9 <result property="city" column="city" />
10 <result property="district" column="district" />
11 </association>
12 </resultMap>
查询结果：
第四种(最常用)：
这种方法借助于Address的查询方法进行实现：
首先在addressMapper.java接口文件中，添加查询的接口：
1 package com.Aiden.dao;
2
3 import com.Aiden.domain.Address;
4
5 public interface addressMapper {
6 /**
7 * 根据ID查询地址消息
8 * @param id
9 * @return
10 */
11 public Address findById(Integer id);
12 }
然后在addressMapper.xml中添加select标签：
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="com.Aiden.dao.addressMapper">
6
7 <select id="findAddressById" parameterType="Integer" resultType="Address">
8 select * from t_address where id=#{id}
9 </select>
10
11 </mapper>
修改personMapper.xml中的resultMap为：
1 <resultMap type="Person" id="resultPersonWithAddress">
2 <id property="id" column="id" />
3 <result property="name" column="name" />
4 <result property="age" column="age" />
5 <association property="address" column="addressId" select="com.Aiden.dao.addressMapper.findAddressById"/>
6 </resultMap>
查询结果：
一对多关系：
简单的说一对多的关系可以用多个一对一关系实现。
首先在数据库中新增公司信息表（t_company）用于存储人员的地址信息：
1 CREATE TABLE `t_company` (
2 `Id` int(11) NOT NULL AUTO_INCREMENT,
3 `companyName` varchar(100) DEFAULT NULL,
4 PRIMARY KEY (`Id`)
5 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
然后在人员信息表（t_person）中增加addressId字段：
1 ALTER TABLE t_person ADD companyId int(11);
在com.Aiden.domain中新增公司信息的实体类（Company.java）：
1 package com.Aiden.domain;
2
3 import java.util.List;
4
5 public class Company {
6 private Integer id;
7 private String companyName;
8 private List<Person> persons;
9 public Company() {
10 super();
11 }
12 public Company(String companyName) {
13 super();
14 this.companyName = companyName;
15 }
16 public Company(Integer id, String companyName) {
17 super();
18 this.id = id;
19 this.companyName = companyName;
20 }
21 public Integer getId() {
22 return id;
23 }
24 public void setId(Integer id) {
25 this.id = id;
26 }
27 public String getCompanyName() {
28 return companyName;
29 }
30 public void setCompanyName(String companyName) {
31 this.companyName = companyName;
32 }
33 public List<Person> getPersons() {
34 return persons;
35 }
36 public void setPersons(List<Person> persons) {
37 this.persons = persons;
38 }
39 @Override
40 public String toString() {
41 return "Company [id=" + id + ", companyName=" + companyName + "]";
42 }
43 }
在com.Aiden.dao中新增公司信息的接口（companyMapper.java）：
1 package com.Aiden.dao;
2
3 import com.Aiden.domain.Company;
4
5 public interface companyMapper {
6 /**
7 * 根据ID查询公司详情
8 * @param id
9 * @return
10 */
11 public Company findCompanyById(Integer id);
12
13 }
在com.Aiden.dao中新增公司信息的映射文件（companyMapper.xml）：
1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="com.Aiden.dao.companyMapper">
6
7 <resultMap type="Company" id="resultCompany">
8 <id property="id" column="id"/>
9 <result property="companyName" column="companyName"/>
10 <collection property="persons" column="id" select="com.Aiden.dao.personMapper.findPersonWithByCompanyId"/>
11 </resultMap>
12
13 <select id="findCompanyById" parameterType="Integer" resultMap="resultCompany">
14 select * from t_company where id=#{id}
15 </select>
16
17 </mapper>
在person实体类中添加company属性及get、set方法：
1 private Company company;
2
3 public Company getCompany() {
4 return company;
5 }
6 public void setCompany(Company company) {
7 this.company = company;
8 }
在personMapper.java中添加新的查询接口：
/**
* 根据公司Id查询人员信息
* @param id
* @return
*/
public List<Person> findPersonWithByCompanyId(Integer companyId);
在personMapper.xml添加查询select及resultMap：
1 <select id="findPersonWithByCompanyId" parameterType="Integer" resultMap="resultPersonWithCompany">
2 select * from t_person where companyId=#{companyId}
3 </select>
4
5
6 <resultMap type="Person" id="resultPersonWithCompany">
7 <id property="id" column="id" />
8 <result property="name" column="name" />
9 <result property="age" column="age" />
10 <association property="address" column="addressId" select="com.Aiden.dao.addressMapper.findAddressById"/>
11 <association property="company" column="companyId" select="com.Aiden.dao.companyMapper.findCompanyById"/>
12 </resultMap>
在测试类MybatisDemo02.java中添加测试方法，运行：
1 @Test
2 public void testFindPersonWithByCompanyId() {
3 logger.info("一对多关系映射——根据公司ID查询人员");
4 List<Person> person=personMapper.findPersonWithByCompanyId(1);
5 for (Person p : person) {
6 System.out.println(p);
7 }
8 }
运行结果：
