主从表查询方案优点:
-
FIREDAC 原生支持,性能最优
-
代码简洁,易于维护
-
自动关联,减少手动处理错误
-
内存效率高,只加载需要的数据
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;
中年大叔学Delphi