mybatis如何使用注解实现一对多关联查询
mybatis 注解实现一对多关联查询
@Select("select id,mockexam_section as section,id as sectionId"+ " from t_p_qb_mockexam_section"+ " where mockexam_charpter_id = #{charpterId} and is_delete = 0"+ " order by mockexam_section_idx asc")@Results({@Result(property = "questionList",column = "sectionId",many = @Many(select = "com.zikaoshu.baseinfo.mapper.BaseinfoQuestionMapper.listQuestionResDto"))})List listSectionQuestionDto(@Param("charpterId") Integer charpterId); @Select("select id,type,discuss_title as discussTitle,stem1,material,a,b,c,d,e,answer,analysis,mockeaxm_section_id as sectionId"+ " from t_p_qb_question_mockexam"+ " where mockeaxm_section_id = #{id} and is_delete = 0"+ " order by q_sequence,gmt_create asc")ListlistQuestionResDto(@Param("id") Integer id);
mybatis多对多查询(xml方式和注解方式) 前面总结了一对一,多对一和一对多的多表查询,今天总结一下多对多的mybatis多表查询。同样有xml方式和注解方式,步骤和前两种查询差不多,最主要的区别就在表和sql语句上了。
数据库表及关系
这里采用用户和角色的例子
一个用户可以有多个角色
一个角色可以赋予多个用户
在进行多表查询时,我们需要一张中间表,中间表中包含各自的主键,在中间表中是外键。
文章图片
文章图片
文章图片
多对多查询(xml方式)
这次我们首先清理一下思路,我们先在数据库里把我们需要的数据查出来再写代码。
我们查询用户时要同时查出其对应的角色,借助中间表,根据UID查询RID,再根据RID查询角色表,中间表的数据我们不需要,所以不显示。
这里我们可以用左外连接来进行多表的查询,查询所有用户,用户有角色信息就连接到该用户后面,没有则为空。
select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from user uleft outer join user_role ur on u.id=ur.uidleft outer join role r on ur.rid = r.id
文章图片
当我们查询角色想要得到相应的用户时道理是一样的,SQL语句也只要换一下连接顺序。
select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from role rleft outer join user_role ur on r.id=ur.ridleft outer join user u on ur.uid = u.id
文章图片
查询出来结果后剩下的内容就很简单。
在User和role里加入多对多实体映射
public class Role implements Serializable {private String roleId; private String roleName; private String roleDesc; //多对多映射关系,一个角色有多个用户private Listusers; public List getUsers() {return users; }public void setUsers(List users) {this.users = users; }public String getRoleId() {return roleId; }public void setRoleId(String roleId) {this.roleId = roleId; }public String getRoleName() {return roleName; }public void setRoleName(String roleName) {this.roleName = roleName; }public String getRoleDesc() {return roleDesc; }public void setRoleDesc(String roleDesc) {this.roleDesc = roleDesc; }@Overridepublic String toString() {return "role{" +"roleId='" + roleId + '\'' +", roleName='" + roleName + '\'' +", roleDesc='" + roleDesc + '\'' +'}'; }}
public class User implements Serializable{private Integer id; private String username; private String address; private String sex; private Date birthday; //多对多映射关系,一个用户具备多个角色private Listroles; public List getRoles() {return roles; }public void setRoles(List roles) {this.roles = roles; }@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", address='" + address + '\'' +", sex='" + sex + '\'' +", birthday=" + birthday +'}'; }public Integer getId() {return id; }public void setId(Integer id) {this.id = id; }public String getUsername() {return username; }public void setUsername(String username) {this.username = username; }public String getAddress() {return address; }public void setAddress(String address) {this.address = address; }public String getSex() {return sex; }public void setSex(String sex) {this.sex = sex; }public Date getBirthday() {return birthday; }public void setBirthday(Date birthday) {this.birthday = birthday; }}
然后配置xml,配置映射封装和sql语句
select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from user uleft outer join user_role ur on u.id=ur.uidleft outer join role r on ur.rid = r.id
select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from role rleft outer join user_role ur on r.id=ur.ridleft outer join user u on ur.uid = u.id
测试结果
文章图片
文章图片
注解方式
思路是一样的,但我们使用注解时,不能像xml方式一样只使用一条sql语句完成直接封装,所以这里要按上面说的思路完成分步查询。
public interface IUserDao {/*** 查询所有操作,并携带账户信息* @return*/@Select("select * from user")@Results(id = "userRoleMap",value = https://www.it610.com/article/{//id表示主键@Result(id = true,column ="id",property = "id"),@Result(column = "username",property = "username"),@Result(column = "address",property = "address"),@Result(column = "sex",property = "sex"),@Result(column = "birthday",property = "birthday"),@Result(property = "roles",column = "id",many = @Many(select = "com.itcc.dao.IRoleDao.findByUid",fetchType = FetchType.LAZY))})ListfindAll(); /*** 根据id查询一个用户* @param rid*/@Select("select * from user where id in(select uid from user_role where rid = #{rid})")@Results({@Result(id = true,column = "id",property = "id"),@Result(column = "username",property = "username"),@Result(column = "address",property = "address"),@Result(column = "sex",property = "sex"),@Result(column = "birthday",property = "birthday")})List findByRId(Integer rid); }
public interface IRoleDao {/*** 查询所有角色信息* @return*/@Select("select * from role")@Results({@Result(id = true,column = "id",property = "roleId"),@Result(column = "role_name",property = "roleName"),@Result(column = "role_desc",property = "roleDesc"),@Result(property = "users",column = "id",many = @Many(select = "com.itcc.dao.IUserDao.findByRId",fetchType = FetchType.LAZY))})ListfindAll(); @Select("select * from role where ID in(select rid from user_role where uid = #{uid})")@Results({@Result(id = true,column = "id",property = "roleId"),@Result(column = "role_name",property = "roleName"),@Result(column = "role_desc",property = "roleDesc")})List findByUid(String uid); }
最终的测试结果和上面一样。
【mybatis如何使用注解实现一对多关联查询】以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。
推荐阅读
- 考研英语阅读终极解决方案——阅读理解如何巧拿高分
- 由浅入深理解AOP
- 如何寻找情感问答App的分析切入点
- 【译】20个更有效地使用谷歌搜索的技巧
- 关于QueryWrapper|关于QueryWrapper,实现MybatisPlus多表关联查询方式
- mybatisplus如何在xml的连表查询中使用queryWrapper
- mybatisplus|mybatisplus where QueryWrapper加括号嵌套查询方式
- MybatisPlus|MybatisPlus LambdaQueryWrapper使用int默认值的坑及解决
- MybatisPlus使用queryWrapper如何实现复杂查询
- 如何在Mac中的文件选择框中打开系统隐藏文件夹