欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mybatis中一对多关联表查询

程序员文章站 2022-04-15 23:15:29
整体目录结构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...

整体目录结构

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

相关标签: 框架