如何实现后端开发框架（六）-多表自定义查询

如何实现后端开发框架（六）-多表自定义查询

1. 问题描述

Mybatis-Plus中对单表的处理比较完善，提供了很多实现方法，但是在实际开发过程中遇到的很多都是多表之间的联合查询，那么该如何处理呢？

2. 实现思路

Mybatis-Plus提供了一个内置变量${ew.customSqlSegment}，这个变量值存储的是queryWrapper转化后的SQL语句，我们可以利用这个参数值来简化多表联合查询的处理。

3. 实现步骤

以下代码将会使用《如何实现后端开发框架（四）-分页查询》和《如何实现后端开发框架（五）-单表自定义查询》中的工具类。

3.1 多表实体类

用户表：

@Data
@EqualsAndHashCode(callSuper = true)
@TableName("SECURITY_USER")
public class User extends MyBaseEntity {
  private static final long serialVersionUID = 1L;

  @TableField(value = "ACCOUNT")
  private String account;

  @TableField("REAL_NAME")
  private String realName;

  @TableField("SORT")
  private Integer sort;

  @TableField(exist = false)
  private String orgName;
}

组织表：

@Data
@EqualsAndHashCode(callSuper = true)
@TableName("SECURITY_ORGANIZATION")
public class Organization extends MyBaseEntity {
  private static final long serialVersionUID = 1L;

  @TableField(value = "NAME")
  private String name;
}

用户组织关联表：

@Data
@EqualsAndHashCode(callSuper = true)
@TableName("SECURITY_ORGANIZATION_USER_REL")
public class OrganizationUserRel extends MyBaseEntity {
  private static final long serialVersionUID = 1L;

  @TableField("ORG_ID")
  private String orgId;

  @TableField("USER_ID")
  private String userId;

  @TableField("RELATION_TYPE")
  private String relationType;
}

3.2 Service实现类

Service接口类：

public interface UserService extends MyBaseService<User> {
  List<User> listTest(Wrapper wrapper);

  MyPage<User> pageListTest(MyPage<User> page, Wrapper wrapper);
}

ServiceImpl实现类：

@Service
public class UserServiceImpl extends MyBaseServiceImpl<UserMapper, User> implements UserService {
  @Override
  public List<User> listTest(Wrapper wrapper) {
    List<User> result = baseMapper.listTest(wrapper);
    return result;
  }

  @Override
  public MyPage<User> pageListTest(MyPage<User> page, Wrapper wrapper) {
    MyPage<User> result = baseMapper.pageListTest(page, wrapper);
    return result;
  }
}

3.3 Mapper实现类

Mapper接口类：

public interface UserMapper extends MyBaseMapper<User> {
  @SelectProvider(type = UserSqlProvider.class, method = "listTest")
  List<User> listTest(@Param(Constants.WRAPPER) Wrapper wrapper);

  @SelectProvider(type = UserSqlProvider.class, method = "pageListTest")
  MyPage<User> pageListTest(MyPage<User> page, @Param(Constants.WRAPPER) Wrapper wrapper);
}

MapperProvider实现类：

public class UserSqlProvider {
  public String listTest(@Param(Constants.WRAPPER) Wrapper wrapper) {
    String sql =
        "SELECT A.*,C.NAME AS orgName FROM SECURITY_USER AS A INNER JOIN SECURITY_ORGANIZATION_USER_REL AS B ON A.ID = B.USER_ID INNER JOIN SECURITY_ORGANIZATION AS C ON B.ORG_ID = C.ID ";
    sql += " ${ew.customSqlSegment} ";
    return sql;
  }

  public String pageListTest(@Param(Constants.WRAPPER) Wrapper wrapper) {
    String sql =
        "SELECT A.*,C.NAME AS orgName FROM SECURITY_USER AS A INNER JOIN SECURITY_ORGANIZATION_USER_REL AS B ON A.ID = B.USER_ID INNER JOIN SECURITY_ORGANIZATION AS C ON B.ORG_ID = C.ID ";
    sql += " ${ew.customSqlSegment} ";
    return sql;
  }
}

4. 测试代码

@RestController
@RequestMapping("/test/user")
public class UserController extends MyBaseController<UserService, User> {
  /**
   * 根据条件查询所有记录
   *
   * @param user
   * @return
   */
  @RequestMapping(value = "/list", method = RequestMethod.POST)
  public List<User> list(@RequestBody(required = false) User user) {
    List<SqlParam> sqlParams = new ArrayList<>();
    // A:用户表；B:用户组织关联表；C:组织表
    SqlParam sqlParam1 = new SqlParam("C.NAME", user.getOrgName(), SqlOperator.EQ);
    SqlParam sqlParam2 = new SqlParam("A.REAL_NAME", user.getRealName(), SqlOperator.LIKE);
    sqlParams.add(sqlParam1);
    sqlParams.add(sqlParam2);
    QueryWrapper queryWrapper = DaoUtils.generateQueryWrapper(sqlParams);
    List<User> result = myBaseService.listTest(queryWrapper);
    return result;
  }

  /**
   * 根据条件分页查询记录
   *
   * @param user
   * @return
   */
  @RequestMapping(value = "/pageList", method = RequestMethod.POST)
  public MyPage<User> pageList(@RequestBody User user) {
    List<SqlParam> sqlParams = new ArrayList<>();
    // A:用户表；B:用户组织关联表；C:组织表
    SqlParam sqlParam1 = new SqlParam("C.NAME", user.getOrgName(), SqlOperator.EQ);
    SqlParam sqlParam2 = new SqlParam("A.REAL_NAME", user.getRealName(), SqlOperator.LIKE);
    sqlParams.add(sqlParam1);
    sqlParams.add(sqlParam2);
    QueryWrapper queryWrapper = DaoUtils.generateQueryWrapper(sqlParams);
    MyPage<User> page = new MyPage<>(user.getCurrentPage(), user.getPageSize());
    MyPage<User> result = myBaseService.pageListTest(page, queryWrapper);
    return result;
  }
}

5. 完整代码

完整代码见以下Git仓库中的multiple-table-query子项目：

https://github.com/randy0098/framework-samples