五、关联查询

数据库信息
emp表：



emp实体类创建：

import java.util.Date;

public class Emp {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private double sal;
    private double comm;
    private Dept dept;
    
    public Emp() {
        super();
    }
    
    public int getEmpno() {
        return empno;
    }
    public void setEmpno(int empno) {
        this.empno = empno;
    }
    public String getEname() {
        return ename;
    }
    public void setEname(String ename) {
        this.ename = ename;
    }
    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }
    public int getMgr() {
        return mgr;
    }
    public void setMgr(int mgr) {
        this.mgr = mgr;
    }
    public Date getHiredate() {
        return hiredate;
    }
    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }
    public double getSal() {
        return sal;
    }
    public void setSal(double sal) {
        this.sal = sal;
    }
    public double getComm() {
        return comm;
    }
    public void setComm(double comm) {
        this.comm = comm;
    }
    public Dept getDept() {
        return dept;
    }
    public void setDept(Dept dept) {
        this.dept = dept;
    }
    
    
}

dept表：



dept实体类创建：

import java.util.List;

public class Dept {
    private int deptno;
    private String dname;
    private String loc;
    private List<Emp> emps;//关系映射：一对多
    
    public Dept() {
        super();
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    public int getDeptno() {
        return deptno;
    }
    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
    public String getDname() {
        return dname;
    }
    public void setDname(String dname) {
        this.dname = dname;
    }
    public String getLoc() {
        return loc;
    }
    public void setLoc(String loc) {
        this.loc = loc;
    }
}

1、查询所有员工的所有信息（多对一映射）

  • EmpMapper.java代码：
public List<HashMap<String, Object>> findAllEmpDept();
  • EmpMapper.xml代码：
 <select id="findAllEmpDept" resultType="map">
    select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno
 </select>

注意：
如果写select * 会出现两列deptno，所以要写具体的字段名
返回值map的key是字段名，value是信息

  • test代码：
    @Test
    public void findAllEmpDept() throws Exception {
        String path = "SqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<HashMap<String, Object>> list = empMapper.findAllEmpDept();
        for (HashMap<String, Object> hm : list) {
            System.out.println(hm.get("empno")+"\t"+hm.get("ename")+"\t"+hm.get("deptno")+"\t"+hm.get("dname"));
        }
    }

2、查询员工信息及所在部门信息（多对一映射）

在Emp实体类中添加属性

private Dept dept;
  • EmpMapper.java代码：
public List<Emp> getEmp();
  • EmpMapper.xml代码：
 <select id="getEmp" resultMap="_empdept">
    select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno order by empno desc
 </select>

 <resultMap type="Emp" id="_empdept" autoMapping="true">
    <id column="empno" property="empno"/>
    <result column="ename" property="ename"/>
    <result column="job" property="job"/>
    <result column="mgr" property="mgr"/>
    <result column="hiredate" property="hiredate"/>
    <result column="sal" property="sal"/>
    <result column="comm" property="comm"/>
    <association property="dept" javaType="Dept" autoMapping="true">
        <id column="deptno" property="deptno"/>
        <result column="dname" property="dname"/>
        <result column="loc" property="loc"/>
    </association>
 </resultMap>

resultMap：结果映射，把查询到的列跟目标对象绑定
主键用id，非主键用result
autoMapping="true"当列名完全一致可以使用 ，基本属性自动映射，引用数据类型不可以
association：多对一映射
column：数据库表字段名
property：实体类属性名
property="dept"映射类属性的名字Emp类中private Dept dept中“dept”
javaType="Dept"映射类的类型

  • test代码：
    @Test
    public void getEmp() throws Exception {
        String path = "SqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> list = empMapper.getEmp();
        for (Emp e : list) {
            System.out.println(e.getEmpno()+"\t"+e.getEname()+"\t"+e.getDept().getDeptno()+"\t"+e.getDept().getDname());
        }
    }

3、查所有部门信息及部门下员工信息（一对多映射）

在Dept实体类中添加属性

private List<Emp> emps;
  • EmpMapper.java代码：
public List<Dept> getDept();
  • EmpMapper.xml代码：
 <select id="getDept" resultMap="_deptemp">
    select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno order by empno desc
 </select>
 <resultMap type="Dept" id="_deptemp" autoMapping="true">
    <id column="deptno" property="deptno"/>
    <collection property="emps" ofType="Emp" column="deptno" autoMapping="true">
    </collection>
 </resultMap>

注意：
一对多时id映射不能省略
collection一对多关系映射
ofType：集合（list）里元素的类型
column：分组字段——相同的捏合到一起
autoMapping="true"一对多时主键映射不能省略（id列）

  • test代码：
    @Test
    public void getDept() throws Exception {
        String path = "SqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Dept> list = empMapper.getDept();
        for (Dept d : list) {
            System.out.println(d.getDeptno()+"\t"+d.getDname()+"\t"+d.getLoc());
            System.out.println("-----------------------------");
            List<Emp> list2=d.getEmps();
            for (Emp e : list2) {
                System.out.println(e.getEmpno()+"\t"+e.getEname()+"\t"+e.getSal()+"\t"+e.getHiredate());
            }
            System.out.println("=============================");
        }       
    }

4、多对多——学生选课

学生可以选择多门课，每门课可以有多个学生
创建实体类：
Student：

import java.util.List;

public class Student {
    private int sid;
    private String sname;
    private List<StudentLesson> list;
    
    public Student() {
        super();
    }
    public List<StudentLesson> getList() {
        return list;
    }
    public void setList(List<StudentLesson> list) {
        this.list = list;
    }
    public int getSid() {
        return sid;
    }
    public void setSid(int sid) {
        this.sid = sid;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
}

Lesson：

import java.util.List;

public class Lesson {
    private int lid;
    private String lname;
    private List<StudentLesson> list;
    
    public Lesson() {
        super();
    }
    
    public List<StudentLesson> getList() {
        return list;
    }
    public void setList(List<StudentLesson> list) {
        this.list = list;
    }
    public int getLid() {
        return lid;
    }
    public void setLid(int lid) {
        this.lid = lid;
    }
    public String getLname() {
        return lname;
    }
    public void setLname(String lname) {
        this.lname = lname;
    }
}

StudentLesson：

public class StudentLesson {
    private int id;
    private Lesson l;
    private Student s;
    private double sc;
    
    public StudentLesson() {
        super();
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public Lesson getL() {
        return l;
    }
    public void setL(Lesson l) {
        this.l = l;
    }
    public Student getS() {
        return s;
    }
    public void setS(Student s) {
        this.s = s;
    }
    public double getSc() {
        return sc;
    }
    public void setSc(double sc) {
        this.sc = sc;
    }
}

多对多关系映射时，创建一个关系表，数据库信息如下：
student表：



lesson表：



studentlesson表：

将多对多的关系分解为两个一对多的关系

一个学生对关系表多个记录
一门课对关系表多个记录
所以在Student和Lesson实体类中添加StudentLesson的属性:

private List<StudentLesson> list;

在StudentLesson中添加属性：

    private Lesson l;
    private Student s;
  • EmpMapper.java代码：
    public List<Student> getStudent();
    public List<Lesson> getLesson();
  • EmpMapper.xml代码：
 <select id="getStudent" resultMap="_studentlesson">
    select s.*,l.*,sl.id,sl.sc from student s,lesson l,studentlesson sl where s.sid=sl.sid and l.lid=sl.lid
 </select>
 <resultMap type="Student" id="_studentlesson">
    <id column="sid" property="sid"/>
    <result column="sname" property="sname"/>
    <collection property="list" ofType="StudentLesson" column="sid">
        <id column="id" property="id"/>
        <result column="sc" property="sc"/>
        <association property="l" javaType="Lesson">
            <id column="lid" property="lid"/>
            <result column="lname" property="lname"/>
        </association>
    </collection>
 </resultMap>

 <select id="getLesson" resultMap="_lessonstudent">
    select s.*,l.*,sl.id,sl.sc from student s,lesson l,studentlesson sl where s.sid=sl.sid and l.lid=sl.lid
 </select>
 <resultMap type="Lesson" id="_lessonstudent">
    <id column="lid" property="lid"/>
    <result column="lname" property="lname"/>
    <collection property="list" ofType="StudentLesson" column="lid">
        <id column="id" property="id"/>
        <result column="sc" property="sc"/>
        <association property="s" javaType="Student">
            <id column="sid" property="sid"/>
            <result column="sname" property="sname"/>
        </association>
    </collection>
 </resultMap>
  • test代码：
    @Test
    public void getLesson() throws Exception {
        String path = "SqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Lesson> list = empMapper.getLesson();
        for (Lesson l : list) {
            System.out.println("===================================");
            System.out.println(l.getLid()+"\t"+l.getLname()+"\t");
            System.out.println("-----------------------------------");
            List<StudentLesson> list2=l.getList();
            for (StudentLesson sl : list2) {
                System.out.println(sl.getS().getSid()+"\t"+sl.getS().getSname()+"\t"+sl.getSc());
            }
            System.out.println("===================================");
            System.out.println();
        }
    }
    @Test
    public void getStudent() throws Exception {
        String path = "SqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(path);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Student> list = empMapper.getStudent();
        for (Student s : list) {
            System.out.println("===================================");
            System.out.println(s.getSid()+"\t"+s.getSname()+"\t");
            System.out.println("-----------------------------------");
            List<StudentLesson> list2=s.getList();
            for (StudentLesson sl : list2) {
                System.out.println(sl.getL().getLid()+"\t"+sl.getL().getLname()+"\t"+sl.getSc());
            }
            System.out.println("===================================");
            System.out.println();
        }
    }
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容