Mybatis---关联查询

最新推荐文章于 2024-02-26 01:37:56 发布

菜到极致就是渣

最新推荐文章于 2024-02-26 01:37:56 发布

阅读量260

点赞数

分类专栏： Mybatis 文章标签： mybatis 数据库

本文链接：https://blog.csdn.net/gaoqiandr/article/details/132845440

版权

Mybatis 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、一对一关联查询

（1）持久层接口方法

public interface StudentMapper {
    List<Student> findAll();
}

（2）映射文件

<?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">
<mapper namespace="com.gq.mapper.StudentMapper">
   <resultMap id="findMap" type="com.gq.pojo.Student">
      <id property="sid" column="sid"></id>
      <result property="sex" column="sex"></result>
      <result property="name" column="name"></result>
      <association property="classes" column="classId" javaType="com.gq.pojo.Classes">
         <id property="cid" column="cid"></id>
         <result property="className" column="className"></result>
      </association>
   </resultMap>
   <select id="findAll" resultMap="findMap">
      select *from student left join classes on student.classId=classes.cid
   </select>
</mapper>

（3）测试方法

 @Test
    public void yiduiyiTest() throws Exception{
        InputStream is= Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(is);
        SqlSession session=sqlSessionFactory.openSession();
        StudentMapper studentMapper=session.getMapper(StudentMapper.class);
        List<Student> studentList=studentMapper.findAll();
        studentList.forEach(System.out::println);
        session.commit();
        session.close();
        is.close();
    }

二、一对多关联查询

查询班级时，将关联的学生也查出来，这就是一对多关联查询

（1）持久层接口方法

public interface StudentMapper {
    //查询班级，关联查询出学生
    List<Classes> findClassesAll();
}

（2）映射文件

   <resultMap id="ClassMap" type="com.gq.pojo.Classes">
      <id property="cid" column="cid"></id>
      <result property="className" column="className"></result>
<!-- proprty是属性名，column是关联列名，oftype：集合的泛型-->
      <collection property="studentList" column="classId" ofType="com.gq.pojo.Student">
         <id property="sid" column="sid"></id>
         <result property="name" column="name"></result>
         <result property="sex" column="sex"></result>
      </collection>
    </resultMap>
 <select id="findClassesAll" resultMap="ClassMap">
    select *from classes left join student on student.classId=classes.cid
 </select>

（3）测试方法

@Test
    public void YiDuiDuoTest() throws Exception {
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
        SqlSession session = sqlSessionFactory.openSession();
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        List<Classes> classes=studentMapper.findClassesAll();
        classes.forEach(System.out::println);
        session.commit();
        session.close();
        is.close();
    }

三、多对多查询

多对多关联查询实际上就是两个一对多查询。

比如说：

一个老师对应多个班级，也就是说老师类中有一个班级集合属性

一个班级对应多个老师，也就是班级类中有一个老师集合属性

（1）持久层接口方法

　查询老师时，会将关联的班级也查询出来。
public interface TeacherMapper {
    List<Teacher> findTeacherAll();
}

（2）映射文件

    <resultMap id="TeacherMap" type="com.gq.pojo.Teacher">
        <id property="tid" column="tid"></id>
        <result property="tname" column="tname"></result>
        <collection property="classes" column="tid" ofType="com.gq.pojo.Classes">
            <id property="cid" column="cid"></id>
            <result property="className" column="className"></result>
        </collection>
    </resultMap>
    <select id="findTeacherAll" resultMap="TeacherMap">
        select *from teacher
            left join classes_teacher on teacher.tid=classes_teacher.tid
            left join classes on classes.cid=classes_teacher.cid
    </select>

（3）测试方法

    @Test
    public void Test1() throws Exception {
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
        SqlSession session = sqlSessionFactory.openSession();
        TeacherMapper teacherMapper=session.getMapper(TeacherMapper.class);
        List<Teacher> teachers=teacherMapper.findTeacherAll();
        teachers.forEach(System.out::println);
        session.commit();
        session.close();
        is.close();
    }