mybatis 多对一查询的两种实现方式

第一步 搭建运行环境

1.创建Mysql数据库、表，插入几条数据

CREATE DATABASE `mybatis` 
USE mybatis;

CREATE TABLE `teacher` (
  `id` int(4) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `tid` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_tid` (`tid`),
  CONSTRAINT `FK_tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、创建一个普通的maven项目

3、导入lombok jar包

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.18</version>
    <scope>provided</scope>
</dependency>　

4、连接到数据库的准备工作

数据库的配置文件 db.properties

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username = root
pwd = your_password

mybatis的核心配置文件 mybatis-config.xml

<?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>
    <!--法二：读取外部配置文件，为property赋值-->
    <properties resource="db.properties">
        <property name="pwd" value="123456"/><!--优先读取外部文件中的同名属性-->
    </properties>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <typeAliases>
        <!--<typeAlias type="com.User" alias="User"/>-->
        <package name="com.xiahui.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="${pwd}"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

工具类 MybatisUtils 

package com.xiahui.utils;

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 java.io.IOException;
import java.io.InputStream;

//通过sqlSessionFactory生成SqlSession
public class MybatisUtils {
    static private SqlSessionFactory sqlSessionFactory;
    static{
        try{
            //获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取SqlSession实例
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
}

 

第二步 编写代码　

1、创建实体类Teacher和Student，多个学生听一个老师上课，多对一关系

Student 

package com.xiahui.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

Teacher

package com.xiahui.pojo;

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

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

2、创建Mapper接口

StudentMapper

package com.xiahui.dao;

import com.xiahui.pojo.Student;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentMapper {
   /* @Select("select * from student")*/
    List<Student> getAllStudent();
    List<Student> getAllStudent2();

}

3、创建Mapper.xml文件

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.xiahui.dao.StudentMapper">
     <!--查询多对一，方法一：查询嵌套-->
        <select id="getAllStudent" resultMap="studentTeacher">
            select * from student
        </select>
       <resultMap id="studentTeacher" type="Student">
            <result property="id" column="id" />
            <result property="name" column="name" />
            <association property="teacher" column="tid" select="getTeacher"/>
        </resultMap>
        <select id="getTeacher" resultType="Teacher">
            select * from teacher where id=#{tid}
        </select>

    <!--查询方法二：结果嵌套-->
    <select id="getAllStudent2" resultMap="studentTeacher2">
        select * from student, teacher where student.tid=teacher.id
    </select>
    <resultMap id="studentTeacher2" type="Student">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <association property="teacher" column="tid" javaType="Teacher">
            <result property="id" column="id" />
            <result property="name" column="name" />
        </association>
    </resultMap>

</mapper>

 

4、在MyBatis的核心配置文件中注册Mapper接口或其xml文件

在 mybatis-config.xml <configuration></configuration>标签内配置

<mappers>
      <mapper resource="com/xiahui/dao/StudentMapper.xml"/>
 </mappers>

 

第三步 测试

package com.xiahui.dao;

import com.xiahui.pojo.Student;
import com.xiahui.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class StudentTest {
    @Test
    public void getAllStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> allStudent = mapper.getAllStudent();
        System.out.println(allStudent);
        sqlSession.close();
    }

    @Test
    public void getAllStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> allStudent = mapper.getAllStudent2();
        System.out.println(allStudent);
        sqlSession.close();
    }
}

 

结果嵌套更加清晰简单，推荐使用它处理多对一的查询

参考 https://www.bilibili.com/video/BV1NE411Q7Nx?p=20