一、关联查询
当查询的内容涉及到具有关联关系的多个表时,就需要使用关联查询。根据表与表间的关联关系的不同。关联查询分为四种:
- 一对一关联查询;
- 一对多关联查询;
- 多对一关联查询;
- 多对多关联查询;
二、一对多关联查询
1、通过多表连接查询方式实现
定义实体类及DB表
一对一关联查询,其解决方案与多对一解决方案是相同的。
一对多关联查询:在查询一方对象的时候,同时将其所关联的多方对象也都查询出来。
Demo中使用国家(country)和部长(minister)进行示例。
1 public class Minister {
2 private Integer mid;
3 private String mname;
4
5 public Integer getMid() {
6 return mid;
7 }
8
9 public void setMid(Integer mid) {
10 this.mid = mid;
11 }
12
13 public String getMname() {
14 return mname;
15 }
16
17 public void setMname(String mname) {
18 this.mname = mname;
19 }
20
21 @Override
22 public String toString() {
23 return "Minister [mid=" + mid + ", mname=" + mname + "]";
24 }
25
26 }
Minister
1 import java.util.Set;
2
3 public class Country {
4 private Integer cid;
5 private String cname;
6 // 关联属性
7 private Set<Minister> ministers;// 一般用set
8
9 public Integer getCid() {
10 return cid;
11 }
12
13 public void setCid(Integer cid) {
14 this.cid = cid;
15 }
16
17 public String getCname() {
18 return cname;
19 }
20
21 public void setCname(String cname) {
22 this.cname = cname;
23 }
24
25 public Set<Minister> getMinisters() {
26 return ministers;
27 }
28
29 public void setMinisters(Set<Minister> ministers) {
30 this.ministers = ministers;
31 }
32
33 @Override
34 public String toString() {
35 return "Country [cid=" + cid + ", cname=" + cname + ", ministers=" + ministers + "]";
36 }
37
38 }
Country
这里关联属性,一般一对多,用Set。(Set不能重复,list和array)。
数据库创建contry表和minister表
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.BasicConfigurator;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.jmu.bean.Country;
import com.jmu.dao.ICountryDao;
import com.jmu.utils.MybatisUtils;
public class MyTest {
private ICountryDao dao;
private SqlSession sqlSession;
@Before
public void Before() {
sqlSession = MybatisUtils.getSqlSession();
dao = sqlSession.getMapper(ICountryDao.class);
BasicConfigurator.configure();
}
@After
public void after(){
if (sqlSession!=null) {
sqlSession.commit();
}
}
@Test
public void test01() {
Country country = dao.selectCountryById(2);
System.out.println(country);
}
}
com.jmu.test.MyTest
1 public interface ICountryDao {
2 Country selectCountryById(int cid);
3 }
com.jmu.dao.ICountryDao
对应实体类,Mapper.xml中
1 <mapper namespace="com.jmu.dao.ICountryDao">
2 <resultMap type="Country" id="countryMapper">
3 <id column="cid" property="cid" />
4 <result column="cname" property="cname" />
5 <collection property="ministers" ofType="Minister"><!-- ofType="Minister",要封装的类,也是集合的泛型 -->
6 <id column="mid" property="mid" />
7 <result column="mname" property="mname" />
8 </collection>
9 </resultMap>
10 <select id="selectCountryById" resultMap="countryMapper">
11 select cid,cname,mid ,mname
12 from country,minister
13 where countryId=cid and cid=#{xxx}
14 </select>
15 </mapper>
/mybatis8-one2many/src/com/jmu/dao/mapper.xml
输出:
0 [main] DEBUG com.jmu.dao.ICountryDao.selectCountryById - ==> Preparing: select cid,cname,mid ,mname from country,minister where countryId=cid and cid=?
57 [main] DEBUG com.jmu.dao.ICountryDao.selectCountryById - ==> Parameters: 2(Integer)
96 [main] TRACE com.jmu.dao.ICountryDao.selectCountryById - <== Columns: cid, cname, mid, mname
96 [main] TRACE com.jmu.dao.ICountryDao.selectCountryById - <== Row: 2, England, 4, ddd
99 [main] TRACE com.jmu.dao.ICountryDao.selectCountryById - <== Row: 2, England, 5, eee
99 [main] DEBUG com.jmu.dao.ICountryDao.selectCountryById - <== Total: 2
Country [cid=2, cname=England, ministers=[Minister [mid=4, mname=ddd], Minister [mid=5, mname=eee]]]
output
2、通过多表单独查询方式实现
1 <mapper namespace="com.jmu.dao.ICountryDao">
2 <select id="selectMinisterByCountry" resultType="Minister">
3 select mid,mname from minister where countryId=#{ooo}
4 </select>
5 <resultMap type="Country" id="countryMapper">
6 <id column="cid" property="cid" />
7 <result column="cname" property="cname" />
8 <collection property="ministers" ofType="Minister"
9 select="selectMinisterByCountry" column="cid" /><!-- ofType="Minister",要封装的类,也是集合的泛型 -->
10 </resultMap>
11 <select id="selectCountryById" resultMap="countryMapper">
12 select cid,cname from
13 country where cid=#{xxx}
14 </select>
15 </mapper>
/mybatis8-one2many2-2/src/com/jmu/dao/mapper.xml
1 0 [main] DEBUG com.jmu.dao.ICountryDao.selectCountryById - ==> Preparing: select cid,cname from country where cid=?
2 45 [main] DEBUG com.jmu.dao.ICountryDao.selectCountryById - ==> Parameters: 2(Integer)
3 79 [main] TRACE com.jmu.dao.ICountryDao.selectCountryById - <== Columns: cid, cname
4 80 [main] TRACE com.jmu.dao.ICountryDao.selectCountryById - <== Row: 2, England
5 84 [main] DEBUG com.jmu.dao.ICountryDao.selectMinisterByCountry - ====> Preparing: select mid,mname from minister where countryId=?
6 85 [main] DEBUG com.jmu.dao.ICountryDao.selectMinisterByCountry - ====> Parameters: 2(Integer)
7 91 [main] TRACE com.jmu.dao.ICountryDao.selectMinisterByCountry - <==== Columns: mid, mname
8 92 [main] TRACE com.jmu.dao.ICountryDao.selectMinisterByCountry - <==== Row: 4, ddd
9 93 [main] TRACE com.jmu.dao.ICountryDao.selectMinisterByCountry - <==== Row: 5, eee
10 94 [main] DEBUG com.jmu.dao.ICountryDao.selectMinisterByCountry - <==== Total: 2
11 95 [main] DEBUG com.jmu.dao.ICountryDao.selectCountryById - <== Total: 1
12 Country [cid=2, cname=England, ministers=[Minister [mid=4, mname=ddd], Minister [mid=5, mname=eee]]]
output
3、自关联查询
所谓自关联是指,自己既充当一方,又充当多方,是1:n或n:1的变型。
下面以新闻栏目为例
(1)自关联的DB表
1 //新闻栏目:当前的新闻栏目被看作是一方,即父栏目
2
3 import java.util.Set;
4
5 public class NewLabel{
6 private Integer id;
7 private String name;// 栏目名称
8 private Set<NewLabel> children;
9
10 public Integer getId() {
11 return id;
12 }
13
14 public void setId(Integer id) {
15 this.id = id;
16 }
17
18 public String getName() {
19 return name;
20 }
21
22 public void setName(String name) {
23 this.name = name;
24 }
25
26 public Set<NewLabel> getChildren() {
27 return children;
28 }
29
30 public void setChildren(Set<NewLabel> children) {
31 this.children = children;
32 }
33
34 @Override
35 public String toString() {
36 return "NewLable [id=" + id + ", name=" + name + ", children=" + children + "]";
37 }
38
39 }
com.jmu.bean.NewLabel
(2)以一对多方式处理
- 查询指定栏目的所有子孙栏目
新闻栏目:当前的新闻栏目被看作是一方,即父栏目
1 import com.jmu.bean.NewsLabel;
2
3 public interface INewsLabelDao {
4 List<NewsLabel> selectChildrenByParent(int pid) ;
5 }
com.jmu.dao.INewsLabelDao
1 @Test
2 public void test01() {
3 List<NewsLabel> children=dao.selectChildrenByParent(2);
4 for (NewsLabel newLabel : children) {
5 System.out.println(newLabel);
6 }
7 }
MyTest
1 <mapper namespace="com.jmu.dao.INewsLabelDao">
2 <!-- <select id="selectChildrenByParent" resultMap="newslabelMapper">
3 select id,name from newslabel where pid=#{ooo}
4 </select> -->
5 <resultMap type="NewsLabel" id="newslabelMapper">
6 <id column="id" property="id"/>
7 <result column="name" property="name"/>
8 <collection property="children"
9 ofType="NewsLabel"
10 select="selectChildrenByParent"
11 column="id"
12 ></collection>
13 </resultMap>
14 <select id="selectChildrenByParent" resultMap="newslabelMapper">
15 select id,name from newslabel where pid=#{xxx}
16 </select>
17 </mapper>
/mybatis9-oneself-one2many/src/com/jmu/dao/mapper.xml
输出:
0 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ==> Preparing: select id,name from newslabel where pid=?
47 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ==> Parameters: 2(Integer)
83 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <== Columns: id, name
83 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <== Row: 3, NBA
87 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ====> Preparing: select id,name from newslabel where pid=?
88 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ====> Parameters: 3(Integer)
89 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Columns: id, name
89 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Row: 5, 火箭
89 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Preparing: select id,name from newslabel where pid=?
89 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Parameters: 5(Integer)
90 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <====== Total: 0
91 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Row: 6, 湖人
92 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Preparing: select id,name from newslabel where pid=?
92 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Parameters: 6(Integer)
93 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <====== Total: 0
93 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Total: 2
94 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <== Row: 4, CBA
95 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ====> Preparing: select id,name from newslabel where pid=?
95 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ====> Parameters: 4(Integer)
96 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Columns: id, name
96 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Row: 7, 北京金隅
97 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Preparing: select id,name from newslabel where pid=?
97 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Parameters: 7(Integer)
98 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <====== Total: 0
98 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Row: 8, 浙江广厦
98 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Preparing: select id,name from newslabel where pid=?
98 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Parameters: 8(Integer)
99 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <====== Total: 0
99 [main] TRACE com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Row: 9, 青岛双星
100 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Preparing: select id,name from newslabel where pid=?
101 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - ======> Parameters: 9(Integer)
102 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <====== Total: 0
102 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <==== Total: 3
102 [main] DEBUG com.jmu.dao.INewsLabelDao.selectChildrenByParent - <== Total: 2
NewLable [id=3, name=NBA, children=[NewLable [id=5, name=火箭, children=[]], NewLable [id=6, name=湖人, children=[]]]]
NewLable [id=4, name=CBA, children=[NewLable [id=9, name=青岛双星, children=[]], NewLable [id=7, name=北京金隅, children=[]], NewLable [id=8, name=浙江广厦, children=[]]]]
output
- 查询指定栏目及其所有子孙栏目
这里的查询结果,即要包含指定id的当前栏目,还包含其所有子孙栏目。
1 import com.jmu.bean.NewsLabel;
2
3 public interface INewsLabelDao {
4 NewsLabel selectNewsLabelById(int id);
5 }
com.jmu.dao.INewsLabelDao
1 @Test
2 public void test01() {
3 NewsLabel newsLabel=dao.selectNewsLabelById(2);
4 System.out.println(newsLabel);
5 }
MyTest
1 <mapper namespace="com.jmu.dao.INewsLabelDao">
2 <select id="selectNewslabelByParent" resultMap="newslabelMapper">
3 select id,name from newslabel where pid=#{ooo}
4 </select>
5 <resultMap type="NewsLabel" id="newslabelMapper">
6 <id column="id" property="id"/>
7 <result column="name" property="name"/>
8 <collection property="children"
9 ofType="NewsLabel"
10 select="selectNewslabelByParent"
11 column="id"
12 ></collection>
13 </resultMap>
14 <select id="selectNewsLabelById" resultMap="newslabelMapper">
15 select id,name from newslabel where id=#{xxx}
16 </select>
17 </mapper>
mapper.xml
输出:
0 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ==> Preparing: select id,name from newslabel where id=?
48 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ==> Parameters: 2(Integer)
96 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewsLabelById - <== Columns: id, name
97 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewsLabelById - <== Row: 2, 体育新闻
101 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ====> Preparing: select id,name from newslabel where pid=?
105 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ====> Parameters: 2(Integer)
106 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <==== Columns: id, name
106 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <==== Row: 3, NBA
107 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ======> Preparing: select id,name from newslabel where pid=?
107 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ======> Parameters: 3(Integer)
108 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Columns: id, name
108 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Row: 5, 火箭
109 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Preparing: select id,name from newslabel where pid=?
109 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Parameters: 5(Integer)
110 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <======== Total: 0
111 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Row: 6, 湖人
113 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Preparing: select id,name from newslabel where pid=?
114 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Parameters: 6(Integer)
115 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <======== Total: 0
115 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Total: 2
115 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <==== Row: 4, CBA
116 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ======> Preparing: select id,name from newslabel where pid=?
116 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ======> Parameters: 4(Integer)
117 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Columns: id, name
117 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Row: 7, 北京金隅
117 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Preparing: select id,name from newslabel where pid=?
118 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Parameters: 7(Integer)
118 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <======== Total: 0
119 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Row: 8, 浙江广厦
119 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Preparing: select id,name from newslabel where pid=?
120 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Parameters: 8(Integer)
121 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <======== Total: 0
121 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Row: 9, 青岛双星
123 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Preparing: select id,name from newslabel where pid=?
124 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - ========> Parameters: 9(Integer)
125 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <======== Total: 0
125 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <====== Total: 3
126 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewslabelByParent - <==== Total: 2
126 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - <== Total: 1
NewLable [id=2, name=体育新闻, children=[NewLable [id=4, name=CBA, children=[NewLable [id=7, name=北京金隅, children=[]], NewLable [id=9, name=青岛双星, children=[]], NewLable [id=8, name=浙江广厦, children=[]]]], NewLable [id=3, name=NBA, children=[NewLable [id=5, name=火箭, children=[]], NewLable [id=6, name=湖人, children=[]]]]]]
output
(3)以多对一方式实现
- 查询当前栏目及其所有父辈栏目
新闻栏目:当前的新闻栏目被看作是多方,即子栏目
1 //新闻栏目:当前的新闻栏目被看作是多方,即子栏目
2 public class NewsLabel{
3 private Integer id;
4 private String name;// 栏目名称
5 private NewsLabel parent;//父栏目
6 // private Set<NewsLabel> children;//子栏目,完整自关联
7 public Integer getId() {
8 return id;
9 }
10
11 public void setId(Integer id) {
12 this.id = id;
13 }
14
15 public String getName() {
16 return name;
17 }
18
19 public void setName(String name) {
20 this.name = name;
21 }
22
23 public NewsLabel getParent() {
24 return parent;
25 }
26
27 public void setParent(NewsLabel parent) {
28 this.parent = parent;
29 }
30
31 @Override
32 public String toString() {
33 return "NewsLabel [id=" + id + ", name=" + name + ", parent=" + parent + "]";
34 }
35
36
37
38 }
com.jmu.bean.NewsLabel
1 public interface INewsLabelDao {
2 NewsLabel selectNewsLabelById(int id);
3 }
com.jmu.dao.INewsLabelDao
1 public void test01() {
2 NewsLabel newsLabel=dao.selectNewsLabelById(3);
3 System.out.println(newsLabel);
4 }
MyTest
1 <mapper namespace="com.jmu.dao.INewsLabelDao">
2 <!-- <select id="selectNewslabelByParent" resultMap="newslabelMapper">
3 select id,name,pid from newslabel where id=#{ooo}
4 </select> -->
5 <resultMap type="NewsLabel" id="newslabelMapper">
6 <id column="id" property="id"/>
7 <result column="name" property="name"/>
8 <association property="parent"
9 javaType="NewsLabel"
10 select="selectNewsLabelById"
11 column="pid"></association>
12 </resultMap>
13 <select id="selectNewsLabelById" resultMap="newslabelMapper">
14 select id,name,pid from newslabel where id=#{xxx}
15 </select>
16 </mapper>
mapper.xml
输出:
1 0 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ==> Preparing: select id,name,pid from newslabel where id=?
2 57 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ==> Parameters: 3(Integer)
3 104 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewsLabelById - <== Columns: id, name, pid
4 108 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewsLabelById - <== Row: 3, NBA, 2
5 111 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ====> Preparing: select id,name,pid from newslabel where id=?
6 112 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ====> Parameters: 2(Integer)
7 112 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewsLabelById - <==== Columns: id, name, pid
8 113 [main] TRACE com.jmu.dao.INewsLabelDao.selectNewsLabelById - <==== Row: 2, 体育新闻, 0
9 113 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ======> Preparing: select id,name,pid from newslabel where id=?
10 113 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - ======> Parameters: 0(Integer)
11 114 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - <====== Total: 0
12 115 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - <==== Total: 1
13 115 [main] DEBUG com.jmu.dao.INewsLabelDao.selectNewsLabelById - <== Total: 1
14 NewsLabel [id=3, name=NBA, parent=NewsLabel [id=2, name=体育新闻, parent=null]]
output
4、多对多关联查询
例如:一个学生可以选多门课程,而一门课程可以由多门学生选。
一般情况下,多对多关心都会通过一个中间表来建立。例如选课表。
创建DB表和实体类:
middle表中有外键,为多方,即中间表为多方
1 import java.util.Set;
2
3 public class Course {
4 private Integer cid;
5 private String cname;
6 private Set<Student> students;
7
8 public Integer getCid() {
9 return cid;
10 }
11
12 public void setCid(Integer cid) {
13 this.cid = cid;
14 }
15
16 public String getCname() {
17 return cname;
18 }
19
20 public void setCname(String cname) {
21 this.cname = cname;
22 }
23
24 public Set<Student> getStudents() {
25 return students;
26 }
27
28 public void setStudents(Set<Student> students) {
29 this.students = students;
30 }
31
32 @Override
33 public String toString() {
34 return "Course [cid=" + cid + ", cname=" + cname + ", students=" + students + "]";
35 }
36
37 }
com.jmu.bean.Course
1 import java.util.Set;
2
3 public class Student {
4 private Integer sid;
5 private String sname;
6 private Set<Course> courses;
7
8 public Integer getSid() {
9 return sid;
10 }
11
12 public void setSid(Integer sid) {
13 this.sid = sid;
14 }
15
16 public String getSname() {
17 return sname;
18 }
19
20 public void setSname(String sname) {
21 this.sname = sname;
22 }
23
24 public Set<Course> getCourses() {
25 return courses;
26 }
27
28 public void setCourses(Set<Course> courses) {
29 this.courses = courses;
30 }
31
32 @Override
33 public String toString() {
34 return "Student [sid=" + sid + ", sname=" + sname + ", courses=" + courses + "]";
35 }
36
37 }
com.jmu.bean.Student
1 import com.jmu.bean.Student;
2
3 public interface IStudentDao {
4 Student selectStudentById(int sid);
5 }
com.jmu.dao.IStudentDao
1 @Test
2 public void test01() {
3 Student student = dao.selectStudentById(1);
4 System.out.println(student);
5 }
MyTest
1 <mapper namespace="com.jmu.dao.IStudentDao">
2 <resultMap type="Student" id="studentMapper">
3 <id column="sid" property="sid" />
4 <result column="sname" property="sname" />
5 <collection property="courses" ofType="Course">
6 <id column="cid" property="cid" />
7 <result column="cname" property="cname" />
8 </collection>
9 </resultMap>
10 <select id="selectStudentById" resultMap="studentMapper">
11 select sid,sname,cid,cname
12 from student1,middle,course
13 where sid=studentId and cid=courseId and sid=#{xxx}
14 </select>
15 </mapper>
mapper.xml
输出:
0 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentById - ==> Preparing: select sid,sname,cid,cname from student1,middle,course where sid=studentId and cid=courseId and sid=?
69 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentById - ==> Parameters: 1(Integer)
101 [main] TRACE com.jmu.dao.IStudentDao.selectStudentById - <== Columns: sid, sname, cid, cname
102 [main] TRACE com.jmu.dao.IStudentDao.selectStudentById - <== Row: 1, 刘备, 1, JavaSE
112 [main] TRACE com.jmu.dao.IStudentDao.selectStudentById - <== Row: 1, 刘备, 2, JavaEE
112 [main] DEBUG com.jmu.dao.IStudentDao.selectStudentById - <== Total: 2
Student [sid=1, sname=刘备, courses=[Course [cid=1, cname=JavaSE, students=null], Course [cid=2, cname=JavaEE, students=null]]]
output