Solution to the paging error problem of MySQL one-to-many association query

The query data in the xml price inquiry contains a list, so a collection is required

<resultMap id="XX" type="com.XXX.XXXX">
    <id column="o_id" jdbcType="BIGINT" property="id" />
    <result column="o_user_id" jdbcType="BIGINT" property="userId" />
    ....
    <collection property="orderProductList" ofType="com.XXXXXX.XXXXX">
      <id column="p_id" jdbcType="BIGINT" property="id" />
      <result column="p_order_id" jdbcType="BIGINT" property="orderId" />
      ....
    </collection>
  </resultMap>

The general paging query encapsulated by such a query system is incorrect, so you need to add paging to your own SQL to solve it.

<select id="XXX" resultMap="OrderListMap">
    SELECT
    you.nick_name,
    yo.id o_id,
    yo.user_id o_user_id
    FROM
    (
    SELECT * FROM
    youpin_order
    WHERE
    1 = 1
    <if test="status != null">
      and `status` = #{status}
    </if>
    <if test="page != null and limit != null">
    LIMIT #{page},
    #{limit}
    </if>
    )
    LEFT JOIN XXX yop ON yo.id = yop.order_id
    LEFT JOIN XXXX you ON yo.user_id = you.id
  </select>

When passing in parameters, calculation is required

(offset - 1) * limit, limit

Summarize

The above is the solution to the MySQL one-to-many association query paging error problem introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:

MySQL million-level data paging query optimization solution
Optimizing the performance of paging query for MySQL with tens of millions of data
MySQL Oracle and SQL Server paging query example analysis
Detailed explanation of MySQL limit usage and performance analysis of paging query statements
Detailed explanation of Mysql Limit paging query optimization
Introduction to the differences between paging query statements in Oracle, MySQL and SqlServe
Detailed explanation of paging query methods in mysql, mssql and oracle
Comparison of two solutions for paging query in MySQL
Examples of paging queries for three databases: oracle, mysql, and SqlServer
Several implementation methods and advantages and disadvantages of SQL paging query in MySQL

<<: How to use geoip to restrict regions in nginx

>>: Implementation of communication between Vue and Flask