首页 > 其他分享 >delphi基于结构的CRUD（JSON）

delphi基于结构的CRUD（JSON）

时间：2022-08-20 16:37:26浏览次数：70  
标签：rows string CRUD delphi db JSON qry ParamByName row

delphi基于结构的CRUD（JSON）

以采购订单为例。

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 //采购订单主表
    [Serialize(1)] billid: string;
    [Serialize(2)] adddate: string;
    [Serialize(3)] supplierid: string;
    [Serialize(4)] suppliername: string;
    [Serialize(5)] warehouseid: string;
    [Serialize(6)] warehousename: string;
    [Serialize(7)] makerid: string;
    [Serialize(8)] note: string;
    [Serialize(9)] shenhe: string;
    [Serialize(10)] maker: string;
  end;

type
  Ttcgdd2 = record //采购订单从表
    [Serialize(1)] billid: string;
    [Serialize(2)] recno: integer;
    [Serialize(3)] goodsid: string;
    [Serialize(4)] goodsname: string;
    [Serialize(5)] gg: string;
    [Serialize(6)] dw: string;
    [Serialize(7)] dj: double;
    [Serialize(8)] qty: double;
    [Serialize(9)] amount: double;
  end;

  Tmodels = record //采购订单主从表
    [Serialize(1)] status: integer;
    [Serialize(2)] exception: string;
    [Serialize(3)] message: string;
    [Serialize(4)] tcgdds: TArray<Ttcgdd>;
    [Serialize(5)] tcgdd2s: TArray<Ttcgdd2>;
  end;

  TRes = record //应答
    [Serialize(1)] status: integer;
    [Serialize(2)] exception: string;
    [Serialize(3)] 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[i].billid := db.qry.fieldbyname('billid').asstring;
        rows.tcgdds[i].adddate := db.qry.fieldbyname('adddate').asstring;
        rows.tcgdds[i].supplierid := db.qry.fieldbyname('supplierid').asstring;
        rows.tcgdds[i].suppliername := db.qry.fieldbyname('suppliername').asstring;
        rows.tcgdds[i].warehouseid := db.qry.fieldbyname('warehouseid').asstring;
        rows.tcgdds[i].warehousename := db.qry.fieldbyname('warehousename').asstring;
        rows.tcgdds[i].makerid := db.qry.fieldbyname('makerid').asstring;
        rows.tcgdds[i].note := db.qry.fieldbyname('note').asstring;
        rows.tcgdds[i].shenhe := db.qry.fieldbyname('shenhe').asstring;
        rows.tcgdds[i].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[i].billid := db.qry.fieldbyname('billid').asstring;
        rows.tcgdd2s[i].recno := db.qry.fieldbyname('recno').asinteger;
        rows.tcgdd2s[i].goodsid := db.qry.fieldbyname('goodsid').asstring;
        rows.tcgdd2s[i].goodsname := db.qry.fieldbyname('goodsname').asstring;
        rows.tcgdd2s[i].gg := db.qry.fieldbyname('gg').asstring;
        rows.tcgdd2s[i].dw := db.qry.fieldbyname('dw').asstring;
        rows.tcgdd2s[i].dj := db.qry.fieldbyname('dj').AsFloat;
        rows.tcgdd2s[i].qty := db.qry.fieldbyname('qty').AsFloat;
        rows.tcgdd2s[i].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.

 

标签：rows,string,CRUD,delphi,db,JSON,qry,ParamByName,row
From： https://www.cnblogs.com/hnxxcxg/p/16607986.html

相关文章

  • delphi基于结构的CRUD(protobuf)
    delphi基于结构的CRUD(protobuf)以采购订单为例。unitproto.tcgddtcgdd2;//代码由代码工厂自动生成//2022-08-2016:04:14{$Idef.inc}interfaceuses{$IFDEF......
  • JSON概念和JSON语法的定义与值的获取
    JSON概念概念： JavaScript Object NotationJavaScript对象标记法var p={"name":"张三","age":23,"gender":"男"}; json现在多用于存储和交换文本信息的语法......
  • FireDAC使用Mormot开发Rest服务器，返回JSON数据格式的问题
    FireDAC沿用了一贯的DataSnap的数据返回方式，这也使得开发出来的REST Server很给难给第三方应用提供服务用什么办法可以解决FireDAC直接序列返回的Json只是数据信息，而不是......
  • Delphi使用TStopwatch计时器精确计时【转】
    引用 System.Diagnostics单元属性：Elapsed获取当前实例测量得出的总运行时间。ElapsedMilliseconds 获取当前实例测量得出的总运行时间（以毫秒为......
  • MybatisPlus核心功能——实现CRUD增删改查操作 (包含条件构造器)
    CRUD官方文档：https://baomidou.com/（建议多看看官方文档，每种功能里面都有讲解）【本文章使用的mybatisplus版本为3.5.2】条件构造器一般都是用service层的方法，因为比ma......
  • JQuery_DOM操作_属性操作_CRUD操作
    CRUD操作append()：父元素将子元素追加到末尾对象1.append(对象2)：将对象2添加到对象1元素内部，并且在末尾prepend()：父元素将子元素追加到开头对象1.prepend(对象2)：将......
  • 让人纠结的PG字段json类型
    PostgreSQL确实牛逼得很。PostgreSQL有个json数据类型。当你用json类型设定表的字段时，你用select语句相当爽。问题是当你使用中间件时如FireDAC等，进行数据保存时，就会出现......
  • Newtonsoft.Json
    1、序列化stringjsonStr=JsonConvert.SerializeObject(obj)2、反序列化Tobj=JsonConvert.DeserializeObject(jsonStr)3、Newtonsoft.Json扩展方法类///<summa......
  • 学习：json数据
    json本质上是一个固定格式的字符串JSON是一种轻量级的数据交换语音Json可以实现不同平台的数据交换也可以使用它保存业务数据格式Json使用键值对的方式表示一个业务对......
  • JQuery_DOM操作_属性操作_CRUD操作
    CRUD操作append()：父元素将子元素追加到末尾对象1.append(对象2)：将对象2添加到对象1元素内部，并且在末尾prepend()：父元素将子元素追加到开头对象1.prepend(对象2)：将......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99