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

多对多关联查询

创建表及准备数据：

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)
);

创建工程及导入JAR包：

创建持久化类：

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]]]