MyBatis如何實(shí)現(xiàn)多表查詢(xún)（多對(duì)一、一對(duì)多）

更新時(shí)間：2021年05月06日 09:53:13 作者：Mq_sir

這篇文章主要給大家介紹了關(guān)于MyBatis如何實(shí)現(xiàn)多表查詢(xún)（多對(duì)一、一對(duì)多）的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

MyBatis實(shí)現(xiàn)多表查詢(xún)

一、多對(duì)一查詢(xún)

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

創(chuàng)建兩張表，一張老師表，一張學(xué)生表

將老師主鍵id關(guān)聯(lián)學(xué)生外鍵tid

創(chuàng)建sql的語(yǔ)句

create table teacher(
   id int  primary key,
	teacher_name varchar(30) not null
)

insert into teacher(id,teacher_name) values (1,'毛老師')

create table student(
  id int  primary key,
	student_name varchar(30) not null,
	tid int default null
)

//建立主外鍵關(guān)聯(lián)
alter table student add constraint teacher_student_id foreign key (tid) references teacher(id)

insert into student values (1,'小明',1)
insert into student values (2,'小毛',1)
insert into student values (3,'小紅',1)
insert into student values (4,'大黃',1)
insert into student values (5,'超兒',1)

項(xiàng)目結(jié)構(gòu)

使用Lombok插件，創(chuàng)建實(shí)體類(lèi)。

（提高整潔度，主要想toulan）

@Data
public class Student {
    private int id;
    private String name;
    //學(xué)生需要關(guān)聯(lián)一個(gè)老師
    private Teacher teacher;
}

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

1、嵌套查詢(xún)處理

編寫(xiě)接口

public interface StudentMapper {
    //查詢(xún)所有學(xué)生的信息以及對(duì)應(yīng)老師的信息
    public List<Student> getStudent();
}

2. 編寫(xiě)StudentMapper.xml的查詢(xún)語(yǔ)句（重點(diǎn)）

<mapper namespace="dao.StudentMapper">
<!--    思路：
        1. 查詢(xún)所有學(xué)生的信息
        根據(jù)查詢(xún)出來(lái)的學(xué)生tid，尋找對(duì)應(yīng)的老師

-->
    <select id="getStudent" resultMap="StudentTeacher">
    select * from student
    </select>
    <resultMap id="StudentTeacher" type="pojo.Student">
<!--        復(fù)雜的屬性需要單獨(dú)處理 是對(duì)象就使用association，是集合就使用collection-->
<!--    select 子查詢(xún)    -->
        <result property="name" column="student_name"/>
        <association property="teacher" column="tid" javaType="pojo.Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="pojo.Teacher">
        select * from teacher where id=#{id}
    </select>

測(cè)試類(lèi)

    @Test
    public void getStudent(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

測(cè)試結(jié)果

2、聯(lián)合查詢(xún)處理

編寫(xiě)接口

//按照結(jié)果嵌套查詢(xún)
public List<Student> getStudent2();

2. 編寫(xiě)StudentMapper.xml的查詢(xún)語(yǔ)句（重點(diǎn)）

<!--    按照結(jié)果嵌套處理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.student_name sname,t.teacher_name tname
    from student s,teacher t
    where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher2" type="pojo.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="pojo.Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

編寫(xiě)測(cè)試類(lèi)

    @Test
    public void getStudent(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

測(cè)試結(jié)果

二、一對(duì)多查詢(xún)

更改實(shí)體類(lèi)

@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<Student> students;
}

1、嵌套查詢(xún)處理編寫(xiě)接口

Teacher getTeacher2(@Param("tid") int id);

由于字段不一致，要做映射

主要TeacherMapper.xml的查詢(xún)語(yǔ)句（重點(diǎn)）

<select id="getTeacher2" resultMap="TeacherStudent2">
            select * from teacher where id=#{tid}
    </select>
    <resultMap id="TeacherStudent2" type="pojo.Teacher">
        <result property="name" column="teacher_name"/>
        <collection property="students" javaType="ArrayList" ofType="pojo.Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="pojo.Student">
        select * from student where tid=#{tid}
    </select>

測(cè)試類(lèi)

   @Test
    public void getTeacher(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher);
        sqlSession.close();
    }

測(cè)試結(jié)果：

Teacher(id=0, name=毛老師, students=[Student(id=1, name=null, tid=1), Student(id=2, name=null, tid=1), Student(id=3, name=null, tid=1), Student(id=4, name=null, tid=1), Student(id=5, name=null, tid=1)])

2、聯(lián)合查詢(xún)處理

編寫(xiě)接口

//獲取指定老師下的所有學(xué)生及老師的信息
    Teacher getTeacher(@Param("tid") int id);

由于字段不一致，要做映射

主要TeacherMapper.xml的查詢(xún)語(yǔ)句（重點(diǎn)）

<!--    按結(jié)果嵌套查詢(xún)-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,s.student_name sname,t.teacher_name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id and t.id=#{tid}
    </select>
    <resultMap id="TeacherStudent" type="pojo.Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--        復(fù)雜的屬性需要單獨(dú)處理 是對(duì)象就使用association，是集合就使用collection
        javaType="" 指定的屬性類(lèi)型
        集合中的泛型信息，使用ofType獲取-->
        <collection property="students" ofType="pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

測(cè)試類(lèi)

   @Test
    public void getTeacher(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }

測(cè)試結(jié)果：

Teacher(id=1, name=毛老師, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小毛, tid=1), Student(id=3, name=小紅, tid=1), Student(id=4, name=大黃, tid=1), Student(id=5, name=超兒, tid=1)])

總結(jié)：

本章就使用了簡(jiǎn)單的兩張表聯(lián)合查詢(xún)，介紹簡(jiǎn)單的使用，更復(fù)雜的多表聯(lián)合主要在編寫(xiě)sql的時(shí)候難度大點(diǎn)，或者是嵌套查詢(xún)要更嚴(yán)謹(jǐn)點(diǎn)

官方文檔也給了詳細(xì)的非常復(fù)雜的多表查詢(xún)?nèi)缦拢?mybatis，這么復(fù)雜的看的我頭疼

<!-- 非常復(fù)雜的語(yǔ)句 -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
  select
       B.id as blog_id,
       B.title as blog_title,
       B.author_id as blog_author_id,
       A.id as author_id,
       A.username as author_username,
       A.password as author_password,
       A.email as author_email,
       A.bio as author_bio,
       A.favourite_section as author_favourite_section,
       P.id as post_id,
       P.blog_id as post_blog_id,
       P.author_id as post_author_id,
       P.created_on as post_created_on,
       P.section as post_section,
       P.subject as post_subject,
       P.draft as draft,
       P.body as post_body,
       C.id as comment_id,
       C.post_id as comment_post_id,
       C.name as comment_name,
       C.comment as comment_text,
       T.id as tag_id,
       T.name as tag_name
  from Blog B
       left outer join Author A on B.author_id = A.id
       left outer join Post P on B.id = P.blog_id
       left outer join Comment C on P.id = C.post_id
       left outer join Post_Tag PT on PT.post_id = P.id
       left outer join Tag T on PT.tag_id = T.id
  where B.id = #{id}
</select>

在我們編寫(xiě)的時(shí)候注意點(diǎn)：

不要忘記注冊(cè)Mapper.xml
在初學(xué)的時(shí)候盡量不要給實(shí)體類(lèi)取別名，為了不要混淆，加深理解
實(shí)體類(lèi)字段要和數(shù)據(jù)庫(kù)字段一致，如果不一致，那就要用result標(biāo)簽做映射
復(fù)雜的屬性需要單獨(dú)處理，是對(duì)象就使用association，是集合就使用collection來(lái)映射
javaType="" 指定的屬性類(lèi)型|
集合中的泛型信息，使用ofType獲取
多注意復(fù)雜屬性的嵌套使用

JavaType & ofType

JavaType 用來(lái)指定實(shí)體類(lèi)中屬性的類(lèi)型
ofType 用來(lái)指定映射到List或者集合中的實(shí)體類(lèi)pojo類(lèi)型，泛型中的約束類(lèi)型

到此這篇關(guān)于MyBatis如何實(shí)現(xiàn)多表查詢(xún)（多對(duì)一、一對(duì)多）的文章就介紹到這了,更多相關(guān)MyBatis多表查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: