Mybatis注解实现多表关联查询
目录
1、Demo描述
1)功能描述:
学生提出问题,多个医生可以回答一个学生的问题。医生回答问题时,页面上不仅要显示问题的基本信息,也要显示该问题必要的学生信息。
2)E-R图:
由于学生和问题是1对多的关系,医生和问题是多对1的关系,又因为问题还与其他的属性捆绑,个人觉得使用中间表来显示学生-问题-医生三者之间的关系会比较清晰
2、Entity类
备注:省略getter,setter,toString
public class Student {
private String id;
private String username;
private String password;
private Integer gender;
private Integer age;
private String tel;
private String email;
//学生 - 问题:1对多
private List<Question> questionList;
...
}
public class Question implements Serializable{
private String id;
private String createTime;
private String updateTime;
private String content;
private Integer viewCount;
private Integer likes;
private boolean anonymous;
private boolean status;
private Student student;
//问题 - 医生:多对1
private List<Doctor> doctors;
...
}
public class Doctor {
private String id;
private String username;
private String password;
private Integer gender;
private Integer age;
private Integer teachYear;
private String graduatedSchool;
private String tel;
private String email;
...
}
3、数据库表设计
1)student表
2)question表
3)doctor表
4)中间表及其外键约束
4、方法实现
0)备注:
- 方法功能描述:通过对问题集合的查询,该方法会一次性地将每个问题对应的学生id,username和医生id封装在问题的student,doctors对象中,并返回问题集合。
- @Result:若查询时,数据库表字段和实体类字段命名不一致,需要手动配置映射关系
- @one:1对1的关联查询,配置result时,相当于将子查询的结果与实体类的字段进行匹配
- @many:1对多的关联查询,配置result时,相当于将子查询的结果与实体类的字段进行匹配
- @Select(),@Insert()等注解简化mapper.xml的配置
1)Mapper中的方法及其实现
- getQuestionsWithStuAndDoctors() 主查询
@Results({
@Result(column = "id",property = "id"),
@Result(column = "id",property = "student",one = @One(select = "com.wangxiaoxi.mheal.mapper.StudentMapper.getStuByQuesId",fetchType=FetchType.DEFAULT)),
@Result(column = "id",property = "doctors",many = @Many(select = "com.wangxiaoxi.mheal.mapper.DoctorMapper.getDoctorsByQuesId",fetchType=FetchType.DEFAULT)),
})
@Select("select * from question order by updateTime")
public List<Question> getQuestionsWithStuAndDoctors();
-
getStuByQuesId() 子查询
-
将结果封装到student字段中
@Results({ @Result(column = "s_id",property = "id"), @Result(column = "s_id",property = "username",one = @One(select = "com.wangxiaoxi.mheal.mapper.StudentMapper.getUsernameById",fetchType = FetchType.DEFAULT)) }) @Select("select distinct s_id from askAndAns where q_id = #{q_id}") Student getStuByQuesId(String q_id);
-
getUsernameById() 子子查询,将结果封装到student对象的username字段中
@Select("select username from student where id = #{s_id}") public String getUsernameById(String s_id);
-
-
getDoctorsByQuesId() 子查询,将结果封装到doctors字段中
@Results({
@Result(column = "d_id",property = "id")
})
@Select("select d_id from askAndAns where q_id = #{q_id}")
List<Doctor> getDoctorsByQuesId(String q_id);
2)测试结果
@Test
void contextLoads() {
/**------------------------------中间表查询测试------------------------------------*/
List<Question> questions = questionMapper.getQuestionsWithStuAndDoctors();
for(Question q : questions){
System.out.println(q);
}
}
2020-03-16 21:13:41.922 DEBUG 8388 --- [ main] c.w.m.m.Q.getQuestionsWithStuAndDoctors : ==> Preparing: select * from question order by updateTime
2020-03-16 21:13:41.922 DEBUG 8388 --- [ main] c.w.m.m.Q.getQuestionsWithStuAndDoctors : ==> Parameters:
2020-03-16 21:13:41.938 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getStuByQuesId
//子查询1
: ====> Preparing: select distinct s_id from askAndAns where q_id = ?
2020-03-16 21:13:41.939 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getStuByQuesId : ====> Parameters: f2882808-4db0-4d3c-bc81-4f8e484aef2b(String)
2020-03-16 21:13:41.951 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getUsernameById
//子子查询1
: ======> Preparing: select username from student where id = ?
2020-03-16 21:13:41.953 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getUsernameById : ======> Parameters: 1614010820(String)
2020-03-16 21:13:41.962 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getUsernameById : <====== Total: 1
2020-03-16 21:13:41.963 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getStuByQuesId : <==== Total: 1
2020-03-16 21:13:41.963 DEBUG 8388 --- [ main] c.w.m.m.DoctorMapper.getDoctorsByQuesId
//子查询2
: ====> Preparing: select d_id from askAndAns where q_id = ?
2020-03-16 21:13:41.964 DEBUG 8388 --- [ main] c.w.m.m.DoctorMapper.getDoctorsByQuesId : ====> Parameters: f2882808-4db0-4d3c-bc81-4f8e484aef2b(String)
2020-03-16 21:13:41.969 DEBUG 8388 --- [ main] c.w.m.m.DoctorMapper.getDoctorsByQuesId : <==== Total: 2
2020-03-16 21:13:41.970 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getStuByQuesId : ====> Preparing: select distinct s_id from askAndAns where q_id = ?
2020-03-16 21:13:41.971 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getStuByQuesId : ====> Parameters: f2882808-4db0-4d3c-bc81-4f8e484aef3b(String)
2020-03-16 21:13:41.974 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getUsernameById : ======> Preparing: select username from student where id = ?
2020-03-16 21:13:41.975 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getUsernameById : ======> Parameters: 1614010821(String)
2020-03-16 21:13:41.977 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getUsernameById : <====== Total: 1
2020-03-16 21:13:41.978 DEBUG 8388 --- [ main] c.w.m.m.StudentMapper.getStuByQuesId : <==== Total: 1
2020-03-16 21:13:41.979 DEBUG 8388 --- [ main] c.w.m.m.DoctorMapper.getDoctorsByQuesId : ====> Preparing: select d_id from askAndAns where q_id = ?
2020-03-16 21:13:41.979 DEBUG 8388 --- [ main] c.w.m.m.DoctorMapper.getDoctorsByQuesId : ====> Parameters: f2882808-4db0-4d3c-bc81-4f8e484aef3b(String)
2020-03-16 21:13:41.983 DEBUG 8388 --- [ main] c.w.m.m.DoctorMapper.getDoctorsByQuesId : <==== Total: 1
2020-03-16 21:13:41.984 DEBUG 8388 --- [ main] c.w.m.m.Q.getQuestionsWithStuAndDoctors : <== Total: 2
//总查询
Question{id='f2882808-4db0-4d3c-bc81-4f8e484aef2b', createTime='2020-03-07 10:27:19', updateTime='2020-02-26 10:27:19', content='医生,最近压力大,请问如何才能正确排解压力 ', viewCount=0, likes=0, anonymous=false, status=false, student=Student{id='1614010820', username='王XX', password='null', gender=null, age=null, tel='null', email='null', questionList=null}, doctorList=[Doctor{id='161401080801', username='null', password='null', gender=null, age=null, teachYear=null, graduatedSchool='null', tel='null', email='null'}, Doctor{id='161401080802', username='null', password='null', gender=null, age=null, teachYear=null, graduatedSchool='null', tel='null', email='null'}]}
Question{id='f2882808-4db0-4d3c-bc81-4f8e484aef3b', createTime='2020-03-07 10:27:19', updateTime='2020-02-26 10:27:19', content='医生,最近压力大,请问如何才能正确排解压力 ', viewCount=0, likes=0, anonymous=false, status=false, student=Student{id='1614010821', username='王小希', password='null', gender=null, age=null, tel='null', email='null', questionList=null}, doctorList=[Doctor{id='161401080802', username='null', password='null', gender=null, age=null, teachYear=null, graduatedSchool='null', tel='null', email='null'}]}
2020-03-16 21:13:42.087 INFO 8388 --- [extShutdownHook] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor'
2020-03-16 21:13:42.122 INFO 8388 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2020-03-16 21:13:42.171 INFO 8388 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
Process finished with exit code 0
5、注意事项
- 注意sql查询结果是否唯一,若查询结果出现重复值,用distinct关键字,查出一个值即可,否则会出现returned more than one row, where no more than one was expected.异常
- 在执行子查询时,发现**@one和@many处理效果相同**,也许这么写可以提高代码的可读性
- 注解的作用可以提高代码的可读性,增强代码功能,减少代码的编写量。column为子查询的参数,property为子查询要封装的对象@Result,@one,@many在查询中嵌套子查询来增强主查询的功能。
6、参考文档
1、MyBatis学习总结(十)—基于注解的多表查询(一对一,一对多,多对多)