这篇文章主要介绍了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") List listQuestionResDto(@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 u left outer join user_role ur on u.id=ur.uid left outer join role r on ur.rid = r.id
当我们查询角色想要得到相应的用户时道理是一样的，SQL语句也只要换一下连接顺序。
select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from role r left outer join user_role ur on r.id=ur.rid left 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 List users; 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; } @Override public String toString() { return "role{" + "roleId='" + roleId + '\'' + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
public class User implements Serializable{ private Integer id<em style="color:transparent">来源gao.dai.ma.com搞@代*码网</em>; private String username; private String address; private String sex; private Date birthday; //多对多映射关系，一个用户具备多个角色 private List roles; public List getRoles() { return roles; } public void setRoles(List roles) { this.roles = roles; } @Override public 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语句
<!--定义resultMap--> <!--配置角色映射--> <!--查询所有用户信息--> select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from user u left outer join user_role ur on u.id=ur.uid left outer join role r on ur.rid = r.id
<!--查询所有角色信息--> select u.*,r.id as rid,r.ROLE_NAME,r.ROLE_DESC from role r left outer join user_role ur on r.id=ur.rid left outer join user u on ur.uid = u.id
测试结果
注解方式
思路是一样的，但我们使用注解时，不能像xml方式一样只使用一条sql语句完成直接封装，所以这里要按上面说的思路完成分步查询。
public interface IUserDao { /** * 查询所有操作,并携带账户信息 * @return */ @Select("select * from user") @Results(id = "userRoleMap",value = { //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)) }) List findAll(); /** * 根据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)) }) List findAll(); @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); }
最终的测试结果和上面一样。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持gaodaima搞代码网。
以上就是mybatis如何使用注解实现一对多关联查询的详细内容，更多请关注gaodaima搞代码网其它相关文章！
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mybatis如何使用注解实现一对多关联查询
转载请注明原文链接：mybatis如何使用注解实现一对多关联查询
