Mybatis 数据库多表关联分页的问题

Z时代
2024-01-10
分类：IT

举个例子：
有两个实体类 User 和 Address

public class User {
    private int id;
    private String username; // 用户名
    private List<Address> addresses;
    // getter setter...
}
public class Address {
    private int id;
    private String detail; // 详细地址
    private User user;    //所属用户
    // getter setter...}

数据库：

create table t_user(
    id int(10) primary key auto_increment,
    username varchar(50)
);
create table t_address(
    id int(10) primary key auto_increment,
    detail varchar(255),
    user_id int(10),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES t_user(id));

mybatis映射配置：


<?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="org.mkh.shop.model.User">
    <resultMap type="User" id="userMap" autoMapping="true">
        <id property="id" column="u_id"/>
        <collection property="address" ofType="Address">
            <id property="id" column="a_id"/>
            <result property="detail" column="detail"/>
        </collection>
    </resultMap>
    <select id="find" resultType="User" parameterType="map">
        select  *,
                ta.id as 'a_id',
                tu.id as 'u_id' 
        from t_user tu 
            left join t_address ta on ta.user_id=tu.id 
        <where> 
            <if test="name != null">
                (username like #{name})
            </if>
        </where>
        <if test="sort != null">
            order by ${sort} 
            <choose>
                <when test="order != null">${order}</when>
                <otherwise>asc</otherwise>
            </choose> 
        </if>
        limit #{pageOffset},#{pageSize}
    </select>
    <select id="find_count" resultType="int" parameterType="map">
        select count(*) 
        from t_user tu 
            left join t_address ta on ta.user_id=tu.id 
        <where> 
            <if test="name != null">
                (username like #{name})
            </if>
        </where>
    </select>    
</mapper>

用户和地址的关系为：一个用户有多个地址，一个地址只能属于一个用户，一对多
假设现在的需求是，分页查询用户，用表格显示，并把每个用户的所有地址显示出来
那么问题来了
按照上面的查询返回的分页的数据是没有问题的，但是分页的总记录数却是不对的。
比如查出来的数据（数据库数据，并非页面显示）如下：

u_id	username	a_id	detail
1	user1	1	北京市海淀区
1	user1	2	北京市朝阳区
2	user2	3	天津市

因为我的需求是分页显示用户，所以一个用户就是一条数据在页面显示大概是这样子，理论上是两条数据，

用户id	用户名	地址
1	user1	1. 北京市海淀区 2. 北京市朝阳区
2	user2	1. 天津市
共1页，共2条数据，每页显示10条

用户id

用户名

地址

user1

1. 北京市海淀区

2. 北京市朝阳区

user2

1. 天津市

共1页，共2条数据，每页显示10条

但是，按mybatis的find_count配置查出来是3条，这种问题如何解决？
查询count（*）的时候将所有left join 后面关联的表去除吗？这样会不会导致返回的数据不准确

补充说明：感觉大家是理解错我的意思了，其实我这个问题主要是在SQL 上，而不再mybatis上，因为我查询出来的数据映射完毕之后是完全没有问题的，只是在分页的总记录数上出现了问题，导致分页不正确

回答：

这种情况不能这么分页，你需要对主表数据进行分页。

本来查询出100条数据，由于一对多会折叠去重很多数据，导致实际结果少于100条。

这种情况下可以采取嵌套查询方式解决，就是需要N+1次执行，可以懒加载。

或者看这里：https://my.oschina.net/flags/...

有关 MyBatis 内容可以访问：http://mybatis.tk/

回答：

在使用left join 后会发现如果右表数量有重复的话，查询出来的数量大于左表的数量，
如表一 test1

ID NAME SEX AGE 1 1 1 1 2 2 2 1

3 3 3 1

表二 test2
ID NAME SEX KJXYH
1 1 1 kjxy-物采中心-20130001
2 1 1 kjxy-物采中心-20130003
3 1 1 kjxy-物采中心-20130002

普通的left join
Select t1.* ,test2.kjxyh From test1 t1 Left Join test2 On t1.Name = test2.Name And t1.sex =test2.sex
查询出来的结果：
ID NAME SEX AGE KJXYH
1 1 1 1 kjxy-物采中心-20130001
1 1 1 1 kjxy-物采中心-20130003
1 1 1 1 kjxy-物采中心-20130002
2 2 2 1
3 3 3 1
查询结果为五条数据，不是我们想要的结果，下面看看如果我用这种方式查询的结果
select test1.*,t2.kjxyh from test1 left join(
Select * From
(
select a.*,row_number() over(partition by Name,sex order by id) r
from test2 a
) where r = 1 ) t2
on test1.Name = t2.Name And test1.sex = t2.sex

ID NAME SEX AGE KJXYH
1 1 1 1 kjxy-物采中心-20130001
2 2 2 1
3 3 3
1

会发现数据如只有三条了。按照排序获取到了三条，以左表为基表，然后从test2中按照指定的排序获取到了test2中的kjxyh，但是有时候需要展示所有的kjxyh该怎么办呢，看看下面这条sql语句：
select test1.*,b.kj from test1,
( select test1.id,wm_concat(test2.kjxyh) kj from test1
left join test2 on test1.name=test2.name and test1.sex=
test2.sex group by test1.name,test1.sex,test1.id) b
where test1.id in(b.id)
查询结果：
ID NAME SEX AGE KJ
1 1 1 1 kjxy-物采中心-20130001,kjxy-物采中心-20130003,kjxy-物采中心-20130002
2 2 2 1
3 3 3 1
这样在kj这一列就展示了所有的kjxyh，是否满足你的要求呢？

回答：

刚才写了个例子测了一下

` 这是sql语句分页和条件我就不加了

两个实体类
public class User {


private int id;
private String username; // 用户名
private List<Address> addresses;

public class Address {

private int id;
private String detail; // 详细地址
private int user_id; // 所属用户

映射文件
<resultMap type="com.atguigu.mybatis.entity.User" id="userMap" autoMapping="true">

    <result property="id" column="u_id"/>
    <collection property="addresses" ofType="com.atguigu.mybatis.entity.Address" autoMapping="true">
        <result property="id" column="a_id"/>
        <result property="user_id" column="u_id"/>
    </collection>
</resultMap><select id="select_all_user_address"  resultMap="userMap" >

select tu.*,ta.*, ta.id as 'a_id', tu.id as 'u_id' from t_user tu left join t_address ta on ta.user_id=tu.id </select>

测试结果图片描述

封装成的 List<User>的size是没问题的

回答：

关键词group by 自己查一下具体操作

回答：

SELECT 
    *, ta.id AS 'a_id', tu.id AS 'u_id'
FROM
    t_user tu
        LEFT JOIN    t_address ta ON ta.user_id = tu.id;

你希望想要两条但是你的sql查出三条，所以显示错误，
应该拆分逻辑：
应该先查出你想要的用户

<select id="find" resultType="User" parameterType="map">
        select  *
        from t_user tu 
        <where> 
            <if test="name != null">
                (username like #{name})
            </if>
        </where>
        <if test="sort != null">
            order by ${sort} 
            <choose>
                <when test="order != null">${order}</when>
                <otherwise>asc</otherwise>
            </choose> 
        </if>
        limit #{pageOffset},#{pageSize}    </select>

然后在

    <resultMap type="User" id="userMap" autoMapping="true">
        <id property="id" column="u_id"/>
        <collection " property="addresses" javaType= "ArrayList" column="u_id"
 ofType="Address" select= "??" />    </resultMap>

??是自己实现个用userId查List<Address>的方法

回答：

<select id="find_count" resultType="int" parameterType="map">
    select count(*) 
    from t_user tu 
        left join t_address ta on ta.user_id=tu.id 
    <where> 
        <if test="name != null">
            (username like #{name})
        </if>
    </where></select>

改:

<select id="find_count" resultType="int" parameterType="map">
    select count(*) 
    from t_user tu
   <where> 
        <if test="name != null">
            (username like #{name})
        </if>
    </where>
    group by username
</select>

回答：

用子查询就不会有问题了

select count(*) from (
   // query 在这里即使关联100张表, 也不可能存在问题)

楼主可以看一下Mybatis-PageHelpercount sql 转换实现

建议楼主直接使用 Mybatis-PageHelper 实现分页

回答：

看了下上面的回答好像没有楼主要的答案,不如试试这个?

SELECT tu.id as uid,tu.username as username ,group_concat(ta.detail) as detail
FROM t_user tu
LEFT JOIN t_address ta ON (tu.id = ta.user_id)group by tu.id