一个简陋的mysql封装类(Windows下)

         mysql的一个在windows下的封装类，做成了dll，用着可能不是很顺手，以后有时间再修改。

mysql_cpp.h

#ifndef __mysql_cpp_h__#define __mysql_cpp_h__typedef unsigned charuchar;typedefunsigned shortushort;typedef unsigned intuint;typedefunsigned longulong;#define DEFAULT_HOST"127.0.0.1"#define DEFAULT_ENCODING"utf8"#define DEFAULT_PORT3306#define DEFAULT_CONN_NUM5#define DEFAULT_TIMEOUT3000#include <string>#include <vector>#include <Windows.h>#include <mysql.h>#pragma comment( lib, "libmysql.lib" )#endif // __mysql_cpp_h__

mysql_connection_pool.h

#ifndef __mysql_connection_pool_h__#define __mysql_connection_pool_h__#ifndefDLLEXPORT#defineDLLAPI__declspec(dllimport)#else#defineDLLAPI__declspec(dllexport)#endif#define DLLCLSDLLAPI#include "mysql_cpp.h"class CMysqlConnection;class DLLCLS CMysqlConnectionPool{public:CMysqlConnectionPool( const std::string& username, const std::string& password, const std::string& dbname,const std::string& host = DEFAULT_HOST, ushort port = DEFAULT_PORT,const std::string& encoding = DEFAULT_ENCODING );~CMysqlConnectionPool();boolinitConnections( ushort num = DEFAULT_CONN_NUM );CMysqlConnection*getConnection();voidputConnection( CMysqlConnection* connection );protected:booladdConnection();voidclearConnections();private:std::string_username;std::string_password;std::string_dbname;std::string_host;std::string_encoding;ushort_port;ushort_connection_num;std::vector<CMysqlConnection*>_connections;CRITICAL_SECTION_cs;HANDLE_semaphore;};#endif // __mysql_connection_pool_h__

mysql_connection.h

#ifndef __mysql_connection_h__#define __mysql_connection_h__#ifndefDLLEXPORT#defineDLLAPI__declspec(dllimport)#else#defineDLLAPI__declspec(dllexport)#endif#define DLLCLSDLLAPI#include "mysql_cpp.h"class CMysqlRecordSet;class DLLCLS CMysqlConnection{friend class CMysqlConnectionPool;public:virtual ~CMysqlConnection();CMysqlRecordSet*executeQuery( const std::string& sql);boolexecuteNonQuery( const std::string& sql );interrorNum() const;const char*errorString() const;protected:CMysqlConnection( MYSQL *mysql );private:CMysqlConnection( const CMysqlConnection& );CMysqlConnection& operator = ( const CMysqlConnection& );MYSQL*_mysql;};#endif // __mysql_connection_h__

mysql_record_set.h

#ifndef __mysql_record_set_h__#define __mysql_record_set_h__#ifndefDLLEXPORT#defineDLLAPI__declspec(dllimport)#else#defineDLLAPI__declspec(dllexport)#endif#define DLLCLSDLLAPI#include "mysql_cpp.h"class CMysqlRecord;class DLLCLS CMysqlRecordSet{friend class CMysqlConnection;public:~CMysqlRecordSet();ulonggetRecordsNum() const { return _row_num; }ulonggetFieldsNum() const { return _field_num; }const char*getFieldName( ulong idx ) const {if( idx < _field_num ) return _fields[idx].c_str(); elsereturn NULL;}CMysqlRecord*getRecord( ulong idx ) const {if( idx < _row_num )return _records[idx];elsereturn NULL;}protected:CMysqlRecordSet();bool initRecordSet( MYSQL_RES *res );private:ulong_field_num;ulong_row_num;std::vector<std::string>_fields;std::vector<CMysqlRecord*>_records;};#endif // __mysql_record_set_h__

mysql_record.h

#ifndef __mysql_record_h__#define __mysql_record_h__#ifndefDLLEXPORT#defineDLLAPI__declspec(dllimport)#else#defineDLLAPI__declspec(dllexport)#endif#define DLLCLSDLLAPI#include "mysql_cpp.h"class DLLCLS CMysqlRecord{friend class CMysqlRecordSet;public:~CMysqlRecord();const char*getFieldValue( uint index ) const {if( index < _values.size() )return _values[index].c_str();elsereturn NULL;}protected:CMysqlRecord();bool initRecord( MYSQL_ROW row, ulong fieldnum );private:std::vector<std::string>_values;};#endif // __mysql_record_h__

mysql_connection_pool.cpp

#define DLLEXPORT#include "../include/mysql_connection_pool.h"#include "../include/mysql_connection.h"CMysqlConnectionPool::CMysqlConnectionPool( const std::string& username, const std::string& password, const std::string& dbname, const std::string& host /* = DEFAULT_HOST */, ushort port /* = DEFAULT_PORT */,const std::string& encoding /* = DEFAULT_ENCODING */ ): _username( username ), _password( password ), _dbname( dbname ), _host( host ), _port( port ), _encoding( encoding ), _connection_num( 0 ), _semaphore( NULL ){InitializeCriticalSection( &_cs );}CMysqlConnectionPool::~CMysqlConnectionPool(){clearConnections();DeleteCriticalSection( &_cs );if( NULL != _semaphore ) {CloseHandle( _semaphore );_semaphore = NULL;}}bool CMysqlConnectionPool::initConnections( ushort num /* = DEFAULT_CONN_NUM */ ){if( 0 == num )return false;_semaphore = CreateSemaphore( NULL, num, num, NULL );if( NULL == _semaphore )return false;for( int i = 0; i < num; i++ ) {if( !addConnection() ) {clearConnections();return false;}}_connection_num = num;return true;}CMysqlConnection* CMysqlConnectionPool::getConnection(){if( _connections.empty() )return NULL;CMysqlConnection *ret = NULL;if( WAIT_OBJECT_0 == WaitForSingleObject(_semaphore, DEFAULT_TIMEOUT) ) {EnterCriticalSection( &_cs );ret = _connections.front();_connections.erase( _connections.begin() );LeaveCriticalSection( &_cs );}return ret;}void CMysqlConnectionPool::putConnection( CMysqlConnection* connection ){if( NULL == connection )return;EnterCriticalSection( &_cs );_connections.push_back( connection );LeaveCriticalSection( &_cs );ReleaseSemaphore( _semaphore, 1, NULL );}bool CMysqlConnectionPool::addConnection(){MYSQL *mysql = mysql_init( NULL );if( NULL == mysql )return false;my_bool reconn = 1;if( mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconn) ) {mysql_close( mysql );return false;}if( !mysql_real_connect(mysql, _host.c_str(), _username.c_str(), _password.c_str(), _dbname.c_str(), _port,NULL, 0) ) {mysql_close( mysql );return false;}if( mysql_set_character_set(mysql, _encoding.c_str()) ) {mysql_close( mysql );return false;}CMysqlConnection *connection = new CMysqlConnection( mysql );if( NULL == connection )return false;EnterCriticalSection( &_cs );_connections.push_back( connection );LeaveCriticalSection( &_cs );return true;}void CMysqlConnectionPool::clearConnections(){EnterCriticalSection( &_cs );std::vector<CMysqlConnection*>::iterator iter = _connections.begin();while( iter != _connections.end() ) {delete (*iter);iter++;}_connections.clear();LeaveCriticalSection( &_cs );}

mysql_connection.cpp

#define DLLEXPORT#include "../include/mysql_connection.h"#include "../include/mysql_record_set.h"CMysqlConnection::CMysqlConnection( MYSQL *mysql ): _mysql( mysql ){}CMysqlConnection::~CMysqlConnection(){if( NULL != _mysql ) {mysql_close( _mysql );_mysql = NULL;}}CMysqlRecordSet* CMysqlConnection::executeQuery( const std::string& sql ){if( NULL == _mysql || sql.empty() )return NULL;if( 0 != mysql_real_query(_mysql, sql.c_str(), sql.size()) )return NULL;MYSQL_RES* res = mysql_store_result( _mysql );if( NULL == res )return NULL;CMysqlRecordSet *p = new CMysqlRecordSet;if( p->initRecordSet(res) )return p;else {delete p;return NULL;}}bool CMysqlConnection::executeNonQuery( const std::string& sql ){if( NULL == _mysql || sql.empty() )return false;if( 0 == mysql_real_query(_mysql, sql.c_str(), sql.size()) )return true;elsereturn false;}int CMysqlConnection::errorNum() const{if( NULL == _mysql )return -1;elsereturn mysql_errno( _mysql );}const char* CMysqlConnection::errorString() const{if( NULL == _mysql )return NULL;elsereturn mysql_error( _mysql );}

mysql_record_set.cpp

#define DLLEXPORT#include "../include/mysql_record_set.h"#include "../include/mysql_record.h"CMysqlRecordSet::CMysqlRecordSet(): _field_num( 0 ), _row_num( 0 ){}CMysqlRecordSet::~CMysqlRecordSet(){_field_num= 0;_row_num= 0;_fields.clear();std::vector<CMysqlRecord*>::iterator iter = _records.begin();while( iter != _records.end() ) {delete (*iter);iter++;}_records.clear();}bool CMysqlRecordSet::initRecordSet( MYSQL_RES *res ){if( NULL == res )return false;MYSQL_FIELD*field = NULL;while( NULL != (field = mysql_fetch_field(res)) ) {_fields.push_back( field->name );_field_num++;}MYSQL_ROW row = NULL;while( NULL != (row = mysql_fetch_row(res)) ) {CMysqlRecord *record = new CMysqlRecord;if( record->initRecord(row, _field_num) ) {_records.push_back( record );_row_num++;}elsedelete record;}mysql_free_result( res );return true;}

mysql_record.cpp

#define DLLEXPORT#include "../include/mysql_record.h"CMysqlRecord::CMysqlRecord(){}CMysqlRecord::~CMysqlRecord(){_values.clear();}bool CMysqlRecord::initRecord( MYSQL_ROW row, ulong fieldnum ){if( NULL == row )return false;ulong i = 0;while( i < fieldnum ) {_values.push_back( row[i] );i++;}return true;}

测试程序：

#include "../include/mysql_connection_pool.h"#include "../include/mysql_connection.h"#include "../include/mysql_record_set.h"#include "../include/mysql_record.h"#pragma comment( lib, "../bin/mysql_cpp.lib" )void executeQuery( CMysqlConnection *connection, const char *sql ){int records_num= 0;int fields_num= 0;printf( "%s\n", sql );CMysqlRecordSet *recordset = connection->executeQuery( sql );if( NULL != recordset ) {records_num = recordset->getRecordsNum();fields_num= recordset->getFieldsNum();printf( "records num = %d\n", records_num );printf( "fields num = %d\n", fields_num );for( int i = 0; i < fields_num; i++ )printf( "field %d is %s\n", i+1, recordset->getFieldName(i) );for( int i = 0; i < records_num; i++ ) {printf( "record %d: ", i+1 );CMysqlRecord *record = recordset->getRecord( i );for( int j = 0; j < fields_num; j++ )printf( "(%s)", record->getFieldValue(j) );printf( "\n" );}delete recordset;}printf( "\n\n" );}int main( void ){const char *sql1 = "SELECT * FROM table1";const char *sql2 = "SELECT * FROM table2 WHERE id>'5'";const char *sql3 = "SELECT name,timestamp FROM table3 WHERE timestamp>'2011-10-19 08:45:47'";const char *sql4 = "INSERT INTO table2(name) VALUES('testname1'),('testname2')";const char *sql5 = "INSERT INTO table3(name) VALUES('nametest1'),('nametest2')";CMysqlConnectionPool *pool = new CMysqlConnectionPool( "root", "123456", "testdb1" );if( NULL == pool )return 1;pool->initConnections();CMysqlConnection *connection = pool->getConnection();if( NULL == connection )return NULL;executeQuery( connection, sql1 );executeQuery( connection, sql2 );executeQuery( connection, sql3 );if( connection->executeNonQuery(sql4) )printf( "%s\n\n\n", sql4 );if( connection->executeNonQuery(sql5) )printf( "%s\n\n\n", sql5 );executeQuery( connection, sql2 );executeQuery( connection, sql3 );pool->putConnection( connection );delete pool;return 0;}/*SELECT * FROM table1records num = 8fields num = 2field 1 is idfield 2 is namerecord 1: (1)(haha)record 2: (2)(hehe)record 3: (3)(haha)record 4: (4)(hehe)record 5: (5)(haha)record 6: (6)(hehe)record 7: (7)(haha)record 8: (8)(hehe)SELECT * FROM table2 WHERE id>'5'records num = 8fields num = 3field 1 is idfield 2 is namefield 3 is timestamprecord 1: (6)(hehe)(2011-10-18 13:32:12)record 2: (7)(haha)(2011-10-18 13:32:13)record 3: (8)(hehe)(2011-10-18 13:32:13)record 4: (9)(haha)(2011-10-27 09:41:37)record 5: (10)(testname1)(2011-11-18 15:21:18)record 6: (11)(testname2)(2011-11-18 15:21:18)record 7: (12)(testname1)(2011-11-18 15:21:57)record 8: (13)(testname2)(2011-11-18 15:21:57)SELECT name,timestamp FROM table3 WHERE timestamp>'2011-10-19 08:45:47'records num = 4fields num = 2field 1 is namefield 2 is timestamprecord 1: (nametest1)(2011-11-18 15:21:18)record 2: (nametest2)(2011-11-18 15:21:18)record 3: (nametest1)(2011-11-18 15:21:57)record 4: (nametest2)(2011-11-18 15:21:57)INSERT INTO table2(name) VALUES('testname1'),('testname2')INSERT INTO table3(name) VALUES('nametest1'),('nametest2')SELECT * FROM table2 WHERE id>'5'records num = 10fields num = 3field 1 is idfield 2 is namefield 3 is timestamprecord 1: (6)(hehe)(2011-10-18 13:32:12)record 2: (7)(haha)(2011-10-18 13:32:13)record 3: (8)(hehe)(2011-10-18 13:32:13)record 4: (9)(haha)(2011-10-27 09:41:37)record 5: (10)(testname1)(2011-11-18 15:21:18)record 6: (11)(testname2)(2011-11-18 15:21:18)record 7: (12)(testname1)(2011-11-18 15:21:57)record 8: (13)(testname2)(2011-11-18 15:21:57)record 9: (14)(testname1)(2011-11-18 15:22:45)record 10: (15)(testname2)(2011-11-18 15:22:45)SELECT name,timestamp FROM table3 WHERE timestamp>'2011-10-19 08:45:47'records num = 6fields num = 2field 1 is namefield 2 is timestamprecord 1: (nametest1)(2011-11-18 15:21:18)record 2: (nametest2)(2011-11-18 15:21:18)record 3: (nametest1)(2011-11-18 15:21:57)record 4: (nametest2)(2011-11-18 15:21:57)record 5: (nametest1)(2011-11-18 15:22:45)record 6: (nametest2)(2011-11-18 15:22:45)请按任意键继续. . .*/