springboot mybatis 一对多、多对一级联查询

最新推荐文章于 2024-07-31 22:53:57 发布

o_瓜田李下_o

最新推荐文章于 2024-07-31 22:53:57 发布

阅读量680

点赞数 1

分类专栏： mybatis 文章标签： mybatis 一对多、多对一级联查询

本文链接：https://blog.csdn.net/weixin_43931625/article/details/100849887

版权

mybatis 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

mybatis 一对多、多对一级联查询

**********************

示例

school：id、name

student：id、name、age、school_id

一个school有多个student，一个student对应一个school

***************

pojo 层

Student

@Data
@EqualsAndHashCode(callSuper = false)
public class Student extends Model<Student> {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    private String name;

    private Integer age;

    private Integer schoolId;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }

}

StudentMap

@Data
public class StudentMap {

    private Integer id;
    private String name;
    private Integer age;

    private School school;
}

School

@Data
@EqualsAndHashCode(callSuper = false)
public class School extends Model<School> {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    private String name;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }

}

SchoolMap

@Data
public class SchoolMap {

    private Integer id;
    private String name;

    private List<Student> studentList;
}

***************

dao 层

StudentMapper

public interface StudentMapper extends BaseMapper<Student> {

    List<Student> getStudentBySchoolId();
    List<StudentMap> getAllStudentMap(Page<?> page);
}

SchoolMapper

public interface SchoolMapper extends BaseMapper<School> {

    School getSchoolById(Integer id);
    List<SchoolMap> getAllSchoolMap(Page<?> page);
}

***************

xml文件

studentMapper.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="com.example.demo.dao.StudentMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.pojo.Student">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <result column="school_id" property="schoolId" />
    </resultMap>

    <resultMap id="BaseResultMap2" type="studentMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <association column="school_id" property="school"
                     select="com.example.demo.dao.SchoolMapper.getSchoolById"/>
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, age, school_id
    </sql>

    <select id="getStudentBySchoolId" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from student where school_id = #{schoolId};
    </select>

    <select id="getAllStudentMap" resultMap="BaseResultMap2">
        select <include refid="Base_Column_List"/> from student
    </select>
</mapper>

schoolMapper.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="com.example.demo.dao.SchoolMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.pojo.School">
        <id column="id" property="id" />
        <result column="name" property="name" />
    </resultMap>

    <resultMap id="BaseResultMap2" type="schoolMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection column="id" property="students"
                    select="com.example.demo.dao.StudentMapper.getStudentBySchoolId"/>
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name
    </sql>

    <select id="getSchoolById" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/> from school where id = #{id};
    </select>

    <select id="getAllSchoolMap" resultMap="BaseResultMap2">
        select <include refid="Base_Column_List"/> from school
    </select>
</mapper>

***************

controller层

StudentController

@RestController
@RequestMapping("/student")
public class StudentController {

    @Resource
    private StudentMapper studentMapper;

    @RequestMapping("/get")
    public List<StudentMap> getAll(){
        return studentMapper.getAllStudentMap(new Page<>(0,2));
    }
}

SchoolController

@RestController
@RequestMapping("/school")
public class SchoolController {

    @Resource
    private SchoolMapper schoolMapper;

    @RequestMapping("/get")
    public List<SchoolMap> getAll(){
        return schoolMapper.getAllSchoolMap(new Page<>(0,1));
    }
}

**********************

输出显示

localhost:8080/student/get

[
    {
        "id": 1,
        "name": "瓜田李下1",
        "age": 1,
        "school": {
            "id": 2,
            "name": "海贼王1"
        }
    },
    {
        "id": 2,
        "name": "瓜田李下2",
        "age": 2,
        "school": {
            "id": 3,
            "name": "海贼王2"
        }
    }
]

localhost:8080/school/get

[
    {
        "id": 1,
        "name": "海贼王0",
        "students": [
            {
                "id": 3,
                "name": "瓜田李下3",
                "age": 3,
                "schoolId": 1
            },
            {
                "id": 6,
                "name": "瓜田李下6",
                "age": 6,
                "schoolId": 1
            },
            {
                "id": 9,
                "name": "瓜田李下9",
                "age": 9,
                "schoolId": 1
            }
        ]
    }
]