mybatis如何使用注解实现一对多关联查询

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(Shttp://tring roleDesc) {

this.roleDesc = roleDesc;

}

@Override

public 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 List roles;

public List getRoles() {

return roles;

}

public void setRoles(List roles) {

this.roles = roles;

}

@Override

public String toString() {

return "User{" +

"id=" + id +

", username='pJvAtEcDkx" + 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 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);

}

最终的测试结果和上面一样。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。