mybatis的多对一与一对多

mybatis的多对一

SQL环境的搭建

CREATE TABLE `teacher`(
  `id` INT(10) NOT NULL PRIMARY KEY,
  `name` VARCHAR(30) DEFAULT NULL
    
) ENGINE=INNODB DEFAULT CHARSET = utf8;
INSERT INTO `teacher`(`id`,`name`) VALUE (1,"李老师");

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

• 多个学生对应一个老师
• 对于学生这边而言,关联多个学生关联一个老师[多对一]
• 对于老师而言,集合,一个老师,有很多学生[一对多]

测试环境搭建

1.新建两个实体类

package org.westos.pogo;

public class Teacher {
    private int id;
    private String name;

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

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Teacher(int id) {
        this.id = id;
    }

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }
}

package org.westos.pogo;

public class Student {
    private int id;
    private String name;

    //学生需要关联一个老师
    private Teacher teacher;

    public Student() {
    }

    public Student(int id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

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

2.建立Mapper接口

package org.westos.dao;

public interface StudentMapper {
}

package org.westos.dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.westos.pogo.Teacher;

public interface TeacherMapper {

    @Select("select * from teacher where id = #{id}")
    Teacher getTeacher(@Param("id") int id);
}

注意我这里测试的时候是使用注解测试的.所以在测试的时候,核心配置文件中映射的应该是类不是配置文件.

3.建立Mapper.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">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="org.westos.dao.StudentMapper">
</mapper>

<?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">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="org.westos.dao.TeacherMapper">
</mapper>

4.在核心配置文件中绑定配置,

<mappers>
    <mapper resource="org/westos/dao/*.xml"/>
    <!--<mapper class="org.westos.dao.TeacherMapper"/>-->

</mappers>

6.测试查询能否成功

 @Test
    public void getTeacher(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }

实例SQL

SELECT s.`id`,s.`name`,t.`name` FROM student s, teacher t WHERE s.`tid` = t.`id`

编写接口

package org.westos.dao;
import org.westos.pogo.Student;
import java.util.List;
public interface StudentMapper {
    //查询学生所对应的老师
    List<Student> getStudent();
    List<Student> getStudent2();
}

按照查询嵌套处理

<select id="getStudent" resultMap="StudentTeacher">
	select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
      select * from teacher where id = #{id}

总结

目的是查询对应学生的老师

学生实体类中,老师的类型是Teacher 所以当在学生接口写获取学生对应的老师时, 查询嵌套法逻辑

• 接口在那个实体类实现的先查那个表.
• 然后依次,用resultMap将数据名称对齐,若实体类中存在类型是Java其他对象的类型,则 使用 association 对应你所要已经查出来的数据, 然后指定指令
• 最后在所需要表的查询中使用Where匹配

按照结果嵌套处理

   <select id="getStudent2" resultMap="StudentTeacher2">
        SELECT s.id sid,s.name sname ,t.name tname
        FROM student s, teacher t
        WHERE s.tid stid = t.id tid
    </select>

    <resultMap id="StudentTeacher2" type="Student" >
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="stid"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
            <result property="id" column="tid" />
        </association>
    </resultMap>

这种查询结果相对最好理解,将查询式都写好.然后依次用resultMap 对应所需要的字段

以上这两种的的 结果是一样的但是运行过程不一样,具体需要看源码分析

第一种就属于子查询结构

第二种属于联表查询

11.一对多

比如:一个老师拥有多个学生.

搭建环境

package org.westos.pogo;

import java.util.List;

public class Teacher {
    private int id;
    private String name;
    private List<Student> students;

    public Teacher() {
    }

    public Teacher(int id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

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

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

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

package org.westos.pogo;

public class Student {
    private int id;
    private String name;
    private  int tid;

    public Student() {
    }

    public Student(int id, String name, int tid) {
        this.id = id;
        this.name = name;
        this.tid = tid;
    }

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

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }
}

Teacher接口

package org.westos.dao;

import org.apache.ibatis.annotations.Param;
import org.westos.pogo.Teacher;



public interface TeacherMapper {

    //获取指定老师下的所有学生以及学生信息
    Teacher getTeacher(@Param("tid") int id);
    Teacher getTeacher2(@Param("tid") int id);
}

两种不同的写法.

    <select id="getTeacher" resultMap="getStudent">
        select s.id sid, s.tid stid,s.name sname , t.id tid , t.name tname
        from student s , teacher t
        where s.tid = t.id and  s.tid = #{tid}
    </select>
    <resultMap id="getStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid" />
            <result property="tid" column="stid" />
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
    <!--复杂的属性，我们需要单独处理 对象，Association  集合：collection
    javaTyper="" 指定属性的类型
    集合中的泛型星系，我们使用ofType获取
    -->
    <select id="getTeacher2" resultMap="getStudents">
        select * from teacher where id = #{tid}
    </select>
    <resultMap id="getStudents" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentId" column="id"/>
    </resultMap>
    <select id="getStudentId" resultType="Student">
        select * from student where tid = #{tid}
    </select>