如何實(shí)現(xiàn)后端開(kāi)發(fā)框架（六）-多表自定義查詢

1. 問(wèn)題描述

Mybatis-Plus中對(duì)單表的處理比較完善，提供了很多實(shí)現(xiàn)方法，但是在實(shí)際開(kāi)發(fā)過(guò)程中遇到的很多都是多表之間的聯(lián)合查詢，那么該如何處理呢伊者？

2. 實(shí)現(xiàn)思路

Mybatis-Plus提供了一個(gè)內(nèi)置變量${ew.customSqlSegment}，這個(gè)變量值存儲(chǔ)的是queryWrapper轉(zhuǎn)化后的SQL語(yǔ)句，我們可以利用這個(gè)參數(shù)值來(lái)簡(jiǎn)化多表聯(lián)合查詢的處理箕慧。

3. 實(shí)現(xiàn)步驟

以下代碼將會(huì)使用《如何實(shí)現(xiàn)后端開(kāi)發(fā)框架（四）-分頁(yè)查詢》和《如何實(shí)現(xiàn)后端開(kāi)發(fā)框架（五）-單表自定義查詢》中的工具類。

3.1 多表實(shí)體類

用戶表：

@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;
}

用戶組織關(guān)聯(lián)表：

@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實(shí)現(xiàn)類

Service接口類：

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

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

ServiceImpl實(shí)現(xiàn)類：

@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實(shí)現(xiàn)類

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實(shí)現(xiàn)類：

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. 測(cè)試代碼

@RestController
@RequestMapping("/test/user")
public class UserController extends MyBaseController<UserService, User> {
  /**
   * 根據(jù)條件查詢所有記錄
   *
   * @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:用戶組織關(guān)聯(lián)表颠焦；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;
  }

  /**
   * 根據(jù)條件分頁(yè)查詢記錄
   *
   * @param user
   * @return
   */
  @RequestMapping(value = "/pageList", method = RequestMethod.POST)
  public MyPage<User> pageList(@RequestBody User user) {
    List<SqlParam> sqlParams = new ArrayList<>();
    // A:用戶表；B:用戶組織關(guān)聯(lián)表往枣；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. 完整代碼

完整代碼見(jiàn)以下Git倉(cāng)庫(kù)中的multiple-table-query子項(xiàng)目：

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