MyBatis之两表关联查询

1. 数据库表

学生表:

教师表:

2. 需求

要求查询所有教师的信息, 包括他的学生信息.
查询结果样式:

3. 代码

实体类

pojo包下:

Student.java

package com.xxx.pojo;
public class Student {
    private int id;			// 学生编号
    private String name;	// 学生姓名
    private int age;		// 学生年龄
    private int tid;		// 学生的教师编号
    // get/set此处略
}

Teacher.java

package com.xxx.pojo;
import java.util.List;
public class Teacher {
    private int id;						// 教师编号
    private String name;				// 教师姓名
    private List<Student> students;		// 学生集合
    // get/set, toString此处略
}

mapper包下:

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.xxx.mapper.StudentMapper">
    <!--按tid查询学生-->
    <select id="selByTid" parameterType="int" resultType="student">
        select * from student where tid=#{0}
    </select>
</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.xxx.mapper.TeacherMapper">
   	<!--查询所有教师包含学生信息. id和name与表中列名对应, students不对应, 要用resultMap-->
    <select id="selTeacher" resultMap="mymap">
        select * from teacher
    </select>
	<!--对应的mymap-->
    <resultMap id="mymap" type="teacher">
        <id property="id" column="id"/>
        <!--由于id在下面这个查询中作为参数了, 所以必须再次加载, name属性可不必再次加载-->
        <collection property="students" select="com.xxx.mapper.StudentMapper.selByTid" column="id"/>
    </resultMap>
</mapper>

说明:

对于Teacher实体类, 它包含id, name, 和students. 其中students与表中列名名不一致, 是一个集合.而且是包含学生信息的集合. 所以在查询教师信息的时候就必须查出这个students.
按需求分析, 要想查出所有教师的信息并包含每位教师的学生.这个学生集合可以通过学生tid属性去查, 即通过tid查出每个教师所教的学生.

 <select id="selByTid" parameterType="int" resultType="student">
        select * from student where tid=#{0}
 </select>

例如: 查询tid=2的所有学生, 就是查询教师编号id=2的那位教师(教师id为主键)
所以在查询教师的学生信息时以id为参数, 这个id是教师的id.

MyBatis配置文件: src目录下

mybatis.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>
    <settings>
    	<!--打印日志-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <typeAliases>
    	<!--别名, 包下所有类的别名为类名,不区分大小写-->
        <package name="com.xxx.pojo"/>
    </typeAliases>
    <environments default="default">
        <environment id="default">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/dbname"/>
                <property name="username" value="username"/>
                <property name="password" value="password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/xxx/mapper/StudentMapper.xml"/>
        <mapper resource="com/xxx/mapper/TeacherMapper.xml"/>
    </mappers>
</configuration>

测试类

package com.xxx.test;

import com.xxx.pojo.Teacher;
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;
import java.util.List;

/**
 * @date 2019/8/8 16:32
 */
public class TestMapper {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession();
        List<Teacher> teachers = session.selectList("com.xxx.mapper.TeacherMapper.selTeacher");
        for (Teacher teacher : teachers) {
            System.out.println(teacher);
        }
        session.close();
    }
}

控制台打印结果