项目场景:
例如:有学生表student包含外键classid,班级表class又包含班主任外键teacherid,学生表stduent又与course表示多对多关系
标的设计大致如此 ![请添加图片描述](https://img-blog.csdnimg.cn/bc2070870f834654903c4f1f6b55e54f.bmp?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzQxOTQz,size_16,color_FFFFFF,t_70)
问题描述:
如何查询学生的所有信息,并且全部显示出来?这里涉及了几张表的查询其实也不难sql语句。这时候你需要封装一个新的实体类来暂存数据,否则xml的关系映射没法做。上码 `内联代码片`。@Data
@TableName("people_single_student")
public class PeopleSingleStudentEntity implements Serializable {
//字段标识
private Integer id;
/**
* 学生id
*/
@TableId
private Integer studentId;
/**
* 学生姓名
*/
private String sname;
/**
* 性别
*/
private String sex;
//声明对象映射关系,xml配置有用
private PeopleClassEntity classs;
private PeopleTeacherEntity teacher;
private PeopleCourseEntity course;
}
SQL语句:
老实sql这没啥说的。
先用多对多的关系中间表查,left join on 与right join on 使用查出学生与课程的数据,再和学生表中classid外键关系查班级,再和班主任表中查班级对应的老师即可。
SELECT distinct student.student_id
,student.sname
,student.sex
,class.classname
,teacher.tname
,course.coursename
FROM people_student_course a LEFT JOIN people_student student ON
a.student_id
=student.student_id
LEFT JOIN people_course course ON a.course_id
=course.course_id
INNER JOIN
people_class class ON student.classid
=class.class_id
INNER JOIN people_teacher teacher ON class.teacher_id
=teacher.teacher_id
group by student.student_id limit #{param1},#{param2}
xml配置:
关系映射搞对就行一对一association,多对多collection
记住,在前面,否则可能会报错。
内联代码片
。
<resultMap type="io.renren.modules.generator.entity.PeopleSingleStudentEntity" id="kkMap">
<id column="id" property="id"></id>
<result property="studentId" column="student_id"/>
<result property="sname" column="sname"/>
<result property="sex" column="sex"></result>
<association property="classs" javaType="io.renren.modules.generator.entity.PeopleClassEntity">
<id column="classid" property="classId"></id>
<result column="classname" property="classname"></result>
</association>
<association property="teacher" javaType="io.renren.modules.generator.entity.PeopleTeacherEntity">
<id property="teacherId" column="teacherid"></id>
<result column="tname" property="tname"></result>
</association>
<collection property="course" ofType="io.renren.modules.generator.entity.PeopleCourseEntity">
<id property="courseId" column="course_id"></id>
<result property="coursename" column="coursename"></result>
</collection>
</resultMap>