mybatis如何使用註解實現一對多關聯查詢

Posted on by

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<SectionQuestionDto> 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<QuestionResDto> 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<User> users;
    public List<User> getUsers() {
        return users;
    }
    public void setUsers(List<User> 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;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
    //多對多映射關系，一個用戶具備多個角色
    private List<Role> roles;
    public List<Role> getRoles() {
        return roles;
    }
    public void setRoles(List<Role> 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-->
    <resultMap id="userWithRole" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="address" column="address"></result>
        <result property="sex" column="sex"></result>
        <result property="birthday" column="birthday"></result>
        <!--配置角色映射-->
        <collection property="roles" ofType="role">
            <id property="roleId" column="rid"></id>
            <result property="roleName" column="role_name"></result>
            <result property="roleDesc" column="role_desc"></result>
        </collection>
    </resultMap>
    <!--查詢所有用戶信息-->
    <select id="findAll" resultMap="userWithRole">
        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>

<resultMap id="roleUserMap" type="role">
        <id property="roleId" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
        <collection property="users" ofType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>
    <!--查詢所有角色信息-->
    <select id="findAll" resultMap="roleUserMap">
        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
    </select>

測試結果

在這裡插入圖片描述在這裡插入圖片描述

註解方式

思路是一樣的，但我們使用註解時，不能像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<User> 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<User> 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<Role> 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<Role> findByUid(String uid);
}

最終的測試結果和上面一樣。

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：