mybatis如何使用注解實(shí)現(xiàn)一對(duì)多關(guān)聯(lián)查詢

更新時(shí)間：2021年07月09日 08:44:27 作者：CoderYin

這篇文章主要介紹了mybatis如何使用注解實(shí)現(xiàn)一對(duì)多關(guān)聯(lián)查詢的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

mybatis 注解實(shí)現(xiàn)一對(duì)多關(guān)聯(lián)查詢

@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多對(duì)多查詢(xml方式和注解方式）

前面總結(jié)了一對(duì)一，多對(duì)一和一對(duì)多的多表查詢，今天總結(jié)一下多對(duì)多的mybatis多表查詢。同樣有xml方式和注解方式，步驟和前兩種查詢差不多，最主要的區(qū)別就在表和sql語(yǔ)句上了。

數(shù)據(jù)庫(kù)表及關(guān)系

這里采用用戶和角色的例子

一個(gè)用戶可以有多個(gè)角色

一個(gè)角色可以賦予多個(gè)用戶

在進(jìn)行多表查詢時(shí)，我們需要一張中間表，中間表中包含各自的主鍵，在中間表中是外鍵。

在這里插入圖片描述

多對(duì)多查詢（xml方式）

這次我們首先清理一下思路，我們先在數(shù)據(jù)庫(kù)里把我們需要的數(shù)據(jù)查出來(lái)再寫代碼。

我們查詢用戶時(shí)要同時(shí)查出其對(duì)應(yīng)的角色，借助中間表，根據(jù)UID查詢RID，再根據(jù)RID查詢角色表，中間表的數(shù)據(jù)我們不需要，所以不顯示。

這里我們可以用左外連接來(lái)進(jìn)行多表的查詢，查詢所有用戶，用戶有角色信息就連接到該用戶后面，沒(méi)有則為空。

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

在這里插入圖片描述

當(dāng)我們查詢角色想要得到相應(yīng)的用戶時(shí)道理是一樣的，SQL語(yǔ)句也只要換一下連接順序。

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

在這里插入圖片描述

查詢出來(lái)結(jié)果后剩下的內(nèi)容就很簡(jiǎn)單。

在User和role里加入多對(duì)多實(shí)體映射

public class Role implements Serializable {
    private String roleId;
    private String roleName;
    private String roleDesc;
    //多對(duì)多映射關(guān)系，一個(gè)角色有多個(gè)用戶
    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;
    //多對(duì)多映射關(guān)系，一個(gè)用戶具備多個(gè)角色
    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語(yǔ)句

<!--定義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>

測(cè)試結(jié)果

在這里插入圖片描述

注解方式

思路是一樣的，但我們使用注解時(shí)，不能像xml方式一樣只使用一條sql語(yǔ)句完成直接封裝，所以這里要按上面說(shuō)的思路完成分步查詢。

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();
    /**
     * 根據(jù)id查詢一個(gè)用戶
     * @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);
}

最終的測(cè)試結(jié)果和上面一樣。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: