在用mybatis写mysql语句时,遇到了1对多关系的问题
先看看关系:
以A表为基础查询,A表一条记录对应B表的一条记录,而A表的一条记录对应C表的n条记录,然后B表和c表是1对1的关系。这时候的查询语句为:
<?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">
<!--联合查询-->
<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>
查询原理: