MyBatis日记（五）：一对一关系，一对多关系

一对一关系：

首先在数据库中新增地址表（t_address）用于存储人员的地址信息：

 CREATE TABLE `t_address` (

   `id` int(11) NOT NULL AUTO_INCREMENT,

   `country` varchar(100) DEFAULT NULL,

   `city` varchar(100) DEFAULT NULL,

   `district` varchar(100) DEFAULT NULL,

   PRIMARY KEY (`id`)

 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

然后在人员信息表（t_person）中增加addressId字段：

ALTER TABLE t_person ADD addressId int(11);

在com.Aiden.domain中新增地址信息的实体类（Address.java）：

 package com.Aiden.domain;

 public class Address {

     private Integer id;

     private String country;

     private String city;

     private String district;

     public Address() {

         super();

     }

     public Address(String country, String city, String district) {

         super();

         this.country = country;

         this.city = city;

         this.district = district;

     }

     public Address(Integer id, String country, String city, String district) {

         super();

         this.id = id;

         this.country = country;

         this.city = city;

         this.district = district;

     }

     public Integer getId() {

         return id;

     }

     public void setId(Integer id) {

         this.id = id;

     }

     public String getCountry() {

         return country;

     }

     public void setCountry(String country) {

         this.country = country;

     }

     public String getCity() {

         return city;

     }

     public void setCity(String city) {

         this.city = city;

     }

     public String getDistrict() {

         return district;

     }

     public void setDistrict(String district) {

         this.district = district;

     }

     @Override

     public String toString() {

         return "Address [id=" + id + ", country=" + country + ", city=" + city + ", district=" + district + "]";

     }

 }

在com.Aiden.dao中新增地址信息的接口（addressMapper.java）：

package com.Aiden.dao;

public interface addressMapper {}

在com.Aiden.dao中新增地址信息的映射文件（addressMapper.xml）：

 <?xml version="1.0" encoding="UTF-8"?>

 <!DOCTYPE mapper

 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <mapper namespace="com.Aiden.dao.addressMapper">

 </mapper>

在Person实体类中增加address属性并添加get、set方法：

 private Address address;

 public Address getAddress() {

     return address;

 }

 public void setAddress(Address address) {

     this.address = address;

 }

在com.Aiden.service包中创建新的Junit测试类（MyBatisDemo02.Java），并添加测试方法：

 package com.Aiden.service;

 import static org.junit.Assert.*;

 import java.util.List;

 import org.apache.ibatis.session.SqlSession;

 import org.apache.log4j.Logger;

 import org.junit.After;

 import org.junit.Before;

 import org.junit.Test;

 import com.Aiden.dao.personMapper;

 import com.Aiden.domain.Person;

 import com.Aiden.util.SqlSessionFactoryUtil;

 /**

  * 一对一、一对多关系映射

  * @author 郭祥跃

  *

  */

 public class MybatisDemo02 {

     private static Logger logger=Logger.getLogger(MybatisDemo02.class);

     private static SqlSession sqlSession=null;

     private static personMapper personMapper=null;

     @Before

     public void setUp() throws Exception {

         sqlSession=SqlSessionFactoryUtil.openSession();

         System.out.println(sqlSession);

         personMapper=sqlSession.getMapper(personMapper.class);

     }

     @After

     public void tearDown() throws Exception {

         sqlSession.close();

     }

     @Test

     public void testFindPersonWithAddressById() {

         logger.info("一对多关系映射");

         List<Person> person=personMapper.findPersonWithAddressById(3);

         System.out.println(person);

     }

 }

在personMapper.java文件中添加新的接口（findPersonWithAddressById）：

 /**

  * 根据ID查询人员及地址信息

  * @param id

  * @return

  */

 public List<Person> findPersonWithAddressById(Integer id);

在personMapper.xml文件中添加select查询：

 <select id="findPersonWithAddressById" parameterType="Integer" resultMap="resultPersonWithAddress">

    select * from t_person tp,t_address ta where tp.addressId=ta.id and tp.id=#{id}

 </select>

添加resultMap返回结果：此处resultMap标签有几种不同的实现，记之如下：

第一种：

 <resultMap type="Person" id="resultPersonWithAddress">

     <id property="id" column="id"/>

     <result property="name" column="name"/>

     <result property="age" column="age"/>

     <result property="address.id" column="id"/>

     <result property="address.country" column="country"/>

     <result property="address.city" column="city"/>

     <result property="address.district" column="district"/>

 </resultMap>

查询结果：

第二种：

 <resultMap type="Person" id="resultPersonWithAddress">

     <id property="id" column="id"/>

     <result property="name" column="name"/>

     <result property="age" column="age"/>

     <association property="address" resultMap="resultAddress"/>

 </resultMap>

 <resultMap type="Address" id="resultAddress">

     <id property="id" column="id"/>

     <result property="country" column="country"/>

     <result property="city" column="city"/>

     <result property="district" column="district"/>

 </resultMap>

查询结果：

第三种：

 <resultMap type="Person" id="resultPersonWithAddress">

     <id property="id" column="id" />

     <result property="name" column="name" />

     <result property="age" column="age" />

     <association property="address" javaType="Address">

         <id property="id" column="id" />

         <result property="country" column="country" />

         <result property="city" column="city" />

         <result property="district" column="district" />

     </association>

 </resultMap>

查询结果：

第四种(最常用)：

这种方法借助于Address的查询方法进行实现：

首先在addressMapper.java接口文件中，添加查询的接口：

 package com.Aiden.dao;

 import com.Aiden.domain.Address;

 public interface addressMapper {

     /**

      * 根据ID查询地址消息

      * @param id

      * @return

      */

     public Address findById(Integer id);

 }

然后在addressMapper.xml中添加select标签：

 <?xml version="1.0" encoding="UTF-8"?>

 <!DOCTYPE mapper

 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <mapper namespace="com.Aiden.dao.addressMapper">

     <select id="findAddressById" parameterType="Integer" resultType="Address">

         select * from t_address where id=#{id}

     </select>

 </mapper>

修改personMapper.xml中的resultMap为：

 <resultMap type="Person" id="resultPersonWithAddress">

         <id property="id" column="id" />

         <result property="name" column="name" />

         <result property="age" column="age" />

         <association property="address" column="addressId" select="com.Aiden.dao.addressMapper.findAddressById"/>

 </resultMap>

查询结果：

一对多关系：

简单的说一对多的关系可以用多个一对一关系实现。

首先在数据库中新增公司信息表（t_company）用于存储人员的地址信息：

 CREATE TABLE `t_company` (

   `Id` int(11) NOT NULL AUTO_INCREMENT,

   `companyName` varchar(100) DEFAULT NULL,

   PRIMARY KEY (`Id`)

 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

然后在人员信息表（t_person）中增加addressId字段：

ALTER TABLE t_person ADD companyId int(11);

在com.Aiden.domain中新增公司信息的实体类（Company.java）：

 package com.Aiden.domain;

 import java.util.List;

 public class Company {

     private Integer id;

     private String companyName;

     private List<Person> persons;

     public Company() {

         super();

     }

     public Company(String companyName) {

         super();

         this.companyName = companyName;

     }

     public Company(Integer id, String companyName) {

         super();

         this.id = id;

         this.companyName = companyName;

     }

     public Integer getId() {

         return id;

     }

     public void setId(Integer id) {

         this.id = id;

     }

     public String getCompanyName() {

         return companyName;

     }

     public void setCompanyName(String companyName) {

         this.companyName = companyName;

     }

     public List<Person> getPersons() {

         return persons;

     }

     public void setPersons(List<Person> persons) {

         this.persons = persons;

     }

     @Override

     public String toString() {

         return "Company [id=" + id + ", companyName=" + companyName + "]";

     }

 }

在com.Aiden.dao中新增公司信息的接口（companyMapper.java）：

 package com.Aiden.dao;

 import com.Aiden.domain.Company;

 public interface companyMapper {

     /**

      * 根据ID查询公司详情

      * @param id

      * @return

      */

     public Company findCompanyById(Integer id);

 }

在com.Aiden.dao中新增公司信息的映射文件（companyMapper.xml）：

 <?xml version="1.0" encoding="UTF-8"?>

 <!DOCTYPE mapper

 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <mapper namespace="com.Aiden.dao.companyMapper">

     <resultMap type="Company" id="resultCompany">

         <id property="id" column="id"/>

         <result property="companyName" column="companyName"/>

         <collection property="persons" column="id" select="com.Aiden.dao.personMapper.findPersonWithByCompanyId"/>

     </resultMap>

     <select id="findCompanyById" parameterType="Integer" resultMap="resultCompany">

         select * from t_company where id=#{id}

     </select>

 </mapper>

在person实体类中添加company属性及get、set方法：

 private Company company;

 public Company getCompany() {

     return company;

 }

 public void setCompany(Company company) {

     this.company = company;

 }

在personMapper.java中添加新的查询接口：

/**

 * 根据公司Id查询人员信息

 * @param id

 * @return

 */

public List<Person> findPersonWithByCompanyId(Integer companyId);

在personMapper.xml添加查询select及resultMap：

 <select id="findPersonWithByCompanyId" parameterType="Integer" resultMap="resultPersonWithCompany">

         select * from t_person where companyId=#{companyId}

 </select>

 <resultMap type="Person" id="resultPersonWithCompany">

         <id property="id" column="id" />

         <result property="name" column="name" />

         <result property="age" column="age" />

         <association property="address" column="addressId" select="com.Aiden.dao.addressMapper.findAddressById"/>

         <association property="company" column="companyId" select="com.Aiden.dao.companyMapper.findCompanyById"/>

 </resultMap>

在测试类MybatisDemo02.java中添加测试方法，运行：

 @Test

 public void testFindPersonWithByCompanyId() {

     logger.info("一对多关系映射——根据公司ID查询人员");

     List<Person> person=personMapper.findPersonWithByCompanyId(1);

     for (Person p : person) {

         System.out.println(p);

     }

 }

运行结果：