FireDAC（Master-Detail 功能）主从表查询

主从表查询方案优点：

1. FIREDAC 原生支持，性能最优

2. 代码简洁，易于维护

3. 自动关联，减少手动处理错误

4. 内存效率高，只加载需要的数据
   
   JSON格式：
   
   

   {"data": [{"company": "DEMO","creator": "DS","usr_group": "","create_date": "20110607161401513","modifier": "DS","modi_date": "20110607163936093","flag": 6,"list": [{"company": "DEMO","creator": "DS","usr_group": "","create_date": "20110607161401623","modifier": "DS","modi_date": "20110607163936047","flag": 6,}]}],"page": {"pageIndex": 1,"pageSize": 10,"totalCount": 5,"totalPages": 1,"hasPrevious": false,"hasNext": false}
   }

    

   procedure TDBDemoController.GetEmployeesWithDBHelperV9;
   varMasterDBHelper: TDBHelper;DetailDBHelper: TDBHelper;LPageNum, LPageSize, LTotalCount: Integer;LJsonResp: TJDOJsonObject;lSer: TMVCJsonDataObjectsSerializer;
   begin// 分页参数处理LPageNum := StrToIntDef(Context.Request.QueryStringParam('pageIndex'), 1);LPageSize := StrToIntDef(Context.Request.QueryStringParam('pageSize'), 10);LPageNum := Max(1, LPageNum);LPageSize := EnsureRange(LPageSize, 1, 1000);     //第页限制
   MasterDBHelper := TDBHelper.Create(ConnectionDef);DetailDBHelper := TDBHelper.Create(ConnectionDef);try// 先获取总记录数（使用COUNT查询更高效）MasterDBHelper.Query.SQL.Text := 'SELECT COUNT(*) as TotalCount FROM COPTC';MasterDBHelper.Query.Open;tryLTotalCount := MasterDBHelper.Query.FieldByName('TotalCount').AsInteger;finallyMasterDBHelper.Query.Close;end;// 如果没有数据，直接返回空结果if LTotalCount = 0 thenbegin// Render(Format('{"data":[],"totalCount":0,"pageIndex":%d,"pageSize":%d,"hasNext":false}', [LPageNum, LPageSize]));raise EMVCException.Create(HTTP_STATUS.NotFound, 'Not Found');Exit;end;// 设置主从关系DetailDBHelper.Query.MasterSource := TDataSource.Create(nil);try// 先设置主查询并打开MasterDBHelper.Query.SQL.Text := 'SELECT * FROM COPTC ORDER BY TC001, TC002';MasterDBHelper.Query.FetchOptions.RecsSkip := (LPageNum - 1) * LPageSize;MasterDBHelper.Query.FetchOptions.RecsMax := LPageSize;MasterDBHelper.Query.Open;// 设置主从关系（在主查询打开后）DetailDBHelper.Query.MasterSource.DataSet := MasterDBHelper.Query;DetailDBHelper.Query.MasterFields := 'TC001;TC002';DetailDBHelper.Query.IndexFieldNames := 'TD001;TD002';// 明细查询 - FIREDAC 会自动处理关联DetailDBHelper.Query.SQL.Text := 'SELECT * FROM COPTD WHERE TD001 = :TC001 AND TD002 = :TC002 ORDER BY TD001, TD002';DetailDBHelper.Query.Open;LJsonResp := TJDOJsonObject.Create;trylSer := TMVCJsonDataObjectsSerializer.Create;try// 序列化主数据lSer.DataSetToJsonArray(MasterDBHelper.Query, LJsonResp.A['data'], TMVCNameCase.ncLowerCase, [], nil);// 利用 FIREDAC 的主从关系自动关联数据
             MasterDBHelper.Query.First;var CurrentIndex := 0;while not MasterDBHelper.Query.Eof dobegin// 当前主记录对应的明细记录会自动过滤lSer.DataSetToJsonArray(DetailDBHelper.Query, LJsonResp.A['data'].O[CurrentIndex].A['list'], TMVCNameCase.ncLowerCase, [], nil);MasterDBHelper.Query.Next;Inc(CurrentIndex);end;// 分页元数据LJsonResp.O['page'].I['pageIndex'] := LPageNum;LJsonResp.O['page'].I['pageSize'] := LPageSize;LJsonResp.O['page'].I['totalCount'] := LTotalCount;LJsonResp.O['page'].I['totalPages'] := Ceil(LTotalCount / LPageSize); // 计算总页数LJsonResp.O['page'].B['hasPrevious'] := (LPageNum > 1);                         //上一页LJsonResp.O['page'].B['hasNext'] := (LPageNum * LPageSize) < LTotalCount;       //下一页
             Render(LJsonResp, False);finallylSer.Free;end;finally// LJsonResp 由 Render 自动释放FreeAndNil(LJsonResp);end;finallyDetailDBHelper.Query.MasterSource.Free;end;finallyDetailDBHelper.Free;MasterDBHelper.Free;end;
   end;