Mybatis一对多关联查询详解

要点

在Student类中添加List<Classes> classList 属性
在Classes类中添加List<Student> students属性

在ClassesMapper.xml中

< resultMap>
…
< collection ofType="Student">
< /collection>
</ resultMap>

在StudentMapper.xml中
< resultMap>
…
< collection ofType="Classes">
< /collection>
</ resultMap>

1.创建数据表

本实例需要两张数据表
student表：

classes表

2.创建实体类

Student：

package com.ytsky.mybatis.demo.entity;

import java.util.List;

public class Student {
    private int id;
    private String stuno;
    private String stuname;
    private String sex;
    private String classno;

    private  List<Classes> classesList;

    public int getId() {
        return id;
    }

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

    public List<Classes> getClassesList() {
        return classesList;
    }

    public void setClassesList(List<Classes> classesList) {
        this.classesList = classesList;
    }

    public String getStuno() {
        return stuno;
    }

    public void setStuno(String stuno) {
        this.stuno = stuno;
    }

    public String getStuname() {
        return stuname;
    }

    public void setStuname(String stuname) {
        this.stuname = stuname;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getClassno() {
        return classno;
    }

    public void setClassno(String classno) {
        this.classno = classno;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", stuno='" + stuno + '\'' +
                ", stuname='" + stuname + '\'' +
                ", sex='" + sex + '\'' +
                ", classno='" + classno + '\'' +
                ", classesList=" + classesList +
                '}';
    }
}

Classes：

package com.ytsky.mybatis.demo.entity;

import java.util.List;

public class Classes {
    private int id;
    private String classno;
    private String name;
    private String major;

    private List<Student> students;

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

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

    public int getId() {
        return id;
    }

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

    public String getClassno() {
        return classno;
    }

    public void setClassno(String classno) {
        this.classno = classno;
    }

    public String getName() {
        return name;
    }

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

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

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

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.ytsky.mybatis.demo.dao.ClassesMapper">
    <select id="selectClassesWithStudent" resultMap="ClassesWithStudentMap">
        select classes.id, classes.classno, name, major
        ,student.id, student.classno, stuno, sex, stuname
        from db_quote.classes
            join db_quote.student
                on db_quote.student.id = db_quote.classes.id
         where db_quote.student.classno="Class001";
    </select>
    <resultMap id="ClassesWithStudentMap" type="Classes">
        <id column="id" property="id" />
        <result column="classno" property="classno" />
        <result column="name" property="name" />
        <result column="major" property="major" />
        <collection property="students" ofType="Student">
            <id column="id" property="id" />
            <result column="classno" property="classno" />
            <result column="stuno" property="stuno" />
            <result column="sex" property="sex" />
        </collection>
    </resultMap>

</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.ytsky.mybatis.demo.dao.StudentMapper">
    <select id="selectStudentWithClasses" resultMap="StudentWithClassesMap">
        select student.id, stuno, stuname, sex, student.classno
            ,classes.id, classes.classno,name, major
        from db_quote.student
            join db_quote.classes
                on classes.id = student.id;
    </select>
    <resultMap id="StudentWithClassesMap" type="Student">
        <id column="id" property="id" />
        <result column="classno" property="classno" />
        <result column="stuno" property="stuno" />
        <result column="stuname" property="stuname" />
        <result column="sex" property="sex" />
        <collection property="classesList" ofType="Classes">
            <id column="id" property="id" />
            <result column="classno" property="classno" />
            <result column="name" property="name" />
            <result column="major" property="major" />
        </collection>
    </resultMap>

</mapper> 

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>
    <properties resource="jdbc.properties"/>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
<!--    <typeAliases>-->
<!--        <typeAlias type="com.ytsky.mybatis.demos.entity.Customer" alias="Customer"/>-->
<!--    </typeAliases>-->
    <typeAliases>
        <package name="com.ytsky.mybatis.demo.entity"/>
    </typeAliases>

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
                <property name="url" value="${jdbc.url}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/CustomerMapper.xml"></mapper>
        <mapper resource="mapper/ProductMapper.xml"></mapper>
        <mapper resource="mapper/ProductTypeMapper.xml"></mapper>
        <mapper resource="mapper/UserMapper.xml"></mapper>
        <mapper resource="mapper/StudentMapper.xml"></mapper>
        <mapper resource="mapper/ClassesMapper.xml"></mapper>
    </mappers>

</configuration> 

4.创建数据操作接口

ClassesMapper:

package com.ytsky.mybatis.demo.dao;

import com.ytsky.mybatis.demo.entity.Classes;

import java.util.List;

public interface ClassesMapper {
    List<Classes> selectClassesWithStudent();
}

StudentMapper:

package com.ytsky.mybatis.demo.dao;

import com.ytsky.mybatis.demo.entity.Student;

import java.util.List;

public interface StudentMapper {
    List<Student>selectStudentWithClasses();
    int updateById(Student student);
}

5.编写测试类

Test1：

package com.ytsky.mybatis.demo.test1;

import com.ytsky.mybatis.demo.dao.ClassesMapper;
import com.ytsky.mybatis.demo.dao.StudentMapper;
import com.ytsky.mybatis.demo.entity.Classes;
import com.ytsky.mybatis.demo.entity.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 java.io.IOException;
import java.util.List;

public class Test1 {
    private static SqlSessionFactory factory;
    static {
        try{
            factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
//        System.out.println(findClassesWithStudent());
        System.out.println(findStudentWithClasses());
    }

    public static List<Classes> findClassesWithStudent(){
        SqlSession session = factory.openSession();
        List<Classes> classes = session.getMapper(ClassesMapper.class).selectClassesWithStudent();
        session.close();
        return classes;
    }
    public static List<Student> findStudentWithClasses(){
        SqlSession session = factory.openSession();
        List<Student> students = session.getMapper(StudentMapper.class).selectStudentWithClasses();
        session.close();
        return students ;
    }

}

6.运行结果