Mybatis-Plus 自定义xml多对多分页查询带条件

1.定义实体类

@Data
@TableName("user")
public class User{
    @TableId
    private String id;
    @TableField("username")
    private String name;
    private String password;
    @TableField(exist = false)
    private List<Role> roles;
}

@TableName("role")
@Data
public class Role {
    @TableId
    private String id;
    private String name;
    @TableField(exist = false)
    private List<User> users;
}

2.定义mapper

//自定义xml多对多分页查询带条件
IPage<User> getUserAndRolePageByCondition(IPage<User> page,@Param("query") User query);

3.编写xml

    <resultMap id="getUserAndRolePageByConditionMap" type="com.hj.entity.User">
        <id property="id" column="id"/>
        <result column="username" property="name"/>
        <result column="password" property="password"/>
        <collection property="roles" ofType="com.hj.entity.Role">
            <id property="id" column="rid"/>
            <result property="name" column="rname"/>
        </collection>
    </resultMap>

    <select id="getUserAndRolePageByCondition" resultMap="getUserAndRolePageByConditionMap">
        select u.id, u.username, u.password, r.id rid, r.name rname
        from user u
                 left join user_role ur
                           on u.id = ur.uid
                 left join role r
                           on r.id = ur.rid
        <where>
            <if test="query.id != null and query.id != ''">
                and u.id = #{query.id}
            </if>
            <if test="query.name != null and query.name != ''">
                and u.username = #{query.name}
            </if>
        </where>
    </select>