Mybatis中一对多关联表查询

整体目录结构

sql语句

drop database if exists course;
create database course;
use course;

create table if not exists student(
    id int primary key auto_increment comment '学号',
    name varchar(4) not null unique comment '姓名',
    sex  char(1)  not null comment '性别',
    classId int not null comment '班级',
    foreign key(classId) references class(id)
);

create table if not exists class(
    id int primary key auto_increment comment '班级id',
    name varchar(4) not null unique comment '班级名称',
    greadeYear datetime  comment '毕业年份'
);

show tables;

insert into class(name,greadeYear) values
('计算机',now()),
('英语',now()),
('数学',now());

insert into student(name,sex,classId) values
('张三','男',1),
('李四','男',2),
('王五','男',3),
('黑凤梨','女',1);

实体类

model.classes

package model;

import java.sql.Timestamp;
import java.util.List;

/**
 * ClassName:Class
 * Package:model
 * Description:
 *
 * @Date:2020/7/30 9:06
 * @Author:DangWei
 */
public class Classes {
    private Integer id;
    private String name;
    private Timestamp greadeYear;

    private List<Student> students;

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

    public void setStudents(List<Student> students) {
        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 Timestamp getGreadeYear() {
        return greadeYear;
    }

    public void setGreadeYear(Timestamp greadeYear) {
        this.greadeYear = greadeYear;
    }

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

model.Student

package model;

/**
 * ClassName:User
 * Package:model
 * Description:
 *
 * @Date:2020/7/29 8:09
 * @Author:DangWei
 */
public class Student {
   private Integer id;
   private String name;
   private String sex;
   private Integer classId;

    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 String getSex() {
        return sex;
    }

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

    public Integer getClassId() {
        return classId;
    }

    public void setClassId(Integer classId) {
        this.classId = classId;
    }

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

Dao接口

ClassesDao

package Dao;

import model.Classes;

import java.util.List;

/**
 * ClassName:ClassDao
 * Package:Dao
 * Description:
 *
 * @Date:2020/7/30 9:29
 * @Author:DangWei
 */
public interface ClassesDao {

    List<Classes> selectClasses();
}

StudentDao

package Dao;

import model.Student;

import java.util.List;

/**
 * ClassName:UserDAO
 * Package:Dao
 * Description:
 *
 * @Date:2020/7/29 8:10
 * @Author:DangWei
 */
public interface StudentDao {

    List<Student> selectStudent();


}

xml映射文件

ClassDao.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="Dao.ClassesDao">

    <resultMap id="result" type="model.Classes">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="greadeYear" column="greadeYear"/>
    </resultMap>

    <resultMap id="student" type="model.Classes" extends="result">
        <collection property="students" ofType="model.Student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="sex"  property="sex" />
            <result column="classId" property="classId"/>
        </collection>
    </resultMap>

    <select id="selectClasses" resultMap="student">
        select * from class c join student s on c.id=s.classId
    </select>


</mapper>

StudentDao.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="Dao.StudentDao">
    <resultMap id="result" type="model.Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="sex"  property="sex" />
        <result column="classId" property="classId"/>
    </resultMap>




    <select id="selectStudent" resultMap="result">
        select * from student
    </select>


</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>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/course?characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="Dao/StudentDao.xml"/>
        <mapper resource="Dao/ClassesDao.xml"/>
    </mappers>

</configuration>

测试类

public class TestMybatis {
    public static void main(String[] args) throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");

        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession session=factory.openSession();

        StudentDao studentDao=session.getMapper(StudentDao.class);

        List<Student> students=studentDao.selectStudent();
        for(Student student:students){
            System.out.println(student);
        }

        System.out.println("==========================我是分割线==========================");

        ClassesDao classesDao=session.getMapper(ClassesDao.class);
        List<Classes> classesList=classesDao.selectClasses();
        for(Classes item:classesList){
            System.out.println(item);
        }
    }
}

输出结果

Student{id=1, name='张三', sex='男', classId=1}
Student{id=2, name='李四', sex='男', classId=2}
Student{id=3, name='王五', sex='男', classId=3}
Student{id=4, name='黑凤梨', sex='女', classId=1}
==========================我是分割线==========================
Classes{id=1, name='计算机', greadeYear=2020-07-30 09:00:06.0, students=[Student{id=1, name='计算机', sex='男', classId=1}]}
Classes{id=2, name='英语', greadeYear=2020-07-30 09:00:06.0, students=[Student{id=2, name='英语', sex='男', classId=2}]}
Classes{id=3, name='数学', greadeYear=2020-07-30 09:00:06.0, students=[Student{id=3, name='数学', sex='男', classId=3}]}

本文地址：https://blog.csdn.net/Mainhxj/article/details/107683341