mybatis-plus联表分页查询

1 准备数据库

-- auto-generated definition
create table tb_account
(
    id        int auto_increment
        primary key,
    user_name varchar(100) null,
    age       int          null,
    birthday  datetime     null,
    json_info json         null
);

-- auto-generated definition
create table tb_info
(
    id   int auto_increment
        primary key,
    name varchar(20) not null,
    info varchar(50) not null
);

2 导入依赖

    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.3.1</version>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.18</version>
    </dependency>

3 创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class TbAccount {

    @TableId(type = IdType.AUTO)
    Integer id;

    String userName;

    Integer age;

    Date birthday;

    String jsonInfo;

}

@Data
public class AccountVO {
    Integer id;

    String userName;

    Integer age;

    Date birthday;

    String info;
}

4 添加分页拦截器

@Configuration
public class MybatisConfig {
    /**
     * 分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

}

5 编写mapper层代码

@Mapper
public interface TbAccountMapper extends BaseMapper<TbAccount> {

    IPage<AccountVO> selectAccountWithInfo(
                            IPage<AccountVO> page, 
                            @Param("ew") QueryWrapper<AccountVO> wrapper);
}

6 编写mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wuhangji.mybatisplus.mapper.TbAccountMapper">

    <resultMap id="accountMap" type="com.wuhangji.mybatisplus.entity.VO.AccountVO">
        <id property="id" column="id"/>
        <result column="user_name" property="userName" jdbcType="VARCHAR"/>
        <result column="age" property="age" jdbcType="INTEGER"/>
        <result column="birthday" property="birthday" jdbcType="TIMESTAMP"/>
        <result column="info" property="info" jdbcType="VARCHAR"/>
    </resultMap>

    <select id="selectAccountWithInfo" resultMap="accountMap">
        SELECT account.*, info.info FROM `tb_account` account 
        LEFT JOIN `tb_info` info ON info.name = account.user_name
        <if test="ew!=null">
            where ${ew.sqlSegment}
        </if>
    </select>
</mapper>

7 编写测试代码

    @Autowired
    private ITbAccountService iTbAccountService;

    @Test
    public void testSelect() {

        Page<AccountVO> accountVOPage = new Page<>(1,2);

        QueryWrapper<AccountVO> objectQueryWrapper = new QueryWrapper<>();
        objectQueryWrapper.ge("info.id",1);
        objectQueryWrapper.le("info.id",10);


        IPage<AccountVO> accountVOS = 											tbAccountMapper.selectAccountWithInfo(accountVOPage, objectQueryWrapper);

        System.out.println(accountVOS.getRecords());

    }

8 查看结果

[
    AccountVO(id=2, 
              userName=李四, 
              age=1000000, 
              birthday=Sun Mar 21 00:00:00 CST 2021, 
              info=李连杰的李), 
    AccountVO(id=4, 
              userName=第四条, 
              age=1000000, 
              birthday=Mon Aug 28 09:24:36 CST 2023, 
              info=44444)
]