Object-relational mapping класс для работы с базой данных – пример реализации

Продолжение статьи Object-relational mapping класс для работы с базой данных – общие сведения. Предлагается пример реализации потомка клоссов TCommonEngine, TCommonListEngine, TCommonDocument.
Напомню, что код реализующий базовые классы можно скачать здесь uCommonEngine. Далее приведу реализацию доступа к таблицам tbl_Temp и tbl_TempLines, а так же к объекту который объединяет в единое целое заголовок из tbl_Temp и связные строки из tbl_TempLines. Скачать исходный код можно здесь uTempEngine.

Исходный код реализующий потомков классов TCommonEngine, TCommonListEngine, TCommonDocument

unit uTempEngine;

interface

uses SysUtils, ADODB, DB, Classes, uCarsConsts, Math, uCommonEngine, Variants;

type
  TTemp = class(TCommonEngine)
  private
    FComments: string;
    procedure SetComments(const Value: string);
  public
    constructor Create;override;
    function GetByID: Boolean; override;
    function GetSelectedFields(AFields: TFields): Boolean;override;
    function GetAdditionalInfo: Boolean; override;
    function Insert: Boolean; override;
    function Update: Boolean; override;
    function Delete: Boolean; override;
    function FillParamsInsertOrUpdate(AParameters: TParameters): Boolean; override;
    function FillParametersSelectOrdelete(AParameters: TParameters): Boolean; override;
    {----  Additional function ------------------------------------------------}
    function GenTempNumber: string;
    {----  Fields -------------------------------------------------------------}
 
    property Comments: string read FComments write SetComments;    
end;

  TTempLine = class(TCommonEngine)
  private
    FSid: Integer;
    FItemName: string;
    FTemp_Sid: Integer;
    FTemp_Id: Integer;
    procedure SetDiscount(const Value: Double);
    procedure SetInvoice_Id(const Value: Integer);
    procedure SetInvoice_Sid(const Value: Integer);
    procedure SetisAddedonService(const Value: Boolean);
    procedure SetItemName(const Value: string);
    procedure SetItemUnit(const Value: string);
    procedure SetLineAmount(const Value: Double);
    procedure SetLineAmountWithNDS(const Value: Double);
    procedure SetLineVatAmount(const Value: Double);
    procedure SetObject_(const Value: Integer);
    procedure SetObjectID(const Value: Integer);
    procedure SetPrice(const Value: Double);
    procedure SetQuantity(const Value: Double);
    procedure SetReservedQty(const Value: Double);
    procedure SetSid(const Value: Integer);
    procedure SetVisitItemID(const Value: Integer);
    procedure SetVisitItemSID(const Value: Integer);

  public
    constructor Create;override;
    function GetByID: Boolean; override;
    function GetSelectedFields(AFields: TFields): Boolean;override;
    function GetAdditionalInfo: Boolean; override;
    function Insert: Boolean; override;
    function Update: Boolean; override;
    function Delete: Boolean; override;
    function FillParamsInsertOrUpdate(AParameters: TParameters): Boolean; override;
    function FillParametersSelectOrdelete(AParameters: TParameters): Boolean; override;
    function FillForeignKey(AForeignCodeValues: Pointer): Boolean;override;
    {----  Additional function ------------------------------------------------}
    {----  Fields -------------------------------------------------------------}
    property Sid: Integer read FSid write SetSid;
    property Temp_Id: Integer read FInvoice_Id write SetInvoice_Id;
    property Temp_Sid: Integer read FInvoice_Sid write SetInvoice_Sid;
    property ItemName: string read FItemName write SetItemName;

  end;

  TTempLineList = class(TCommonListEngine)
  public
    constructor Create;override;
    destructor Destroy;override;
    function AddItem: Pointer;override;
    function SetKeyValues(AFields: TFields; AItem: Pointer): Boolean;override;
    function Save(ASaveItems: Boolean = True): Boolean;override;
    function Refresh: Boolean;override;
    function Delete: Boolean; override;
    function GetAll: Boolean; override;
    {----  Additional function ------------------------------------------------}
    procedure FillParams(AParams: TParameters; AParamValues: Pointer);
    {----  Fields -------------------------------------------------------------}
  end;

  TDOCTemp = class(TCommonDocument)
  public
    FTemp: TTemp;
    FTempLineList: TTempLineList;
    constructor Create;override;
    function Save(ASaveItems: Boolean = True): Boolean;override;
    function Delete: Boolean;override;
    function GetByID: Boolean;override;
    function Refresh:Boolean;override;
    function GetAdditionalInfo: Boolean; override;

    function SetAllConnection: Boolean;virtual;
    function SetKeyValuesAll: Boolean;override;
    function SetKeyValuesInHeaders: Boolean;override;
    function SetKeyValuesInItems: Boolean;override;

    destructor Destroy; override;
    {----  Additional function ------------------------------------------------}
    function SetKeyValuesInTempHeader: Boolean;
    function SetKeyValuesInTempLines: Boolean;
    procedure ClearTempLines;
    {----  Fields -------------------------------------------------------------}
  end;

implementation
{ TTempLine }

constructor TTempLine.Create;
begin
  inherited;
  TableName:= 'tbl_TempLines';
  InsertString:=
     'INSERT INTO tbl_TempLines '
    +'  (Sid, Temp_Id, Temp_Sid, Object, ObjectID, '
    +'   Price, Quantity, ReservedQty, Discount, LineAmount, '
    +'   VisitItemID, VisitItemSID, ItemName, ItemUnit, '
    +'   LineAmountWithNDS, LineVatAmount, isAddedonService) '
    +'VALUES '
    +'  (:Sid,:Invoice_Id,:Invoice_Sid,:Object,:ObjectID, '
    +'   :Price,:Quantity,:ReservedQty,:Discount,:LineAmount, '
    +'   :VisitItemID,:VisitItemSID,:ItemName,:ItemUnit, '
    +'   :LineAmountWithNDS,:LineVatAmount,:isAddedonService) ';
  UpDateString:=
     'UPDATE tbl_TempLines '
    +'  SET Temp_Id = :Temp_Id,Temp_Sid = :Temp_Sid, '
    +'    ItemName = :ItemName, '
    +'WHERE id = :id and Sid = :Sid';
  SelectString:=
     'SELECT Id, Sid, Temp_Id, Temp_Sid, Object, '
    +'  ,ItemName, '
    +'FROM tbl_TempLines '
    +'Where Id = :Id and sid = :sid';
  DeleteString:=
    'Delete From tbl_TempLines where id = :id and sid = :sid';
end;

function TTempLine.Delete: Boolean;
begin
  Result:= inherited Delete;
end;

function TTempLine.FillForeignKey(AForeignCodeValues: Pointer): Boolean;
var
  lKeyValues: PKeyValues;
begin
  Result:= inherited FillForeignKey(AForeignCodeValues);
  lKeyValues:= AForeignCodeValues;
  Invoice_Id:= lKeyValues^.ID;
  Invoice_Sid:= lKeyValues^.SID;
end;

function TTempLine.FillParametersSelectOrdelete(
  AParameters: TParameters): Boolean;
begin
  Result:= inherited FillParametersSelectOrdelete(AParameters);
  with AParameters do
  begin
    ParamByName('sid').Value:= Sid;
  end;
end;

function TTempLine.FillParamsInsertOrUpdate(
  AParameters: TParameters): Boolean;
begin
  Result:= inherited FillParamsInsertOrUpdate(AParameters);
  with AParameters do
  begin
    ParamByName('Id').DataType:= ftInteger;
    ParamByName('Id').Value:= Id;

    ParamByName('Sid').DataType:= ftInteger;
    ParamByName('Sid').Value:= FSid;

    ParamByName('Temp_Id').DataType:= ftInteger;
    ParamByName('Temp_Id').Value:= FTemp_Id;

    ParamByName('Temp_Sid').DataType:= ftInteger;
    ParamByName('Temp_Sid').Value:= FTemp_SId;

    ParamByName('ItemName').DataType:= ftString;
    ParamByName('ItemName').Value:= FItemName;
  end;
end;

function TTempLine.GetAdditionalInfo: Boolean;
begin
  Result:= inherited GetAdditionalInfo;
end;

function TTempLine.GetByID: Boolean;
begin
  Result:= inherited GetByID;
end;

function TTempLine.GetSelectedFields(AFields: TFields): Boolean;
begin
  Result:= inherited GetSelectedFields(AFields);
  with AFields do
  begin
    Sid:= FieldByName('Sid').AsInteger;
    Temp_Id:= FieldByName('Temp_Id').AsInteger;
    Temp_Sid:= FieldByName('Temp_Sid').AsInteger;
    ItemName:= FieldByName('ItemName').AsString;
  end;                                                           
end;

function TTempLine.Insert: Boolean;
begin
  Result:= inherited Insert;
end;

procedure TTempLine.SetItemName(const Value: string);
begin
  FItemName := Value;
end;

procedure TTempLine.SetTemp_Id(const Value: Integer);
begin 
  FTemp_Id:= Value;
end;

procedure TTempLine.SetTemp_SId(const Value: Integer); 
begin 
  FTemp_SId:= Value;
end;

function TTempLine.Update: Boolean;
begin
  Result:= inherited Update;
end;

{ TTemp }

constructor TTemp.Create;
begin
  inherited;
  TableName:= 'tbl_Temps';
  InsertString:=
     'INSERT INTO tbl_Temps '
    +'  (Sid,  Comments, ) '
    +'VALUES ');';
  UpDateString:=
     'UPDATE tbl_Temps '
    +'  SET Comments = :Comments '
    +'WHERE id = :id and sid = :sid';
  SelectString:=
     'SELECT id, Sid, Comments '
    +'FROM tbl_Temps '
    +'Where id = :id and Sid = :Sid';
  DeleteString:=
    'Delete From tbl_Temps where id = :id and sid = :sid';
end;

function TTemp.Delete: Boolean;
begin
  Result:= inherited Delete;
end;

function TTemp.FillParametersSelectOrdelete(
  AParameters: TParameters): Boolean;
begin
  Result:= inherited FillParametersSelectOrdelete(AParameters);
  with AParameters do
  begin
    ParamByName('sid').Value:= Sid;
  end;
end;

function TTemp.FillParamsInsertOrUpdate(AParameters: TParameters): Boolean;
begin
  Result:= inherited FillParamsInsertOrUpdate(AParameters);
  with AParameters do
  begin
    ParamByName('Sid').DataType:= ftInteger;
    ParamByName('Sid').Value:= Sid;
   
    ParamByName('Comments').DataType:= ftString;
    ParamByName('Comments').Value:= Comments;   
  end;
end;

function TTemp.GenTempNumber: string;
  var
  lIDStr, lSIDStr: string;
begin
  lIDStr:= IntTostr(ID);
  lSIDStr:= IntToStr(SID);
  Result:= 'СФ-'+StringOfChar('0',3-Length(lSIDStr))+lSIDStr+'-'+
    StringOfChar('0',6-Length(lIDStr))+lIDStr;
end;

function TTemp.GetAdditionalInfo: Boolean;
begin
  Result:= inherited GetAdditionalInfo;
end;

function TTemp.GetByID: Boolean;
begin
  Result:= inherited GetByID;
  FForeignCode:=  GenTempNumber;
end;

function TTemp.GetSelectedFields(AFields: TFields): Boolean;
begin
  Result:= inherited GetSelectedFields(AFields);
  with AFields do
  begin
    Sid:= FieldByName('Sid').AsInteger;
    FComments:= FieldByName('Comments').AsString;
  end;
end;

function TTemp.Insert: Boolean;
begin
  Result:= inherited Insert;
  FForeignCode:=  GenTempNumber;
end;

procedure TTemp.SetAgreementID(const Value: Integer);
begin
  FAgreementID := Value;
end;

procedure TTemp.SetComments(const Value: string);
begin
  FComments := Value;
end;


function TTemp.Update: Boolean;
begin
  Result:= inherited Update;
end;

{ TDOCTemp }


procedure TDOCTemp.ClearTempLines;
begin
  FTempLineList.ClearItems;
end;

constructor TDOCTemp.Create;
begin
  inherited Create;
  OnSetKayValue:= SetKeyValuesInTempHeader;
  FTemp:= TTemp.Create;
  FTemp.OnSetKayValue:= SetKeyValuesInTempLines;
  FObjectList.Add(FTemp);
  FTempLineList:= TTempLineList.Create;
  FObjectItemList.Add(FTempLineList);
end;

function TDOCTemp.Delete: Boolean;
begin
  Result:= inherited Delete;
end;

destructor TDOCTemp.Destroy;
begin

  inherited;
end;


function TDOCTemp.GetAdditionalInfo: Boolean;
begin
  Result:= inherited GetAdditionalInfo;
end;

function TDOCTemp.GetByID: Boolean;
begin
  Result:= inherited GetByID;
end;

function TDOCTemp.Refresh: Boolean;
begin
  Result:= inherited Refresh;
end;

function TDOCTemp.Save(ASaveItems: Boolean = True): Boolean;
begin
  Result:= inherited Save(ASaveItems);
end;

function TDOCTemp.SetAllConnection: Boolean;
begin
  Result:= inherited SetAllConnection;
end;

function TDOCTemp.SetKeyValuesAll: Boolean;
begin
  Result:= inherited SetKeyValuesAll;
end;

function TDOCTemp.SetKeyValuesInTempHeader: Boolean;
begin
  Result:= True;
  FTemp.FConnection:= FConnection;
  FTemp.ID:= ID;
  FTemp.SID:= SID;
end;

function TDOCTemp.SetKeyValuesInTempLines: Boolean;
begin
  Result:= True;
  FTempLineList.ID:= ID;
  FTempLineList.SID:= SID;
end;

function TDOCTemp.SetKeyValuesInHeaders: Boolean;
begin
  Result:= inherited SetKeyValuesInHeaders;
end;

function TDOCTemp.SetKeyValuesInItems: Boolean;
begin
  Result:= inherited SetKeyValuesInItems;
end;

{ TTempLineList }

function TTempLineList.AddItem: Pointer;
var
  lTempLine: TTempLine;
begin
  inherited AddItem;
  lTempLine:= TTempLine.Create;
  FItems.Add(lTempLine);
  lTempLine.Fconnection:= FConnection;
  Result:= Pointer(lTempLine);
end;

constructor TTempLineList.Create;
begin
  inherited;
  SelectAllString:=
     'SELECT Id,Sid '
    +'  FROM tbl_TempLines '
    +'Where Temp_Id = :Temp_Id '
    +'  and Temp_Sid = :Temp_Sid ';
  OnFillParams:= FillParams;
end;

function TTempLineList.Delete: Boolean;
begin
  Result:= inherited Delete;
end;

destructor TTempLineList.Destroy;
begin

  inherited;
end;

procedure TTempLineList.FillParams(AParams: TParameters;
  AParamValues: Pointer);
var
  lKeyValues: PKeyValues;
begin
  lKeyValues:= AParamValues;
  with AParams do
  begin
    ParamByName('Temp_Id').DataType:= ftInteger;
    ParamByName('Temp_Id').Value:= lKeyValues^.ID;

    ParamByName('Temp_SId').DataType:= ftInteger;
    ParamByName('Temp_SId').Value:= lKeyValues^.SID;
  end;
end;

function TTempLineList.GetAll: Boolean;
begin
  Result:= inherited GetAll;
end;

function TTempLineList.Refresh: Boolean;
var
  i: Integer;
  lTempLine: TTempLine;
begin
  for I:= 0 to FItems.Count - 1 do
  begin
    lTempLine:= FItems[i];
    lTempLine.Temp_Id:= ID;
    lTempLine.Temp_Sid:= SID;
  end;
  Result:= inherited Refresh;
end;

function TTempLineList.Save(ASaveItems: Boolean = True): Boolean;
begin
  Result:= inherited Save(ASaveItems);
end;

function TTempLineList.SetKeyValues(AFields: TFields;
  AItem: Pointer): Boolean;
var
  lTempLine: TTempLine;
begin
  Result:= inherited SetKeyValues(AFields,AItem);
  lTempLine:= TTempLine(AItem);
  with AFields do
  begin
    lTempLine.SID:= FieldByName('sid').AsInteger;
  end;
end;

end.

Share

Tags: , , , ,

4 Responses to “Object-relational mapping класс для работы с базой данных – пример реализации”

  1. python пишет:

    Данный пост реально помог мне принять очень важное для себя решение. За что автору отдельное спасибо. Жду от Вас новых постов!

  2. рад что информация оказалась полезной

  3. [...] предлагалась MVC модель, в которой для базы данных были предложены классы, [...]

  4. Рад что пригодилось

Leave a Reply