mybatis多對(duì)多查詢的實(shí)現(xiàn)(xml方式和注解方式）

更新時(shí)間：2024年08月02日 09:46:45 作者：qq_42524262

本文主要介紹了mybatis多對(duì)多查詢的實(shí)現(xiàn),有xml方式和注解方式兩種,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

前言

前面總結(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)行多表的查詢，查詢所有用戶，用戶有角色信息就連接到該用戶后面，沒有則為空。

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é)果和上面一樣。

到此這篇關(guān)于mybatis多對(duì)多查詢的實(shí)現(xiàn)(xml方式和注解方式）的文章就介紹到這了,更多相關(guān)mybatis多對(duì)多查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: