delphi ORM和泛型模板

delphi中间件發表於2024-03-30

delphi ORM和泛型模板

1）定義資料模型（data-model）

資料模型是ORM資料序列/還原所必需的。

  TTable<T: record> = record      //1個表
    rows: TArray<T>;              //表的行
  end;

  TTable2<T, T2: record> = record  //2個表
    table1: TTable<T>;             //表1
    table2: TTable<T2>;            //表2
  end;

  TTable3<T, T2, T3: record> = record  //3個表
    table1: TTable<T>;                 //表1
    table2: TTable<T2>;                //表2
    table3: TTable<T3>;                //表3
  end;

　2）實現ORM CRUD泛型模板

/// <author>cxg 2024-3-30</author>
unit db.crud;

interface

uses
  Data.DB,
  System.Classes, System.SysUtils, serialize, yn.log, db.unidacpool, db.unidac,
  global;

type
  TCRUD<T: record> = record  //1個表
    dbid: string;
    func: string;
    sql: string;
    db: TDB;
    table: TTable<T>;
    req: TRequest;
    res: TResponse;
    type
      TTableModel = reference to procedure(db: TDB; table: TTable<T>; i: integer);
    procedure open(OnTableModel: TTableModel);  //查詢
    procedure execsql(OnTableModel: TTableModel);  //執行事務性SQL
  end;

  TCRUD2<T, T2: record> = record   //2個表
    dbid: string;
    func: string;
    sqls: array of string;
    db: TDB;
    tables: TTable2<T, T2>;
    req: TRequest;
    res: TResponse;
    type
      TTableModel2 = reference to procedure(db: TDB; tables: TTable2<T, T2>; i: integer);
    procedure open(OnTableModel, OnTableModel2: TTableModel2);  //查詢
    procedure execsql(OnTableModel, OnTableModel2: TTableModel2);  //執行事務性SQL
  end;

implementation

{ TCRUD<T> }

procedure TCRUD<T>.execsql(OnTableModel: TTableModel);
//執行事務性SQL
begin
  if req.Body = nil then
    Exit;
  var pool: TDBPool := GetDBPool(dbid);  //database pool
  db := pool.Lock;
  try
    try
      table := serialize.TSerial<TTable<T>>.unjson(TStream(req.Body));  //json string--->record
      db.startTrans;                //開啟事務
      for var i: Integer := 0 to High(table.rows) do
      begin
        db.qry.Close;
        db.qry.SQL.Clear;
        db.qry.sql.add(sql);
        OnTableModel(db, table, i);  //set field value
        db.qry.ExecSQL;
      end;
      db.commitTrans;         //提交事務
      res.Send(success);
    except
      on E: Exception do
      begin
        db.rollbackTrans;     //回滾事務
        res.Send(error(E.Message));
        WriteLog(func + E.Message);
      end;
    end;
  finally
    pool.Unlock(db);
  end;
end;

procedure TCRUD<T>.open(OnTableModel: TTableModel);
//查詢
begin
  var pool: TDBPool := GetDBPool(dbid);
  db := pool.Lock;
  try
    try
      var where: string;              //where條件
      if req.Body <> nil then
        where := TEncoding.UTF8.GetString(TBytesStream(req.Body).Bytes);
      var lsql: string;
      if where = '' then             //拼SQL查詢命令
        lsql := sql
      else
        lsql := sql + ' where ' + where;
      db.select(lsql);       //查詢
      SetLength(table.rows, db.qry.RecordCount);    //記錄條數
      var i: Integer := 0;
      db.qry.First;                    //dataset--->record
      while not db.qry.Eof do
      begin
        OnTableModel(db, table, i);    //set field value
        db.qry.Next;
        Inc(i);
      end;
      res.Send(TSerial<TTable<T>>.json(table));   //send json string
    except
      on E: Exception do
      begin
        res.Send(error(E.Message));
        writelog(func + E.Message);
      end;
    end;
  finally
    pool.Unlock(db);
  end;
end;

{ TCRUD2<T, T2> }

procedure TCRUD2<T, T2>.execsql(OnTableModel, OnTableModel2: TTableModel2);
//2個表執行事務性SQL
begin
  if req.Body = nil then
    Exit;
  var pool: TDBPool := GetDBPool(dbid);  //database pool
  db := pool.Lock;
  try
    try
      tables := serialize.TSerial<TTable2<T, T2>>.unjson(TStream(req.Body));  //json string--->record
      db.startTrans;      //開啟事務
      for var i: Integer := 0 to High(tables.table1.rows) do  //遍歷table1 record
      begin
        db.qry.Close;
        db.qry.SQL.Clear;
        db.qry.sql.add(sqls[0]);
        OnTableModel(db, tables, i);    //set field value
        db.qry.ExecSQL;
      end;
      for var i: Integer := 0 to High(tables.table2.rows) do   //遍歷table2 record
      begin
        db.qry.Close;
        db.qry.SQL.Clear;
        db.qry.sql.add(sqls[1]);
        OnTableModel2(db, tables, i);  //set field value
        db.qry.ExecSQL;
      end;
      db.commitTrans;     //提交事務
      res.Send(success);
    except
      on E: Exception do
      begin
        db.rollbackTrans;   //回滾事務
        res.Send(error(E.Message));
        WriteLog(func + E.Message);
      end;
    end;
  finally
    pool.Unlock(db);
  end;
end;

procedure TCRUD2<T, T2>.open(OnTableModel, OnTableModel2: TTableModel2);
//2個表查詢
begin
  var pool: TDBPool := GetDBPool(dbid);   //database pool
  db := pool.Lock;
  try
    try
      db.select(sqls[0]);    //table1 查詢
      SetLength(tables.table1.rows, db.qry.RecordCount);    //記錄條數
      var i: Integer := 0;
      db.qry.First;
      while not db.qry.Eof do
      begin
        OnTableModel(db, tables, i);   //set field value
        db.qry.Next;
        Inc(i);
      end;
      db.select(sqls[1]);     //table2 查詢
      SetLength(tables.table2.rows, db.qry.RecordCount);  //記錄條數
      i := 0;
      db.qry.First;
      while not db.qry.Eof do
      begin
        OnTableModel2(db, tables, i);   //set field value
        db.qry.Next;
        Inc(i);
      end;
      res.Send(TSerial<TTable2<T, T2>>.json(tables));  //send json string
    except
      on E: Exception do
      begin
        res.Send(error(E.Message));
        writelog(func + E.Message);
      end;
    end;
  finally
    pool.Unlock(db);
  end;
end;

end.

　3）透過CRUD泛型模板實現資源（resource）資料操作

3.1）單表的

unit danwei;
/// <author>cxg 2024-1-21</author>

interface

uses
  db.crud, yn.log, danwei.model, global,
  db.unidac, system.Classes, serialize, System.SysUtils;

type
  TRESTdanwei = class(Trpc)  //單位的遠端方法類
    procedure select(const req: TRequest; const res: TResponse);  //查詢
    procedure insert(const req: TRequest; const res: TResponse);  //新增
    procedure update(const req: TRequest; const res: TResponse);  //修改
    procedure delete(const req: TRequest; const res: TResponse);  //刪除
  end;

implementation

procedure TRESTdanwei.select(const req: TRequest; const res: TResponse);
//查詢
begin
  var crud: TCRUD<Tdanwei>;
  crud.dbid := '1';
  crud.func := 'TRESTdanwei.select()';
  crud.req := req;
  crud.res := res;
  crud.sql := 'select * from tunit';
  crud.open(
    procedure(db: TDB; table: TTable<Tdanwei>; i: Integer)
    begin
      table.rows[i].unitid := db.qry.FieldByName('unitid').AsString;//dataset-->model
      table.rows[i].unitname := db.qry.FieldByName('unitname').AsString;
    end);
end;

procedure TRESTdanwei.delete(const req: TRequest; const res: TResponse);
//刪除
begin
  var crud: TCRUD<Tdanwei>;
  crud.dbid := '1';
  crud.func := 'TRESTdanwei.delete()';
  crud.req := req;
  crud.res := res;
  crud.sql := 'delete from tunit where unitid=:unitid';
  crud.execsql(
    procedure(db: TDB; table: TTable<Tdanwei>; i: Integer)
    begin
      db.qry.ParamByName('unitid').AsString := table.rows[i].unitid;
    end);
end;

procedure TRESTdanwei.insert(const req: TRequest; const res: TResponse);
//新增
begin
  var crud: TCRUD<Tdanwei>;
  crud.dbid := '1';
  crud.func := 'TRESTdanwei.insert()';
  crud.req := req;
  crud.res := res;
  crud.sql := 'insert into tunit(unitid,unitname) values (:unitid,:unitname)';
  crud.execsql(
    procedure(db: TDB; table: TTable<Tdanwei>; i: Integer)
    begin
      db.qry.ParamByName('unitid').AsString := table.rows[i].unitid;
      db.qry.ParamByName('unitname').AsString := table.rows[i].unitname;
    end);
end;

procedure TRESTdanwei.update(const req: TRequest; const res: TResponse);
//修改
begin
  var crud: TCRUD<Tdanwei>;
  crud.dbid := '1';
  crud.func := 'TRESTdanwei.update()';
  crud.req := req;
  crud.res := res;
  crud.sql := 'update tunit set unitid=:unitid,unitname=:unitname where unitid=:key';
  crud.execsql(
    procedure(db: TDB; table: TTable<Tdanwei>; i: Integer)
    begin
      db.qry.ParamByName('unitid').AsString := table.rows[i].unitid;
      db.qry.ParamByName('unitname').AsString := table.rows[i].unitname;
      db.qry.ParamByName('key').AsString := table.rows[i].unitid;
    end);
end;

initialization
  RegisterClass(TRESTdanwei);

finalization
  UnRegisterClass(TRESTdanwei);

end.

　　3.2）多表的

unit tables ;
/// <author>cxg 2024-1-21</author>
/// 多表演示
interface

uses
  db.crud, yn.log, danwei.model, product.model, global,
  db.unidac, system.Classes, serialize, System.SysUtils;

type
  TRESTtables = class(Trpc)  //單位的遠端方法類
    procedure select(const req: TRequest; const res: TResponse);  //查詢
    procedure insert(const req: TRequest; const res: TResponse);  //新增
    procedure update(const req: TRequest; const res: TResponse);  //修改
    procedure delete(const req: TRequest; const res: TResponse);  //刪除
  end;

implementation

procedure TRESTtables.select(const req: TRequest; const res: TResponse);
//多表查詢
begin
  var crud: TCRUD2<Tdanwei, Tproduct>;
  crud.dbid := '1';
  crud.func := 'TRESTtables.select()';
  crud.req := req;
  crud.res := res;
  var sql1: string := 'select top 2 * from tunit';
  var sql2: string := 'select top 2 * from tgoods';
  crud.sqls := [sql1, sql2];
  crud.open(
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      tables.table1.rows[i].unitid := db.qry.FieldByName('unitid').AsString;//dataset-->model
      tables.table1.rows[i].unitname := db.qry.FieldByName('unitname').AsString;
    end,
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      tables.table2.rows[i].goodsid := db.qry.FieldByName('goodsid').AsString;//dataset-->model
      tables.table2.rows[i].jj := db.qry.FieldByName('jj').AsFloat;
    end);
end;

procedure TRESTtables.delete(const req: TRequest; const res: TResponse);
//多表刪除
begin
  var crud: TCRUD2<Tdanwei, Tproduct>;
  crud.dbid := '1';
  crud.func := 'TRESTtables.delete()';
  crud.req := req;
  crud.res := res;
  var sql1: string := 'delete from tunit where unitid=:unitid';
  var sql2: string := 'delete from tgoods where goodsid=:goodsid';
  crud.sqls := [sql1, sql2];
  crud.execsql(
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      db.qry.ParamByName('unitid').AsString := tables.table1.rows[i].unitid;
    end,
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      db.qry.ParamByName('goodsid').AsString := tables.table2.rows[i].goodsid;
    end);
end;

procedure TRESTtables.insert(const req: TRequest; const res: TResponse);
//多表新增
begin
  var crud: TCRUD2<Tdanwei, Tproduct>;
  crud.dbid := '1';
  crud.func := 'TRESTtables.insert()';
  crud.req := req;
  crud.res := res;
  var sql1: string := 'insert into tunit(unitid,unitname) values (:unitid,:unitname)';
  var sql2: string := 'insert into tgoods(goodsid,jj) values (:goodsid,:jj)';
  crud.sqls := [sql1, sql2];
  crud.execsql(
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      db.qry.ParamByName('unitid').AsString := tables.table1.rows[i].unitid;
      db.qry.ParamByName('unitname').AsString := tables.table1.rows[i].unitname;
    end,
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      db.qry.ParamByName('goodsid').AsString := tables.table2.rows[i].goodsid;
      db.qry.ParamByName('jj').AsFloat := tables.table2.rows[i].jj;
    end);
end;

procedure TRESTtables.update(const req: TRequest; const res: TResponse);
//多表修改
begin
  var crud: TCRUD2<Tdanwei, Tproduct>;
  crud.dbid := '1';
  crud.func := 'TRESTtables.update()';
  crud.req := req;
  crud.res := res;
  var sql1: string := 'update tunit set unitid=:unitid,unitname=:unitname where unitid=:key';
  var sql2: string := 'update tgoods set goodsid=:goodsid,jj=:jj where goodsid=:key';
  crud.sqls := [sql1, sql2];
  crud.execsql(
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      db.qry.ParamByName('unitid').AsString := tables.table1.rows[i].unitid;
      db.qry.ParamByName('unitname').AsString := tables.table1.rows[i].unitname;
      db.qry.ParamByName('key').AsString := tables.table1.rows[i].unitid;
    end,
    procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
    begin
      db.qry.ParamByName('goodsid').AsString := tables.table2.rows[i].goodsid;
      db.qry.ParamByName('jj').AsFloat := tables.table2.rows[i].jj;
      db.qry.ParamByName('key').AsString := tables.table2.rows[i].goodsid;
    end);
end;

initialization
  RegisterClass(TRESTtables);

finalization
  UnRegisterClass(TRESTtables);

end.

泛型類和泛型方法
2020-11-21
泛型
Java泛型知識點：泛型類、泛型介面和泛型方法
2017-04-21
Java泛型
TypeScript 泛型介面和泛型類
2018-07-30
TypeScript泛型
泛型--泛型萬用字元和泛型的上下限
2020-12-02
泛型字元
Scala 泛型型別和方法
2014-12-21
泛型型別
Java集合和泛型
2017-12-12
Java泛型
Go Internals: Go 反射 vs Java 泛型 vs cpp 模板
2020-03-04
Go反射Java泛型
C++ primer 模板與泛型程式設計
2014-05-14
C++泛型程式設計
泛型類、泛型方法及泛型應用
2021-10-09
泛型
07.集合和泛型
2020-06-13
泛型
C++ 泛型程式設計基礎：模板通識
2016-11-16
C++泛型程式設計
【C++ 泛型程式設計01：模板】函式模板與類别範本
2023-02-05
C++泛型程式設計函式
TypeScript 基本型別和泛型的使用
2019-02-27
TypeScript型別泛型
【java】【泛型】泛型genetic
2018-04-25
Java泛型
java泛型之泛型方法。
2016-07-01
Java泛型
Go 泛型之泛型約束
2023-12-28
Go泛型
java泛型之泛型陣列。
2016-07-01
Java泛型陣列
泛型
2024-03-07
泛型
c++泛型程式設計中函式模板過載和模板特化同時存在時的查詢規則
2013-10-21
C++泛型程式設計函式
菜鳥譯文（二）——使用Java泛型構造模板方法模式
2014-08-19
Java泛型模式
泛型最佳實踐：Go泛型設計者教你如何用泛型
2022-04-23
泛型Go
Kotlin 泛型中的 in 和 out
2019-03-27
Kotlin泛型
談談 "JS 和設計泛型"
2019-03-29
JS泛型
TypeScript學習（四）—— 介面和泛型
2019-04-24
TypeScript泛型
型別 VS 泛型
2020-05-18
型別泛型
TypeScript 泛型型別
2018-08-26
TypeScript泛型型別
泛型(一)
2019-01-11
泛型
泛型(三)
2019-01-12
泛型
泛型(二)
2019-01-12
泛型
泛型(四)
2019-01-12
泛型
泛型(五)
2019-01-12
泛型
Go 泛型
2022-11-24
Go泛型
va泛型
2016-11-16
泛型
Java 泛型
2017-05-02
Java泛型
Java泛型
2024-03-10
Java泛型
泛型類、泛型方法、型別萬用字元的使用
2019-03-29
泛型型別字元
Go中泛型和反射比較指南
2024-02-14
Go泛型反射
Dart4Flutter – 03 – 類和泛型
2018-06-22
DartFlutter泛型

delphi ORM和泛型模板

相關文章