Mybatis-plus将查询结果封装到指定实体

需求背景:
需要将岗位角色与广告配置建立多对多的关系,通过多选岗位角色作为条件来查询广告配置

在网上看了几篇类似的文章,感觉都不太靠谱,自己总结一下
关键记录下Mybatis-plus中foreach和collection的使用 还有判断集合使用 list.size > 0
废话不多说,直接上代码

实体类

//广告类
@TableName(value = "TB_ADVERTISEMENT")
public class Advertisement {

	@TableId(value = "ID")
    private Long id;
    private String name;
    private String place;
    private String url;
    private String orderNum;
    private List<JobRole> jobRoles;

	//get.. set.. toString..
}

//岗位角色类
@TableName(value = "TB_JOB_ROLE")
public class JobRole {

	@TableId(value = "ID")
    private Long id;
    private String name;

	//get.. set.. toString..
}

//广告和岗位角色关联关系中间表
@TableName(value = "TB_AD_JOB_ROLE")
public class AdJobRole {

	@TableId(value = "ID")
    private Long id;
    private Long aId;//广告id
    private Long jrId;//岗位角色id
    
    //get.. set.. toString..
}

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="xxx.xxx.AdvertisementDao">


    <select id="getPage" resultMap="resultPage">
        select a.id,a,name,a,place,a.url,a.order_num,c.id as "c_id",c.name as "c_name" from TB_ADVERTISEMENT a left join TB_AD_JOB_ROLE b on a.id=b.a_id left join TB_JOB_ROLE c on b.jr_id = c.id
        <where>
            1=1
            <if test="name!=null and name!=''">
                and a.name =#{name}
            </if>
            <if test="place!=null and place!=''">
                and a.place =#{place}
            </if>
            <if test="jobRoleIds!=null and jobRoleIds.size > 0">
                and b.jr_id in
                <foreach collection="jobRoleIds" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <resultMap id="resultPage" type="xxx.xxx.Advertisement">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="place" column="place"/>
        <result property="url" column="url"/>
        <result property="orderNum" column="order_num"/>
        <collection property="jobRoles" ofType="xxx.xxx.JobRole">
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
        </collection>
    </resultMap>
</mapper>

DAO

@Mapper
public interface AdvertisementDao extends BaseMapper<Advertisement> {
    IPage<Advertisement> getPage(Page<?> page, String name, String place, List<Long> jobRoleIds);
}

以上这种方法实现分页查询,因为使用collection,Mybatis对结果集做了处理,所以total会有问题,这是分页工具的BUG.如果要解决这个问题,只有写子查询,如下:

<?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="xxx.xxx.AdvertisementDao">


    <select id="getPage" resultMap="resultPage">
        select a.id,a,name,a,place,a.url,a.order_num from TB_ADVERTISEMENT a 
        <where>
            1=1
            <if test="name!=null and name!=''">
                and a.name =#{name}
            </if>
            <if test="place!=null and place!=''">
                and a.place =#{place}
            </if>
            <!--这里无法将jobRoleIds作为参数传递到子查询中-->
        </where>
    </select>

    <resultMap id="resultPage" type="xxx.xxx.Advertisement">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="place" column="place"/>
        <result property="url" column="url"/>
        <result property="orderNum" column="order_num"/>
        <collection property="jobRoles"  column="id" ofType="xxx.xxx.JobRole"  select="getJobRoles" />
    </resultMap>
    
	<select id="getJobRoles" resultMap="JobRole" >
        select c.id,c.name from  TB_AD_JOB_ROLE b on a.id=b.a_id left join TB_JOB_ROLE c on b.jr_id = c.id where b.a_id = #{id}
    </select>

    <resultMap id="JobRole" type="xxx.xxx.JobRole">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>
</mapper>

DAO

@Mapper
public interface AdvertisementDao extends BaseMapper<Advertisement> {
    IPage<Advertisement> getPage(Page<?> page, String name, String place);
}

但是这种方法会性能上有N+1问题,慎用