Mybatis 一对一 关联查询 与 resultMap

业务背景：

  公司跟品牌是一对一关系，根据品牌编号查询品牌信息，附带查询品牌所属的公司（一对一关联查询）

数据库结构：

  品牌表： t_brand

  公司表：t_corporation

CREATE TABLE t_brand (
  brandid int(11) NOT NULL AUTO_INCREMENT,
  brandname varchar(30) DEFAULT NULL,
  corpid int(11) DEFAULT NULL,
  PRIMARY KEY (brandid)
)  DEFAULT CHARSET=utf8 COMMENT='品牌';

INSERT INTO t_brand VALUES ('1', 'Apple 苹果', '1');
INSERT INTO t_brand VALUES ('2', '华为', '2');
INSERT INTO t_brand VALUES ('3', 'OPPO', '3');
INSERT INTO t_brand VALUES ('4', '小米', '4');


CREATE TABLE t_corporation (
  corpid int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  PRIMARY KEY (corpid)
)  DEFAULT CHARSET=utf8;


INSERT INTO t_corporation VALUES ('1', 'Apple Inc. ');
INSERT INTO t_corporation VALUES ('2', '华为技术有限公司');
INSERT INTO t_corporation VALUES ('3', '欧珀移动通信有限公司');
INSERT INTO t_corporation VALUES ('4', '小米科技有限责任公司');

实体类：

package com.cheese.pojo;

public class Corporation {
	private int corpid;
	private String name;	
	
	public int getCorpid() {
		return corpid;
	}
	public void setCorpid(int corpid) {
		this.corpid = corpid;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

package com.cheese.pojo;

public class Brand {
	private int brandid;
	private String brandname;
	
	private Corporation corp;//Corporation对象作为品牌的一个属性
	
	public int getBrandid() {
		return brandid;
	}
	public void setBrandid(int brandid) {
		this.brandid = brandid;
	}
	public String getBrandname() {
		return brandname;
	}
	public void setBrandname(String brandname) {
		this.brandname = brandname;
	}
	public Corporation getCorp() {
		return corp;
	}
	public void setCorp(Corporation corp) {
		this.corp = corp;
	}		
}

Mapper文件：brandMapper.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">
<!-- namespace的值设置成包名+sql映射文件名，保证namespace取值唯一 -->
<mapper namespace="com.cheese.mapping.brandMapper">

    <!-- 
            方式一：嵌套结果：使用嵌套结果来映射结果集
        select b.brandid,b.brandname,b.corpid,c.`name` from t_brand b,t_corporation c 
        where b.corpid=c.corpid and b.brandid=1;
    -->
    <select id="getBrand" parameterType="int" resultMap="BrandResultMap">
        select b.brandid,b.brandname,b.corpid,c.`name` from t_brand b,t_corporation c 
        where b.corpid=c.corpid and b.brandid=#{brandid}
    </select>
    <!-- resultMap映射实体类和字段之间的一一对应关系 -->
    <resultMap type="com.cheese.pojo.Brand" id="BrandResultMap">
        <id property="brandid" column="brandid"/>
        <result property="brandname" column="brandname"/>
        
        <association property="corp" javaType="com.cheese.pojo.Corporation">  <!--配置关联关系-->
            <id property="corpid" column="corpid"/>
            <result property="name" column="name"/>
        </association>
    </resultMap>
    
    <!-- 
          方式二：嵌套查询：通过单独的一个SQL语句来返回关联的对象
        select b.brandid,b.brandname,b.corpid from t_brand b where b.brandid=1;
        select c.corpid,c.`name` from t_corporation c where c.corpid=1;
    -->
     <select id="getBrand2" parameterType="int" resultMap="BrandResultMap2">
        select b.brandid,b.brandname,b.corpid from t_brand b where b.brandid=#{brandid}
     </select>
     <select id="getCorp" parameterType="int" resultType="com.cheese.pojo.Corporation">
        select c.corpid,c.`name` from t_corporation c where c.corpid=#{corpid};
     </select>       
     <!-- resultMap映射实体类和字段之间的一一对应关系 -->
     <resultMap type="com.cheese.pojo.Brand" id="BrandResultMap2">
        <id property="brandid" column="brandid"/>
        <result property="brandname" column="brandname"/>
        
        <association property="corp" column="corpid" select="getCorp"/>  <!--配置关联关系-->
     </resultMap>   


</mapper>

mybatis配置文件：config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/base6?useUnicode=true&characterEncoding=UTF8"/>
                <property name="username" value="bts" />
                <property name="password" value="b111" />
            </dataSource>
        </environment>              
    </environments>
        
    <mappers>
        <!-- 注册brandMapper.xml文件  -->       
        <mapper resource="com/cheese/mapping/brandMapper.xml"/>

    </mappers>        
</configuration>

测试程序：

package com.cheese.test;


import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import com.cheese.pojo.Brand;

public class TestQuery {
    
    @Test
    public void getBrand(){
        SqlSession sqlSession = SessionUtil.getSession();
    	/**
    	 * com.cheese.mapping.brandMapper.getBrand
		 * 定位到brandMapper.xml 中 id 为 getBrand 的语句
    	 */
        String getBrandStr = "com.cheese.mapping.brandMapper.getBrand";//
        //执行查询，返回Brand对象
        Brand brand = sqlSession.selectOne(getBrandStr,1);//查询t_brand表中brandid为1的记录
        sqlSession.close();
        System.out.println("品牌：" + brand.getBrandname() + " --> " + brand.getCorp().getName());
    }   
    
    @Test
    public void getBrand2(){
    	SqlSession sqlSession = SessionUtil.getSession();
    	/**
    	 * com.cheese.mapping.brandMapper.getBrand2
		 * 定位到brandMapper.xml 中 id 为 getBrand2 的语句
    	 */
    	String getBrandStr = "com.cheese.mapping.brandMapper.getBrand2";//
    	//执行查询，返回Brand对象
    	Brand brand = sqlSession.selectOne(getBrandStr,1);//查询t_brand表中brandid为1的记录
    	sqlSession.close();
    	System.out.println("品牌：" + brand.getBrandname() + " ==> " + brand.getCorp().getName());
    }   
}