MyBatis_关联关系查询

一、关联查询

      当查询的内容涉及到具有关联关系的多个表时，就需要使用关联查询。根据表与表间的关联关系的不同。关联查询分为四种：

  1. 一对一关联查询；
  2. 一对多关联查询；
  3. 多对一关联查询；
  4. 多对多关联查询；

二、一对多关联查询

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

 

posted @ 2017-12-25 17:32  honghj  阅读(523)  评论(0)  编辑  收藏  举报