您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> Delphi

Delphi FireDAC SQLite 資料增刪改查（五）：TFDQuery 參數化執行、交易包裹、LiveBinding 綁定

編輯：Delphi

前言

FireDAC TFDQuery 是 SQLite 資料操作的核心元件，支援參數化 SQL、陣列 DML、交易控制、LiveBinding 雙向綁定。本文以完整表單示範 INSERT/UPDATE/DELETE/SELECT 的標準化寫法，並對比 ExecSQL 與 Open 的適用場景。

表單元件配置

object DBGrid1: TDBGrid; Left = 16; Top = 88; Width = 361; Height = 329; DataSource = DataSource1; end
object FDConnection1: TFDConnection; Left = 34; Top = 24; end
object FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink; Left = 143; Top = 24; end
object FDGUIxWaitCursor1: TFDGUIxWaitCursor; Provider = 'Forms'; Left = 260; Top = 24; end
object FDQuery1: TFDQuery; Connection = FDConnection1; Left = 344; Top = 24; end
object DataSource1: TDataSource; DataSet = FDQuery1; Left = 420; Top = 24; end
object Button1: TButton; Left = 400; Top = 88; Width = 75; Height = 25; Caption = 'INSERT'; OnClick = Button1Click; end
object Button2: TButton; Left = 400; Top = 136; Width = 75; Height = 25; Caption = 'UPDATE'; OnClick = Button2Click; end
object Button3: TButton; Left = 400; Top = 192; Width = 75; Height = 25; Caption = 'DELETE'; OnClick = Button3Click; end
object Button4: TButton; Left = 400; Top = 240; Width = 75; Height = 25; Caption = 'SELECT'; OnClick = Button4Click; end

核心代碼

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDConnection1.Params.Add('DriverID=SQLite');
  FDConnection1.Params.Add('Database=C:\Data\CrudDemo.sdb');
  FDConnection1.Connected := True;

  FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS Items(ID INTEGER PRIMARY KEY, Name TEXT, Price REAL, Qty INTEGER)');
  FDQuery1.Open('SELECT * FROM Items');
end;

procedure TForm1.Button1Click(Sender: TObject); // INSERT
begin
  FDConnection1.StartTransaction;
  try
    FDConnection1.ExecSQL('INSERT INTO Items(Name, Price, Qty) VALUES(:n, :p, :q)',
      ['Apple', 12.5, 100]);
    FDConnection1.Commit;
    FDQuery1.Refresh; // 重新查詢
  except FDConnection1.Rollback; raise; end;
end;

procedure TForm1.Button2Click(Sender: TObject); // UPDATE
begin
  if FDQuery1.IsEmpty then Exit;
  FDConnection1.StartTransaction;
  try
    FDConnection1.ExecSQL('UPDATE Items SET Price = :p WHERE ID = :id',
      [15.0, FDQuery1.FieldByName('ID').AsInteger]);
    FDConnection1.Commit;
    FDQuery1.Refresh;
  except FDConnection1.Rollback; raise; end;
end;

procedure TForm1.Button3Click(Sender: TObject); // DELETE
begin
  if FDQuery1.IsEmpty then Exit;
  if MessageDlg('確定刪除？', mtConfirmation, [mbYes, mbNo], 0) <> mrYes then Exit;
  FDConnection1.StartTransaction;
  try
    FDConnection1.ExecSQL('DELETE FROM Items WHERE ID = :id',
      [FDQuery1.FieldByName('ID').AsInteger]);
    FDConnection1.Commit;
    FDQuery1.Refresh;
  except FDConnection1.Rollback; raise; end;
end;

procedure TForm1.Button4Click(Sender: TObject); // SELECT with parameters
begin
  FDQuery1.Close;
  FDQuery1.SQL.Text := 'SELECT * FROM Items WHERE Price >= :minPrice AND Qty > 0 ORDER BY Name';
  FDQuery1.ParamByName('minPrice').AsFloat := 10.0;
  FDQuery1.Open;
end;

關鍵技術點

場景	方法	說明
INSERT/UPDATE/DELETE	`ExecSQL`	不返回游標，效能高，支援參數陣列批次執行
SELECT	`Open` / `Refresh`	返回雙向游標，支援 LiveBinding、編輯後 `Post`
參數綁定	`:name` / `?`	防 SQL 注入、自動類型轉換、Prepared Statement 快取
交易	`StartTransaction/Commit/Rollback`	ACID 保證，批次寫入必須加交易否則極慢

進階：陣列 DML 批次插入

FDConnection1.Params.Add('ArrayDMLSize=1000'); // 設定批次大小
FDQuery1.SQL.Text := 'INSERT INTO Items(Name, Price, Qty) VALUES(:n, :p, :q)';
for I := 1 to 10000 do begin
  FDQuery1.Params[0].AsStrings[I-1] := 'Item' + I.ToString;
  FDQuery1.Params[1].AsFloats[I-1] := Random(100) + 0.5;
  FDQuery1.Params[2].AsIntegers[I-1] := Random(100);
end;
FDConnection1.StartTransaction;
FDQuery1.Execute(10000, 0); // 一次性提交 1 萬筆
FDConnection1.Commit;