Mybatis 一对一关联查询与 resultMap

水深无声 2021-09-22 10:24 507阅读 0赞

**业务背景：**

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

**数据库结构：**

** ** 品牌表： 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">
    
    <mapper namespace="com.cheese.mapping.brandMapper">
    
        
        <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 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>
        
        
         <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 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>
                   
            <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());
        }   
    }