java - Mybatis database multi-table related paging problem

Question

For example:
There are two entity classes User and 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...
}

database:

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 mapping configuration:

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

The relationship between users and addresses is: one user has multiple addresses, one address can only belong to one user, one-to-many
Assume that the current demand is to query users in pages, display them in a table, and put each user All addresses are displayed
Then the problem comes
There is no problem with the paging data returned according to the above query, but the total number of paging records is wrong.
For example, the data found (database data, not page display) is as follows:

u_id	username	a_id	detail
1	user1	1	Haiding district, Beijing
1	user1	2	Chaoyang District, Beijing
2	user2	3	Tianjin City

Because my requirement is to display users in pages, so a user is a piece of data displayed on the page, which looks like this. In theory, it is two pieces of data,

User id	username	address
1	user1	1. Haidian District, Beijing 2. Chaoyang District, Beijing
2	user2	1. Tianjin City
A total of 1 page, a total of 2 pieces of data, each page displays 10 pieces

However, according to the find_count configuration of mybatis, it is found that there are 3 items. How to solve this problem?
When querying count(*), should all tables associated with left join be removed? Will this cause the returned data to be inaccurate

---

Additional explanation: I feel that everyone has misunderstood what I meant. In fact, my problem is mainly on SQL, not mybatis, because after the data I queried was mapped, there was no problem at all. There is just a problem with the total number of paging records, resulting in incorrect paging

Answer 1

I just wrote an example and tested it

`

Two entity classes
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; // 所属用户

Mapping file
<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 , -->
<!-- t_address ta where ta.user_id=tu.id -->

        
         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>

Test results

The size of the encapsulated List<User> is no problem

Answer 2

Keyword group by and check the specific operation yourself

Answer 3

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;

You hope to want two items, but your sql finds three items, so an error is displayed.
You should split the logic:
You should first find out the user you want

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

Then in

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

??It’s a method of checking List<Address> using userId by yourself

Answer 4

In this case, paging cannot be done like this. You need to paginate the main table data.

Originally, 100 pieces of data were queried, but because one-to-many will fold and deduplicate a lot of data, the actual results were less than 100 pieces.

In this case, nested query can be used to solve the problem, which requires N+1 executions and can be loaded lazily.

Or look here: https://my.oschina.net/flags/...

For MyBatis content, please visit: http://mybatis.tk

Answer 5

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

Change:

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

Answer 6

There will be no problem if you use subquery

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

The original poster can take a look at Mybatis-PageHelper count sql conversion implementation

It is recommended that the author directly use Mybatis-PageHelper to implement paging