数据库连接池_uDatabasePool

{

自定义DataBase类

功能：操作数据库函数集

by wfj

2013.7.4

}

unit uDatabasePool;

 

interface

 

uses

  System.SysUtils, System.DateUtils, {$IF CompilerVersion>=23.0}System.SyncObjs{$ELSE}SyncObjs{$IFEND},

  System.Generics.Collections, FMX.Objects, Data.FMTBcd, Data.DB, Data.SqlExpr, Data.DBXInterbase;

 

type

  time_t = int64;

 

type

  CMyDB = Class(TObject)

  protected

    iBusy: integer;

    tLastUse: time_t;

  public

    function  isBusy():boolean;

    procedure setBusy(i: integer);  //表示是否正在使用 0未使用；1正在检测数据库连接；2正在使用数据库

    procedure ConnectDb();

    procedure DisconnectDb();

    procedure TestConnection();

    function  DBIdleSecond(): integer; //返回数据库空闲时间（秒）

  public

    { Public declarations }

    m_DBSession: TSQLConnection;

    m_Query: TSQLQuery;

    constructor Create();

    destructor Destroy; override;

    function isConnected(): boolean;

end;

 

type

  CDatabasePool=Class(TObject)

  private

  {}

  protected

    m_ConList: TObjectList;

 cs: TCriticalSection;

 m_ConnectCount: integer; //连接池中的连接数

 

    procedure Initial();

    procedure myFinal();

 function GetInstance(): CDatabasePool;

 procedure MyFreeInstance();

  public

    constructor Create(const count: integer=5);

    destructor Destroy; override;

 

    function  GetConnection(const isCreate: boolean=true): CMyDB;//isCreate表示当没有找到时是否创建一个数据库连接

    procedure ReleaseConnection(var db: CMyDB);

    procedure TestConnections();

    procedure ClearAllConnections();

    function  GetCount(): integer;

    //status 0表示空闲，1表示连接在上面，2表示正忙

    procedure GetStatus(id: integer; status: integer);

  public

    m_pThis: CDatabasePool;

end;

 

 

var

  MySqliteConn: CMyDB;

 

function ExecSQL(const ASql: String):boolean;

function RecordExists(const ASql: String):boolean;

function CheckExecSQL(const aCategoryTitle: string; const aId: string; const ASql: String):boolean;

function OpenSQL(var query: TSQLQuery; const ASql: String):boolean;

implementation

 

uses ufMain, PublicVar;

 

{ CMyDB }

 

constructor CMyDB.Create;

begin

  setBusy(2);

  m_DBSession := nil;

  m_Query := nil;

end;

 

destructor CMyDB.Destroy;

begin

  if (m_Query<>nil) then

  begin

    m_Query.Close;

   FreeAndNil(m_Query);

  end;

  if (m_DBSession<>nil) then

  begin

    m_DBSession.Connected:= false;

   FreeAndNil(m_DBSession);

  end;

end;

 

function CMyDB.isConnected(): boolean;

begin

  if (m_DBSession<>nil) then

  begin

    if m_DBSession.Connected then

     result:= true

    else

     result:= false;

  end else

    result:= false;

end;

 

function CMyDB.isBusy():boolean;

begin

  if iBusy>0 then

    result:= true

  else

    result:= false;

end;

 

procedure CMyDB.setBusy(i: integer);

begin

  iBusy := i;

  if (i = 2) then //使用数据库

    tLastUse:= MilliSecondOfTheDay(Time());

end;

 

procedure CMyDB.ConnectDb();

begin

  try

    if (m_DBSession=nil) then

    begin

     m_DBSession := TSQLConnection.Create(nil);

    end;

    if (m_Query=nil) then

    begin

     m_Query := TSQLQuery.Create(nil);

     m_Query.SQLConnection := m_DBSession;

    end;

    if (m_DBSession.Connected) then

     m_DBSession.Connected:= false;

    m_DBSession.DriverName:= 'Sqlite';

    {$IFDEF IOS}

     m_DBSession.Params.Values['Database'] := AppPath + PathDelim +

       'Documents' + PathDelim  + 'TASKS.s3db';

    {$ELSE}

     m_DBSession.Params.Values['Database'] := AppPath + 'Data' + PathDelim + 'TASKS.s3db';

    {$ENDIF}

    m_DBSession.Connected:= true;

  except

 

  end;

end;

 

procedure CMyDB.DisconnectDb();

begin

  if (m_DBSession<>nil) then

  begin

    if (m_DBSession.Connected) then

    begin

     m_DBSession.Connected:= false;

    end;

  end;

end;

 

function CMyDB.DBIdleSecond(): integer;

var

  t: time_t;

begin

  t:= MilliSecondOfTheDay(Time());

  result:= t-tLastUse;

end;

 

procedure CMyDB.TestConnection();

var

  Query1: TSQLQuery;

begin

  if (m_DBSession<>nil) then

  begin

    if (not m_DBSession.Connected) then

     exit;

 

   //当数据库连接的情况下，检查是否是真的连上的。

    Query1 := m_Query;

    try

     Query1.Close();

     Query1.SQL.Clear();

     Query1.SQL.Add('select * from Posts limit 1');

     Query1.ExecSQL();

    except

     m_DBSession.Connected:= false;

    end;

    Query1.Close();

  end;

end;

 

{CDatabasePool}

constructor CDatabasePool.Create(const count: integer);

begin

  m_pThis :=nil;

m_ConnectCount := count;

  m_ConList := TObjectList.Create();

  cs := TCriticalSection.Create;

end;

 

destructor CDatabasePool.Destroy;

begin

  if (m_ConList<>nil) then

  begin

    m_ConList.Clear;

   FreeAndNil(m_ConList);

  end;

 

  if (cs<>nil) then

  begin

    FreeAndNil(cs);

  end;

end;

 

function CDatabasePool.GetInstance(): CDatabasePool;

begin

if (m_pThis=nil) then

  begin

m_pThis := CDatabasePool.Create();

end;

result := m_pThis;

end;

 

procedure CDatabasePool.MyFreeInstance();

begin

  FreeAndNil(m_pThis);

end;

 

// 返回值表示可用数据库连接,NULL表示没有可用的连接

//isCreate表示当没有找到时是否创建一个数据库连接

 

function CDatabasePool.GetConnection(const isCreate: boolean=true): CMyDB;

var

  i, count: integer;

  pdb: CMyDB;

begin

  if (m_pThis=nil) then

    result := nil;

 

  m_pThis.cs.Acquire();

  try

    count := m_pThis.m_ConList.Count;

    for i := 0 to count-1 do

    begin

      pdb := CMyDB(m_pThis.m_ConList.Items[i]);

      if (pdb=nil) then continue;

      if (not pdb.isBusy()) then

     begin

       Result := pdb;

       Result.setBusy(2);

       break;

     end;

    end;

  finally

   m_pThis.cs.Release();

  end;

 

  if (Result<>nil) then

  begin

    if (not Result.isConnected()) then

    begin

     Result.ConnectDb();

      if (not Result.isConnected()) then

     begin

       //不能连接上数据库

       m_pThis.cs.Acquire();

       try

         Result.setBusy(0);

       finally

         m_pThis.cs.Release();

       end;

     end;

    end;

  end else  //没有找到空闲的数据库连接

  begin

    if ( isCreate ) then

    begin

      pdb := CMyDB.Create();

     try

       pdb.ConnectDb();

     finally

       if (not pdb.isConnected()) then

       begin

         //不能连接上数据库

         FreeAndNil(pdb);

       end else begin //连接上数据库，放到链表中

         m_pThis.cs.Acquire();

         try

          m_pThis.m_ConList.Add(pdb);

           Result := pdb;

         finally

          m_pThis.cs.Release();

         end;

       end;

     end;

    end;

  end;

end;

 

procedure CDatabasePool.ReleaseConnection(var db: CMyDB);

var

  count: integer;

begin

  if ((m_pThis=nil)or (db=nil)) then

    exit;

 

  m_pThis.cs.Acquire();

  try

    count := m_pThis.m_ConList.Count;

    if (count > m_pThis.m_ConnectCount) then

     m_pThis.m_ConList.Remove(db)

    else

     db.setBusy(0);

  finally

   m_pThis.cs.Release();

  end;

end;

 

procedure CDatabasePool.TestConnections();

var

  i, count: integer;

  pdb: CMyDB;

begin

  if (m_pThis=nil) then

    exit;

 

  count := 0;

  m_pThis.cs.Acquire();

  try

    count := m_pThis.m_ConList.Count;

  finally

   m_pThis.cs.Release();

  end;

 

  for i := 0 to count-1 do

  begin

    pdb := nil;

   m_pThis.cs.Acquire();

     try

       pdb := CMyDB(m_pThis.m_ConList.Items[i]);

       if (not pdb.isBusy()) then

         pdb.setBusy(1)

       else

         pdb := nil;

     finally

       m_pThis.cs.Release();

     end;

 

    if (pdb<>nil) then

    begin

     //检测数据库连接

      if (pdb.DBIdleSecond()>6000) then

       pdb.DisconnectDb()

     else

       pdb.TestConnection();

 

     m_pThis.cs.Acquire();

     try

         pdb.setBusy(0);

     except

       pdb.DisconnectDb();

     end;

     m_pThis.cs.Release();

    end;

  end;

end;

 

procedure CDatabasePool.ClearAllConnections();

begin

    if (m_pThis=nil) then

     exit;

 

   m_pThis.cs.Acquire();

    try

     m_pThis.m_ConList.Clear();

    finally

     m_pThis.cs.Release();

    end;

end;

 

procedure CDatabasePool.Initial();

begin

  GetInstance();

end;

 

procedure CDatabasePool.myFinal;

begin

  ClearAllConnections();

  MyFreeInstance();

end;

 

function CDatabasePool.GetCount(): integer;

Var

  count: Integer;

begin

  if (m_pThis=nil) then

    result := 0;

 

  count := 0;

  m_pThis.cs.Acquire();

  try

    count := m_pThis.m_ConList.Count;

  finally

   m_pThis.cs.Release();

  end;

  result:= count;

end;

 

procedure CDatabasePool.GetStatus(id: integer; status: integer);

var

  pdb: CMyDB;

begin

  status := 0;

 

  if (m_pThis=nil) then

    exit;

 

  if (id>=m_pThis.m_ConList.Count) then

    exit;

 

  m_pThis.cs.Acquire();

  try

    pdb := CMyDB(m_pThis.m_ConList.Items[id]);

    if (pdb.isBusy()) then

     status := 2

    else if pdb.isConnected() then

     status := 1

    else

     status := 0;

  finally

   m_pThis.cs.Release();

  end;

end;

//------------------------------------------------------------------------------

{

  作用：插入、更新等非返回数据集的操作

  参数：query实例，sql语句

  返回：真或假

}

function ExecSQL(const ASql: String):boolean;

//var

//  myQuery: TSQLQuery;

begin

  if MySqliteConn=nil then

  begin

    MySqliteConn:= CMyDB.Create;

   MySqliteConn.ConnectDb;

  end;

  if not MySqliteConn.isConnected then

  begin

    result:= false;

    exit;

  end;

 

  //myQuery:= TSQLQuery.Create(nil);

  //myQuery.SQLConnection:= MySqliteConn.m_DBSession;

  //执行sql

  try

    with MySqliteConn.m_Query do begin

     Sql.Clear;

     Sql.Add(ASql);

     ExecSQL();

    end;

  finally

   MySqliteConn.m_Query.Close;

   //FreeAndNil(myQuery);

  end;

  result:= true;

end;

 

{

  作用：判断记录是否存在，插入、更新等非返回数据集的操作

  参数：query实例，sql语句

  返回：真或假

}

function CheckExecSQL(const aCategoryTitle: string; const aId: string; const ASql: String):boolean;

//var myQuery: TSQLQuery;

begin

  if MySqliteConn=nil then

  begin

    MySqliteConn:= CMyDB.Create;

   MySqliteConn.ConnectDb;

  end;

  if not MySqliteConn.isConnected then

  begin

    result:= false;

    exit;

  end;

 

  //myQuery:= TSQLQuery.Create(nil);

  //myQuery.SQLConnection:= MySqliteConn.m_DBSession;

  //执行sql

  try

    with MySqliteConn.m_Query do begin

     Sql.Clear;

     Sql.Add('select id from Posts where CategoryTitle='''+aCategoryTitle+''' and Id='''+aId+'''');

     Open;

      if RecordCount<=0 then

     begin

       Close;

       Sql.Clear;

       Sql.Add(ASql);

       ExecSQL();

     end;

    end;

  finally

   MySqliteConn.m_Query.Close;

   //FreeAndNil(myQuery);

  end;

  result:= true;

end;

 

{

  作用：判断记录是否存在

  参数：sql语句

  返回：真或假

}

function RecordExists(const ASql: String):boolean;

//var myQuery: TSQLQuery;

begin

  if MySqliteConn=nil then

  begin

    MySqliteConn:= CMyDB.Create;

   MySqliteConn.ConnectDb;

  end;

  if not MySqliteConn.isConnected then

  begin

    result:= false;

    exit;

  end;

 

  //myQuery:= TSQLQuery.Create(nil);

  //myQuery.SQLConnection:= MySqliteConn.m_DBSession;

  //打开表

  try

    try

      with MySqliteConn.m_Query do begin

       Sql.Clear;

       Sql.Add(ASql);

       Open;

       if RecordCount>0 then

         result:= false

       else

         result:= true;

       close;

     end;

    except

     result:= false;

    end;

  finally

   MySqliteConn.m_Query.Close;

   //FreeAndNil(myQuery);

  end;

end;

 

{

  作用：打开表操作，并在参数中返回打开的表

  参数：query实例(返回)，sql语句

  返回：真或假

}

function OpenSQL(var query: TSQLQuery;const ASql: String):boolean;

begin

  if MySqliteConn=nil then

  begin

    MySqliteConn:= CMyDB.Create;

   MySqliteConn.ConnectDb;

  end;

  if not MySqliteConn.isConnected then

  begin

    result:= false;

    exit;

  end;

 

  query.SQLConnection := MySqliteConn.m_DBSession;

  //打开表

  try

    with query do begin

     Close;

     Sql.Clear;

     Sql.Add(ASql);

     Open;

      if RecordCount>0 then  //有记录

       result:= true

     else

       result:= false;

    end;

  except

    result:= false;

  end;

end;

 

end.