MyBatisPlus的join联表查询（MPJBaseMapper）

最新推荐文章于 2024-02-20 18:03:42 发布

没有颜值怎么办i

最新推荐文章于 2024-02-20 18:03:42 发布

阅读量3.4k

点赞数 4

分类专栏：工作经验积累文章标签： java Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_60265082/article/details/129274736

版权

工作经验积累专栏收录该内容

10 篇文章 0 订阅

订阅专栏

添加pom依赖

<!--MPJ-->
        <dependency>
            <groupId>com.github.yulichang</groupId>
            <artifactId>mybatis-plus-join</artifactId>
            <version>1.2.4</version>
        </dependency>

Mapper层由继承BaseMapper改为MPJBaseMapper

实际操作：

class test {
    @Resource
    private UserMapper userMapper;

    void testJoin() {
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class,
                new MPJLambdaWrapper<UserDO>()
                        .selectAll(UserDO.class)
                        .select(UserAddressDO::getTel)
                        .selectAs(UserAddressDO::getAddress, UserDTO::getUserAddress)
                        .select(AreaDO::getProvince, AreaDO::getCity)
                        .leftJoin(UserAddressDO.class, UserAddressDO::getUserId, UserDO::getId)
                        .leftJoin(AreaDO.class, AreaDO::getId, UserAddressDO::getAreaId)
                        .eq(UserDO::getId, 1)
                        .like(UserAddressDO::getTel, "1")
                        .gt(UserDO::getId, 5));
    }
}

对应sql

SELECT 
    t.id,
    t.name,
    t.sex,
    t.head_img,
    t1.tel,
    t1.address AS userAddress,
    t2.province,
    t2.city 
FROM 
    user t 
    LEFT JOIN user_address t1 ON t1.user_id = t.id 
    LEFT JOIN area t2 ON t2.id = t1.area_id 
WHERE (
    t.id = ? 
    AND t1.tel LIKE ? 
    AND t.id > ?)

说明:

UserDTO.class 查询结果返回类(resultType)

selectAll() 查询指定实体类的全部字段

select() 查询指定的字段,支持可变参数,同一个select只能查询相同表的字段

故将UserAddressDO和AreaDO分开为两个select()

selectAs() 字段别名查询,用于数据库字段与业务实体类属性名不一致时使用

leftJoin() 参数说明

第一个参数: 参与连表的实体类class

第二个参数: 连表的ON字段,这个属性必须是第一个参数实体类的属性

第三个参数: 参与连表的ON的另一个实体类属性

默认主表别名是t,其他的表别名以先后调用的顺序使用t1,t2,t3…

条件查询,可以查询主表以及参与连接的所有表的字段,全部调用mp原生的方法,正常使用没有sql注入风险

分页查询


    @GetMapping(value = "/list")
    public Result<IPage<BjdbcTerminationApplication>> queryPageList(BjdbcTerminationApplication bjdbcTerminationApplication,
                                   @RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
                                   @RequestParam(name="pageSize", defaultValue="10") Integer pageSize,
                                   HttpServletRequest req) {
        LoginUser user = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        AgencyManagement organName = agencyManagementService.getOrganName(user.getUsername());   //该用户的部门信息
        Page<BjdbcTerminationApplication> page = new Page<BjdbcTerminationApplication>(pageNo, pageSize);
        IPage<BjdbcTerminationApplication>pageList = bjdbcTerminationApplicationMapper.selectJoinPage(
                page,
                BjdbcTerminationApplication.class,
                new MPJLambdaWrapper<>()
                .selectAll(BjdbcTerminationApplication.class)
                .selectAs(SysUser::getRealname,BjdbcTerminationApplication::getRealname)
                .selectAs(AgencyManagement::getOrganName,BjdbcTerminationApplication::getOrganName)
                .selectAs(AgencyManagement::getCompanyCode,BjdbcTerminationApplication::getCompanyCode)
                .leftJoin(SysUser.class,SysUser::getUsername,BjdbcTerminationApplication::getApplicantZh)
                .leftJoin(AgencyManagement.class,AgencyManagement::getId,BjdbcTerminationApplication::getAgencyName)
                .eq(!organName.getLevel().equals("1"),AgencyManagement::getId,organName.getId())
                .ge(StringUtils.isNotEmpty(bjdbcTerminationApplication.getApplyStartTime()),BjdbcTerminationApplication::getCreateTime,bjdbcTerminationApplication.getApplyStartTime())
                .le(StringUtils.isNotEmpty(bjdbcTerminationApplication.getApplyEndTime()),BjdbcTerminationApplication::getCreateTime,bjdbcTerminationApplication.getApplyEndTime())
                //只查看当前部门数据
//                .eq(StringUtils.isNotEmpty(bjdbcTerminationApplication.getAgencyId()),BjdbcTerminationApplication::getAgencyId,bjdbcTerminationApplication.getAgencyId())
                //经办机构名称模糊查询
                .like(StringUtils.isNotEmpty(bjdbcTerminationApplication.getOrganName()),AgencyManagement::getOrganName,bjdbcTerminationApplication.getOrganName())
                .like(StringUtils.isNotEmpty(bjdbcTerminationApplication.getUnitNo()),BjdbcTerminationApplication::getUnitNo,bjdbcTerminationApplication.getUnitNo())
                .eq(StringUtils.isNotEmpty(bjdbcTerminationApplication.getApprovalStatus()),BjdbcTerminationApplication::getApprovalStatus,bjdbcTerminationApplication.getApprovalStatus())
                .orderByDesc(BjdbcTerminationApplication :: getCreateTime)
        );
        return Result.OK(pageList);
    }