MyBatis--多对多关联查询（级联查询）

拼搏现实的明天。 2021-08-13 16:25 571阅读 0赞

#### 多对多关联查询 ####

**创建表及准备数据：**

create table book
    (
        bid int primary key auto_increment,
        bname varchar(20)                                     
    );
    
    create table category
    (
        cid int primary key auto_increment,
        cname varchar(20)
    );
    
    create table middle
    (
        m_bid int,
        m_cid int,
        constraint fk_bid foreign key(m_bid) references book(bid),
        constraint fk_cid foreign key(m_cid) references category(cid)
    );

![在这里插入图片描述][20191028232342231.png]

![在这里插入图片描述][20191028232416632.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

**创建工程及导入JAR包：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]  
**创建持久化类：**

package pers.zhang.po;
    
    import java.util.List;
    
    public class Book { 
        private Integer bid;
        private String bname;
        private List<Category> categories;//映射
    
        public Integer getBid() { 
            return bid;
        }
        public void setBid(Integer bid) { 
            this.bid = bid;
        }
        public String getBname() { 
            return bname;
        }
        public void setBname(String bname) { 
            this.bname = bname == null ? null : bname.trim();
        }
        public List<Category> getCategories() { 
            return categories;
        }
        public void setCategories(List<Category> categories) { 
            this.categories = categories;
        }
    	@Override
    	public String toString() { 
    		return "Book [bid=" + bid + ", bname=" + bname + ", categories=" + categories + "]";
    	}
        
    }

package pers.zhang.po;
    
    public class Category { 
    
    	private Integer cid;
    	private String cname;
    	public Integer getCid() { 
    		return cid;
    	}
    	public void setCid(Integer cid) { 
    		this.cid = cid;
    	}
    	public String getCname() { 
    		return cname;
    	}
    	public void setCname(String cname) { 
    		this.cname = cname;
    	}
    	@Override
    	public String toString() { 
    		return "Category [cid=" + cid + ", cname=" + cname + "]";
    	}
    	
    	
    }

**编写配置文件：BookMapper.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="pers.zhang.mapper.BookMapper" >
        
        <resultMap type="pers.zhang.po.Book" id="bookMap">
            <id property="bid" column="bid" />
            <result property="bname" column="bname" />
            
            <collection property="categories" ofType="pers.zhang.po.Category">
                <id property="cid" column="cid" />
                <result property="cname" column="cname" />    
            </collection>
        </resultMap>
    
        <select id="queryAll" resultMap="bookMap">
    		select * 
    		from book b, middle m, category c
    		where b.bid=m.m_bid and m.m_cid=c.cid
        </select>
    </mapper>

**创建BookMapper接口：**

package pers.zhang.mapper;
    
    
    import java.util.List;
    
    import pers.zhang.po.Book;
    
    public interface BookMapper { 
    
    	public List<Book> queryAll();
    }

**测试：**

package pers.zhang.test;
    
    import java.io.InputStream;
    import java.util.List;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.Test;
    
    import pers.zhang.mapper.BookMapper;
    import pers.zhang.po.Book;
    
    public class MyTest3 { 
    
    	@Test
    	public void test1() throws Exception{ 
    		//加载核心配置文件
    		String resource = "sqlMapConfig.xml";
    		InputStream in = Resources.getResourceAsStream(resource);
    		//创建SqlSessionFactory
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    		//获得sqlSession
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		//获得Mapper
    		BookMapper mapper = sqlSession.getMapper(BookMapper.class);
    		Integer id = 3;
    		List<Book> bookList = mapper.queryAll();
    		for (Book book : bookList) { 
                System.out.println(book);
            }
    	}
    }

控制台打印：

DEBUG [main] - ==>  Preparing: select * from book b, middle m, category c where b.bid=m.m_bid and m.m_cid=c.cid 
    DEBUG [main] - ==> Parameters: 
    DEBUG [main] - <==      Total: 5
    Book [bid=1, bname=SQL技术, categories=[Category [cid=3, cname=mysql]]]
    Book [bid=2, bname=SSM+MySQL详解, categories=[Category [cid=1, cname=java], Category [cid=3, cname=mysql]]]
    Book [bid=3, bname=C++和java对比, categories=[Category [cid=2, cname=c++], Category [cid=1, cname=java]]]

[20191028232342231.png]: /images/20210813/d722ca012f804d66ae5cc4f0a9829172.png
[20191028232416632.png]: /images/20210813/46ce690a56ec43e6bc5882d5b26fc6f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/c0b0bbea8bef424faf7fc1b22e2d7cce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/890adb46ab98480c8c10f14d82680e02.png