个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章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<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语句上了.

数据库表及关系

这里采用用户和角色的例子 。

一个用户可以有多个角色 。

一个角色可以赋予多个用户 。

在进行多表查询时，我们需要一张中间表，中间表中包含各自的主键，在中间表中是外键.

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

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

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

多对多查询（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

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

当我们查询角色想要得到相应的用户时道理是一样的，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

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

查询出来结果后剩下的内容就很简单.

在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>

测试结果 。

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

注解方式

思路是一样的，但我们使用注解时，不能像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);}

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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我.

原文链接：https://blog.csdn.net/CoderYin/article/details/90768007 。

最后此篇关于mybatis如何使用注解实现一对多关联查询的文章就讲到这里了,如果你想了解更多关于mybatis如何使用注解实现一对多关联查询的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： Java实现堆排序和图解
文章推荐： springmvc配置线程池Executor做多线程并发操作的代码实例
文章推荐： Spring线程池ThreadPoolExecutor配置并且得到任务执行的结果
文章推荐： Java对文件进行基本操作案例讲解
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com