利用Jsonarray拼接sql
function getjsontoWhereSql(const ajsonarray: TJSONArray): string;var
i: Integer;
ajsonobject: TJSONObject;
afieldname,afieldvalue:string;
sqlwherestring:string;
afieldoperator:string;
begin
sqlwherestring := ' ';
for i := 0 to ajsonarray.Size - 1 do
begin
ajsonobject := ajsonarray.Get(i) as TJSONObject;
afieldname := ajsonobject.Pairs.JsonString.ToString;
afieldname := ReplaceStr(afieldname, '"', '');
afieldvalue := ajsonobject.Pairs.JsonValue.Value;
afieldoperator := ajsonobject.Pairs.JsonValue.Value;
sqlwherestring := sqlwherestring + ' ' + afieldname + ' ' + afieldoperator +
' ' + QuotedStr(afieldvalue) + ' and ';
end;
Result := ' where ' + LeftStr(sqlwherestring,
Length(sqlwherestring) - 5);
end;
///////////////
function getjsontoInsertSql(const ajsonarray:TJSONArray; tablename: string): string;
var
i,j: Integer;
ajsonobject: TJSONObject;
sqlstring:string;
afieldname,afieldvalue:string;
begin
sqlstring := '';
for i := 0 to ajsonarray.Size - 1 do
begin
sqlstring := sqlstring + ' insert into ' + tablename + '(';
ajsonobject := ajsonarray.Get(i) as TJSONObject;
for j := 0 to ajsonobject.Size - 1 do
begin
afieldname := ajsonobject.Pairs.JsonString.ToString;
sqlstring := sqlstring + ReplaceStr(afieldname, '"', '') + ',';
end;
sqlstring := LeftStr(sqlstring, Length(sqlstring) - 1);
sqlstring := sqlstring + ')values(';
for j := 0 to ajsonobject.Size - 1 do
begin
afieldvalue := ajsonobject.Pairs.JsonValue.Value;
sqlstring := sqlstring + QuotedStr(afieldvalue) + ',';
end;
sqlstring := LeftStr(sqlstring, Length(sqlstring) - 1);
sqlstring := sqlstring + ')';
end;
Result := sqlstring;
end;
///////////
function TForm1.getjsontoSelectSql(const ajsonwherearray: TJSONArray;
tablename: string;const theSql:string): string;
var
sqlwherestring:string;
begin
sqlwherestring := getjsontoWhereSql(ajsonwherearray);
result := thesql + ' from ' +tablename + sqlwherestring;
end;
/////////
function getjsontoUpdadteSql(const ajsonUpdatearray,ajsonWherearray: TJSONArray;
tablename: string): string;
var
i,j: Integer;
ajsonobject: TJSONObject;
sqlstring:string;
afieldname,afieldvalue:string;
setvaluesql,sqlwherestring:string;
afieldoperator:string;
begin
sqlstring := '';
for i := 0 to ajsonUpdatearray.Size - 1 do
begin
sqlstring := sqlstring + ' update ' + tablename + ' set ';
ajsonobject := ajsonUpdatearray.Get(i) as TJSONObject;
setvaluesql := '';
for j := 0 to ajsonobject.Size - 1 do
begin
afieldname := ajsonobject.Pairs.JsonString.ToString;
afieldname := ReplaceStr(afieldname, '"', '');
afieldvalue := ajsonobject.Pairs.JsonValue.Value;
setvaluesql := setvaluesql + afieldname + ' = ' + QuotedStr(afieldvalue) + ',';
end;
setvaluesql := LeftStr(setvaluesql, Length(setvaluesql) - 1);
//////
sqlwherestring := getjsontoWhereSql(ajsonWherearray);
sqlstring := sqlstring + setvaluesql + sqlwherestring;
end;
Result := sqlstring;
end;
////////////////
function getjsontoDeleteSql(const ajsonWherearray: TJSONArray; tablename: string): string;
var
i: Integer;
ajsonobject: TJSONObject;
sqlstring:string;
afieldname,afieldvalue:string;
sqlwherestring:string;
afieldoperator:string;
begin
sqlwherestring := ' ';
for i := 0 to ajsonWherearray.Size - 1 do
begin
ajsonobject := ajsonWherearray.Get(i) as TJSONObject;
afieldname := ajsonobject.Pairs.JsonString.ToString;
afieldname := ReplaceStr(afieldname, '"', '');
afieldvalue := ajsonobject.Pairs.JsonValue.Value;
afieldoperator := ajsonobject.Pairs.JsonValue.Value;
sqlwherestring := sqlwherestring + ' ' + afieldname + ' ' + afieldoperator +
' ' + QuotedStr(afieldvalue) + ' and ';
end;
sqlwherestring := ' where ' + LeftStr(sqlwherestring,Length(sqlwherestring) - 5);
sqlstring := ' delete from ' + tablename + sqlwherestring;
Result := sqlstring;
end;
////////生成Jsonarray
procedure TForm1.Button1Click(Sender: TObject);
var
i: Integer;
JSONCars: TJSONArray;
Car, Price: TJSONObject;
begin
JSONCars := TJSONArray.Create;
try
for i := low(Cars) to high(Cars) do
begin
Car := TJSONObject.Create;
JSONCars.AddElement(Car);
Car.AddPair('manufacturer', Cars);
Car.AddPair('name', Cars);
Price := TJSONObject.Create;
Car.AddPair('price', Cars);
//Price.AddPair('value', TJSONNumber.Create(Cars.ToInteger));
// Price.AddPair('currency', Cars);
end;
JSON := JSONCars.ToString;
memo3.Text := json;
finally
JSONCars.Free;
end;
end;
页:
[1]