delphi ORM和泛型模板实现CRUD
<p>delphi ORM和泛型模板实现CRUD</p><p>1）定义数据模型（data-model）</p>
<p>数据模型是ORM数据序列/还原所必需的。</p>
<div class="cnblogs_Highlighter">
<pre class="brush:csharp;gutter:true;">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;
</pre>
</div>
<p> 2）实现ORM CRUD泛型模板</p>
<div class="cnblogs_Highlighter">
<pre class="brush:csharp;gutter:true;">/// <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);
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);
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); //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); //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.
</pre>
</div>
<p> 3）通过CRUD泛型模板实现资源（resource）数据操作</p>
<p>3.1）单表的</p>
<div class="cnblogs_Highlighter">
<pre class="brush:csharp;gutter:true;">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.unitid := db.qry.FieldByName('unitid').AsString;//dataset-->model
table.rows.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.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.unitid;
db.qry.ParamByName('unitname').AsString := table.rows.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.unitid;
db.qry.ParamByName('unitname').AsString := table.rows.unitname;
db.qry.ParamByName('key').AsString := table.rows.unitid;
end);
end;
initialization
RegisterClass(TRESTdanwei);
finalization
UnRegisterClass(TRESTdanwei);
end.
</pre>
</div>
<p> 3.2）多表的</p>
<div class="cnblogs_Highlighter">
<pre class="brush:csharp;gutter:true;">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 := ;
crud.open(
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
tables.table1.rows.unitid := db.qry.FieldByName('unitid').AsString;//dataset-->model
tables.table1.rows.unitname := db.qry.FieldByName('unitname').AsString;
end,
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
tables.table2.rows.goodsid := db.qry.FieldByName('goodsid').AsString;//dataset-->model
tables.table2.rows.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 := ;
crud.execsql(
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
db.qry.ParamByName('unitid').AsString := tables.table1.rows.unitid;
end,
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
db.qry.ParamByName('goodsid').AsString := tables.table2.rows.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 := ;
crud.execsql(
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
db.qry.ParamByName('unitid').AsString := tables.table1.rows.unitid;
db.qry.ParamByName('unitname').AsString := tables.table1.rows.unitname;
end,
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
db.qry.ParamByName('goodsid').AsString := tables.table2.rows.goodsid;
db.qry.ParamByName('jj').AsFloat := tables.table2.rows.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 := ;
crud.execsql(
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
db.qry.ParamByName('unitid').AsString := tables.table1.rows.unitid;
db.qry.ParamByName('unitname').AsString := tables.table1.rows.unitname;
db.qry.ParamByName('key').AsString := tables.table1.rows.unitid;
end,
procedure(db: TDB; tables: TTable2<Tdanwei, Tproduct>; i: integer)
begin
db.qry.ParamByName('goodsid').AsString := tables.table2.rows.goodsid;
db.qry.ParamByName('jj').AsFloat := tables.table2.rows.jj;
db.qry.ParamByName('key').AsString := tables.table2.rows.goodsid;
end);
end;
initialization
RegisterClass(TRESTtables);
finalization
UnRegisterClass(TRESTtables);
end.
</pre>
</div>
<p> </p>
<p> </p>
<p> </p>
</div>
<div id="MySignature" role="contentinfo">
<p>本文来自博客园，作者：{咏南中间件}，转载请注明原文链接：https://www.cnblogs.com/hnxxcxg/p/18105199</p><br><br>
来源：https://www.cnblogs.com/hnxxcxg/p/18105199
頁:
[1]