delphi连接mysql数据库

delphi要连接Mysql数据库的办法可以通过ODBC来进行，但使用ODBC的时候需要配置数据源，在配置的时候也有可能出现连接信息泄露的问题，在网上找了半天，终于找到了一个使用mysql中libmysql.dll接口的办法，下载后发现mysql.pas只提供一系列函数接口，于是花了一个晚上的时间把这些函数根据自己平时的习惯封装成对应的几个类。封装后的代码如下。完整的源代码打包下载

//---------------------------------------------
// unit: mysqli
// author: 王王王
// blog: http://www.cnblogs.com/wjh00a
// 2011-3-15
//---------------------------------------------

unit mysqli;

interface

uses SysUtils,mysql;

type

//---------------------------------------------
// TMySqliConn
//---------------------------------------------
TMySqliConn = class(TObject)
private
handle: PMYSQL;
public
constructor Create(host,user,passwd,db: string);
destructor Destory;
function GetHandle():PMYSQL;
end;

//---------------------------------------------
// TMysqliQueryResult
//---------------------------------------------
TMySqliQueryResult = class(TObject)
private
handle: PMYSQL_RES;
rowIndex: Integer;
function FieldIndex(fieldName: string): Integer;
public
constructor Create(qryResult: PMYSQL_RES);
destructor Destory();
function GetFieldNameByIndex(index: Integer): string;
function GetDataByFieldName(fieldName: string): string;
function Num_Rows(): Integer;
function Num_Fields(): Integer;
function BOF(): Boolean;
function EOF(): Boolean;
procedure MoveToFirst;
procedure MoveNext;
procedure MoveToLast;
procedure Free();
end;

//---------------------------------------------
// TMysqli
//---------------------------------------------
TMySqli = class(TObject)
private
hMysql: PMYSQL;
strSql: string;
procedure setSql(const Value: string);
public
constructor Create(conn: TMysqliConn);
function Query(): TMySqliQueryResult;
function Excute(): Integer;
function Insert_Id(): Integer;
property SQL : string read strSql write setSql;
end;

implementation

//---------------------------------------------
// TMysqlConn
//---------------------------------------------
{ TMysqlConn }

constructor TMySqliConn.Create(host,user,passwd,db: string);
begin
libmysql_fast_load(nil);
handle := mysql_init(nil);
if handle=nil then
raise Exception.Create('mysql_init failed');
if (mysql_real_connect(handle,PAnsiChar(host),PAnsiChar(user),PAnsiChar(passwd),
nil, 0, nil, 0)=nil) then
raise Exception.Create(mysql_error(handle));
mysql_select_db(handle,PAnsiChar(db));
mysql_set_character_set(handle,'utf8');
end;

destructor TMySqliConn.Destory;
begin
if libmysql_status=LIBMYSQL_READY then
mysql_close(handle);
libmysql_free;
end;

function TMySqliConn.GetHandle: PMYSQL;
begin
Result := handle;
end;

//---------------------------------------------
// TMysqli
//---------------------------------------------
{ TMysqli }

constructor TMySqli.Create(conn: TMysqliConn);
begin
hMysql := conn.GetHandle;
end;

function TMySqli.Query: TMySqliQueryResult;
begin
if mysql_real_query(hMysql, PAnsiChar(SQL), Length(SQL))<>0
then
raise Exception.Create(mysql_error(hMysql));
Result := TMySqliQueryResult.Create(mysql_store_result(hMysql));
end;

function TMySqli.Excute: Integer;
begin
Query;
Result := mysql_affected_rows(hMysql);
end;

function TMySqli.Insert_Id: Integer;
begin
Result := mysql_insert_id(hMysql);
end;

procedure TMySqli.setSql(const Value: string);
begin
strSql := Value;
end;

//---------------------------------------------
// TMysqliQueryResult
//---------------------------------------------
{ TMysqliQueryResult }

constructor TMySqliQueryResult.Create(qryResult: PMYSQL_RES);
begin
handle := qryResult;
end;

destructor TMySqliQueryResult.Destory;
begin
Free;
end;

function TMySqliQueryResult.GetFieldNameByIndex(index: Integer): string;
var
field: PMYSQL_FIELD;
begin
if (index<0) or (index>Num_Fields) then
Exception.Create('TMysqliQueryResult GetFieldNameByIndex unknow index');
field := mysql_fetch_field_direct(handle, index);
Result := mysql_field_name(field);
end;

function TMySqliQueryResult.FieldIndex(fieldName: string): Integer;
var
fieldCount,i: Integer;
fName: string;
res: Integer;
begin
res := -1;
fieldCount := Num_Fields;
for i:=0 to fieldCount-1 do
begin
fName := GetFieldNameByIndex(i);
if fName = fieldName then
begin
res := i;
Break;
end;
end;
Result := res;
end;

function TMySqliQueryResult.GetDataByFieldName(fieldName: string): string;
var
col: Integer;
row: PMYSQL_ROW;
begin
if handle = nil then
Exception.Create('TMysqli GetDataByFieldName qryResult = nil');
if BOF or EOF then
Exception.Create('TMysqli GetDataByFieldName BOF or EOF');
col := FieldIndex(fieldName);
if col < 0 then
Exception.Create('TMysqli GetDataByFieldName Field not exist');
mysql_data_seek(handle, rowIndex);
row := mysql_fetch_row(handle);
Result := row^[col];
end;

function TMySqliQueryResult.Num_Rows: Integer;
begin
Result := mysql_num_rows(handle);
end;

function TMySqliQueryResult.Num_Fields: Integer;
begin
Result := mysql_num_fields(handle);
end;

function TMySqliQueryResult.BOF: Boolean;
begin
if rowIndex < 0 then
Result := true
else
Result := false;
end;

function TMySqliQueryResult.EOF: Boolean;
begin
if rowIndex >= Num_Rows then
Result := true
else
Result := false;
end;

procedure TMySqliQueryResult.MoveToFirst;
begin
rowIndex := 0;
end;

procedure TMySqliQueryResult.MoveNext;
begin
if not EOF then
rowIndex := rowIndex + 1;
end;

procedure TMySqliQueryResult.MoveToLast;
begin
rowIndex := Num_Rows - 1;
end;

procedure TMySqliQueryResult.Free;
begin
if handle<>nil then
mysql_free_result(handle);
end;

end.