五、Mybtis實現(xiàn)關(guān)聯(lián)表查詢

5.1一對一關(guān)聯(lián)查詢

5.1.1創(chuàng)建表和數(shù)據(jù)

CREATE TABLE teacher(
   t_id INT PRIMARY KEY AUTO_INCREMENT,
   t_name VARCHAR(20)
);

CREATE TABLE class(
   c_id INT PRIMARY KEY AUTO_INCREMENT,
   c_name VARCHAR(20),teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCESteacher(t_id);
INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

5.1.2定義兩個實體類

Classes.java

package com.entity;

public class Classes {

    private int id;
    private String name;
    private Teacher teacher;
    public Classes()
    {
        
    }
    public Classes(int id, String name, Teacher teacher) {
        super();
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    @Override
    public String toString() {
        return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher
                + "]";
    }
    
}

Teacher.java

package com.entity;

public class Teacher {
    private int id;
    private String name;
    public Teacher()
    {
        
    }
    public Teacher(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + "]";
    }
    

}

5.1.3定義映射文件

ClassesMapper.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.config.ClassMapper">
    <!-- 根據(jù)班級 id 查詢班級信息(帶老師的信息) ## 1 聯(lián)表查詢 select * from class c,teacher t where 
        c.teacher_id=t.t_id and c.c_id=1; -->

    <!-- 方式一： 嵌套結(jié)果：使用嵌套結(jié)果映射來處理重復(fù)的聯(lián)合結(jié)果的子集 封裝聯(lián)表查詢的數(shù)據(jù)(去除重復(fù)的數(shù)據(jù)) select * from class 
        c, teacher t where c.teacher_id=t.t_id and c.c_id=1 -->
    <select id="getClass" parameterType="int" resultMap="getClassMap">
        select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id};
    </select>

    <resultMap id="getClassMap" type="com.entity.Classes">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" javaType="com.entity.Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
    </resultMap>


    <!-- 方式二： 嵌套查詢：通過執(zhí)行另外一個 SQL 映射語句來返回預(yù)期的復(fù)雜類型 SELECT * FROM class WHERE c_id=1; 
        SELECT * FROM teacher WHERE t_id=1 //1 是上一個查詢得到的 teacher_id 的值 -->
    <select id="getClass2" parameterType="int" resultMap="getClass2Map">
        SELECT * FROM class WHERE c_id=#{id};
    </select>

    <select id="getTeacher" parameterType="int" resultType="com.entity.Teacher">
        SELECT t_id id,t_name name FROM teacher WHERE t_id=#{id}
    </select>

    <resultMap id="getClass2Map" type="com.entity.Classes">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            select="getTeacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
    </resultMap>


</mapper>
  • association：用于一對一的關(guān)聯(lián)查詢
  • property：對象屬性名稱
  • javaType：對象屬性的類型
  • column：所對應(yīng)的外鍵字段名稱
  • select：使用另一個查詢封裝的結(jié)構(gòu)

5.1.4config.xml中注冊ClassMapper.xml

<mappers>
      <mapper resource="com/config/ClassMapper.xml"/>
</mappers>

5.1.5測試類

package com.test;

import java.io.IOException;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.entity.Classes;
import com.util.MybatisUtil;

public class Test4 {

    //第一種方式的測試
    @Test
    public void getClass1() throws IOException
    {
        SqlSession session=MybatisUtil.getSession();
        String statement="com.config.ClassMapper.getClass";
        
        Classes classes=session.selectOne(statement, 1);
        session.close();
        System.out.println(classes);
    }
    
    //第二種方式的測試
        @Test
        public void getClass2() throws IOException
        {
            SqlSession session=MybatisUtil.getSession();
            String statement="com.config.ClassMapper.getClass2";
            
            Classes classes=session.selectOne(statement, 1);
            session.close();
            System.out.println(classes);
        }
}

5.2一對多關(guān)聯(lián)表查詢

Paste_Image.png

5.2.1創(chuàng)建表和數(shù)據(jù)

CREATE TABLE student(
    s_id INT PRIMARY KEY AUTO_INCREMENT,
    s_name VARCHAR(20),class_id INT
);
INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

5.2.2定義實體類

Classes.java

package com.entity;

import java.util.List;

public class Classes {

    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> list;

    public Classes() {

    }

    public Classes(int id, String name, Teacher teacher, List<Student> list) {
        super();
        this.id = id;
        this.name = name;
        this.teacher = teacher;
        this.list = list;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher
                + ", list=" + list + "]";
    }

}

5.2.3定義 **sql **映射文件 Class2Mapper.xml

Class2Mapper.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.config.Class2Mapper">
    <!-- 根據(jù) classId 查詢對應(yīng)的班級信息,包括學(xué)生,老師 -->

    <!-- 方式一: 嵌套結(jié)果: 使用嵌套結(jié)果映射來處理重復(fù)的聯(lián)合結(jié)果的子集 SELECT * FROM class c,student s WHERE 
        c.C_id=s.class_id AND c.c_id=#{id} -->

    <select id="getClass" parameterType="int" resultMap="getClassMap">
        SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=#{id}
    </select>

    <resultMap id="getClassMap" type="com.entity.Classes">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" javaType="com.entity.Teacher">
             <id property="id" column="t_id" />
             <result property="name" column="t_name" />
        </association>
        <collection property="list" ofType="com.entity.Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>
    
      <!--
        方式二：嵌套查詢：通過執(zhí)行另外一個 SQL 映射語句來返回預(yù)期的復(fù)雜類型
        SELECT * FROM class WHERE c_id=1;
        SELECT * FROM teacher WHERE t_id=1 //1 是上一個查詢得到的 teacher_id 的值
        SELECT * FROM student WHERE class_id=1 //1 是第一個查詢得到的 c_id 字段的值
        -->
     <select id="getClass2" resultMap="getClass2Map">
         SELECT * FROM class WHERE c_id=#{id};
     </select>
     
     <select id="getTeacher" resultType="com.entity.Teacher">
         SELECT t_id id,t_name name FROM teacher WHERE t_id=#{id}
     </select>
     
     <select id="getStudent" resultType="com.entity.Student">
         SELECT s_id id,s_name name FROM student WHERE class_id=#{id}
     </select>
     
     <resultMap id="getClass2Map" type="com.entity.Classes">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id" select="getTeacher">
        </association>
        <collection property="list" column="c_id" select="getStudent">
        
        </collection>
    </resultMap>
</mapper>
  • collection：做一對多關(guān)聯(lián)映射查詢
  • ofType：指點集合中元素對象類型

5.2.4將映射文件注冊到config.xml中

config.xml

<mappers>
      <mapper resource="com/config/Class2Mapper.xml"/>
</mappers>

5.2.5測試類

Test5.java

package com.test;

import java.io.IOException;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;



import com.entity.Classes;
import com.util.MybatisUtil;

public class Test5 {
    
    //方式一
    @Test
    public void testSelect1() throws IOException
    {
        SqlSession session=MybatisUtil.getSession();
        String statement="com.config.Class2Mapper.getClass";
        
        Classes classes=session.selectOne(statement, 1);
        session.close();
        System.out.println(classes);
    }
    
    //方式二
        @Test
        public void testSelect2() throws IOException
        {
            SqlSession session=MybatisUtil.getSession();
            String statement="com.config.Class2Mapper.getClass2";
            
            Classes classes=session.selectOne(statement, 1);
            session.close();
            System.out.println(classes);
        }

}
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 1. Java基礎(chǔ)部分 基礎(chǔ)部分的順序：基本語法，類相關(guān)的語法，內(nèi)部類的語法，繼承相關(guān)的語法，異常的語法，線程的語...
    子非魚_t_閱讀 34,728評論 18贊 399
  • Spring Cloud為開發(fā)人員提供了快速構(gòu)建分布式系統(tǒng)中一些常見模式的工具（例如配置管理，服務(wù)發(fā)現(xiàn)，斷路器，智...
    卡卡羅2017閱讀 136,569評論 19贊 139
  • 1. 簡介 1.1 什么是 MyBatis ？ MyBatis 是支持定制化 SQL、存儲過程以及高級映射的優(yōu)秀的...
    笨鳥慢飛閱讀 6,248評論 0贊 4
  • “人神共奮說紅樓”系列之18：兩個為科舉所困的作家，如何使用他們的“陽具之筆”？ “不想科舉”的賈寶玉的人生理想是...
    人神共奮閱讀 597評論 0贊 7
  • 琳瑯菁選：你缺的，可能只是一枚讓人怦然心動的書簽 過去的30年里 Silverleaf一直熱愛書籍和珠寶 最近幾年...
    琳瑯leanlong閱讀 904評論 0贊 0

友情鏈接更多精彩內(nèi)容