Mybatis 查询包含包含集合List的方法

最近项目里有个小功能，多表联合查询的时候，有个表里的数据是集合返回的，考虑原本的sql不带集合形式，如果遍历再去表里查询，耗时费力，作为追求完美的程序员，是绝对不允许这样的。所以原本的sql语句需要修改，考虑使用collection。

简述下功能：
主要有三个表，团队表，团队成员表，作品表（demo中的其他表与本次collection使用无关）。

这是返回主体的DO

public class TeamInfoDO implements Serializable {
    private static final long serialVersionUID = -393331611771259644L;

    private String name;
    private Integer status;
    private String leaderName;
    private String phone;
    private String email;
    private String province;
    private String city;
    private String county;
    private String zoneId;
    private Date startDate;
    private Date endDate;
    private Integer entryProjectStatus;
    private List<TeamStaffDO> staffDOS;
}

这是主体DO中的List包含对象

在这里插入代码片public class TeamStaffDO implements Serializable {

    private static final long serialVersionUID = -1576912728499567654L;

    private Long id;

    private Long teamId;

    @NotNull(message = "姓名不能为空")
    private String name;

    @NotNull(message = "身份证号不能为空")
    private String idCard;

    private Boolean isLeader;

    private Date gmtCreate;

    private String createNick;

    private Date gmtModified;

    private String modifiedNick;
}

mapper中的resultMap如下：

<resultMap id="BaseResultMap" type="com.xxx.domain.TeamInfoDO" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="phone" property="phone" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="province" property="province" jdbcType="VARCHAR" />
    <result column="city" property="city" jdbcType="VARCHAR" />
    <result column="county" property="county" jdbcType="VARCHAR" />
    <result column="signup_date" property="signupDate" jdbcType="TIMESTAMP" />
    <result column="leader_name" property="leaderName" jdbcType="VARCHAR" />
    <result column="zone_name" property="zoneName" jdbcType="VARCHAR" />
    <result column="work_name" property="workName" jdbcType="VARCHAR" />
    <result column="work_description" property="workDescription" jdbcType="VARCHAR" />
    <result column="subject_id" property="subjectId" jdbcType="BIGINT" />
    <collection property="staffs" ofType="com.xxx.domain.TeamStaffDO" select="findStaffByTeamId" column="{teamId=id}" />
  </resultMap>

collection中的property如下：

<resultMap id="staffs" type="com.xxx.TeamInfoDO">
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
  </resultMap>

teamId作为动态参数传入，id为对应sql中的参数名。对应collection的ofType为团队成员的do，select方法如下：

<select id="findStaffByTeamId" resultType="com.xxx.domain.TeamStaffDO">
    SELECT id, team_id, name,id_card, is_leader,gmt_create,create_nick
    FROM team_staff
    WHERE team_id = #{teamId}
  </select>

最后，select的sql如下：
<select id="selectList" resultMap="BaseResultMap" parameterType="com.xxx.domain.TeamAdminParamDO" > select team.id as id, team.name as name, team.status as status, team.phone as phone, team.email as email, team.province as province, team.city as city, team.county as county, team.gmt_create as signup_date, team_staff.name as leader_name, zone.id as zone_id, zone.name as zone_name, work.name as work_name, work.description as work_description, work.subject_id as subject_id, ep.status entryProjectStatus from team left join team_staff on team.id = team_staff.team_id left join work on team.id = work.team_id left join zone on domain_subject_work.zone_id = zone.id left join entry_team_project etp on etp.entry_team_id = team.id left join entry_project ep on ep.id = etp.entry_project_id where team_staff.is_leader = 1 and team.deleted = 0 <if test="status!=null and status != 0" > and team.status in (2,3) </if> <if test="name != null and name != ''" > and team.name like CONCAT('%',#{name,jdbcType=VARCHAR},'%') </if> <if test="leaderName != null and leaderName != ''" > and team_staff.name like CONCAT('%',#{leaderName,jdbcType=VARCHAR},'%') </if> <if test="phone != null and phone != ''" > and team.phone like CONCAT('%',#{phone,jdbcType=VARCHAR},'%') </if> <if test="email != null and email != ''" > and team.email like CONCAT('%',#{email,jdbcType=VARCHAR},'%') </if> <if test="zoneId != null and zoneId != ''" > and zone.id = #{zoneId,jdbcType=BIGINT} </if> <if test="startDate != null and endDate != null" > and team.gmt_create between #{startDate,jdbcType=TIMESTAMP} and DATE_ADD(#{endDate,jdbcType=TIMESTAMP}, INTERVAL 1 DAY) </if> <if test="province != null" > and team.province = #{province,jdbcType=VARCHAR} and team.city = #{city,jdbcType=VARCHAR} and team.county = #{county,jdbcType=VARCHAR} </if> order by team.gmt_create desc </select>

主要语句就是

<collection property="staffs" ofType="com.xxx.domain.TeamStaffDO" select="findStaffByTeamId" column="{teamId=id}" />

<resultMap id="staffs" type="com.xxx.TeamInfoDO">
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="name" property="name" jdbcType="VARCHAR" />
  </resultMap>

left join team_staff on team.id = team_staff.team_id