MyBatis实现一对多和多对一

  • 作者:rex
  • 分类: 软件开发
  • 时间:2020年9月22日
  • 70人已阅读
简介利用MyBatis实现多对一和一对多

MyBatis实现一对多和多对一

一对多和多对一

一个老师有多个学生,多个学生对应一个老师。

Student实体类

@Data
public class Student{
    private int id; //学生id
    private String name; //学生姓名
    private Teacher teaher; //学生对应的老师
}

Teacher实体类

@Data
public class Teacher{
    private int id; //老师id
    private String name; //老师姓名
    private List<Student> students; //老师对应的学生
}

测试数据库环境的创建SQL

CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师');

CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

查询所有的学生信息以及对应的老师信息

Sql

select s.id sid,s.name sname,t.name tname
from student s,teacher t
where tid = t.id

StudentMapper.java下定义接口getStudents

public interface StudentMapper{
    List<Student> getStudents();
}

MyBatis StudentMapper.xml配置

<select id="getStudents" resultMap="StudentMap">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where tid = t.id  
</select>

<reslutMap id="StudentMap" Type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

TestDao测试类方法编写

public void Test(){
    SqlSession sqlSession = MyBatisUtils.getSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudents();
    for(Student student: studentList){
        System.out.println(student);
    }
    sqlSession.close();
}

按老师的ID查询这个老师的信息以及其所属的全部学生

Sql

select s.id sid, s.name sname, t.id tid, t.name tname
from student s, teacher t
where s.tid = t.id and t.id = #{tid}

TeacherMapper.java编写接口

public interface TeacherMapper{
    Teacher getTeacher(int id);
}

TeacherMapper.xml编写

<select id="getTeacher" resultMap="TeacherMap">
    select s.id sid, s.name sname, t.id tid, t.name tname
    from student s, teacher t
    where s.tid = t.id and t.id = #{tid}
</select>

<resultMap id = "TeacherMap" Type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

TestDao测试类编写

public void test(){
    SqlSession sqlSession = MyBatisUtils.getSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = mapper.getTeacher(1);
    System.out.println(teacher);
    sqlSession.close();
}

文章评论

Top