526互联

mybatis实现多对一和一对多

发布时间 2023-12-23 18:07:23作者: 有何和不可

1、多对一实现

概述：多个学生对应一个老师

1.1 按照查询嵌套查询

学生实体类

@Data
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId
    private Long id;
    /**用户名称*/
    private String username;
    /**用户类型*/
    private String type;
    /**关联的老师*/
    private Teacher teacher;
    /**逻辑删除*/
    @TableField("is_delete")
    private int flag;
}

老师实体类

@Data
public class Teacher implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableId
    private Long id;
    /**用户名称*/
    private String username;
    /**逻辑删除*/
    @TableField("is_delete")
    private int flag;
}

学生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">
<mapper namespace="com.hu.mapper.UserMapper">
    <!-- 查询全部的学生信息 -->
    <select id="getUserList" resultMap="StudentTeacher">
        SELECT
            `id`,
            `username`,
            `type`,
            `tid`,
            `is_delete`
        FROM
            `user`;
    </select>

    <!-- 多对一结果集映射 -->
    <resultMap id="StudentTeacher" type="User">
        <result column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="type" property="type"/>
        <!-- 复杂属性,对象属性使用:association -->
        <association column="tid" property="teacher" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <!-- 查询老师信息 -->
    <select id="getTeacher" resultType="Teacher">
        SELECT
            `id`,
            `username`,
            `is_delete`
        FROM
            `teacher`
        WHERE `id`=#{tid};
    </select>
</mapper>

执行结果

1.2 按照结果嵌套查询

学生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">
<mapper namespace="com.hu.mapper.UserMapper">
    <!-- 查询全部的学生信息 -->
    <select id="getUserList" resultMap="StudentTeacher">
        SELECT
            u.id AS `sid`,
            u.username,
            u.type,
            u.is_delete AS `sDelete`,
            t.id AS `tid`,
            t.username AS `tecName`,
            t.is_delete AS `tDelete`
        FROM
            `user` AS u,
            `teacher` AS t
        WHERE
            u.tid = t.id;
    </select>
    
    <!-- 多对一结果集映射 -->
    <resultMap id="StudentTeacher" type="User">
        <result column="sid" property="id"/>
        <result column="username" property="username"/>
        <result column="type" property="type"/>
        <result column="sDelete" property="flag"/>
        <!-- 复杂属性,对象属性使用:association -->
        <association property="teacher" javaType="Teacher">
            <result column="tid" property="id"/>
            <result column="tecName" property="username"/>
            <result column="tDelete" property="flag"/>
        </association>
    </resultMap>
   

</mapper>

执行结果

2、一对多实现

概述：一个老师对应多个学生

2.1 按照结果嵌套查询

学生实体类

@Data
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId
    private Long id;
    /**用户名称*/
    private String username;
    /**用户类型*/
    private String type;
    /**关联的老师ID*/
    private Long tid;
    /**逻辑删除*/
    @TableField("is_delete")
    private int flag;
}

老师实体类

@Data
public class Teacher implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableId
    private Long id;
    /**用户名称*/
    private String username;
    /**一个老师对应多个学生*/
    private List<User> userList;
    /**逻辑删除*/
    @TableField("is_delete")
    private int flag;
}

老师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">
<mapper namespace="com.hu.mapper.TeacherMapper">
    <!-- 查询全部老师信息 -->
    <select id="getTeacherList" resultMap="TeacherUser">
        SELECT
            u.id AS `sid`,
            u.username,
            u.type,
            u.is_delete AS `sDelete`,
            t.id AS `tid`,
            t.username AS `tecName`,
            t.is_delete AS `tDelete`
        FROM
            `teacher` AS t,
            `user` AS u
        WHERE
            u.tid = t.id;
    </select>
    <!-- 结果集映射 -->
    <resultMap id="TeacherUser" type="Teacher">
        <result column="tid" property="id"/>
        <result column="tecName" property="username"/>
        <result column="tDelete" property="flag"/>
        <!-- 复杂属性,集合:collection -->
        <collection property="userList" ofType="User">
            <result column="sid" property="id"/>
            <result column="username" property="username"/>
            <result column="type" property="type"/>
            <result column="tid" property="tid"/>
            <result column="sDelete" property="flag"/>
        </collection>
    </resultMap>
</mapper>

执行结果

2.2 按照查询嵌套查询

老师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">
<mapper namespace="com.hu.mapper.TeacherMapper">
    <!-- 查询全部老师信息 -->
    <select id="getTeacherList" resultMap="TeacherUser">
        SELECT
            t.id,
            t.username,
            t.is_delete
        FROM
            `teacher` AS t;
    </select>
    <!-- 结果集映射 -->
    <resultMap id="TeacherUser" type="Teacher">
        <result column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="is_delete" property="flag"/>
        <!-- 复杂属性,集合:collection -->
        <collection property="userList" javaType="ArrayList" ofType="User" select="getUser" column="id"/>
    </resultMap>
    <!-- 查询学生信息 -->
    <select id="getUser" resultType="User">
        SELECT
            u.id,
            u.username,
            u.type,
            u.tid,
            u.is_delete
        FROM
            `user` AS u
        WHERE
             tid = #{tid};
    </select>
</mapper>

执行结果