Mybatis Plus一对一、一对多查询

最新推荐文章于 2024-10-11 11:49:15 发布

原创最新推荐文章于 2024-10-11 11:49:15 发布 · 818 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java #mysql

java基础专栏收录该内容

188 篇文章

订阅专栏

本文介绍了一个基于MyBatis实现的班级信息查询接口及其XML配置文件,该接口能够进行一对一和一对多的数据查询,并详细展示了相关实体类的设计。

@Mapper
public interface ClassMapper extends BaseMapper<Class> {


    Class queryById(@Param("id") Integer id);

    Class queryByIdMany(@Param("id") Integer id);


}

<?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.mapper.ClassMapper">
    <!-- 一对一查询 -->
    <resultMap id="classResultMap" type="com.domain.Class">
        <id column="id" property="id"/>
        <result column="head_teacher_id" property="headTeacherId"/>

        <association property="headTeacher" javaType="com.domain.Teacher">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
        </association>
    </resultMap>

    <select id="queryById" resultMap="classResultMap">
        SELECT *
        FROM class c
                 LEFT JOIN teacher t ON c.head_teacher_id = t.id
        WHERE c.id = #{id}
    </select>

    <!-- 一对多查询 -->
    <resultMap id="classResultMapMany" type="com.domain.Class">
        <id column="id" property="id"/>
        <result column="head_teacher_id" property="headTeacherId"/>

        <association property="headTeacher" javaType="com.domain.Teacher">
            <id column="t_id" property="id"/>
            <result column="t_name" property="name"/>
        </association>

        <collection property="students" javaType="list" ofType="com.domain.Student">
            <id column="s_id" property="id"/>
            <result column="s_name" property="name"/>
            <result column="s_class_id" property="classId"/>
        </collection>
    </resultMap>
    <select id="queryByIdMany" resultMap="classResultMapMany">
        SELECT c.*, t.id t_id, t.name t_name, s.id s_id, s.name s_name, s.class_id s_class_id
        FROM class c
                 LEFT JOIN teacher t ON c.head_teacher_id = t.id
                 LEFT JOIN student s ON s.class_id = c.id
        WHERE c.id = #{id}
    </select>
</mapper>

@ApiModel(value = "test.`class`")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "test.`class`")
public class Class implements Serializable {
    private Integer id;
    private Integer headTeacherId;
    private Teacher headTeacher;
    private List<Student> students;

    private static final long serialVersionUID = 1L;
}

@ApiModel(value="test.student")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "test.student")
public class Student implements Serializable {
    /**
     * 学生id
     */
    @TableId(value = "id", type = IdType.INPUT)
    @ApiModelProperty(value="学生id")
    private Integer id;

    /**
     * 学生姓名
     */
    @TableField(value = "`name`")
    @ApiModelProperty(value="学生姓名")
    private String name;

    /**
     * 班级id
     */
    @TableField(value = "class_id")
    @ApiModelProperty(value="班级id")
    private Integer classId;

    private static final long serialVersionUID = 1L;
}

@ApiModel(value="test.teacher")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "test.teacher")
public class Teacher implements Serializable {
    /**
     * 老师id
     */
    @TableId(value = "id", type = IdType.INPUT)
    @ApiModelProperty(value="老师id")
    private Integer id;

    /**
     * 老师名字
     */
    @TableField(value = "`name`")
    @ApiModelProperty(value="老师名字")
    private String name;

    private static final long serialVersionUID = 1L;
}