07.MyBatis中的关联查询

关联查询：

一对一：

两种方式实现:

1.通过业务扩展的方式进行一对一查询，新建一个实体类，继承其中属性多的一个，然后写上另一个类中的属性：

实体类:

 映射文件:

<!-- 业务扩展的方式进行一对一查询，创建一个新的实体类继承属性多的一方然后重写属性少的一方 -->
  <select id="queryStudentWithOO" parameterType="int" resultType="StudentBusiness">
    select * from student s inner join studentcard c on s.sid = c.cid and sid = #{sid}
  </select>

测试类:

//一对一查寻
    public static void queryStudentWithOO() throws IOException {
        //读取配置文件
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //创建sqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //获取session
        SqlSession session = sessionFactory.openSession();
        //定位映射文件
        studentMapper studentMapper = session.getMapper(studentMapper.class);
        //定位sql语句并执行
        StudentBusiness studentBusiness = studentMapper.queryStudentWithOO(3);
        //提交事务
        session.commit();
        System.out.println(studentBusiness);
        //关闭连接
        session.close();
    }

2.通过ResultMap的方式实现一对一

然后在映射文件中使用association进行对象属性的映射:

实体类:

配置文件:

 <select id="queryStudentWithMap" parameterType="int" resultMap="StudentBusiness1">
    select * from student s inner join studentcard c on s.sid = c.cid and sid = #{sid}
  </select>
<resultMap type="StudentBusiness" id="StudentBusiness1">
  <id property="sid" column="sid"/>
  <result property="sname" column="sname"/>
  <result property="age" column="age"/>
  <result property="sex" column="sex"/>
  <result property="cid" column="cid"/>
  <result property="stuInfo" column="stuInfo"/>
      <association property="address" javaType="Address">
          <result property="homeAddress" column="homeaddress"/>
          <result property="schoolAddress" column="schooladdress"/>
      </association>
  </resultMap>

测试类:

//一对一查寻，使用ResultMap的方式
    public static void queryStudentWithMap() throws IOException {
        //读取配置文件
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //创建sqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //获取session
        SqlSession session = sessionFactory.openSession();
        //定位映射文件
        studentMapper studentMapper = session.getMapper(studentMapper.class);
        //定位sql语句并执行
        StudentBusiness studentBusiness = studentMapper.queryStudentWithMap(3);
        //提交事务
        session.commit();
        System.out.println(studentBusiness);
        //关闭连接
        session.close();
    }

2.一对多:

一个班级对应多个学生，应在班级的实体类中定义一个学生的属性,然后在映射文件中使用collection进行对象属性集合进行映射:

实体类:

 

配置文件：

 <!-- 一对多查询 -->
  <select id="queryStudentWithOM" parameterType="int" resultMap="Studentclass1">
    select * from student s inner join studentclass c on s.cno = c.cno and c.cno = #{cno}
  </select>
  
  <resultMap type="StudentClass" id="Studentclass1">
      <id property="cno" column="cno"/>
      <result property="cname" column="cname"/>
      <!-- 循环集合用collection，属性值写集合属性名，类型写集合中的元素的类型并且使用的是ofType -->
      <collection property="students" ofType="student">
          <result property="sname" column="sname"/>
          <result property="age" column="age"/>
          <result property="sex" column="sex"/>
      </collection>
  </resultMap>

测试类:

//一对多查寻
    public static void queryStudentWithOM() throws IOException {
        //读取配置文件
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //创建sqlSessionFactory
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //获取session
        SqlSession session = sessionFactory.openSession();
        //定位映射文件
        studentMapper studentMapper = session.getMapper(studentMapper.class);
        //定位sql语句并执行
         StudentClass studentClass = studentMapper.queryStudentWithOM(2);
         System.out.println(studentClass);
        //提交事务
        session.commit();
        List<Student> students = studentClass.getStudents();
        for (Student student : students) {
            System.out.print(studentClass.getCno() + "," + studentClass.getCname() + ",");
            System.out.println(student.getSname()+"," +student.getAge()+"," +student.getSex());
        }
        //关闭连接
        session.close();
    }