Mybatis系列–11-多对一详解

概述

本文主要讲述多对一的关系，多对一的例子生活中很多，比如老师和学生分别在数据库中对应两张表，学生表中又存在一列老师的tid，来存储其对应的班主任信息，学生与老师的关系可以看做是多对一的关系，也称为关联关系，老师与学生的关系是一对多的关系，也叫做集合关系

实践

实践部分主要讲述如何通过Mybatis实现查询出每个学生对应的班主任的信息

方法一 按照查询嵌套处理

0. 数据库中简历使用的表及数据

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');

1. pom.xml依赖文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>Mybatis-Study</artifactId>
        <groupId>org.example</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>Mybatis-Study05</artifactId>
    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
</project>

2. 老师实体类Teacher.java

package com.kuang.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 功能描述
 *
 * @since 2022-07-20
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

3. 学生实体类Student.java

package com.kuang.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 功能描述
 *
 * @since 2022-07-20
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

4. StudentMapper.java文件

package com.kuang.dao;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.kuang.pojo.Student;
import com.kuang.pojo.Teacher;

/**
 * 功能描述
 *
 * @since 2022-07-20
 */
public interface StudentMapper {
    @Select("select * from `student`")
    List<Student> getList();

    Teacher getTeacher(int id);

    List<Student> getStudentList();

}

5. TeacherMapper.java文件

package com.kuang.dao;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.kuang.pojo.Teacher;

/**
 * 功能描述
 *
 * @since 2022-07-20
 */
public interface TeacherMapper {
    @Select("select * from `teacher`")
    List<Teacher> getList();
}

6. Mybatis-config.xml配置文件 及db.properties文件

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"></properties>
    <!--    <typeAliases>-->
    <!--        <typeAlias type="com.com.kuang.pojo.User" alias="User"></typeAlias>-->
    <!--    </typeAliases>-->
    <typeAliases>
        <package name="com.com.kuang.pojo" />
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.kuang.dao" />
    </mappers>

</configuration>

db.properties

driver=com.mysql.jdbc.Driver
url=xxx
username=root
password=xxxx

7. Mybatis工具类

package com.kuang.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * 功能描述
 *
 * @since 2022-07-13
 */
public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {

        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = null;
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static SqlSession getSqlsession() {
        return sqlSessionFactory.openSession(true);
    }
}

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.dao.StudentMapper">
    <select id="getTeacher" resultType="com.kuang.pojo.Teacher">
        select *
        from teacher
        where id = #{id}
    </select>

    <select id="getStudentList" resultMap="StudentTeacher">
        select *
        from student
    </select>

    <resultMap id="StudentTeacher" type="com.kuang.pojo.Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <association property="teacher" column="tid" javaType="com.kuang.pojo.Teacher"
            select="getTeacher"></association>
    </resultMap>
</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.dao.TeacherMapper">
</mapper>

测试代码

import java.util.List;

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

import com.kuang.dao.StudentMapper;
import com.kuang.dao.TeacherMapper;
import com.kuang.pojo.Student;
import com.kuang.pojo.Teacher;
import com.kuang.util.MyBatisUtil;

/**
 * 功能描述
 *
 * @since 2022-07-20
 */
public class MyTest {

    @Test
    public void testStudent() {
        SqlSession sqlsession = MyBatisUtil.getSqlsession();
        StudentMapper studentMapper = sqlsession.getMapper(StudentMapper.class);
        List<Student> list = studentMapper.getStudentList();
        for (Student s : list) {
            System.out.println(s);
        }
        sqlsession.close();
    }
}

方法二 按照结果嵌套处理

StudentMappper.xml 文件添加以下内容

    <select id="getStudentList2" resultMap="StudentTeacher2">
        select s.id sid, s.name sname, s.tid stid, t.id, tid, t.name tname
        from student s,
             teacher t
        where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher2" type="com.kuang.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <association property="teacher" javaType="com.kuang.pojo.Teacher">
            <result property="id" column="tid"></result>
            <result property="name" column="tname"></result>
        </association>
    </resultMap>

StudentMappper.java 文件添加以下内容

    List<Student> getStudentList2();

测试代码添加以下文件

    @Test
    public void testStudent2() {
        SqlSession sqlsession = MyBatisUtil.getSqlsession();
        StudentMapper studentMapper = sqlsession.getMapper(StudentMapper.class);
        List<Student> list = studentMapper.getStudentList2();
        for (Student s : list) {
            System.out.println(s);
        }
        sqlsession.close();
    }