五、Mybtis实现关联表查询

5.1一对一关联查询

5.1.1创建表和数据
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"
    !-- 根据班级 id 查询班级信息(带老师的信息) ## 1 联表查询 select * from class c,teacher t where 
        c.teacher_id=t.t_id and c.c_id=1; --

    !-- 方式一: 嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据) 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


    !-- 方式二: 嵌套查询:通过执行另外一个 SQL 映射语句来返回预期的复杂类型 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:用于一对一的关联查询
  • property:对象属性名称
  • javaType:对象属性的类型
  • column:所对应的外键字段名称
  • select:使用另一个查询封装的结构
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一对多关联表查询

Paste_Image.png

5.2.1创建表和数据
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 ListStudent list;

    public Classes() {

    }

    public Classes(int id, String name, Teacher teacher, ListStudent 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"
    !-- 根据 classId 查询对应的班级信息,包括学生,老师 --

    !-- 方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集 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

      !--
        方式二:嵌套查询:通过执行另外一个 SQL 映射语句来返回预期的复杂类型
        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:做一对多关联映射查询
  • 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);
        }

}
最新回复(0)
/jishu3_2FSFcY4vUFdTUHYHjIz0H4JA7h8evfzZm0edsQ_3D_3D4795100
8 简首页