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
