本篇内容主要讲解“如何使用Zeos数据库连接池”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“如何使用Zeos数据库连接池”吧!
unit ZeosConPool;
interface
uses
SysUtils, Windows, Classes, IniFiles, DB,
ZAbstractRODataset, ZDataset, ZAbstractConnection, ZConnection, ZCompatibility;
type
TSQLConntionRecord = record
HostName: string;
Port: Integer;
UserName: string;
DBName: string;
MyDataBase: string;
Password: string;
end;
TSQLConnectionPool = class
private
fDataBase: string;
fServer: string;
fPort: integer;
fUserName: string; // 数据库用户
fPassWord: string; // 密码
FConList: TThreadList; // 池容器
function TestConnection(con: TzConnection): boolean;
function CreateNewConnection: TZConnection;
function GetConnectionRecord: TSQLConntionRecord;
public
function Pop: TZConnection;
procedure Push(con: TZConnection);
constructor Create;
destructor Destroy; override;
end;
TQryPool = class
private
function GetQry: TZQuery;
procedure con(qry: TZQuery);
procedure discon(qry: TZQuery);
public
function Require: TZQuery;
procedure Return(qry: TZQuery);
end;
function GetQueryJsonData(sql: string): string;
var
ConnectionPools: TSQLConnectionPool;
QryPools: TQryPool;
implementation
function _DataSetToJson(ADataset: TDataSet): string;
// [{“CityId”:”18”,”CityName”:”西安”},{“CityId”:”53”,”CityName”:”广州”}]
var
LRecord: string;
LField: TField;
i: integer;
begin
Result := '';
if (not ADataset.Active) or (ADataset.IsEmpty) then Exit;
Result := '[';
ADataset.DisableControls;
ADataset.First;
while not ADataset.Eof do
begin
for i := 0 to ADataset.FieldCount - 1 do
begin
LField := ADataset.Fields[i]; // 取得当前字段
if LRecord = '' then
LRecord := '{"' + LField.FieldName + '":"' + LField.Text + '"'
else
LRecord := LRecord + ',"' + LField.FieldName + '":"' + LField.Text + '"';
if i = ADataset.FieldCount - 1 then
begin
LRecord := LRecord + '}';
if Result = '[' then
Result := Result + LRecord
else
Result := Result + ',' + LRecord;
LRecord := '';
end;
end;
ADataset.Next;
end;
ADataset.EnableControls;
Result := Result + ']';
end;
{ TSQLConnectionPool }
constructor TSQLConnectionPool.Create;
begin
FConList := TThreadList.Create;
end;
destructor TSQLConnectionPool.Destroy;
var
i: Integer;
begin
with FConList.LockList do
try
for i := Count - 1 downto 0 do
begin
TZConnection(Items[i]).Disconnect;
TZConnection(Items[i]).Free;
end;
finally
FConList.UnlockList;
end;
FConList.Free;
end;
//获取SQL连接对象
function TSQLConnectionPool.CreateNewConnection: TZConnection;
var
con: TZConnection;
RecCon: TSQLConntionRecord;
begin
Result := nil;
try
con := TZConnection.Create(nil);
RecCon := GetConnectionRecord;
try
with con do
begin
Protocol := 'MySQL'; // 数据库驱动类型
LibraryLocation := 'libmysql.dll';
ClientCodepage := 'utf8';
LoginPrompt := False;
HostName := RecCon.HostName;
User := RecCon.UserName;
Password := RecCon.Password;
Database := RecCon.DBName;
Port := RecCon.Port;
Connect;
end;
Result := con;
except
on E: exception do
begin
Result := nil;
con.Free;
// 打印日志。。。。
end;
end;
except
end;
end;
//获取配置SQL连接参数
function TSQLConnectionPool.GetConnectionRecord: TSQLConntionRecord;
var
dbIni: TIniFile;
begin
dbIni := TIniFile.Create(ExpandFileName(ExtractFilePath(ParamStr(0)) +
'\DataBase.ini'));
try
with Result do
begin
HostName := dbIni.ReadString('Database', 'Host', '127.0.0.1');
Port := dbIni.ReadInteger('Database', 'Port', 3306);
UserName := dbIni.ReadString('Database', 'UID', 'root');
DBName := dbIni.ReadString('Database', 'Database', 'klys');
MyDataBase := UpperCase(dbIni.ReadString('Database', 'DataBaseType', 'MySql'));
Password := dbIni.ReadString('Database', 'Password', '1234567');
end;
finally
dbIni.Free;
end;
end;
//弹出SQL连接对象
function TSQLConnectionPool.Pop: TZConnection;
begin
with FConList.LockList do
try
if Count > 0 then
begin
Result := TZConnection(Items[Count-1]);
Delete(Count-1);
{
if not TestConnection(Result) then
begin
Result.Free;
Result := Pop; // 递归
end;
}
end
else
begin
Result := CreateNewConnection;
end
finally
FConList.UnlockList;
end;
end;
//回收SQL连接对象
procedure TSQLConnectionPool.Push(con: TZConnection);
begin
if con <> nil then
with FConList.LockList do
try
// Insert(0, con);
Add(con);
finally
FConList.UnlockList;
end;
end;
//测试连接池中的SQL对象是否存活
function TSQLConnectionPool.TestConnection(con: TZConnection): boolean;
begin
Result := false;
try
// con.ExecuteDirect('delete from zd_user where 1<>1');
Result := true;
except
on E: exception do
begin
// 实际应用,一定要打印日志
end;
end;
end;
{ TQryPool }
//qry 关联SQL Connection
procedure TQryPool.con(qry: TZQuery);
var
sqlcon: TZConnection;
begin
sqlcon := ConnectionPools.Pop;
qry.Connection := sqlcon;
end;
//回收SQL Connetion 对象
procedure TQryPool.discon(qry: TZQuery);
begin
ConnectionPools.Push(TZConnection(qry.Connection));
end;
//获取对象
function TQryPool.GetQry: TZQuery;
var
qry: TZQuery;
begin
qry := TZQuery.Create(nil);
con(qry);
Result := qry;
end;
//弹出Qry对象
function TQryPool.Require: TZQuery;
begin
Result := GetQry;
end;
//获取Qry对象
procedure TQryPool.Return(qry: TZQuery);
begin
if qry <> nil then
begin
qry.Close;
ConnectionPools.Push(TZConnection(qry.Connection));
qry.Free;
end;
end;
function GetQueryJsonData(sql: string): string;
var
qry: TZQuery;
uid: integer;
susername, spw: string;
str:String;
begin
qry := QryPools.Require;
try
qry.SQL.Text := sql;
qry.Open;
result := _DataSetToJson(qry);
finally
QryPools.Return(qry);
end;
end;
initialization
ConnectionPools := TSQLConnectionPool.Create();
QryPools := TQryPool.Create;
finalization
QryPools.Free;
ConnectionPools.Free;
end.
到此,相信大家对“如何使用Zeos数据库连接池”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。
原文链接:https://my.oschina.net/u/582827/blog/5022031