Mybatis踩过得坑collection 一对多问题

在这里插入图片描述

昨天在使用分页查询一对多时出现了一个很严重的问题,绑定多少个数据分页总数就会增加多少条这样肯定是不对的

首先看一下原来写的sql

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jjckj.boot.mapper.UsersMapper">

    <resultMap id="userVOResultMap" type="com.jjckj.boot.base.vo.UserVO">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="email" property="email"/>
        <result column="mobile" property="mobile"/>
        <result column="avatar" property="avatar"/>
        <result column="qq" property="qq"/>
        <result column="wechat" property="wechat"/>
        <result column="weibo" property="weibo"/>
        <result column="qq_openid" property="qqOpenid"/>
        <result column="wechat_openid" property="wechatOpenid"/>
        <result column="weibo_openid" property="weiboOpenid"/>
        <result column="creator_id" property="creatorId"/>
        <result column="creator" property="creator"/>
        <result column="create_time" property="createTime"/>
        <result column="modifier_id" property="modifierId"/>
        <result column="modifier" property="modifier"/>
        <result column="modify_time" property="modifyTime"/>
        <result column="del_flag" property="delFlag"/>
        <result column="last_login_ip" property="lastLoginIp"/>
        <result column="last_login_location" property="lastLoginLocation"/>
        <result column="last_login_time" property="lastLoginTime"/>

        <collection property="roleVoList" ofType="com.jjckj.boot.base.vo.RoleVO">
            <id column="role_id" property="id"/>
            <result column="role_code" property="roleCode"/>
            <result column="role_name" property="roleName"/>
            <result column="rcreator_id" property="creatorId"/>
            <result column="rcreator" property="creator"/>
            <result column="rcreate_time" property="createTime"/>
            <result column="rmodifier_id" property="modifierId"/>
            <result column="rmodifier" property="modifier"/>
            <result column="rmodify_time" property="modifyTime"/>
            <result column="rdel_flag" property="delFlag"/>
        </collection>

        <collection property="groupVoList" ofType="com.jjckj.boot.base.vo.GroupVO">
            <id column="group_id" property="id"/>
            <result column="group_name" property="name"/>
            <result column="parent_id" property="parentId"/>
            <result column="group_code" property="code" />
            <result column="group_type" property="type" />
            <result column="leader" property="leader" />
            <result column="address" property="address" />
            <result column="gmobile" property="mobile" />
            <result column="gemail" property="email" />
            <result column="gstatus" property="status" />
            <result column="gsort" property="sort" />
            <result column="gcreator_id" property="creatorId" />
            <result column="gcreate_time" property="createTime" />
            <result column="gmodifier" property="modifier" />
            <result column="gmodify_time" property="modifyTime" />
        </collection>
    </resultMap>

    <select id="pageBootUserVoByQuery" resultMap="userVOResultMap"
            parameterType="com.jjckj.boot.model.query.UserVoQuery">
        select
          u.id,
          u.username,
          u.email,
          u.mobile,
          u.avatar,
          u.qq,
          u.wechat,
          u.weibo,
          u.qq_openid,
          u.wechat_openid,
          u.weibo_openid,
          u.user_source,
          u.last_login_ip,
          u.last_login_location,
          u.last_login_time,
          u.creator_id,
          u.create_time,
          u.modifier_id,
          u.modify_time,
          u.del_flag,
          u.remark,
          r.id as role_id,
          r.role_code,
          r.role_name,
          r.creator_id as rcreator_id,
          r.create_time as rcreate_time,
          r.modifier_id as rmodifier_id,
          r.modify_time as rmodify_time,
          r.del_flag as rdel_flag,
          g.id as group_id,
          g.name as group_name,
          g.parent_id,
          g.code as group_code,
          g.type as group_type,
          g.leader,
          g.address,
          g.mobile as gmobile,
          g.email as gemail,
          g.status as gstatus,
          g.sort as gsort,
          g.creator_id as gcreator_id,
          g.create_time as gcreate_time,
          g.modifier_id as gmodifier_id,
          g.modify_time as gmodify_time
        from
          users as u
          left join role_user as ru on ru.user_id = u.id
          left join role as r on r.id = ru.role_id
          left join group_user gu on gu.user_id = u.id
          left join  groups as g on g.id = gu.group_id
        <where>
            <if test="username != null and username != ''">
                u.username like concat('%', #{username}, '%')
            </if>
        </where>
    </select>
</mapper>

按理说应该是没有问题的是吧,但是使用分页就会先统计总数而不是先查询
在这里插入图片描述
所以我就对sql稍微的改动了一下

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jjckj.boot.mapper.UsersMapper">
	<resultMap id="userVOResultMap" type="com.jjckj.boot.base.vo.UserVO">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="email" property="email"/>
        <result column="mobile" property="mobile"/>
        <result column="avatar" property="avatar"/>
        <result column="qq" property="qq"/>
        <result column="wechat" property="wechat"/>
        <result column="weibo" property="weibo"/>
        <result column="qq_openid" property="qqOpenid"/>
        <result column="wechat_openid" property="wechatOpenid"/>
        <result column="weibo_openid" property="weiboOpenid"/>
        <result column="creator_id" property="creatorId"/>
        <result column="creator" property="creator"/>
        <result column="create_time" property="createTime"/>
        <result column="modifier_id" property="modifierId"/>
        <result column="modifier" property="modifier"/>
        <result column="modify_time" property="modifyTime"/>
        <result column="del_flag" property="delFlag"/>
        <result column="last_login_ip" property="lastLoginIp"/>
        <result column="last_login_location" property="lastLoginLocation"/>
        <result column="last_login_time" property="lastLoginTime"/>

        <collection property="roleVoList" column="id" ofType="com.jjckj.boot.base.vo.RoleVO" select="getRoleVoList">
            <id column="id" property="id"/>
            <result column="role_code" property="roleCode"/>
            <result column="role_name" property="roleName"/>
            <result column="creator_id" property="creatorId"/>
            <result column="create_time" property="createTime"/>
            <result column="modifier_id" property="modifierId"/>
            <result column="modify_time" property="modifyTime"/>
            <result column="del_flag" property="delFlag"/>
        </collection>
        <collection property="groupVoList" column="id" ofType="com.jjckj.boot.base.vo.GroupVO" select="getGroupVoList">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="parent_id" property="parentId"/>
            <result column="code" property="code" />
            <result column="type" property="type" />
            <result column="leader" property="leader" />
            <result column="address" property="address" />
            <result column="mobile" property="mobile" />
            <result column="email" property="email" />
            <result column="status" property="status" />
            <result column="sort" property="sort" />
            <result column="creator_id" property="creatorId" />
            <result column="create_time" property="createTime" />
            <result column="modifier_id" property="modifierId" />
            <result column="modify_time" property="modifyTime" />
        </collection>
    </resultMap>

    <!--查询用户绑定角色-->
    <select id="getRoleVoList" resultType="com.jjckj.boot.base.vo.RoleVO">
        select
            r.id ,
            r.role_code,
            r.role_name,
            r.creator_id,
            r.create_time,
            r.modifier_id,
            r.modify_time,
            r.del_flag
        from
            role as r
            left join role_user as ru on ru.role_id = r.id
        where ru.user_id = #{id}
    </select>

    <!--查询用户 绑定组织-->
    <select id="getGroupVoList" resultType="com.jjckj.boot.base.vo.GroupVO">
        select
            g.id,
            g.name,
            g.parent_id,
            g.code,
            g.type,
            g.leader,
            g.address,
            g.mobile,
            g.email,
            g.status,
            g.sort,
            g.creator_id,
            g.create_time,
            g.modifier_id,
            g.modify_time
        from
            groups as g
            left join group_user as gu on gu.group_id = g.id
        where gu.user_id = #{id}
    </select>

    <select id="pageBootUserVoByQuery" resultMap="userVOResultMap"
            parameterType="com.jjckj.boot.model.query.UserVoQuery">
        select
            u.id,
            u.username,
            u.email,
            u.mobile,
            u.avatar,
            u.qq,
            u.wechat,
            u.weibo,
            u.qq_openid,
            u.wechat_openid,
            u.weibo_openid,
            u.user_source,
            u.last_login_ip,
            u.last_login_location,
            u.last_login_time,
            u.creator_id,
            u.create_time,
            u.modifier_id,
            u.modify_time,
            u.del_flag,
            u.remark
        from
            users as u
        <where>
            <if test="username != null and username != ''">
                u.username like concat('%', #{username}, '%')
            </if>
        </where>
    </select>
</mapper>

现在的总数就是对的了
在这里插入图片描述

  • 0
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 9
    评论
在Java MyBatis中,可以使用collection来实现一对多的关系。具体步骤如下: 1. 首先,在数据库中创建两个表,一个是主表(例如:Order),另一个是从表(例如:Item)。主表和从表之间通过外键进行关联。 2. 在Java代码中,创建对应的实体类(例如:Order和Item),并为它们添加相应的属性和getter/setter方法。 3. 创建Mapper接口(例如:OrderMapper),定义查询方法,用于获取主表数据及其关联的从表数据。 4. 在Mapper XML文件中,编写SQL语句,使用MyBatis提供的collection标签来映射一对多的关系。示例代码如下: ```xml <!-- 查询订单及其对应的商品列表 --> <select id="getOrderWithItems" resultMap="orderResultMap"> SELECT o.id, o.order_no, i.id as item_id, i.name as item_name, i.price as item_price FROM orders o LEFT JOIN items i ON o.id = i.order_id WHERE o.id = #{orderId} </select> <resultMap id="orderResultMap" type="Order"> <id property="id" column="id"/> <result property="orderNo" column="order_no"/> <!-- 使用collection标签映射一对多关系 --> <collection property="items" ofType="Item"> <id property="id" column="item_id"/> <result property="name" column="item_name"/> <result property="price" column="item_price"/> </collection> </resultMap> ``` 5. 在Mapper接口中定义对应的方法,用于调用SQL语句并返回结果。 6. 在Java代码中,通过MyBatis的SqlSessionFactory和Mapper接口来执行查询操作,并获取一对多关系的数据。 这样,你就可以使用Java MyBatis实现一对多关系的查询了。希望能够帮到你!
评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

AbelEthan

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值