Mybatis Link（MyBatis Plus X）连表查询设置返回字段，以及统计查询应用（4）

五. MyBatis Link 连表查询设置返回字段，以及统计查询应用

1. 连表查询返回字段设置

    设置返回结果字段有三种方式，每一种方式达到的效果是一样的。

    如果连表查询没有设置别名，推荐使用第一种方式，书写简单，如果设置了别名可以使用第三种方式。

    具体如下：

    1. fw.select(TestAVo.class, "c_id", "crt_tm");

    @Log
    @ApiOperation(value = "listTestAATestB") 
    @GetMapping("listTestAATestB")
    public Object listTestAATestB(String query) { 
        FindWrapper<TestAVo> fw = getWrapper(query, TestAVo.class);
        // fw.addTableName("t_test_a", "t_test_a_ro");
        // fw.addTableName("t_test_b", "t_test_b_ro");
        
        fw.select(TestAVo.class, "c_id", "crt_tm");
        fw.select(TestBVo.class, "c_id", "crt_tm");
        // fw.select("t_test_a.c_id", "t_test_a.crt_tm", "t_test_b.c_id", "t_test_b.crt_tm");
        // fw.selectAlias("t_test_a", "c_id", "crt_tm");
        // fw.selectAlias("t_test_b", "c_id", "crt_tm");
        List<TestADto> list = testAMgr.listTestAATestB(fw);
        return buildList(list);
    }

    2. fw.select("t_test_a.c_id", "t_test_a.crt_tm", "t_test_b.c_id", "t_test_b.crt_tm");

    @Log
    @ApiOperation(value = "listTestAATestB") 
    @GetMapping("listTestAATestB")
    public Object listTestAATestB(String query) { 
        FindWrapper<TestAVo> fw = getWrapper(query, TestAVo.class);
        // fw.addTableName("t_test_a", "t_test_a_ro");
        // fw.addTableName("t_test_b", "t_test_b_ro");
        
        // fw.select(TestAVo.class, "c_id", "crt_tm");
        // fw.select(TestBVo.class, "c_id", "crt_tm");
        fw.select("t_test_a.c_id", "t_test_a.crt_tm", "t_test_b.c_id", "t_test_b.crt_tm");
        // fw.selectAlias("t_test_a", "c_id", "crt_tm");
        // fw.selectAlias("t_test_b", "c_id", "crt_tm");
        List<TestADto> list = testAMgr.listTestAATestB(fw);
        return buildList(list);
    }

    3. fw.selectAlias("t_test_a", "c_id", "crt_tm");

    @Log
    @ApiOperation(value = "listTestAATestB") 
    @GetMapping("listTestAATestB")
    public Object listTestAATestB(String query) { 
        FindWrapper<TestAVo> fw = getWrapper(query, TestAVo.class);
        // fw.addTableName("t_test_a", "t_test_a_ro");
        // fw.addTableName("t_test_b", "t_test_b_ro");
        
        // fw.select(TestAVo.class, "c_id", "crt_tm");
        // fw.select(TestBVo.class, "c_id", "crt_tm");
        // fw.select("t_test_a.c_id", "t_test_a.crt_tm", "t_test_b.c_id", "t_test_b.crt_tm");
        fw.selectAlias("t_test_a", "c_id", "crt_tm");
        fw.selectAlias("t_test_b", "c_id", "crt_tm");
        List<TestADto> list = testAMgr.listTestAATestB(fw);
        return buildList(list);
    }

    三种方式返回一样的结果

{
  "code": 0,
  "data": {
    "list": [
      {
        "testAVo2": null,
        "testAVo": {
          "crtTm": "2017-11-07 15:17:11",
          "crtBy": null,
          "updTm": null,
          "updBy": null,
          "editFlag": null,
          "id": null,
          "bid": null,
          "aid": null,
          "cid": 1
        },
        "testBVo": {
          "crtTm": "2017-11-07 15:17:11",
          "crtBy": null,
          "updTm": null,
          "updBy": null,
          "editFlag": null,
          "id": null,
          "aid": null,
          "cid": 1
        },
        "testCVo": null,
        "testDVo": null,
        "testBList": null,
        "testCList": null
      }
    ]
  }
}

2. 统计查询应用

    如果想要对某张表的某个字段进行求和

    1）返回结果是Dto

    1.TestEDto对象增加字段sumA1

/**
 * @author yuyi ([e-mail])
 * @since 2019-03-20
 */
@Data
public class TestEDto implements Serializable {
    private static final long serialVersionUID = 1L;

    protected TestEVo testEVo;
    protected Integer sumA1;

}

    2.实现方法

    @Log
    @ApiOperation(value = "统计查询") 
    @GetMapping("test1")
    // @RequiresPermissions("test:e:info")
    public Object test1(String query) {
        FindWrapper<TestEVo> fw = getWrapper(query, TestEVo.class);
        fw.select("sum(a1) as sumA1");
        TestEDto result = testEMgr.get(fw);
        // List<Map<String, Object>> result = testEMgr.listMaps(fw);
        return build(result); 
    } 

    3.返回结果

{
  "code": 0,
  "data": {
    "testEVo": null,
    "sumA1": 17
  }
}

  2）返回结果是Map

    1.实现方法

    @Log
    @ApiOperation(value = "统计查询") 
    @GetMapping("test1")
    // @RequiresPermissions("test:e:info")
    public Object test1(String query) {
        FindWrapper<TestEVo> fw = getWrapper(query, TestEVo.class);
        fw.select("sum(a1) as sumA1");
        // TestEDto result = testEMgr.get(fw);
        List<Map<String, Object>> result = testEMgr.listMaps(fw);
        return build(result); 
    } 

    2.返回结果

{
  "code": 0,
  "data": {
    "list": [
      {
        "sumA1": 17
      }
    ]
  }
}