Delphi XE MySQL数据库操作类 MySQLHelper

  注: 无需odbc配置

  1 {*
  2  *                         MySQL Helper v1.0
  3  *                            2015.6.19
  4  * 说明:
  5  *     这是一个操作MySQL的类,该类必须和libmysql.dll,dbxmys.dll两个文件一起使用.
  6  * 安装:
  7  *     将dll拷贝到C:\Windows\System32下和项目目录下,发行的时候放到exe目录下即可.
  8  * 使用:
  9  *    //使用insert,update,delete语句时请使用:TMySQLHelper.ExecSQL();返回受影响行数Integer;
 10  *    //使用select语句时请使用TMySQLHelper.Query();返回数据集TSQLQuery;
 11  * 测试:
 12  *     WIN7 SP1 X86 , MySQL 5.6.17 , Delphi XE 测试通过.
 13  * ==========================================
 14  * var
 15  *   MySQLHelper : TMySQLHelper;
 16  * begin
 17  *   MySQLHelper :=  TMySQLHelper.Create;
 18  *   MySQLHelper.User_name := 'root';
 19  *   MySQLHelper.Password  := 'root';
 20  *   MySQLHelper.Database  := 'Test';
 21  *   ShowMessage('影响行数:'+IntToStr(MySQLHelper.ExecSQL('INSERT INTO test(name)values(''FangJun'')')));
 22  *   MySQLHelper.Free;
 23  * end;
 24  * ==========================================
 25  * var
 26  *   MySQLHelper : TMySQLHelper;
 27  *   SQLQuery : TSQLQuery;
 28  * begin
 29  *   MySQLHelper :=  TMySQLHelper.Create;
 30  *   MySQLHelper.User_name := 'root';
 31  *   MySQLHelper.Password  := 'root';
 32  *   MySQLHelper.Database  := 'Test';
 33  *   SQLQuery := TSQLQuery.Create(nil);
 34  *   SQLQuery := MySQLHelper.Query('select * from test');
 35  *   while not SQLQuery.Eof do
 36  *     begin
 37  *       ShowMessage('姓名:'+VarToStr(SQLQuery.FieldValues['name']);
 38  *       SQLQuery.Next;
 39  *     end;
 40  *   MySQLHelper.MySQLClose;
 41  *   MySQLHelper.Free;
 42  * end;
 43  * ==========================================
 44 }
 45 unit MySQLHelper;
 46 
 47 interface
 48 
 49 uses
 50    SysUtils,StdCtrls,Classes,Variants,DB,SqlExpr,DBXMySQL;
 51 
 52 type
 53   TMySQLHelper = class(TObject)
 54   private
 55     _PORT          : Integer;
 56     _HOST          : string;
 57     _DATABASE      : string;
 58     _USER_NAME     : string;
 59     _PASSWORD      : string;
 60     _SERVERCHARSET : string;
 61     _SQLQuery      : TSQLQuery;
 62     _SQLConnection : TSQLConnection;
 63 
 64     procedure Set_PORT(const Value: Integer);
 65     procedure Set_HOST(const Value: string);
 66     procedure Set_DATABASE (const Value: string);
 67     procedure Set_USER_NAME(const Value: string);
 68     procedure Set_PASSWORD (const Value: string);
 69     procedure Set_SERVERCHARSET(const Value: string);
 70     function  MySQLConnection:TSQLConnection;
 71 
 72   public
 73     constructor Create; overload;
 74     property Post:Integer write Set_PORT;
 75     property Host:string write Set_HOST;
 76     property Database:string write Set_DATABASE;
 77     property User_name:string write Set_USER_NAME;
 78     property Password:string write Set_PASSWORD;
 79     property ServerCharSet:string write Set_SERVERCHARSET;
 80 
 81     function ExecSQL(const SQL:string):Integer;
 82     function Query(const SQL:string):TSQLQuery;
 83     procedure MySQLClose;
 84   end;
 85 
 86 implementation
 87 
 88   //初始化
 89   constructor TMySQLHelper.Create;
 90   begin
 91     _HOST := '127.0.0.1';
 92     _PORT := 3306;
 93     _SERVERCHARSET := 'utf8';
 94   end;
 95 
 96   //执行 SQL 语句 INSERT , UPDATE , DELETE  返回影响行数
 97   function TMySQLHelper.ExecSQL(const SQL:string):Integer;
 98   begin
 99     if not Assigned(_SQLQuery) then
100       _SQLQuery := TSQLQuery.Create(nil);
101     with _SQLQuery do
102     begin
103       Close;
104       SQL.Clear;
105       SQLConnection := MySQLConnection;
106     end;
107     try
108       _SQLQuery.SQL.Add(SQL);
109       result := _SQLQuery.ExecSQL;
110     except on E: Exception do
111       raise Exception.Create('SQL语句执行失败 ：'+E.Message);
112     end;
113     MySQLClose;
114   end;
115 
116   //执行 SQL 语句 Select 返回 数据集
117   function TMySQLHelper.Query(const SQL:string):TSQLQuery;
118   begin
119     if not Assigned(_SQLQuery) then
120        _SQLQuery := TSQLQuery.Create(nil);
121     with _SQLQuery do
122     begin
123       Close;
124       SQL.Clear;
125       SQLConnection := MySQLConnection;
126     end;
127     try
128       _SQLQuery.SQL.Add(SQL);
129       _SQLQuery.Open;
130       _SQLQuery.Active := true;
131       result := _SQLQuery;
132     except on E: Exception do
133       raise Exception.Create('SQL语句查询失败 ：'+E.Message);
134     end;
135   end;
136 
137   //关闭连接
138   procedure TMySQLHelper.MySQLClose;
139   begin
140     _SQLQuery.Close;
141     _SQLConnection.Close;
142   end;
143 
144   //连接MySQL 返回 TSQLConnection
145   function TMySQLHelper.MySQLConnection:TSQLConnection;
146   begin
147     if not Assigned(_SQLConnection) then
148        _SQLConnection := TSQLConnection.Create(nil);
149     with _SQLConnection do
150       begin
151         Close;
152         GetDriverFunc := 'getSQLDriverMYSQL';
153         LibraryName := 'dbxmys.dll';
154         VendorLib := 'LIBMYSQL.dll';
155         DriverName:= 'MySQL';
156         Params.Values['drivername']:= 'MySQL';
157         Params.Values['port']      := IntToStr(_PORT);
158         Params.Values['hostname']  := _HOST;
159         Params.Values['database']  := _DATABASE;
160         Params.Values['user_name'] := _USER_NAME;
161         Params.Values['password']  := _PASSWORD;
162         Params.Values['ServerCharSet'] := _SERVERCHARSET;
163       end;
164     try
165       _SQLConnection.Open;
166       _SQLConnection.Connected := true;
167       result := _SQLConnection;
168     except on E: Exception do
169       raise Exception.Create('数据库连接错误：'+E.Message);
170     end;
171   end;
172 
173 
174   procedure TMySQLHelper.Set_PORT(const Value: Integer);
175   begin
176     if Value<>0 then
177        _PORT := Value
178   end;
179 
180   procedure TMySQLHelper.Set_HOST (const Value: string);
181   begin
182     if Value<>'' then
183        _HOST := Value
184   end;
185 
186   procedure TMySQLHelper.Set_DATABASE (const Value: string);
187   begin
188     _DATABASE := Value
189   end;
190 
191   procedure TMySQLHelper.Set_USER_NAME (const Value: string);
192   begin
193     _USER_NAME := Value;
194   end;
195 
196   procedure TMySQLHelper.Set_PASSWORD (const Value: string);
197   begin
198     _PASSWORD := Value;
199   end;
200 
201   procedure TMySQLHelper.Set_SERVERCHARSET (const Value: string);
202    begin
203     if Value<>'' then
204        _SERVERCHARSET := Value
205   end;
206 
207 end.

 下载地址 MySQLHelper

转载于:https://www.cnblogs.com/fangjunai/p/4587937.html