mysql的一个在windows下的封装类,做成了dll,用着可能不是很顺手,以后有时间再修改。
mysql_cpp.h
#ifndef __mysql_cpp_h__
#define __mysql_cpp_h__
typedef unsigned char uchar;
typedef unsigned short ushort;
typedef unsigned int uint;
typedef unsigned long ulong;
#define DEFAULT_HOST "127.0.0.1"
#define DEFAULT_ENCODING "utf8"
#define DEFAULT_PORT 3306
#define DEFAULT_CONN_NUM 5
#define DEFAULT_TIMEOUT 3000
#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__
#ifndef DLLEXPORT
#define DLLAPI __declspec(dllimport)
#else
#define DLLAPI __declspec(dllexport)
#endif
#define DLLCLS DLLAPI
#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();
bool initConnections ( ushort num = DEFAULT_CONN_NUM );
CMysqlConnection* getConnection ();
void putConnection ( CMysqlConnection* connection );
protected:
bool addConnection ();
void clearConnections();
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__
#ifndef DLLEXPORT
#define DLLAPI __declspec(dllimport)
#else
#define DLLAPI __declspec(dllexport)
#endif
#define DLLCLS DLLAPI
#include "mysql_cpp.h"
class CMysqlRecordSet;
class DLLCLS CMysqlConnection
{
friend class CMysqlConnectionPool;
public:
virtual ~CMysqlConnection();
CMysqlRecordSet* executeQuery ( const std::string& sql);
bool executeNonQuery ( const std::string& sql );
int errorNum () 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__
#ifndef DLLEXPORT
#define DLLAPI __declspec(dllimport)
#else
#define DLLAPI __declspec(dllexport)
#endif
#define DLLCLS DLLAPI
#include "mysql_cpp.h"
class CMysqlRecord;
class DLLCLS CMysqlRecordSet
{
friend class CMysqlConnection;
public:
~CMysqlRecordSet();
ulong getRecordsNum () const { return _row_num; }
ulong getFieldsNum () const { return _field_num; }
const char* getFieldName ( ulong idx ) const {
if( idx < _field_num )
return _fields[idx].c_str();
else
return NULL;
}
CMysqlRecord* getRecord ( ulong idx ) const {
if( idx < _row_num )
return _records[idx];
else
return 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__
#ifndef DLLEXPORT
#define DLLAPI __declspec(dllimport)
#else
#define DLLAPI __declspec(dllexport)
#endif
#define DLLCLS DLLAPI
#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();
else
return 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;
else
return false;
}
int CMysqlConnection::errorNum() const
{
if( NULL == _mysql )
return -1;
else
return mysql_errno( _mysql );
}
const char* CMysqlConnection::errorString() const
{
if( NULL == _mysql )
return NULL;
else
return 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++;
}
else
delete 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 table1
records num = 8
fields num = 2
field 1 is id
field 2 is name
record 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 = 8
fields num = 3
field 1 is id
field 2 is name
field 3 is timestamp
record 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 = 4
fields num = 2
field 1 is name
field 2 is timestamp
record 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 = 10
fields num = 3
field 1 is id
field 2 is name
field 3 is timestamp
record 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 = 6
fields num = 2
field 1 is name
field 2 is timestamp
record 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)
请按任意键继续. . .
*/