mybatis Table 1 multi query

When writing mysql statement with mybatis, we encountered the problem of 1-to-many relationship

First look at the relationship:

Based on table A, A record in table A corresponds to A record in table B, while A record in table A corresponds to n records in table C. then table B and table C are 1-to-1. The query statement at this time is:

<?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="mapper.AMapper">

    <!--Joint query-->
    <resultMap id="AMap" type="dto.A">
        <id column="A_ID" property="AId" jdbcType="DECIMAL" javaType="Long" />
        <result column="B_ID" property="BId" jdbcType="DECIMAL"  javaType="Long" />
        <association property="B" javaType="dto.B">
            <id column="t_B_id" property="BId" jdbcType="DECIMAL" javaType="Long" />

        </association>
        <collection property="C" javaType="java.util.List" ofType="dto.C">
            <id column="C_C_ID" property="CId" jdbcType="DECIMAL" javaType="Long" />
            <result column="C_A_ID" property="AId" jdbcType="DECIMAL"  javaType="Long" />
            <association property="C_B" javaType="dto.B">
                <id column="u_B_id" property="BId" jdbcType="DECIMAL" javaType="Long" />
            </association>
        </collection>
    </resultMap>
    <select id="selectA_WithC_ById" parameterType="java.lang.Long" resultMap="AMap">
        SELECT *
        FROM
        A 
        LEFT JOIN
        B
        ON
        A.A_ID = B.B_id
        LEFT JOIN
        C
        ON
        C.C_ID = A.A_ID
        LEFT JOIN
        B c_b
        ON
        C.C_ID = c_b.B_id
        WHERE
        A.A_ID = #{id}
    </select>
</mapper>

Query principle:

Note: since the table will be duplicate, the same table should be aliased. Similarly, the same column name should be aliased, otherwise it will be wrong. There is another problem. This statement runs directly in mysql. It may not have the same number of results as running in the project, because in the project, duplicate data has been put into one and different data has been put into the list.

It's a bit complicated, but I can write it slowly. Fortunately, I didn't make a mistake when I wrote it.

Tags: Mybatis MySQL Amap Java

Posted on Mon, 04 May 2020 08:17:15 -0700 by kneifelspy