Mybatis04_多对多查询

落日映苍穹つ 2023-03-12 09:27 15阅读 0赞

**Mybatis04\_多对多查询**

customer表结构：  
![在这里插入图片描述][2020051508563520.png]  
customer表内容：  
![在这里插入图片描述][20200515085814922.png]  
goods表结构：  
![在这里插入图片描述][2020051509060789.png]  
goods表内容：  
![在这里插入图片描述][20200515090637663.png]  
中间表customer\_goods表结构：  
![在这里插入图片描述][20200515090812299.png]  
中间表customer\_goods表内容：  
![在这里插入图片描述][20200515090849840.png]  
Customer实体：

package com.blu.entity;
    
    import java.util.List;
    import lombok.Data;
    
    @Data
    public class Customer { 
    	private long id;
    	private String name;
    	private List<Goods> goods;
    }

Goods实体：

package com.blu.entity;
    
    import java.util.List;
    import lombok.Data;
    
    @Data
    public class Goods { 
    	private long id;
    	private String name;
    	private List<Customer> customers;
    }

##### 查询Customer带Goods #####

CustomerRepository接口：

package com.blu.repository;
    
    import com.blu.entity.Customer;
    
    public interface CustomerRepository { 
    	public Customer findById(long id);
    }

CustomerRepository.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.blu.repository.CustomerRepository">
    	<resultMap id="customerMap" type="com.blu.entity.Customer">
    		<id column="cid" property="id"></id>
    		<result column="cname" property="name"></result>
    		<collection property="goods" ofType="com.blu.entity.Goods">
    			<id column="gid" property="id" />
    			<result column="gname" property="name" />
    		</collection>
    	</resultMap>
    	<select id="findById" parameterType="long" resultMap="customerMap">
    		select c.id cid,c.name cname,g.id gid,g.name gname
    		from customer c,goods g,customer_goods cg 
    		where c.id = #{id} and cg.cid = c.id and cg.gid = g.id
    	</select>
    </mapper>

注册mapper

测试方法：

public class Test { 
    	public static void main(String[] args) { 
    		InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
    		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		CustomerRepository customerRepository = sqlSession.getMapper(CustomerRepository.class);
    		Customer customer = customerRepository.findById(1L);
    		System.out.println(customer);
    		sqlSession.close();	
    	}
    }

结果：

Customer(id=1, name=张三, goods=[Goods(id=1, name=电视, customers=null), Goods(id=3, name=洗衣机, customers=null)])

##### 查询Goods带Customer #####

GoodsRepository 接口：

package com.blu.repository;
    
    import com.blu.entity.Goods;
    
    public interface GoodsRepository { 
    	public Goods findById(long id);
    }

GoodsRepository.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.blu.repository.GoodsRepository">
    	<resultMap id="goodsMap" type="com.blu.entity.Goods">
    		<id column="gid" property="id"></id>
    		<result column="gname" property="name"></result>
    		<collection property="customers" ofType="com.blu.entity.Customer">
    			<id column="cid" property="id" />
    			<result column="cname" property="name" />
    		</collection>
    	</resultMap>
    	
    	<select id="findById" parameterType="long" resultMap="goodsMap">
    		select c.id cid,c.name cname,g.id gid,g.name gname
    		from customer c,goods g,customer_goods cg
    		where g.id = #{id} and cg.cid = c.id and cg.gid = g.id
    	</select>
    </mapper>

注册mapper

测试方法：

public class Test { 
    	public static void main(String[] args) { 
    		InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
    		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		GoodsRepository goodsRepository = sqlSession.getMapper(GoodsRepository.class);
    		Goods goods = goodsRepository.findById(3L);
    		System.out.println(goods);
    		sqlSession.close();
    	}
    }

结果：

Goods(id=3, name=洗衣机, customers=[Customer(id=1, name=张三, goods=null), Customer(id=3, name=李华, goods=null)])

[2020051508563520.png]: /images/20230312/bef5918cf5e84724bf6ac7ee70a8713a.png
[20200515085814922.png]: /images/20230312/5bec8fe1586b4585bb5e7241d0df00cf.png
[2020051509060789.png]: /images/20230312/180cb80518ae48fdbc72fb209cf8034b.png
[20200515090637663.png]: /images/20230312/57a93655e3dc481695dcbabe033c8a1e.png
[20200515090812299.png]: /images/20230312/6615c8c383e5486fb1f75359e766545e.png
[20200515090849840.png]: /images/20230312/09a64ee6da4b465f9ef92681b006eb53.png