场景:
两张表:学生基础信息表A,学生成绩表B,两张表通过userId关联
返回结果:以学生为维度返回列表,学生有一个外部属性:成绩列表
语文 100
张三 u001 成绩 数学 99
英语 88
李四 u002 成绩 语文 66
数学 77
英语 99
王五 u003 成绩 语文 22
数学 55
英语 33
Mapper.xml
<?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="com.user.mapper.read.UserReaderMapper" >
<resultMap id="BaseResultMap" type="com.user.pojo.UserPojo" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="user_id" property="userId" jdbcType="VARCHAR" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
<collection property="scorePojos" column="user_id" select="queryUserScoreList" />
</resultMap>
<resultMap id="UserScoreResultMap" type="com.user.pojo.UserScorePojo" >
<result column="user_id" property="userId" jdbcType="VARCHAR" />
<result column="subject" property="subject" jdbcType="VARCHAR" />
<result column="score" property="score" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, user_id, user_name
</sql>
<select id="queryUserList" resultMap="BaseResultMap" parameterType="com.user.param.UserQueryParam" >
select
<include refid="Base_Column_List" />
from user_table
where 1=1
<if test="userId!=null and userId!='' ">
AND (user_id = #{userId,jdbcType=VARCHAR} )
</if>
<if test="userName!=null and userName!='' ">
AND (user_name = #{userName,jdbcType=VARCHAR} )
</if>
</select>
<select id="queryUserScoreList" resultMap="UserScoreResultMap" parameterType="com.user.param.UserQueryParam" >
select
subject, score ,user_id
from user_score_table
</select>
</mapper>
Mapper.java
public interface UserReaderMapper extends BaseReaderMapper{
List<UserPojo> queryUserList(UserQueryParam param);
}