Sunny带你半小时掌握Mybati关联映射

大阳光男孩

已于 2022-04-26 19:33:13 修改

阅读量468

点赞数 2

文章标签： mysql java sql

于 2022-04-26 12:37:33 首次发布

本文链接：https://blog.csdn.net/m0_55337678/article/details/124425068

版权

什么是多对一？

生活中常见多对一关系的例子，在学校你会发现你的班级只有一个，但是班级可以拥有多个学生，那么这就是一个典型的多对一关系，可以通过对象进行关系的表示，多对一的图解如下：

环境需要的SQL脚本：

CREATE TABLE `studentclass` (
	`id` INT(10) NOT NULL,
	`classNumber` VARCHAR(30) DEFAULT NULL,
	PRIMARY KEY (`id`)
);

INSERT INTO studentclass(`id`,`classNumber`) VALUES (08090101,'计算机与科学1班');

CREATE TABLE `Universitystudent` (
	`id` INT(10) NOT NULL,
	`name` VARCHAR(30) DEFAULT NULL,
	`cid` INT(10) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `fkcid`(`cid`),
	CONSTRAINT `fkcid` FOREIGN KEY (`cid`) REFERENCES `studentclass` (`id`)
);

INSERT INTO `Universitystudent`(`id`,`name`,`cid`) VALUES (1,'李阳',08090101);
INSERT INTO `Universitystudent`(`id`,`name`,`cid`) VALUES (2,'程奕霖',08090101);
INSERT INTO `Universitystudent`(`id`,`name`,`cid`) VALUES (3,'谢雨萱',08090101);
INSERT INTO `Universitystudent`(`id`,`name`,`cid`) VALUES (4,'王二蛋',08090101);
INSERT INTO `Universitystudent`(`id`,`name`,`cid`) VALUES (5,'陈杰',08090101);

1.建和数据库相对于的映射类Universitystudent.java和Studentclass.java

Universitystudent.java

public class Universitystudent {
    private int id;
    private String name;
    private Studentclass studentclass;

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

    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 Studentclass getStudentclass() {
        return studentclass;
    }

    public void setStudentclass(Studentclass studentclass) {
        this.studentclass = studentclass;
    }
}

Studentclass.java

public class Studentclass {
    private int id;
    private String classNumber;

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

    public int getId() {
        return id;
    }

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

    public String getClassNumber() {
        return classNumber;
    }

    public void setClassNumber(String classNumber) {
        this.classNumber = classNumber;
    }
}

2.在建对应的Mapper接口和文件Mapper映射文件

UniversitystudentMapper接口

public interface UniversitystudentMapper {
    //需求：查询班级下所对于的学生,第一个是按照查询嵌套处理
    List<Universitystudent> findUniversityStudent();
    //这个是按照查询嵌套处理
    List<Universitystudent> findUniversityStudentTwo();
}

UniversitystudentMapper.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="top.yunhuisu.mapper.UniversitystudentMapper">
    <!-- 1.按照查询嵌套处理 -->
    <select id="findUniversityStudent" resultMap="getStudent">
        SELECT * FROM universitystudent
    </select>
    <!-- 嵌套查询 -->
    <select id="getStudentClass" resultType="studentclass">
        SELECT * FROM studentclass WHERE id = #{cid}
    </select>
    <resultMap id="getStudent" type="universitystudent">
        <association
                property="studentclass"
                column="cid"
                javaType="studentclass"
                select="getStudentClass"
        ></association>
    </resultMap>
    <!-- 2.按照结果嵌套处理 -->
    <select id="findUniversityStudentTwo" resultMap="getStudentTwo">
        SELECT
               us.id sid,us.`name` sname,sc.classNumber Class
        FROM
               studentclass sc,universitystudent us
        WHERE
               us.cid = sc.id;
    </select>
    <resultMap id="getStudentTwo" type="universitystudent">
        <id property="id" column="sid"></id>
        <result property="name" column="sname"></result>
        <association property="studentclass" javaType="studentclass">
            <result property="classNumber" column="Class"></result>
        </association>
    </resultMap>
</mapper>

3.测试代码

@Test
    public void testFindUniversityStudent() {
        MybatisUtils mybatisUtils = new MybatisUtils();
        //注意哈，我这样偷懒用的链式写法哈，正常写不要这样干哈
        mybatisUtils.
                getSqlSession().
                getMapper(UniversitystudentMapper.class).
                findUniversityStudent().
                forEach(System.out::println);
        mybatisUtils.
                getSqlSession().
                getMapper(UniversitystudentMapper.class).
                findUniversityStudentTwo().
                forEach(System.out::println);
    }

运行图

什么是一对多？

我们还是刚刚上面多对一的例子讲解，在学校你会发现有很多的学生，每个学生都只有一个班级，但是班级可以拥有多个学生，那么这就是一个典型的一对多关系，可以通过对象进行关系的表示，多对一的图解如下：

环境需要的SQL脚本不变

1.我们需要改一下我们刚刚那两个实体类

Studentclass.java

public class Studentclass {
    private int id;
    private String classNumber;
    List<Universitystudent> universitystudents;

    @Override
    public String toString() {
        return "Studentclass{" +
                "id=" + id +
                ", classNumber='" + classNumber + '\'' +
                ", universitystudents=" + universitystudents +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getClassNumber() {
        return classNumber;
    }

    public void setClassNumber(String classNumber) {
        this.classNumber = classNumber;
    }

    public List<Universitystudent> getUniversitystudents() {
        return universitystudents;
    }

    public void setUniversitystudents(List<Universitystudent> universitystudents) {
        this.universitystudents = universitystudents;
    }
}

Universitystudent.java

public class Universitystudent {
    private int id;
    private String name;
    private int cid;

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

    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 getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }
}

3.我们再写StudentclassMapper.java的接口

public interface StudentClassMapper {
    //需求根据班级Id查询该班下的学生
    List<Studentclass> byIdClass(@Param("id")int id);
}

4.我们写映射文件StudentclassMapper.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="top.yunhuisu.mapper.StudentClassMapper">
    <select id="byIdClass" resultMap="getStudentClass">
        SELECT * FROM studentclass WHERE id = #{id}
    </select>
    <resultMap id="getStudentClass" type="studentclass">
        <collection
                property="universitystudents"
                column="id"
                ofType="universitystudent"
                javaType="ArrayList"
                select="getUniversityStudent"
        ></collection>
    </resultMap>
    <select id="getUniversityStudent" resultType="universitystudent">
        SELECT * FROM universitystudent WHERE cid = #{id}
    </select>
    <select id="byIdClassTwo" resultMap="getStudentClassTwo">
        SELECT
               sc.classNumber Class,
               us.id ucid,
               us.`name` ucname
        FROM
             studentclass sc,universitystudent us
        WHERE sc.id = us.cid AND sc.id = #{id}
    </select>
    <resultMap id="getStudentClassTwo" type="studentclass">
        <result property="classNumber" column="Class"></result>
        <collection property="universitystudents" ofType="universitystudent">
            <result property="id" column="ucid"></result>
            <result property="name" column="ucname"></result>
        </collection>
    </resultMap>
</mapper>

5.测试代码

public void testByIdClass() {
        MybatisUtils mybatisUtils = new MybatisUtils();
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        StudentClassMapper studentMapper = sqlSession.getMapper(StudentClassMapper.class);
        List<Studentclass> studentClassesList = studentMapper.byIdClass(8090101);
        for (Studentclass studentclass : studentClassesList) {
            System.out.println(studentclass);
        }
        List<Studentclass> studentClassesListTwo = studentMapper.byIdClassTwo(8090101);
        for (Studentclass studentclass : studentClassesListTwo) {
            System.out.println(studentclass);
        }
    }

测试图片：