526互联

mybatisplus实现一次多表联查+分页查询

发布时间 2023-11-21 17:26:29作者: 且行且思

以书籍整理为例，书籍是一个单独的表，书籍类别是另一个表，通过书籍通过类别的ID可以查询到书籍类别的具体名称，下面分享一下代码：

//mapper
IPage<BookInfo> selectBookPage(IPage<BookInfo> page, @Param(Constants.WRAPPER) QueryWrapper<BookInfo> wrapper);
 
//service
public IPage<BookInfo> getOne(Params params){
    IPage<BookInfo> pages = new Page<>(params.getPageNum(), params.getPageSize());
    QueryWrapper<BookInfo> queryWrapper = new QueryWrapper<>();
    if (!StringUtils.isBlank(params.getName())) {
        queryWrapper.like("book.name", "%" + params.getName() + "%");
　　　　　　　　　　　　　　　　//这里的列名不添加上去可能会有冲突，我两个表里都有name字段
    }
    if (!StringUtils.isBlank(params.getAuthor())) {
        queryWrapper.like("author",  "%" +params.getAge()+ "%");
    }
    return bookMapper.selectBookPage(pages,queryWrapper);
}

 

//bookMapper.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="com.example.mapper.BookMapper">
    <resultMap id="orderMap" type="com.example.entity.vo.BookInfo">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="price" column="price"/>
        <result property="author" column="author"/>
        <result property="press" column="press"/>
        <result property="img" column="img"/>
        <result property="typeId" column="typeId"/>
        <result property="typeName" column="typeName"/>
    </resultMap>
    <select id="selectBookPage" resultMap="orderMap">
        select book.*,type.name as typeName
        from book left join type on book.typeId =type.id
        ${ew.customSqlSegment}
    </select>
</mapper>