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
}
]
}
]