Mybatis多表操作

1.多表操作概述

多表模型分类

一对一：在任意一方建立外键，关联对方的主键

一对多：在多的一方建立外键，关联一的一方的主键

多对多：借助中间表，中间表至少两个字段，分别关联两张表的主键

2.一对一操作

一对一模型： 人和身份证，一个人只有一个身份证

数据准备

sql数据准备：

CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT
);
INSERT INTO person VALUES (NULL,'张三',23);
INSERT INTO person VALUES (NULL,'李四',24);
INSERT INTO person VALUES (NULL,'王五',25);

CREATE TABLE card(
    id INT PRIMARY KEY AUTO_INCREMENT,
    number VARCHAR(30),
    pid INT,
    CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id)
);
INSERT INTO card VALUES (NULL,'12345',1);
INSERT INTO card VALUES (NULL,'23456',2);
INSERT INTO card VALUES (NULL,'34567',3);

bean.Person:

package Mybatis03.bean;

public class Person {
    private Integer id;     //主键id
    private String name;    //人的姓名
    private Integer age;    //人的年龄

    public Person() {
    }

    public Person(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

bean.Card:

package Mybatis03.bean;

public class Card {
    private Integer id;  //主键id
    private String number;  //身份证号
    private Person p;  //所属人的对象

    public Card() {
    }

    public Card(Integer id, String number, Person p) {
        this.id = id;
        this.number = number;
        this.p = p;
    }

    public Integer getId() {
        return id;
    }

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

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Person getP() {
        return p;
    }

    public void setP(Person p) {
        this.p = p;
    }

    @Override
    public String toString() {
        return "Card{" +
                "id=" + id +
                ", number='" + number + '\'' +
                ", p=" + p +
                '}';
    }
}

jdbc.properties:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1
username=root
password=123456

MyBatisConfig.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration 核心根标签-->
<configuration>
    <!--引入数据库连接的配置文件-->
    <properties resource="jdbc.properties"/>
    <!--配置LOG4J：以下name和value是固定写法-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <!--起别名-->
    <typeAliases>
        <package name="mybatis03.bean"/>
</typeAliases>

    <!--集成分页助手插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
    </plugins>

        <!--每一个environment标签都是一个具体的连接数据库的环境 ，default：设置默认使用的环境的id-->
    <environments default="mysql">
        <!--environment配置数据库环境  id属性唯一标识-->
        <environment id="mysql">
            <!-- transactionManager事务管理。  type属性，采用JDBC默认的事务-->
            <transactionManager type="JDBC"/>
            <!-- 设置数据源的类型，以下表示使用数据库连接池缓存数据库连接-->
            <dataSource type="POOLED">
                <!-- property获取数据库连接的配置信息 -->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--mappers引入映射配置文件-->
    <mappers>
        <!--mapper引入指定的映射配置文件-->
        <mapper resource="Mybatis03/one_to_one/OneToOneMapper.xml"/>
    </mappers>
</configuration>

功能实现

<resultMap>：配置字段和对象属性的映射关系标签。
    id 属性：唯一标识
    type 属性：实体对象类型
<id>：配置主键映射关系标签。
<result>：配置非主键映射关系标签。
    column 属性：表中字段名称
    property 属性： 实体对象变量名称
<association>：配置被包含对象的映射关系标签。
    property 属性：被包含对象的变量名
    javaType 属性：被包含对象的数据类型
 

新建one_to_one包存放对应映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Mybatis03.table01.OneToOneMapper">
    <!--配置字段和实体对象属性的映射关系-->
    <resultMap id="oneToOne" type="card">
        <!--id是给主键配置的-->
        <id column="cid" property="id"/>
        <result column="number" property="number"/>
        <!--
        association：配置被包含对象的映射关系
        property：被包含对象的变量名
        javaType：被包含对象的数据类型
        -->
        <association property="p" javaType="person">
            <id column="pid" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="OneToOne">
        SELECT c.id cid,number,pid,name,age FROM card c,person p WHERE c.pid=p.id
    </select>
</mapper>

创建接口table01.OneToOneMapper

package table01;

import Mybatis03.bean.Card;

import java.util.List;

public interface OneToOneMapper {
    //查询全部
    public abstract List<Card> selectAll();
}

测试类：

package table01;

import Mybatis03.bean.Card;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;


import java.io.InputStream;
import java.util.List;

public class Test01 {
    @Test
    public void selectAll() throws Exception{
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        // 2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取StudentMapper接口的实现类对象
        OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);
        //5.调用实现类的方法，接收结果
        List<Card> list = mapper.selectAll();
        //6.处理结果
        for(Card c : list){
            System.out.println(c);
        }
        //7.释放资源
        sqlSession.close();
        is.close();
    }
}

3.一对多操作 

数据准备

一对多模型： 一对多模型：班级和学生，一个班级可以有多个学生

sql数据准备：

CREATE TABLE classes(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
INSERT INTO classes VALUES (NULL,'一班');
INSERT INTO classes VALUES (NULL,'二班');

CREATE TABLE student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(30),
  age INT,
  cid INT,
  CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
);
INSERT INTO student VALUES (NULL,'张三',23,1);
INSERT INTO student VALUES (NULL,'李四',24,1);
INSERT INTO student VALUES (NULL,'王五',25,2);
INSERT INTO student VALUES (NULL,'赵六',26,2);

 bean.Classes：

package Mybatis03.bean;

import java.util.List;

public class Classes {
    private Integer id;     //主键id
    private String name;    //班级名称
    private List<Student> students; //班级中所有学生对象		一个班级里多个学生

    public Classes() {
    }

    public Classes(Integer id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", students=" + students +
                '}';
    }
}

bean.Student：

package Mybatis03.bean;

public class Student {
    private Integer id;     //主键id
    private String name;    //学生姓名
    private Integer age;    //学生年龄

    public Student() {
    }

    public Student(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

功能实现

<resultMap>：配置字段和对象属性的映射关系标签。
    id 属性：唯一标识
    type 属性：实体对象类型
<id>：配置主键映射关系标签。
<result>：配置非主键映射关系标签。
    column 属性：表中字段名称
    property 属性： 实体对象变量名称
<collection>：配置被包含集合对象的映射关系标签。
    property 属性：被包含集合对象的变量名
    ofType 属性：集合中保存的对象数据类型
 

新建one_to_many包存放映射配置文件

<?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="Mybatis03.table02.OneToManyMapper">
    <resultMap id="OneToMany" type="classes">
        <id column="cid" property="id"/>
        <result column="cname" property="name"/>

        <!--
            collection：配置被包含的集合对象映射关系（如果被包含的对象是多的一方，需要用collection）
            property：被包含对象的变量名
            ofType：被包含对象的实际数据类型（collection标签中指定被包含对象类型用的是ofType）
        -->
        <collection property="students" ofType="student">
            <id column="sid" property="id"/>
            <result column="sname" property="name"/>
            <result column="sage" property="age"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="oneToMany">
        SELECT c.id cid,c.name cname,s.id sid,s.name sname,s.age sage FROM classes c,student s WHERE c.id=s.cid
    </select>
</mapper>

配置映射文件： 

<mappers>
        <!--mapper引入指定的映射配置文件-->
        <mapper resource="Mybatis03/one_to_one/OneToOneMapper.xml"/>
        <mapper resource="Mybatis03/one_to_one/OneToManyMapper.xml"/>
    </mappers>

创建接口：

package Mybatis03.table02;

import Mybatis03.bean.Classes;

import java.util.List;

public interface OneToManyMapper {
    //查询全部
    public abstract List<Classes> selectAll();
}

测试类：

package Mybatis03.table02;

import Mybatis03.bean.Classes;
import Mybatis03.bean.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.InputStream;
import java.util.List;

public class Test01 {
    @Test
    public void selectAll() throws Exception {
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取OneToManyMapper接口的实现类对象
        OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);
        //5.调用实现类的方法，接收结果
        List<Classes> classes = mapper.selectAll();
        //6.处理结果
        for(Classes cls : classes){
            System.out.println(cls.getId()+","+cls.getName());
            List<Student> students = cls.getStudents();
            for(Student student : students){
                System.out.println("\t"+student);
            }
        }
        //7.释放资源
        sqlSession.close();
        is.close();
    }
}

4.多对多操作

数据准备

多对多模型：学生和课程，一个学生可以选择多门课程、一个课程也可以被多个学生所选择

sql数据准备：

CREATE TABLE course(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
INSERT INTO course VALUES (NULL,'语文');
INSERT INTO course VALUES (NULL,'数学');

CREATE TABLE stu_cr(
  id INT PRIMARY KEY AUTO_INCREMENT,
  sid INT,
  cid INT,
  CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
  CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
);
INSERT INTO stu_cr VALUES (NULL,1,1);
INSERT INTO stu_cr VALUES (NULL,1,2);
INSERT INTO stu_cr VALUES (NULL,2,1);
INSERT INTO stu_cr VALUES (NULL,2,2);

bean.Course：

package Mybatis03.bean;

public class Course {
    private Integer id;     //主键id
    private String name;    //课程名称

    public Course() {
    }

    public Course(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

修改bean.Student：

package Mybatis03.bean;


import java.util.List;

public class Student {
    private Integer id; //主键id
    private String name;//学生姓名
    private Integer age;//学生年龄
    private List<Course> courses;//学生所选择的课程集合

    public Student() {
    }

    public Student(Integer id, String name, Integer age, List<Course> courses) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.courses = courses;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public List<Course> getCourses() {
        return courses;
    }

    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", courses=" + courses +
                '}';
    }
}

功能实现 

<resultMap>：配置字段和对象属性的映射关系标签。
    id 属性：唯一标识
    type 属性：实体对象类型
 <id>：配置主键映射关系标签。
 <result>：配置非主键映射关系标签。
    column 属性：表中字段名称
    property 属性： 实体对象变量名称
<collection>：配置被包含集合对象的映射关系标签。
    property 属性：被包含集合对象的变量名
    ofType 属性：集合中保存的对象数据类型

配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!--MyBatis的DTD约束-->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Mybatis03.table03.ManyToManyMapper">
    <resultMap id="ManyToMany" type="student">
        <id column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="sage" property="age"/>
        <collection property="courses" ofType="course">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="ManyToMany">
        SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHERE sc.sid=s.id AND sc.cid=c.id
    </select>
</mapper>

 配置映射文件：

<mappers>
        <!--mapper引入指定的映射配置文件-->
        <mapper resource="Mybatis03/one_to_one/OneToOneMapper.xml"/>
        <mapper resource="Mybatis03/one_to_one/OneToManyMapper.xml"/>
        <mapper resource="Mybatis03/one_to_one/ManyToManyMapper.xml"/>
    </mappers>

接口：

package Mybatis03.table03;

import Mybatis03.bean.Student;

import java.util.List;

public interface ManyToManyMapper {
    //查询全部
    public abstract List<Student> selectAll();
}

测试类：

package Mybatis03.table03;

import Mybatis03.bean.Course;
import Mybatis03.bean.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.InputStream;
import java.util.List;

public class Test01 {
    @Test
    public void selectAll() throws Exception {
        //1.加载核心配置文件
        InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
        //2.获取SqlSession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //3.通过工厂对象获取SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //4.获取ManyToManyMapper接口的实现类对象
        ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);
        //5.调用实现类的方法，接收结果
        List<Student> students = mapper.selectAll();
        //6.处理结果
        for (Student student : students) {
            System.out.println(student.getId()+","+student.getName()+","+student.getAge());
            List<Course> courses = student.getCourses();
            for(Course course : courses){
                System.out.println("\t"+course);
            }
        }
        //7.释放资源
        sqlSession.close();
        is.close();
    }
}