Mybatis plus入门（三）：连表查询，自定义sql

目录

• 模糊、分页、统计总条数、调用自定义的getUserBooks方法，传入参数Page和QueryWrapper

代码案例

• 模糊、分页、统计总条数、调用自定义的getUserBooks方法，传入参数Page和QueryWrapper

IPage<UserBookRespDTO> getUserBooks(@Param("username") String username, @Param("sex") String sex, @Param("bookname") String bookname, @Param("page") Page<UserBookRespDTO> page, @Param(Constants.WRAPPER) Wrapper<UserBookRespDTO> wrapper);

    <select id="getUserBooks" resultType="com.ychen.mybatis.demo02.model.dto.UserBookRespDTO">
        SELECT u.id, u.username, u.password, u.sex, u.birthday, b.bookname, b.author, b.price
        FROM user AS u LEFT JOIN book AS b ON u.username = b.author
        <where>
            <if test="username !=null and username!=''">
                or u.username like concat('%',#{username},'%')
            </if>
            <if test="sex !=null and sex!=''">
                or u.sex like concat('%',#{sex},'%')
            </if>

            <if test="bookname != null and bookname!=''">
                or b.bookname like concat('%',#{bookname}'%')
            </if>
        </where>
        ${ew.customSqlSegment}
    </select>

    @RequestMapping("/test2")
    @ResponseBody
    public String test2(@RequestBody UserBookReqDTO req){
        int pageNo = 1;
        int pageSize = 3;
        String username = req.getUsername();
        String sex = req.getSex();
        String bookname = req.getBookname();
        // 构建wrapper对象
        QueryWrapper<UserBookRespDTO> wrapper = new QueryWrapper<>();
        // 构建page对象
        Page<UserBookRespDTO> page = new Page<UserBookRespDTO>(pageNo, pageSize);
        // 调用mapper层方法
        IPage<UserBookRespDTO> userPageList = userMapper.getUserBooks(username, sex, bookname, page, wrapper);
        System.out.println(userPageList);
        return "success";
    }