MyBatis多對(duì)多一對(duì)多關(guān)系查詢嵌套處理

更新時(shí)間：2023年10月15日 12:05:48 作者：phang

這篇文章主要為大家介紹了MyBatis多對(duì)多一對(duì)多關(guān)系查詢嵌套處理示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

1. 數(shù)據(jù)準(zhǔn)備

數(shù)據(jù)庫(kù)

student 數(shù)據(jù)庫(kù)字段：

id int(10)

name varchar(30)

tid int(10)

teacher 數(shù)據(jù)庫(kù)字符：

id int(10)

name varchar(30)

實(shí)體類

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語(yǔ)句

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`) VALUES ('5', '小王', '1');

實(shí)體接口

編寫(xiě)實(shí)體類對(duì)應(yīng)的Mapper接口【兩個(gè)】

public interface StudentMapper {
}
public interface TeacherMapper {
}

mapper.xml

編寫(xiě)Mapper接口對(duì)應(yīng)的 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.一對(duì)多的關(guān)系

多個(gè)學(xué)生對(duì)應(yīng)一個(gè)老師.

2.1 按查詢嵌套處理

1、給StudentMapper接口增加方法:

//獲取所有學(xué)生及對(duì)應(yīng)老師的信息
public List<Student> getStudents();

編寫(xiě)對(duì)應(yīng)的Mapper文件:

<mapper namespace="com.dao.StudentMapper">
<!--
   需求：獲取所有學(xué)生及對(duì)應(yīng)老師的信息
   思路：
       1. 獲取所有學(xué)生的信息
       2. 根據(jù)獲取的學(xué)生信息的老師ID->獲取該老師的信息
       3. 思考問(wèn)題，這樣學(xué)生的結(jié)果集中應(yīng)該包含老師，該如何處理呢，數(shù)據(jù)庫(kù)中我們一般使用關(guān)聯(lián)查詢？
           1. 做一個(gè)結(jié)果集映射：StudentTeacher
           2. StudentTeacher結(jié)果集的類型為 Student
           3. 學(xué)生中老師的屬性為teacher，對(duì)應(yīng)數(shù)據(jù)庫(kù)中為tid。
              多個(gè) [1,...）學(xué)生關(guān)聯(lián)一個(gè)老師=> 一對(duì)一，一對(duì)多
           4. 查看官網(wǎng)找到：association – 一個(gè)復(fù)雜類型的關(guān)聯(lián)；使用它來(lái)處理關(guān)聯(lián)查詢
   -->
    <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關(guān)聯(lián)屬性 property屬性名 javaType屬性類型 column在多的一方的表中的列名-->
        <association property="teacher" column="tid"  javaType="com.pojo.Teacher" select="getTeacher" />
    </resultMap>
 <!--
   這里傳遞過(guò)來(lái)的id，只有一個(gè)屬性的時(shí)候，下面可以寫(xiě)任何值
   association中column多參數(shù)配置：
       column="{key=value,key=value}"
       其實(shí)就是鍵值對(duì)的形式，key是傳給下個(gè)sql的取值名稱，value是片段一中sql查詢的字段名。
   -->
        <select id="getTeacher" resultType="com.pojo.Teacher">
        select * from teacher where id = #{tid}
    </select>
</mapper>

2.2 按結(jié)果嵌套處理

接口方法編寫(xiě):
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" />
        <association 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. 多對(duì)一的關(guān)系

一個(gè)老師對(duì)應(yīng)多個(gè)學(xué)生

數(shù)據(jù)準(zhǔn)備

Student

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

Teacher

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

    // 一個(gè)老師擁有多個(gè)學(xué)生
    private List&lt;Student&gt; studentList;
}

3.1 按結(jié)果嵌套處理

Mapper接口

TeacherMapper接口編寫(xiě)方法：

//獲取指定老師，及老師下的所有學(xué)生
public Teacher getTeacher(int id);

編寫(xiě)接口對(duì)應(yīng)的Mapper配置文件

<mapper namespace="com.dao.TeacherMapper">
   <!--
   思路:
       1. 從學(xué)生表和老師表中查出學(xué)生id，學(xué)生姓名，老師姓名
       2. 對(duì)查詢出來(lái)的操作做結(jié)果集映射
          * 集合的話，使用collection！
          * JavaType和ofType都是用來(lái)指定對(duì)象類型的
          * JavaType是用來(lái)指定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 : 對(duì)應(yīng)著 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 按查詢嵌套處理

數(shù)據(jù)準(zhǔn)備

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

@Data
public class Teacher {
   private int id;
   private String name;
   //一個(gè)老師多個(gè)學(xué)生
   private List&lt;Student&gt; students;
}

TeacherMapper接口編寫(xiě)方法 :public Teacher getTeacher2(int id);

編寫(xiě)接口對(duì)應(yīng)的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" />
        <!--column是一對(duì)多的外鍵 , 寫(xiě)的是一的主鍵的列名-->
        <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:

@Test
    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多對(duì)多一對(duì)多關(guān)系查詢嵌套處理的詳細(xì)內(nèi)容，更多關(guān)于MyBatis多對(duì)多一對(duì)多嵌套查詢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: