delphi基于结构的CRUD（JSON）

小墨同学 發表於 2022-8-20 16:18:00

delphi基于结构的CRUD（JSON）
以采购订单为例。
<div class="cnblogs_code">
<pre>unit rest.tcgddtcgdd2;
//代码由代码工厂自动生成
//2022-08-20 16:04:54
{$I def.inc}
interface

uses
{$IFDEF firedac}db.firedac, db.firedacPool, {$ENDIF}
{$IFDEF unidac}db.unidac, db.unidacpool,{$ENDIF}
classes, db, System.NetEncoding,
system.JSON.Serializers, yn.log, SysUtils;

type
Ttcgdd = record //采购订单主表
billid: string;
adddate: string;
supplierid: string;
suppliername: string;
warehouseid: string;
warehousename: string;
makerid: string;
note: string;
shenhe: string;
maker: string;
end;

type
Ttcgdd2 = record //采购订单从表
billid: string;
recno: integer;
goodsid: string;
goodsname: string;
gg: string;
dw: string;
dj: double;
qty: double;
amount: double;
end;

Tmodels = record //采购订单主从表
status: integer;
exception: string;
message: string;
tcgdds: TArray<Ttcgdd>;
tcgdd2s: TArray<Ttcgdd2>;
end;

TRes = record //应答
status: integer;
exception: string;
message: string;
end;

function select(url: string; body: TBytes): string;

function insert(url: string; body: TBytes): string;

function update(url: string; body: TBytes): string;

function delete(url: string; body: TBytes): string;

implementation

function select(url: string; body: TBytes): string;
var
db: tdb;
pool: tdbpool;
arr: TArray<string>;
serial: TJsonSerializer;
rows: Tmodels;
i: integer;
res: TRes;
begin
serial := TJsonSerializer.Create;
try
try
 arr := url.Split(['/']);
 pool := GetDBPool(arr[4]);
 db := pool.Lock;
 var where: string;
 db.qry.Close;
 db.qry.SQL.Clear;
 if high(arr) >= 5 then
 where := ' where ' + TNetEncoding.URL.Decode(arr[5]);
 db.qry.SQL.Text := 'select * from tcgdd' + where;
 db.qry.Open;
 if db.qry.isempty then
 begin
 rows.status := 500;
 rows.exception := 'No found any data.';
 result := serial.Serialize<Tmodels>(rows);
 exit;
 end;
 SetLength(rows.tcgdds, db.qry.RecordCount);
 db.qry.First;
 i := 0;
 while not db.qry.Eof do
 begin
 rows.tcgdds.billid := db.qry.fieldbyname('billid').asstring;
 rows.tcgdds.adddate := db.qry.fieldbyname('adddate').asstring;
 rows.tcgdds.supplierid := db.qry.fieldbyname('supplierid').asstring;
 rows.tcgdds.suppliername := db.qry.fieldbyname('suppliername').asstring;
 rows.tcgdds.warehouseid := db.qry.fieldbyname('warehouseid').asstring;
 rows.tcgdds.warehousename := db.qry.fieldbyname('warehousename').asstring;
 rows.tcgdds.makerid := db.qry.fieldbyname('makerid').asstring;
 rows.tcgdds.note := db.qry.fieldbyname('note').asstring;
 rows.tcgdds.shenhe := db.qry.fieldbyname('shenhe').asstring;
 rows.tcgdds.maker := db.qry.fieldbyname('maker').asstring;
 inc(i);
 db.qry.Next;
 end;
 db.qry.Close;
 db.qry.SQL.Clear;
 if high(arr) >= 5 then
 where := ' where ' + TNetEncoding.URL.Decode(arr[6]);
 db.qry.SQL.Text := 'select * from tcgdd2' + where;
 db.qry.Open;
 if db.qry.isempty then
 begin
 rows.status := 500;
 rows.exception := 'No found any data.';
 result := serial.Serialize<Tmodels>(rows);
 exit;
 end;
 SetLength(rows.tcgdd2s, db.qry.RecordCount);
 db.qry.First;
 i := 0;
 while not db.qry.Eof do
 begin
 rows.tcgdd2s.billid := db.qry.fieldbyname('billid').asstring;
 rows.tcgdd2s.recno := db.qry.fieldbyname('recno').asinteger;
 rows.tcgdd2s.goodsid := db.qry.fieldbyname('goodsid').asstring;
 rows.tcgdd2s.goodsname := db.qry.fieldbyname('goodsname').asstring;
 rows.tcgdd2s.gg := db.qry.fieldbyname('gg').asstring;
 rows.tcgdd2s.dw := db.qry.fieldbyname('dw').asstring;
 rows.tcgdd2s.dj := db.qry.fieldbyname('dj').AsFloat;
 rows.tcgdd2s.qty := db.qry.fieldbyname('qty').AsFloat;
 rows.tcgdd2s.amount := db.qry.fieldbyname('amount').AsFloat;
 inc(i);
 db.qry.Next;
 end;
 rows.status := 200;
 rows.message := 'success';
 result := serial.Serialize<Tmodels>(rows);
except
 on E: Exception do
 begin
 res.status := 500;
 res.exception := E.message;
 result := serial.Serialize<TRes>(res);
 end;
end;
finally
pool.Unlock(db);
serial.Free;
end;
end;

function insert(url: string; body: TBytes): string;
var
db: tdb;
pool: tdbpool;
arr: tarray<string>;
serial: TJsonSerializer;
res: TRes;
begin
serial := TJsonSerializer.Create;
try
try
 var rows: Tmodels;
 rows := serial.Deserialize<Tmodels>(TEncoding.UTF8.GetString(body));
 arr := url.Split(['/']);
 pool := GetDBPool(arr[4]);
 db := pool.Lock;
 db.startTrans;
 for var row: Ttcgdd in rows.tcgdds do
 begin
 db.qry.Close;
 db.qry.SQL.Clear;
 db.qry.SQL.Text := 'insert into tcgdd (billid,adddate,supplierid,suppliername,warehouseid,warehousename,makerid,note,shenhe,maker) values (:billid,:adddate,:supplierid,:suppliername,:warehouseid,:warehousename,:makerid,:note,:shenhe,:maker)';
 db.qry.ParamByName('billid').AsString := row.billid;
 db.qry.ParamByName('adddate').AsString := row.adddate;
 db.qry.ParamByName('supplierid').AsString := row.supplierid;
 db.qry.ParamByName('suppliername').AsString := row.suppliername;
 db.qry.ParamByName('warehouseid').AsString := row.warehouseid;
 db.qry.ParamByName('warehousename').AsString := row.warehousename;
 db.qry.ParamByName('makerid').AsString := row.makerid;
 db.qry.ParamByName('note').AsString := row.note;
 db.qry.ParamByName('shenhe').AsString := row.shenhe;
 db.qry.ParamByName('maker').AsString := row.maker;
 db.qry.ExecSQL;
 end;
 for var row: Ttcgdd2 in rows.tcgdd2s do
 begin
 db.qry.Close;
 db.qry.SQL.Clear;
 db.qry.SQL.Text := 'insert into tcgdd2 (billid,recno,goodsid,goodsname,gg,dw,dj,qty,amount) values (:billid,:recno,:goodsid,:goodsname,:gg,:dw,:dj,:qty,:amount)';
 db.qry.ParamByName('billid').AsString := row.billid;
 db.qry.ParamByName('recno').AsInteger := row.recno;
 db.qry.ParamByName('goodsid').AsString := row.goodsid;
 db.qry.ParamByName('goodsname').AsString := row.goodsname;
 db.qry.ParamByName('gg').AsString := row.gg;
 db.qry.ParamByName('dw').AsString := row.dw;
 db.qry.ParamByName('dj').AsFloat := row.dj;
 db.qry.ParamByName('qty').AsFloat := row.qty;
 db.qry.ParamByName('amount').AsFloat := row.amount;
 db.qry.ExecSQL;
 end;
 db.commitTrans;
 res.status := 200;
 res.message := 'success';
 Result := serial.Serialize<TRes>(res);
except
 on E: Exception do
 begin
 db.rollbackTrans;
 res.status := 500;
 res.exception := E.Message;
 Result := serial.Serialize<TRes>(res);
 end;
end;
finally
pool.Unlock(db);
serial.Free;
end;
end;

function update(url: string; body: TBytes): string;
var
db: tdb;
pool: tdbpool;
arr: tarray<string>;
serial: TJsonSerializer;
res: TRes;
begin
serial := TJsonSerializer.Create;
try
try
 var rows: Tmodels;
 rows := serial.Deserialize<Tmodels>(TEncoding.UTF8.GetString(body));
 arr := url.Split(['/']);
 pool := GetDBPool(arr[4]);
 db := pool.Lock;
 db.startTrans;
 for var row: Ttcgdd in rows.tcgdds do
 begin
 db.qry.Close;
 db.qry.SQL.Clear;
 db.qry.SQL.Text := 'update tcgdd set billid=:billid,adddate=:adddate,supplierid=:supplierid,suppliername=:suppliername,warehouseid=:warehouseid,warehousename=:warehousename,makerid=:makerid,note=:note,shenhe=:shenhe,maker=:maker where billid=:key0';
 db.qry.ParamByName('billid').AsString := row.billid;
 db.qry.ParamByName('key0').value := row.billid;
 db.qry.ParamByName('adddate').AsString := row.adddate;
 db.qry.ParamByName('supplierid').AsString := row.supplierid;
 db.qry.ParamByName('suppliername').AsString := row.suppliername;
 db.qry.ParamByName('warehouseid').AsString := row.warehouseid;
 db.qry.ParamByName('warehousename').AsString := row.warehousename;
 db.qry.ParamByName('makerid').AsString := row.makerid;
 db.qry.ParamByName('note').AsString := row.note;
 db.qry.ParamByName('shenhe').AsString := row.shenhe;
 db.qry.ParamByName('maker').AsString := row.maker;
 db.qry.ExecSQL;
 end;
 for var row: Ttcgdd2 in rows.tcgdd2s do
 begin
 db.qry.Close;
 db.qry.SQL.Clear;
 db.qry.SQL.Text := 'update tcgdd2 set billid=:billid,recno=:recno,goodsid=:goodsid,goodsname=:goodsname,gg=:gg,dw=:dw,dj=:dj,qty=:qty,amount=:amount where billid=:key0 and recno=:key1';
 db.qry.ParamByName('billid').AsString := row.billid;
 db.qry.ParamByName('key0').value := row.billid;
 db.qry.ParamByName('recno').AsInteger := row.recno;
 db.qry.ParamByName('key1').value := row.recno;
 db.qry.ParamByName('goodsid').AsString := row.goodsid;
 db.qry.ParamByName('goodsname').AsString := row.goodsname;
 db.qry.ParamByName('gg').AsString := row.gg;
 db.qry.ParamByName('dw').AsString := row.dw;
 db.qry.ParamByName('dj').AsFloat := row.dj;
 db.qry.ParamByName('qty').AsFloat := row.qty;
 db.qry.ParamByName('amount').AsFloat := row.amount;
 db.qry.ExecSQL;
 end;
 db.commitTrans;
 res.status := 200;
 res.message := 'success';
 Result := serial.Serialize<TRes>(res);
except
 on E: Exception do
 begin
 db.rollbackTrans;
 res.status := 500;
 res.exception := E.Message;
 Result := serial.Serialize<TRes>(res);
 end;
end;
finally
pool.Unlock(db);
serial.Free;
end;
end;

function delete(url: string; body: TBytes): string;
var
db: tdb;
pool: tdbpool;
arr: tarray<string>;
serial: TJsonSerializer;
res: TRes;
begin
serial := TJsonSerializer.Create;
try
try
 arr := url.Split(['/']);
 pool := GetDBPool(arr[4]);
 db := pool.Lock;
 var where: string;
 db.startTrans;
 db.qry.Close;
 db.qry.SQL.Clear;
 where := ' where ' + TNetEncoding.URL.Decode(arr[3]);
 db.qry.SQL.Text := 'delete from tcgdd' + where;
 db.qry.ExecSQL;
 db.qry.Close;
 db.qry.SQL.Clear;
 where := ' where ' + TNetEncoding.URL.Decode(arr[4]);
 db.qry.SQL.Text := 'delete from tcgdd2' + where;
 db.qry.ExecSQL;
 db.commitTrans;
 res.status := 200;
 res.message := 'success';
 Result := serial.Serialize<TRes>(res);
except
 on E: Exception do
 begin
 db.rollbackTrans;
 res.status := 500;
 res.exception := E.Message;
 Result := serial.Serialize<TRes>(res);
 end;
end;
finally
pool.Unlock(db);
serial.Free;
end;
end;

end.</pre>
</div>
 

</div>
<div id="MySignature" role="contentinfo">
本文来自博客园，作者：{咏南中间件}，转载请注明原文链接：https://www.cnblogs.com/hnxxcxg/p/16607986.html 
来源：https://www.cnblogs.com/hnxxcxg/p/16607986.html

頁: [1]

琼殿技术论坛's Archiver

delphi基于结构的CRUD（JSON）