mybatis多个查询条件三表分页联查

背景

用户表wx_user和活动表act_publish是多对多关系，因此有中间表act_user_activity。要求分页多条件查询用户报名参加活动信息。其中所有条件可有可无。

查询条件dto

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ActListQuery {

    @ApiModelProperty(value = "活动名称")
    private String title;

    @ApiModelProperty(value = "活动开始时间")
    private String activityTime;

    @ApiModelProperty(value = "活动地点")
    private String activityPlace;

    @ApiModelProperty(value = "参与人姓名")
    private String userName;

    @ApiModelProperty(value = "报名状态")
    private String status;


}

查询结果vo

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserActVo {

    @ApiModelProperty(value = "0待完成  1已完成")
    private String status;

    private String openid;

    private String actId;

    private String nickname;

    private String phone;

    @ApiModelProperty(value = "用户图片")
    private String image;

    private String sex;

    private String title;

    private String activityTime;

    private String activityPlace;

    private String activityDetail;


    @ApiModelProperty(value = "活动是否过期")
    private String hasOpen;

    @ApiModelProperty(value = "活动图片")
    private String img;

    @ApiModelProperty(value = "预约开放时间")
    private String intervalStartTime;

    @ApiModelProperty(value = "预约结束时间")
    private String intervalEndTime;

controller

@ApiOperation("分页多条件查询用户报名的活动")
    @PostMapping("pageAct/{current}/{limit}")
    public Result pageListAct(@PathVariable Long current,
                              @PathVariable Long limit,
                              @ApiParam("查询活动多条件组合")
                              @RequestBody ActListQuery actListQuery)
    {
        return actListService.pageActList(current,limit,actListQuery);
    }

service

public interface ActListService extends IService<ActList>
{
    Result findAll();
}

serviceImpl

@Service
public class ActListServiceImpl extends ServiceImpl<ActListMapper, ActList> implements ActListService
{
	    @Override
    public Result pageActList(Long current, Long limit, ActListQuery actListQuery)
    { 
    	List<UserActVo> res = actListMapper.selectUserActivity(current,limit,actListQuery);
        int length = res.size();
        return Result.success().data("total",length).data("rows",res);
    }
}

mapper接口

public interface ActListMapper extends BaseMapper<ActList>{

    List<UserActVo> selectUserActivity(Long current, Long limit, ActListQuery actListQuery);
}

mapper文件

<?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.guigusuqi.act.mapper.ActListMapper">

    <select id="selectUserActivity" resultType="com.guigusuqi.act.dto.UserActVo"  >
select * from act_user_activity as ua , wx_user as u , act_publish as a
        <where>
            ua.openid = u.openid and ua.act_id = a.id
            <if test=" null!= actListQuery.title and '' != actListQuery.title" >
                and title like #{actListQuery.title}
            </if>
            <if test="null != actListQuery.activityTime and '' != actListQuery.activityTime ">
                and activity_time &gt;= #{actListQuery.activityTime}
            </if>
            <if test="null != actListQuery.activityPlace and '' != actListQuery.activityPlace ">
                and activity_place like #{actListQuery.activityPlace}
            </if>
            <if test="null != actListQuery.userName and '' != actListQuery.userName ">
                and nickname like #{actListQuery.userName}
            </if>
            <if test="null != actListQuery.status and '' != actListQuery.status ">
                and status = #{actListQuery.status}
            </if>
        </where>
        limit #{current},#{limit};
    </select>
</mapper>

总结

虽然用了MybatisPlus，但是本功能没有用任何封装好的函数，基本是纯mybatis。不过有一点的是在mybatis传参的时候我是把普通参数和对象参数无脑全部放进去了，也没有@Parms注解，在xml中就直接对象+“.”调用了，不知道是不是mybatisPlus在幕后帮忙。