MyBatis的关联查询

首先要了解对象的三种关联方式

*数据库表的关联关系有三种，一对一，一对多，多对多
一对一是通过在任意一方的主键，引入对方主键作为外键来实现的，就是说主键与外键为同一字段
一对多是通过在“多”的一方，添加“一”的一方的主键作为外键
多对多是通过一张中间关系表，引入两张表的主键作为外键，两个主键成为联合主键或使用新的字段作为主键

*在java类中关联关系也有三种，一对一，一对多，多对多
一对一在本类中定义对方类型的对象,如A类中定义B类类型的属性b,B类中定义A类类型的属性a
一对多一个A类类型对应多个B类类型的情况,需要在A类中以集合的方式引入B类类型的对象,在B类中定义A类类型的属性a
多对多在A类中定义B类类型的集合,在B类中定义A类类型的集合

1.一对多关联查询：

　　搭建实体类：

 1 package com.smbms.entity;
 2 
 3 import java.math.BigInteger;
 4 import java.util.Date;
 5 import java.util.List;
 6 
 7 /**
 8  * 角色
 9  */
10 public class SmbmsRole {
11     private Integer rid;
12     private String roleCode;
13     private String roleName;
14     private BigInteger createdBy;
15     private Date creationDate;
16     private BigInteger modifyBy;
17     private Date modifyDate;
18 
19     //植入多的一方      集合
20     private List<SmbmsUser> userList;
21 
22     public List<SmbmsUser> getUserList() {
23         return userList;
24     }
25 
26     public void setUserList(List<SmbmsUser> userList) {
27         this.userList = userList;
28     }
29 
30     public Integer getRid() {
31         return rid;
32     }
33     public void setRid(Integer rid) {
34         this.rid = rid;
35     }
36     public String getRoleCode() {
37         return roleCode;
38     }
39     public void setRoleCode(String roleCode) {
40         this.roleCode = roleCode;
41     }
42     public String getRoleName() {
43         return roleName;
44     }
45     public void setRoleName(String roleName) {
46         this.roleName = roleName;
47     }
48     public BigInteger getCreatedBy() {
49         return createdBy;
50     }
51     public void setCreatedBy(BigInteger createdBy) {
52         this.createdBy = createdBy;
53     }
54     public Date getCreationDate() {
55         return creationDate;
56     }
57     public void setCreationDate(Date creationDate) {
58         this.creationDate = creationDate;
59     }
60     public BigInteger getModifyBy() {
61         return modifyBy;
62     }
63     public void setModifyBy(BigInteger modifyBy) {
64         this.modifyBy = modifyBy;
65     }
66     public Date getModifyDate() {
67         return modifyDate;
68     }
69     public void setModifyDate(Date modifyDate) {
70         this.modifyDate = modifyDate;
71     }
72 }

　　创建Dao层对象：

1 package com.smbms.dao;
2 import com.smbms.entity.SmbmsRole;
3 public interface ISmbmsRoleDao {
4     //查询经理角色   以及  该角色下对应的员工集合
5     public SmbmsRole getRoleAndUser(Integer id);
6 }

　　创建ISmbmsRoleDao.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">
<!--namespace需要指向接口全路径-->
<mapper namespace="com.smbms.dao.ISmbmsRoleDao">
    <resultMap id="roleAndUserMapper" type="SmbmsRole">
        <id column="rid" property="rid"></id>
        <result column="roleName" property="roleName"/>
        <!--映射多的一方 property代表实体当中多的一方的属性名      ofType代表集合当中泛型类型-->
        <!-- select 代表执行查询的ID   column所引用的条件列   -->
        <collection property="userList" ofType="SmbmsUser">

        </collection>
    </resultMap>

    <select id="getRoleAndUser" resultMap="roleAndUserMapper">
        select u.id,u.userName,u.userRole,r.rid,r.roleName from smbms_user as u,smbms_role as r where u.userRole=r.rid and r.rid=#{id}
    </select>
</mapper>

　　创建test测试类：

 1 package com.smbms.test;
 2 
 3 import com.smbms.dao.ISmbmsRoleDao;
 4 import com.smbms.entity.SmbmsRole;
 5 import com.smbms.entity.SmbmsUser;
 6 import com.smbms.util.MybatisUtil;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.junit.Test;
 9 
10 public class CollectionTest {
11     @Test
12     public void getRoleAndUserTest(){
13         SqlSession sqlSession = MybatisUtil.getSqlSession();
14         ISmbmsRoleDao mapper = sqlSession.getMapper(ISmbmsRoleDao.class);
15 
16 
17         SmbmsRole role = mapper.getRoleAndUser(3);
18         System.out.println("角色："+role.getRoleName());
19         for(SmbmsUser user : role.getUserList()){
20             System.out.print("\t用户："+user.getUserName());
21         }
22     }
23 }

多对一关联查询

　　创建ISmbmsRoleDao.xml文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <!--namespace需要指向接口全路径-->
 6 <mapper namespace="com.smbms.dao.ISmbmsUserDao">
 7     <resultMap id="userListAndRole" type="SmbmsUser">
 8         <id column="id" property="id"></id>
 9         <result column="userName" property="userName"/>
10         <association property="role" javaType="SmbmsRole" select="getRole" column="userRole">
11             <id column="rid" property="rid"></id>
12             <result column="roleName" property="roleName"/>
13         </association>
14     </resultMap>
15 
16 
17     <select id="getUserList" resultMap="userListAndRole">
18         select * from smbms_user
19     </select>
20     <select id="getRole" resultType="SmbmsRole">
21         select * from smbms_role where rid=#{userRole}
22     </select>
23 </mapper>

　　创建Dao层接口：

 1 package com.smbms.dao;
 2 
 3 import com.smbms.entity.SmbmsUser;
 4 
 5 import java.util.List;
 6 
 7 public interface ISmbmsUserDao {
 8     //查询所有用户信息  包含角色信息
 9     public List<SmbmsUser> getUserList();
10 }

　　创建ISmbmsUserDao.xml文件：

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <!--namespace需要指向接口全路径-->
 6 <mapper namespace="com.smbms.dao.ISmbmsUserDao">
 7     <resultMap id="userListAndRole" type="SmbmsUser">
 8         <id column="id" property="id"></id>
 9         <result column="userName" property="userName"/>
10         <association property="role" javaType="SmbmsRole" select="getRole" column="userRole">
11             <id column="rid" property="rid"></id>
12             <result column="roleName" property="roleName"/>
13         </association>
14     </resultMap>
15　　　<select id="getUserList" resultMap="userListAndRole">
16  　　　　select * from smbms_user
17　　　</select>
18　　　<select id="getRole" resultType="SmbmsRole">
19 　　　　select * from smbms_role where rid=#{userRole}
20　　　</select>
21</mapper>

　　创建测试类：

 1 package com.smbms.test;
 2 
 3 import com.smbms.dao.ISmbmsUserDao;
 4 import com.smbms.entity.SmbmsUser;
 5 import com.smbms.util.MybatisUtil;
 6 import org.apache.ibatis.session.SqlSession;
 7 import org.junit.Test;
 8 
 9 import java.util.List;
10 
11 public class AssociationTest {
12     @Test
13     public void getUserListTest(){
14         SqlSession sqlSession = MybatisUtil.getSqlSession();
15         ISmbmsUserDao mapper = sqlSession.getMapper(ISmbmsUserDao.class);
16 
17         List<SmbmsUser> userList = mapper.getUserList();
18         for(SmbmsUser user:userList){
19             System.out.println("用户："+user.getUserName()+"\t角色："+user.getRole().getRoleName());
20         }
21     }
22 }

多对多关联查询：

　　student实体类：

 1 package com.smbms.entity;
 2 
 3 import java.util.List;
 4 
 5 public class Student {
 6     private Integer stuid;
 7     private String stuname;
 8     private String stuaddress;
 9 
10     //植入Teacher集合，代表一名学员可以被多名教员教授
11     private List<Teacher> teachers;
12 
13     public List<Teacher> getTeachers() {
14         return teachers;
15     }
16 
17     public void setTeachers(List<Teacher> teachers) {
18         this.teachers = teachers;
19     }
20 
21     public Integer getStuid() {
22         return stuid;
23     }
24 
25     public void setStuid(Integer stuid) {
26         this.stuid = stuid;
27     }
28 
29     public String getStuname() {
30         return stuname;
31     }
32 
33     public void setStuname(String stuname) {
34         this.stuname = stuname;
35     }
36 
37     public String getStuaddress() {
38         return stuaddress;
39     }
40 
41     public void setStuaddress(String stuaddress) {
42         this.stuaddress = stuaddress;
43     }
44 }

　　teacher实体类：

package com.smbms.entity;

import java.util.List;

public class Teacher {
    private Integer tid;
    private String tname;

    //植入学员集合，代表一名教员可以教授多名学员
    private List<Student> students;

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }
}

　　DAO层接口：

package com.smbms.dao;

import com.smbms.entity.Student;

import java.util.List;

public interface IStudentDao {
    //查询所有学生信息  以及授课教员
    public List<Student> getStudentInfo();
}

　　创建IStudentDao.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">
<!--namespace需要指向接口全路径-->
<mapper namespace="com.smbms.dao.IStudentDao">
    <resultMap id="studentAndTeacherMapper" type="Student">
        <id column="stuid" property="stuid"/>
        <result column="stuname" property="stuname"/>
        <collection property="teachers" ofType="Teacher">
            <id column="tid" property="tid"></id>
            <result property="tname" column="tname"/>
        </collection>
    </resultMap>
    <select id="getStudentInfo" resultMap="studentAndTeacherMapper">
        select * from student,teacher,stu_t where student.stuid=stu_t.stuid and teacher.tid=stu_t.tid
    </select>
</mapper>

　　创建Test测试文件：

package com.smbms.test;

import com.smbms.dao.IStudentDao;
import com.smbms.entity.Student;
import com.smbms.entity.Teacher;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class ManeyTooManey {
    @Test
    public void getStudentInfo(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        IStudentDao mapper = sqlSession.getMapper(IStudentDao.class);

        List<Student> studentInfo = mapper.getStudentInfo();
        for(Student stu:studentInfo){
            System.out.println("学生："+stu.getStuname());
            for (Teacher teacher:stu.getTeachers()){
                System.out.print("\t教员："+teacher.getTname());
            }
            System.out.println();
        }

    }
}

自关联查询：

　　实体类：

package com.smbms.entity;

import java.util.List;

public class City {
    private Integer cid;
    private String cname;
    private Integer pid;
    //自关联集合     代表的是当前City对象的子集集合
    public List<City> childCitys;

    @Override
    public String toString() {
        return "City{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", pid=" + pid +
                ", childCitys=" + childCitys +
                '}';
    }

    public List<City> getChildCitys() {
        return childCitys;
    }

    public void setChildCitys(List<City> childCitys) {
        this.childCitys = childCitys;
    }

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

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }


}

　　Dao层接口：

package com.smbms.dao;

import com.smbms.entity.City;

import java.util.List;

public interface ICityDao {
    //查询河南省  下的所有子集
    public City getCityAndChildCitys(Integer cid);
}

　　ICityDao.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">
<!--namespace需要指向接口全路径-->
<mapper namespace="com.smbms.dao.ICityDao">
    <resultMap id="CityAndChildCitysMapper" type="City">
        <id column="cid" property="cid"></id>
        <result column="cname" property="cname"/>
        <result column="pid" property="pid"/>
        <collection property="childCitys" ofType="City" select="getCityAndChildCitysMutilSQL" column="cid">
            <id column="cid" property="cid"></id>
            <result column="cname" property="cname"/>
            <result column="pid" property="pid"/>
        </collection>
    </resultMap>

   <select id="getCityAndChildCitys" resultMap="CityAndChildCitysMapper">
       select * from city where cid=#{cid}
   </select>
    <select id="getCityAndChildCitysMutilSQL" resultMap="CityAndChildCitysMapper">
       select * from city where pid=#{cid}
   </select>
</mapper>

　　Test测试类：

package com.smbms.test;

import com.smbms.dao.ICityDao;
import com.smbms.entity.City;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class DGTest {
    @Test
    public void getCityAndChildCitysTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        ICityDao mapper = sqlSession.getMapper(ICityDao.class);

        City city = mapper.getCityAndChildCitys(410000);
        System.out.println(city.toString());
    }
}

posted @ 2019-10-12 15:19 EXTRA· 阅读(160) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

EXTRA·

MyBatis的关联查询

公告