多对多关联查询
创建表及准备数据:
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]]]