Delphi下如何玩SQLite,总的来讲有两个思路,一是封装类(单元类)玩法,二是控件玩法。手段上也千奇百怪,有静态引用 obj 的,有动态链接 dll 的,还有把 dll 嵌入资源文件的,甚至还有用其他语言改写源码的,之前我就看过用 C# 重写的源码。如Delphi 官方的FireDAC就是在 Windwos 平台下默认使用了 sqlite3_x86.obj 或 sqlite3_x64.obj(FireDAC.Phys.SQLiteCli.pas), 而非 dll。具体大家要了解清楚。
Delphi下使用封装类玩SQLite,比较著名和经典的SQLite封装类(单元类,非控件),可能要算是“A simple Delphi wrapper for Sqlite3(Update 10 February 2011)(UTF-8编码)”。众所周知,SQLite 的数据库编码方式:SQLite数据库支持UTF-8编码和UTF-16编码(含UTF-16BE、UTF-16LE),一般采用UTF-8编码,具体各编码的区别请百度了解 。故,A simple Delphi wrapper for Sqlite3(Update 10 February 2011)也有两个版本,是是标准版,另一个是“A simple Delphi wrapper for Sqlite3(Unicode version版)”,前者就是UTF-8编码,后者是UTF-16编码(含UTF-16BE、UTF-16LE)。这点大家要特别的注意,网上众多的乱码之类的,其根源就在于此。精简的2个pas源文件(SQLite3.pas、SQLiteTable3.pas),最后的“新版”还有一个sqlite3udf.pas(UDF,自定义函数),说是精简,其实基本上,读写SQLite3数据库是没有什么问题的,但有一个特别要注意的是,因为是精简的封装类,而不非控件,所以没有TDataset 和 databinding(好像有人进行了改造或升级,但意义不大),玩拖拉控件的人,可能就是不爽了,但确实是很好用,简单且快速。
或许是“A simple Delphi wrapper for Sqlite3”太著名了或许他不想背后默默的贡献代码给别人做嫁衣,最终在“A simple Delphi wrapper for Sqlite3”基础上,有个高手进行了改造、优化、精简,去掉了许多重复的功能(好像是去掉了Sqlite低版本的一些支持和兼容),变成了一个叫“Delphi SQLite Wrapper” ,最终版本为:V2.1.2 2012-08-21。
【Delphi SQLite Wrapper特性(其实两者都有很多的共性)】:
- 至少需要Sqlite3.dll 的3.7.1版本或以上,开源的东东,我想没理由不使用最新版本,所以这个不是问题。(截止到当前Sqlite最新为:SQLite Version 3.39.4 (2022-09-29));
- 它不是组件,只是单元。 所以将此封装类(单元文件)包含到项目中就可以使用,并创建处理Sqlite3数据库的类;
- 它非常轻量级代码,所以没有集成到Delphi数据库模型(控件)中;
- 支持任何Sqlite的SQL命令并遍历结果集、可以使用准备好的查询、可以使用参数化查询、可以使用事务(包括保存点[savepoints])、可以定义自己的UDF(用户定义的SQL函数)、可以定义自己的排序规则;
下面,我就针对上面讲的两个SQLite封装类(单元类,非控件),如果没有的,请先下载对应的SQLite封装类(本文上面有对应的下载地址),进行重点精捷讲解。
一、A simple Delphi wrapper for Sqlite3
请先下载“A simple Delphi wrapper for Sqlite3(Update 10 February 2011)(UTF-8编码)”。下载解压后,可以发现其中有SQLite3.pas、SQLiteTable3.pas、sqlite3.dll,SQLite3.pas、SQLiteTable3.pas这两个是必须要用到的文件,sqlite3.dll是SQLite数据库的dll文件,sqlite3.dll你也可以去SQLite官网下载最新的sqlite3.dll进行替换,以便获取最新的或bug后的功能,为了方便管理,将这几个文件,请放在项目的放定文件夹中,如:SQLite。
如使使用或调用?接下来将SQLite3.pas、SQLiteTable3.pas两个文件添加至你的项目中,在你的公用方法单元或你要使用SQLite的窗体中,我是新建一个SQLite的方法单元文件(PubSQLiteCallMethod.pas),相关修正版代码如下(有需要的可以复制后另存为:PubSQLiteCallMethod.pas):
//SkyWin,2022-10-17修正版
unit PubSQLiteCallMethod;
interface
uses
Windows, Forms, Messages, SysUtils, IniFiles, Variants, Classes, Graphics, Controls, Dialogs, ADODB, DB, ExtCtrls, StdCtrls,
DBGrids, DBGridEh, DBClient, MidasLib, SQLiteTable3;
{const
//SkyWin,SQLite数据库文件名
SQLiteDBFileName = 'MiniHRMDB.s3db';}
var
SelectSQL, InsertSQL, UpdateSQL, DeleteSQL: string;
AppPath, SQLiteDBFileName: string;
SQLItemlist: THashedStringList;
function GetSelectSQL(SQLItemlist: THashedStringList): string;
function GetInsertSQL(SQLItemlist: THashedStringList): string;
function GetUpdateSQL(SQLItemlist: THashedStringList): string;
function GetDeleteSQL(SQLItemlist: THashedStringList): string;
//iOpType:0 Select;1 Insert;2 Update;3 Delete
function GetSQLText(SQLItemlist: THashedStringList; iOpType: Integer): string;
procedure OpenClientDataSet(CurClientDataSet: TClientDataSet; CurSQL: string);
procedure OpenClientDataSet_UTF8(CurClientDataSet: TClientDataSet; CurSQL: string);
function ExecSQL(SQLText: string): Boolean;
function ExecSQL_UTF8(SQLText: string): Boolean;
function ExecBatchSQL(SQLTextlist: TStringList): Boolean;
function ExecBatchSQL_UTF8(SQLTextlist: TStringList): Boolean;
function GetFieldBlobSQL(SQLText: string;var fsFieldBlob: TMemoryStream): Boolean;
//insert the fsFieldBlob into the db
function UpdateFieldBlobSQL(SQLText: string;fsFieldBlob: TFileStream): Boolean;
function SQLiteDBBackup(BackupFileName:string): Boolean;
implementation
{ e.g.
var
SQLText:String;
begin
...
SQLItemlist.Clear;
//必选项
SQLItemlist.Add('table=Bs_Employee');
//可选项
SQLItemlist.Add('sqlwhere=Emp_ID='+QuotedStr(edtEmp_ID.text));
...
SQLText:=GetSelectSQL(SQLItemlist);
...
end;
}
function GetSelectSQL(SQLItemlist: THashedStringList): string;
begin
SelectSQL:=' SELECT * FROM '+SQLItemlist.Values['table'];
if Trim(SQLItemlist.Values['sqlwhere'])<>'' then
begin
SelectSQL:=SelectSQL+' WHERE '+SQLItemlist.Values['sqlwhere'];
end;
Result:=SelectSQL;
end;
function GetInsertSQL(SQLItemlist: THashedStringList): string;
var
FieldNameList, FieldValueList: string;
i: Integer;
begin
FieldNameList:='';
FieldValueList:='';
InsertSQL:='INSERT INTO '+SQLItemlist.Values['table'];
for i:=0 to SQLItemlist.Count-1 do
begin
if SQLItemlist.Names[i]<>'table' then
begin
if (FieldNameList='') then
begin
FieldNameList:=SQLItemlist.Names[i];
FieldValueList:=QuotedStr(SQLItemlist.ValueFromIndex[i]);
end
else
begin
FieldNameList:=FieldNameList+','+SQLItemlist.Names[i];
FieldValueList:=FieldValueList+','+QuotedStr(SQLItemlist.ValueFromIndex[i]);
end;
end;
end;
InsertSQL:=InsertSQL+'('+FieldNameList+')'+' VALUES ('+FieldValueList+')';
Result:=InsertSQL;
end;
function GetUpdateSQL(SQLItemlist: THashedStringList): string;
var
i: Integer;
begin
UpdateSQL:='';
for i:=0 to SQLItemlist.Count-1 do
begin
if (SQLItemlist.Names[i]<>'table') and (SQLItemlist.Names[i]<>'sqlwhere') then
begin
if (UpdateSQL='') then
begin
UpdateSQL:='UPDATE '+SQLItemlist.Values['table']+' SET ';
UpdateSQL:=UpdateSQL+SQLItemlist.Names[i]+'='+QuotedStr(SQLItemlist.ValueFromIndex[i]);
end
else
begin
UpdateSQL:=UpdateSQL+','+SQLItemlist.Names[i]+'='+QuotedStr(SQLItemlist.ValueFromIndex[i]);
end;
end;
end;
if Trim(SQLItemlist.Values['sqlwhere'])<>'' then
begin
UpdateSQL:=UpdateSQL+' WHERE '+SQLItemlist.Values['sqlwhere'];
end;
Result:=UpdateSQL;
end;
function GetDeleteSQL(SQLItemlist: THashedStringList): string;
begin
DeleteSQL:=' DELETE FROM '+SQLItemlist.Values['table'];
if Trim(SQLItemlist.Values['sqlwhere'])<>'' then
DeleteSQL:=DeleteSQL+' WHERE '+SQLItemlist.Values['sqlwhere'];
Result:=DeleteSQL;
end;
//iOpType:0 Select;1 Insert;2 Update;3 Delete
function GetSQLText(SQLItemlist: THashedStringList; iOpType: Integer): string;
begin
case iOpType of
0:
begin
Result:=GetSelectSQL(SQLItemlist);
end;
1:
begin
Result:=GetInsertSQL(SQLItemlist);
end;
2:
begin
Result:=GetUpdateSQL(SQLItemlist);
end;
3:
begin
Result:=GetDeleteSQL(SQLItemlist);
end;
else
Result:='';
end;
end;
procedure OpenClientDataSet(CurClientDataSet: TClientDataSet; CurSQL: string);
var
slDBpath: string;
sldb: TSQLiteDatabase;
sltb: TSQLiteTable;
i: Integer;
begin
//清空历史数据
CurClientDataSet.XMLData:='';
CurClientDataSet.FieldDefs.Clear;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) then
Exit;
sldb:=TSQLiteDatabase.Create(slDBpath);
try
sltb:=sldb.GetTable(CurSQL);
if sltb.Count>0 then
begin
for i:=0 to sltb.ColCount-1 do
begin
//添加字段,字段长度此处简单的设置为200,具体请依实际情况加长或改之!
CurClientDataSet.FieldDefs.Add(sltb.Columns[i], ftWideString, 200);
end;
//构建数据集, 不可缺少的一步
CurClientDataSet.CreateDataSet;
CurClientDataSet.Open;
while not sltb.Eof do
begin
CurClientDataSet.Append;
for i:=0 to sltb.ColCount-1 do
begin
if LowerCase(sltb.FieldByName[sltb.Columns[i]])='null' then
begin
CurClientDataSet.FieldByName(sltb.Columns[i]).AsString:='';
end
else
begin
CurClientDataSet.FieldByName(sltb.Columns[i]).AsString:=sltb.FieldByName[sltb.Columns[i]];
end;
end;
sltb.Next;
end;
try
CurClientDataSet.Post;
except
end;
end;
finally
FreeAndNil(sldb);
end;
end;
procedure OpenClientDataSet_UTF8(CurClientDataSet: TClientDataSet; CurSQL: string);
var
slDBpath: string;
sldb: TSQLiteDatabase;
sltb: TSQLiteTable;
i: Integer;
begin
//清空历史数据
CurClientDataSet.XMLData:='';
CurClientDataSet.FieldDefs.Clear;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) then
Exit;
sldb:=TSQLiteDatabase.Create(slDBpath);
try
sltb:=sldb.GetTable(CurSQL);
if sltb.Count>0 then
begin
for i:=0 to sltb.ColCount-1 do
begin
//添加字段,字段长度此处简单的设置为200,具体请依实际情况加长或改之
CurClientDataSet.FieldDefs.Add(sltb.Columns[i], ftWideString, 200);
end;
//构建数据集, 不可缺少的一步
CurClientDataSet.CreateDataSet;
CurClientDataSet.Open;
while not sltb.Eof do
begin
CurClientDataSet.Append;
for i:=0 to sltb.ColCount-1 do
begin
if LowerCase(sltb.FieldByName[sltb.Columns[i]])='null' then
begin
CurClientDataSet.FieldByName(sltb.Columns[i]).AsString:='';
end
else
begin
CurClientDataSet.FieldByName(sltb.Columns[i]).AsString:=UTF8Decode(sltb.FieldByName[sltb.Columns[i]]);
end;
end;
sltb.Next;
end;
try
CurClientDataSet.Post;
except
end;
end;
finally
FreeAndNil(sldb);
end;
end;
function ExecSQL(SQLText: string): Boolean;
var
slDBpath: string;
sldb: TSQLiteDatabase;
begin
Result:=False;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) then
Exit;
try
sldb:=TSQLiteDatabase.Create(slDBpath);
sldb.ExecSQL(SQLText);
FreeAndNil(sldb);
Result:=True;
except
OutputDebugString(PWideChar(SQLText));
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
function ExecSQL_UTF8(SQLText: string): Boolean;
var
slDBpath: string;
sldb: TSQLiteDatabase;
begin
Result:=False;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) OR (Trim(SQLText)='') then
Exit;
try
sldb:=TSQLiteDatabase.Create(slDBpath);
sldb.ExecSQL(UTF8Encode(SQLText));
FreeAndNil(sldb);
Result:=True;
except
OutputDebugString(PWideChar(SQLText));
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
//批提交或执行 INSERT、UPDATE语句
function ExecBatchSQL(SQLTextlist: TStringList): Boolean;
var
slDBpath: string;
sldb: TSQLiteDatabase;
SQLLineText: string;
I:Integer;
begin
Result:=False;
if (SQLTextlist.Count=0) then Exit;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) then
Exit;
try
sldb:=TSQLiteDatabase.Create(slDBpath);
sldb.BeginTransaction;
for I := 0 to SQLTextlist.Count-1 do
begin
SQLLineText:=Trim(SQLTextlist[I]);
try
if (SQLLineText<>'') then
sldb.ExecSQL(SQLLineText);
except
OutputDebugString(PWideChar(SQLLineText));
sldb.Rollback;
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
sldb.Commit;
FreeAndNil(sldb);
Result:=True;
except
OutputDebugString(PWideChar(SQLLineText));
sldb.Rollback;
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
//批提交或执行 INSERT、UPDATE语句(UTF8)
function ExecBatchSQL_UTF8(SQLTextlist: TStringList): Boolean;
var
slDBpath: string;
sldb: TSQLiteDatabase;
SQLLineText: string;
I:Integer;
begin
Result:=False;
if (SQLTextlist.Count=0) then Exit;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) then
Exit;
try
sldb:=TSQLiteDatabase.Create(slDBpath);
sldb.BeginTransaction;
for I := 0 to SQLTextlist.Count-1 do
begin
SQLLineText:=UTF8Decode(Trim(SQLTextlist[I]));
try
if (SQLLineText<>'') then
sldb.ExecSQL(SQLLineText);
except
OutputDebugString(PWideChar('ExecBatchSQL_UTF8:'+SQLLineText));
sldb.Rollback;
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
sldb.Commit;
FreeAndNil(sldb);
Result:=True;
except
OutputDebugString(PWideChar('ExecBatchSQL_UTF8:'+SQLLineText));
sldb.Rollback;
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
function GetFieldBlobSQL(SQLText: string;var fsFieldBlob: TMemoryStream): Boolean;
var
slDBpath: string;
sldb: TSQLiteDatabase;
sltb: TSQLiteTable;
begin
Result:=False;
fsFieldBlob:=nil;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) OR (Trim(SQLText)='') then
Exit;
try
sldb:=TSQLiteDatabase.Create(slDBpath);
sltb:=sldb.GetTable(SQLText);
fsFieldBlob:=sltb.FieldAsBlob(0);
FreeAndNil(sldb);
if (fsFieldBlob<>nil) then
Result:=True;
except
OutputDebugString(PWideChar(SQLText));
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
//insert the fsFieldBlob into the db
//e.g.: SQLText:'UPDATE BsEmployee SET EmpPicture=? WHERE Emp_ID=13887
function UpdateFieldBlobSQL(SQLText: string;fsFieldBlob: TFileStream): Boolean;
var
slDBpath: string;
sldb: TSQLiteDatabase;
begin
Result:=False;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) then
Exit;
try
sldb:=TSQLiteDatabase.Create(slDBpath);
sldb.UpdateBlob(SQLText, fsFieldBlob);
FreeAndNil(sldb);
Result:=True;
except
OutputDebugString(PWideChar(SQLText));
if Assigned(sldb) then
FreeAndNil(sldb);
end;
end;
function SQLiteDBBackup(BackupFileName:string): Boolean;
var
slDBpath: string;
sldb,sldbBak: TSQLiteDatabase;
begin
Result:=False;
slDBpath:=AppPath+SQLiteDBFileName;
if not FileExists(slDBpath) then
Exit;
try
sldb:=TSQLiteDatabase.Create(slDBpath);
slDBpath:=AppPath+BackupFileName;
sldbBak:=TSQLiteDatabase.Create(slDBpath);
try
if sldb.Backup(sldbBak)=101 then //SQLITE_DONE
Result:=True;
finally
FreeAndNil(sldbBak);
end;
FreeAndNil(sldb);
except
OutputDebugString(PWideChar('SQLiteDBBackup: 失败!'));
if Assigned(sldb) then
FreeAndNil(sldb);
if Assigned(sldbBak) then
FreeAndNil(sldbBak);
end;
end;
initialization
//SQLite数据库文件名(不含程序路径)
SQLiteDBFileName:= 'MiniHRMDB.s3db';
SQLItemlist:=THashedStringList.Create;
AppPath:=ExtractFilePath(ParamStr(0));
finalization
if Assigned(SQLItemlist) then
FreeAndNil(SQLItemlist);
end.
看到上面的代码,基本上,都能理解了,下面我主要再重点说一下使用方法:
1、复制另存为文件,在自己的程序或项目中引用此文件,依实际情况修改自己的SQLite数据库文件名(不含程序路径),SQLiteDBFileName:= '自己的SQLite数据库文件名',当然数据库文件在程序目录时,如果在子目录中仍要含相对路径,如:Data/自己的SQLite数据库文件名。
2、主要使用:OpenClientDataSet、ExecSQL,其他的则视自己的情况使用了,基本上解决了,获取数据、提交数据的功能,如果涉及到FieldBlob,则再使用GetFieldBlobSQL、UpdateFieldBlobSQL;
3、至于GetSelectSQL、GetInsertSQL、GetUpdateSQL、GetDeleteSQL、GetSQLText为非必要的,自己视情况使用;
4、要想看效果或测试程序,那就在窗体上扔几个控件:ClientDataSet1、DataSource1、DBGrid1且互相关联起来(具体操作省略,懒得说,没意义),接着放个适当的地方(FormShow或XXXClick等)调用对应方法(OpenClientDataSet、ExecSQL)就行了。
5、其实核心就是从SQLite中获取的数据传递给TClientDataSet,接着你要如何展示或显示数据,那就随你了(处理TClientDataSet中的数据)。而提交数据采用是执行对应的SQL语句。如果仍不明白的,那我也没办法了,只能说本系列文章不适用您!有点Delphi基础的,基本上一看就会,就明白了。
二、Delphi SQLite Wrapper
与A simple Delphi wrapper for Sqlite3差不多,故无必要再说了,具体参数A simple Delphi wrapper for Sqlite3以及自带的说明文档,基本上没有任何压力和难度。