开发者

MyBatis多对多一对多关系查询嵌套处理

目录
  • 1. 数据准备
    • 数据库
    • 实体类
    • SQL语句
    • 实体接口
    • mapper.XML
  • 2.一对多的关系
    • 2.1 按查询嵌套处理
    • 2.2 按结果嵌套处理
  • 3. 多对一的关系
    • 数据准备
    • 3.1 按结果嵌套处理
      • Mapper接口
    • 3.2 按查询嵌套处理

    1. 数据准备

    数据库

    student 数据库字段:

    id int(10)

    name varchar(30)

    tid int(10)

    teacher 数据库字符:

    id int(10)

    name varchar(30)

    实体类

    Student:

    @Data
    public class Student {
        private int id;
        private String name;
        private Teacher teacher;
    }

    Teacher:

    @Data
    public class Teacher {
        private int id;
        private String name;
    }

    SQL语句

    CREATE TABLE `teacher` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师');
    CREATE TABLE `student` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fktid` (`tid`),
    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8
    INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
    INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
    INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
    INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
    INSERT INTO `student` (`id`, `name`, `tid`) HvbYeAotZzVALUES ('5', '小王', '1');

    实体接口

    编写实体类对应的Mapper接口 【两个】

    public interface StudentMapper {
    }
    public interface TeacherMapper {
    }

    mapper.xml

    编写Mapper接口对应的 mapper.xml配置文件

    StudentMapper.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">
    <mapper namespace="com.kuang.mapper.StudentMapper">
    
    </mapper>

    TeacherMapper.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">
    <mapper namespace="com.kuang.mapper.TeacherMapper">
    
    </mapper>

    2.一对多的关系

    多个学生对应一个老师.

    2.1 按查询嵌套处理

    1、给StudentMapper接口增加方法:

    //获取所有学生及对应老师的信息
    public List<Student> getStudents();
    • 编写对应的Mapper文件:
    <mapper namespace="com.dao.StudentMapper">
    <!--
       需求:获取所有学生及对应老师的信息
       思路:
           1. 获取所有学生的信息
           2. 根据获取的学生信息的老师ID->获取该老师的信息
           3. 思考问题,这样学生的结果集中应该包含老师,该如何处理呢,数据库中我们一般使用关联查询?
               1. 做一个结果集映射:StudentTeacher
               2. StudentTeacher结果集的类型为 Student
               3. 学生中老师的属性为teacher,对应数据库中为tid。
                  多个 [1,...)学生关联一个老师=> 一对一,一对多
               4. 查看官网找到:association – 一个复杂类型的关联;使用它来处理关联查询
       -->
        <select id="getStudents" resultMap="studentAllInfo" >
            select * from student
        </select>
        <resultMap id="studentAllInfo" type="com.pojo.Student">
            <result column="id" property="id" />
            <result column="name" property="name" />
           <!--association关联属性 property属性名 JavaType属性类型 column在多的一方的表中的列名-->
            <association property="teacher" column="tid"  javaType="com.pojo.Teacher" select="getTeacher" />
        </resultMap>
     <!--
       这里传递过来的id,只有一个属性的时候,下面可以写任何值
       association中column多参数配置:
           column="{key=value,key=value}"
           其实就是键值对的形式,key是传给下个sql的取值名称,value是片段一中sql查询的字段名。
       -->
            <select id="getTeacher" resultType="com.pojo.Teacher">
            select * from teacher where id = #{tid}
        </select>
    </mapper>

    2.2 按结果嵌套处理

    • 接口方法编写:

      public List<Student> getStudents2();

    • StudentMapper.xml:
    <select id="getStudents2" resultMap="STInfo">
            select s.id sid, s.name sname, t.id tid, t.name tname
            from student s, teacher t
            where s.tid = t.id
        </select>
        <resultMap id="STInfo" type="com.pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname" />
            <asjavascriptsociation property="teacher" javaType="com.pojo.Teacher">
                <result property="id" column="tid" />
                <result property="name" column="tname" />
            </association>
        </resultMap>

    Test:

    @Test
        public void testStudent01(){
            SqlSession sqlsession = MyBatisUtils.getSqlSession();
            StudentMapper studentMapper = sqlsession.getMapper(StudentMapper.class);
            List&lt;Student&gt; listStudent = studentMapper.getStudents2();
            for (Student student : listStudent) {
                System.out.println(student);
            }
        }

    3. 多对一的关系

    一个老师对应多个学生

    数据准备

    Student

    @Data
    public class Student {
        private int id;
        private String name;
    //    www.devze.comprivate Teacher teacher;
        private int tid;
    }

    Teacher

    @Data
    public class Teacher {
        private int id;
        private String name;
    
        // 一个老师拥有多个学生
        private List&lt;Student&gt; studentList;
    }

    3.1 按结果嵌套处理

    Mapper接口

    TeacherMapper接口编写方法:

    //获取指定老师,及老师下的所有学生
    public Teacher getTeacher(int id);
    

    编写接口对应的Mapper配置文件

    <mapper namespace="com.dao.TeacherMapper">
       <!--
       思路:
           1. 从学生表和老师表中查出学生id,学生姓名,老师姓名
           2. 对查询出来的操作做结果集映射
              * 集合的话,使用collection!
              * JavaType和ofType都是用来指定对象类型的
              * JavaType是用来指定pojo中属性的类型
              * ofType指定的是映射到list集合属性中pojo的类型。
       -->
        <select id="getTeacher" resultMap="TeacherStuent" >
            select s.id sid, s.name sname, t.id tid, t.name tname  from student s, teacher t
            where s.tid = t.id and t.id = #{tid}
        </select>
        <resultMap id="TeacherStuent" type="com.pojo.Teacher">
            <result property="id" column="tid" />
            <result property="name" column="tname" />
            <!--集合的话,使用collection
                property : 对应着 Teacher类中的属性;
                javaType: 指定属性的类型;
                集合中的泛型,使用ofType获取
            !-->
            <collection property="studentList" ofType="com.pojo.Student">
                <result property="id" column="sid" />
                <result property="name" column="sname" />
                <result property="tid" column="tid" />
            </collection>
        </resultMap>
    </mapper>

    Test:

    @Test
        public void testgetTeacher(){
            SqlSession sqlSession = MyBatisUtils.getSqlSession();
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            Teacher teacher = teacherMapper.getTeacher(1);
            System.out.println( teacher );
            sqlSession.close();
        }
    }

    3.2 按查询嵌套处理

    数据准备

    @Data
    public class Student {
       private int id;
       private String name;
       private int tid;
    }
    
    @Data
    public class Teacher {
       private int id;
       private String name;
       //一个老师多个学生
       private List&lt;Student&gt; students;
    }

    TeacherMapper接口编写方法 :public Teacher getTeacher2(int id);

    编写接口对应的Mapper配置文件

    <mapper namespace="com.dao.TeacherMapper">
        <select id="getTeacher2" resultMap="TeacherStuent">
            select * from teacher where id = #{tid}
        </select>
    
        <resultMap id="TeacherStuent" type="com.pojo.Teacher">
            <result property="id" column="id" />
            <result property="name" column="name" />python
            <!--column是一对多的外键 , 写的是一的主键的列名-->
            <collection property="studentList" javaType="ArrayList" ofType="com.pojo.Student" select="getStudentByTId" column="id"/>
        </resultMap>
        
        <select id="getStudentByTId" resultType="com.pojo.Student">
            select * from student where tid = #{id}
        </select>
    </mapper>

    Test:

    @TestHVbYeAotZz
        public void testgetTeacher2(){
            SqlSession sqlSession = MyBatisUtils.getSqlSession();
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            Teacher teacher = teacherMapper.getTeacher2(1);
            System.out.println(teacher);
    
            sqlSession.close();
    
        }

    以上就是MyBatis多对多一对多关系查询嵌套处理的详细内容,更多关于MyBatis多对多一对多嵌套查询的资料请关注编程客栈(www.devze.com)其它相关文章!

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新开发

    开发排行榜