Mybatis 一对多关联查询（单方关联配置）

悠悠 2021-09-22 10:26 489阅读 0赞

**业务背景：**

分类与商品之间为一对多关系，一个分类下可以有多个商品。

**数据库结构：**

分类表：t\_category

商品表：t\_product

CREATE TABLE t_category (
      cid int(11) NOT NULL AUTO_INCREMENT,
      catename varchar(48) DEFAULT NULL,
      PRIMARY KEY (cid)
    )  DEFAULT CHARSET=utf8 COMMENT='商品分类';
    
    INSERT INTO t_category VALUES ('1', '手机');
    INSERT INTO t_category VALUES ('2', '平板');
    INSERT INTO t_category VALUES ('3', '穿戴设备');
    
    CREATE TABLE t_product (
      pid int(11) NOT NULL AUTO_INCREMENT,
      pname varchar(200) DEFAULT NULL,
      type varchar(80) DEFAULT NULL COMMENT '产品型号',
      cateid int(11) DEFAULT NULL COMMENT '分类编号',
      brandid int(11) DEFAULT NULL COMMENT '品牌编号',
      storage int(11) DEFAULT NULL COMMENT '库存数量',
      price float DEFAULT NULL,
      PRIMARY KEY (pid)
    )  DEFAULT CHARSET=utf8 COMMENT='商品信息';
    
    INSERT INTO t_product VALUES ('1', 'iPhone X', 'X', '1', '1', '9', '10081');
    INSERT INTO t_product VALUES ('2', 'iPhone 8', '8', '1', '1', '16', '7399');
    INSERT INTO t_product VALUES ('3', 'Mate10 Pro', 'BLA-AL00', '1', '2', '120', '5399');
    INSERT INTO t_product VALUES ('4', 'P10 Plus', '	VKY-AL00', '1', '2', '150', '4488');
    INSERT INTO t_product VALUES ('5', 'iPad', 'PGW2CH/A', '2', '1', '130', '3288');
    INSERT INTO t_product VALUES ('6', 'iPad mini', 'MK9Q2CH/A', '2', '1', '139', '3188');
    INSERT INTO t_product VALUES ('7', 'M3 青春版 平板电脑', 'BAH-W09', '2', '2', '59', '2199');

**实体类：**

**   ** Category.java

package com.cheese.pojo;
    
    import java.util.List;
    
    public class Category {
    	private int cid;
    	private String cateName;
    	
    	private List<Product> productList;//一个分类关联多个产品，用集合存储“多方”对象
    	
    	public int getCid() {
    		return cid;
    	}
    	public void setCid(int cid) {
    		this.cid = cid;
    	}
    	public String getCateName() {
    		return cateName;
    	}
    	public void setCateName(String cateName) {
    		this.cateName = cateName;
    	}
    	public List<Product> getProductList() {
    		return productList;
    	}
    	public void setProductList(List<Product> productList) {
    		this.productList = productList;
    	}	
    }

Product.java

package com.cheese.pojo;
    
    public class Product {
    	private int pid;
    	private String pname;
    	private String type;//产品型号
    	private int storage;//库存数
    	private float price;
    	
    	public int getPid() {
    		return pid;
    	}
    	public void setPid(int pid) {
    		this.pid = pid;
    	}
    	public String getPname() {
    		return pname;
    	}
    	public void setPname(String pname) {
    		this.pname = pname;
    	}
    	public String getType() {
    		return type;
    	}
    	public void setType(String type) {
    		this.type = type;
    	}
    	public int getStorage() {
    		return storage;
    	}
    	public void setStorage(int storage) {
    		this.storage = storage;
    	}
    	public float getPrice() {
    		return price;
    	}
    	public void setPrice(float price) {
    		this.price = price;
    	}
    }

**Mapper文件：categoryMapper.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.categoryMapper">
     
        
        
        <select id="getCategory" parameterType="int" resultMap="CategoryResultMap">
    		select c.cid,c.catename,p.pid,p.pname,p.price from t_category c,t_product p
    		where c.cid=p.cateid and c.cid=#{cid}       
        </select>
        
        <resultMap type="com.cheese.pojo.Category" id="CategoryResultMap">
            <id property="cid" column="cid"/>
            <result property="cateName" column="catename"/>        
    
            
            <collection property="productList" ofType="com.cheese.pojo.Product">
                <id property="pid" column="pid"/>
                <result property="pname" column="pname"/>
                <result property="type" column="type"/>
                <result property="storage" column="storage"/>
            </collection>
        </resultMap>
        
        
         <select id="getCategory2" parameterType="int" resultMap="CategoryResultMap2">
    		select c.cid,c.catename from t_category c where c.cid=#{cid} 
         </select>
         <select id="getProductList" parameterType="int" resultType="com.cheese.pojo.Product">
            select p.pid,p.pname,p.price from t_product p where cateid=#{cid}
         </select> 
             
         <resultMap type="com.cheese.pojo.Category" id="CategoryResultMap2">
            <id property="cid" column="cid"/>
            <result property="cateName" column="catename"/> 
            
            <collection property="productList" ofType="com.cheese.pojo.Product" column="cid" select="getProductList"></collection>
         </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/categoryMapper.xml"/>
    
        </mappers>        
    </configuration>

**测试程序：**

package com.cheese.test;
    
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import com.cheese.pojo.Category;
    
    public class TestQuery2 {
        
        @Test
        public void getCategory(){
            SqlSession sqlSession = SessionUtil.getSession();
        	/**
        	 * com.cheese.mapping.brandMapper.getBrand
    		 * 定位到brandMapper.xml 中 id 为 getBrand 的语句
        	 */
            String getCategoryStr = "com.cheese.mapping.categoryMapper.getCategory";//映射sql的标识字符串
            //执行查询，返回Brand对象
            Category category = sqlSession.selectOne(getCategoryStr,1);//查询t_category表中id为1的记录
            sqlSession.close();
            System.out.println(category.getCateName() + " : "
              + category.getProductList().get(0).getPname());//打印Category对象的productList集合的第一个元素
        }  
        
        @Test
        public void getCategory2(){
        	SqlSession sqlSession = SessionUtil.getSession();
        	/**
        	 * com.cheese.mapping.brandMapper.getBrand2
        	 * 定位到brandMapper.xml 中 id 为 getBrand2 的语句
        	 */
        	String getCategoryStr = "com.cheese.mapping.categoryMapper.getCategory2";//映射sql的标识字符串
        	//执行查询，返回Brand对象
        	Category category = sqlSession.selectOne(getCategoryStr,1);//查询t_category表中id为1的记录
        	sqlSession.close();
        	System.out.println(category.getCateName() + " -> "
        			+ category.getProductList().get(0).getPname());//打印Category对象的productList集合的第一个元素
        }    
    
    }

**测试结果：**

![SouthEast][]

![SouthEast 1][]

[SouthEast]: /images/20210920/5be0c32a917d4b82bfe1589a23cfb0b2.png
[SouthEast 1]: /images/20210920/e7550a679bdf4d1ea90b092315eefdc3.png