c++ 基于Mysql原生库二次封装的轻量级库

一 、前言

       在C++项目中经常要使用Mysql数据库，在QT项目中使用Mysql还需要 Mysql驱动，但是在QT4.7,4.8版本中，mysql驱动需要自己编译，在编译过程中会遇到很多问题，需要花很多时间来解决，所以我们要想别的办法；一是，不直接使用mysql驱动来操作mysql数据库，可以使用odbc的方式来操作数据库，并且使用ODBC操作数据之前需要配置ODBC源；二是，可以绕开QT的数据库框架，直接使用mysql原生的库来操作mysql；所以我们基于原生的库进行了二次封装。

二、源代码

头文件.h

#ifndef DATABASE_MYSQL_H
#define DATABASE_MYSQL_H
#include <string>
#include <vector>

#include "database_mysql_global.h"

class database_mysql_t;
//批量插入数据
class DATABASE_MYSQL_EXPORT batch_insert_adapter_t
{
public:
	batch_insert_adapter_t(database_mysql_t *database, int buffer_size = 1000000, int flush_row = 50000);
	~batch_insert_adapter_t();
	void begin_add_field(const char * csz_table);                       //开始插入字段
	void add_field(const char *csz_field);         //添加字段 
	void end_add_field();                          //结束插入字段
	void begin_add_row();                          //开始插入数值
	void add_value(char value);
	void add_value(int value);
	void add_value(unsigned int value);	
	void add_value(long value);
	void add_value(unsigned long value);
	void add_value(float value);
	void add_value(double value);
	void add_value(std::string value);
	void add_value(const char * value);
	void add_value(long long value);
	void add_value(unsigned long long value);
	void add_value(void * value, int n_length);
	bool end_add_row();                           //结束添加值
	bool flush();                                 //将数据插入数据库
	int get_sum_row();                            //获取总的行数
private:
	std::string m_str_table;                      //表格名称
	std::vector<std::string> m_v_field_names;     //需要插入的字段名称	
	int  m_n_field;                               //字段个数
	int  m_n_field_added;                         //已经添加的字段数
	database_mysql_t *m_database;
	int  m_buffer_size;
	int  m_flush_row;
	char *m_data_buffer;       //数据缓存
	char * m_p_data;                              //数据缓存当前的位置
	char * m_p_data_start;                        //数据开始位置   
	char * m_p_row_start;                         //行的起始地址
	int    m_n_row_num;                           //行数
	int    m_n_row_max_length;                    //行长度
	int    m_n_sum_row;        
};
//MYSQL返回的行定义
typedef  char** row_t;    
//返回结果集
class DATABASE_MYSQL_EXPORT res_t     //为select的返回结果
{
	friend class database_mysql_t;
public:
	int  row_num();                 //获取结果集的行数
	int  field_num();               //获取字段个数
	unsigned long * field_lengths();//获取当前行的各个字段长度	
	row_t get_next_row();           //获取下一行数据
	row_t get_row(int n_offset);    //获取指定偏移的数据
	row_t operator[](int n_offset); //获取第几行
	inline void erase();            //清空本结果集

	res_t();	
	res_t(void *res);
	res_t(res_t& res);              //拷贝构造函数
	res_t& operator=(res_t& res);   //赋值
	operator bool(void);            //判断结果集是否返回成功
	~res_t();
private:	
	void * m_data;             //数据
};
//数据库适配类
class DATABASE_MYSQL_EXPORT database_mysql_t
{
public:
	database_mysql_t(void);
	virtual ~database_mysql_t(void);
	bool connet(const char *csz_host, const char *csz_user, const char *csz_password, const char *csz_db=0, const unsigned int un_port = 3306);   //连接数据库
	void dis_connect();                                    //断开连接
	bool use_db(const char *csz_db);                       //切换数据库
	bool set_char_set(const char * csz_char_set);          //设置字符集

	bool has_db(const char * csz_db);                      //数据库是否存在
	bool create_db(const char * csz_db);                   //创建数据库，输入为数据库的名称
	bool drop_db(const char * csz_db);                     //删除数据库，输入为数据库的名称
	bool has_table(const char * csz_table);                //该表格是否存在
	bool create_table(const char *csz_sql);                //创建表，输入为创建表的sql语句
	bool drop_table(const char * csz_talbe);               //删除表，输入为表的名称

	res_t select(const char * csz_sql);                    //查询
	int  insert(const char * csz_sql, int n_length = 0);   //插入
	int  update(const char * csz_sql, int n_length = 0);   //更新
	int  remove(const char * csz_sql);                     //删除
	int  get_affected_rows();                              //获取受到影响的行
	bool autocommit(bool bflag);                           //不允许自动事务处理
	bool commit();                                         //执行事务
	bool rollback();                                       //事务回滚
	bool query(const char * csz_data, int n_length = 0);   //查询,返回受影响的行数

	const char * get_error();                              //返回数据库操作的错误信息
	unsigned int get_errno();                              //返回数据库操作错误代码 非错误返回0
private:
	void * m_mysql;                //mysql连接句柄
};

#endif // DATABASE_MYSQL_H

源文件.cpp

#include "stdafx.h"
#include "database_mysql.h"

#define __LCC__
#include <mysql.h>

//批量插入适配器
batch_insert_adapter_t::batch_insert_adapter_t( database_mysql_t *database, int buffer_size/* = 1000000*/, int flush_row/* = 50000*/)
	: m_database(database), m_n_field(0), m_n_field_added(0), m_p_data(0), m_p_data_start(0),m_n_row_num(0), m_n_row_max_length(0),m_p_row_start(0),m_n_sum_row(0),m_buffer_size(buffer_size),m_flush_row(flush_row)
{
	m_data_buffer = new char[buffer_size];
	memset(m_data_buffer, 0, m_buffer_size);
}

batch_insert_adapter_t::~batch_insert_adapter_t()
{
	delete[] m_data_buffer;
}

void batch_insert_adapter_t::begin_add_field(const char * csz_table)
{
	m_str_table = csz_table;
	m_v_field_names.clear();      //将以往的字段名删除
	m_p_data = m_data_buffer;
	m_n_field = 0;
	m_n_field_added = 0;
	m_p_data_start = 0;
	m_n_row_num = 0;
	m_n_row_max_length = 0;
	m_n_sum_row = 0;
}

void batch_insert_adapter_t::add_field( const char *csz_field )
{
	m_v_field_names.push_back(csz_field);
}

void batch_insert_adapter_t::end_add_field()
{
	int pos = sprintf(m_p_data, "insert into %s(", m_str_table.c_str()); 
	m_p_data += pos;
	for (std::vector<std::string>::iterator iter = m_v_field_names.begin(), iter_end = m_v_field_names.end(); iter != iter_end; ++iter)
	{
		pos = sprintf(m_p_data, "%s,", (*iter).c_str());
		m_p_data += pos;
	}
	--m_p_data;
	pos = sprintf(m_p_data, ") values ");
	m_p_data += pos;
	m_p_data_start = m_p_data;           //记录数据开始位置
	m_n_field = (int)m_v_field_names.size();  //获取字段个数
}

void batch_insert_adapter_t::begin_add_row()
{
	m_p_row_start = m_p_data;                      //行的起始地址
	m_n_field_added = 0;
	int pos = sprintf(m_p_data, "(");
	m_p_data += pos;
}

void batch_insert_adapter_t::add_value(char value )
{
	int pos = sprintf(m_p_data, "\'%c\',", value);
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(int value )
{
	int pos = sprintf(m_p_data, "\'%d\',", value);
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(unsigned int value )
{
	int pos = sprintf(m_p_data, "\'%u\',", value);
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(long value )
{
	int pos = sprintf(m_p_data, "\'%d\',", value);
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(unsigned long value )
{
	int pos = sprintf(m_p_data, "\'%u\',", value);
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(float value )
{
	int pos = sprintf(m_p_data, "\'%f\',", value);
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(double value )
{
	int pos = sprintf(m_p_data, "\'%f\',", value);
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(std::string value )
{
	int pos = sprintf(m_p_data, "\'%s\',", value.c_str());
	m_p_data += pos;
	++m_n_field_added;
}

void batch_insert_adapter_t::add_value(const char * value )
{
	int pos = sprintf(m_p_data, "\'%s\',", value);
	m_p_data += pos;
	++m_n_field_added;
}
void batch_insert_adapter_t::add_value(long long value)
{
	int pos = sprintf(m_p_data, "\'%lld\',", value);
	m_p_data += pos;
	++m_n_field_added;
}
void batch_insert_adapter_t::add_value(unsigned long long value)
{
	int pos = sprintf(m_p_data, "\'%llu\',", value);
	m_p_data += pos;
	++m_n_field_added;
}
void batch_insert_adapter_t::add_value(void * value, int n_length )
{
	*m_p_data = '\'';      //用单引号将要插入的值引住
	++m_p_data;
	memcpy(m_p_data, value, n_length);
	m_p_data += n_length;
	*m_p_data = '\'';
	++m_p_data;
	*m_p_data = ',';          //在字段后面添加','
	++m_p_data;
	++m_n_field_added;
}

bool batch_insert_adapter_t::end_add_row()
{
	if (m_n_field_added != m_n_field)
	{
		//log_t::instance().log("字段值添加错误");
		return false;
	}
	*(m_p_data-1) = ')';             //最后一个字段值取出','添加')'
	*m_p_data = ',';                 //添加行间隔','
	++m_p_data;
	++m_n_row_num;                   //行数
	int n_row_length = (int)(m_p_data - m_p_row_start);
	m_n_row_max_length = (m_n_row_max_length > n_row_length) ? m_n_row_max_length : n_row_length;
	if (m_n_row_num > m_flush_row || m_p_data - m_data_buffer + m_n_row_max_length + 100 > m_buffer_size)
	{
		return flush();
	}
	else
		return true;
}

bool batch_insert_adapter_t::flush()
{
	if (m_n_row_num != 0)
	{	
		*(m_p_data-1) = '\0';
		int n_length = (int)(m_p_data - m_data_buffer);
		m_p_data = m_p_data_start;

		if (!m_database->query(m_data_buffer, n_length))
		{
			const char *p = m_database->get_error();
			return false;
		}
		int num = m_database->get_affected_rows();
		if (num != m_n_row_num)
		{
			return false;
		}
		m_n_sum_row += num;
		m_n_row_num = 0;
	}
	return true;
}
int batch_insert_adapter_t::get_sum_row()                            //获取总的行数
{
	return m_n_sum_row;
}

//数据库接口
database_mysql_t::database_mysql_t(void) : m_mysql(0)
{
}


database_mysql_t::~database_mysql_t(void)
{
	if (m_mysql != 0)
	{
		dis_connect();
	}
}

bool database_mysql_t::connet( const char *csz_host, const char *csz_user, const char *csz_password, const char *csz_db, const unsigned int un_port /*= 3306*/ )
{
	m_mysql = mysql_init((MYSQL *)m_mysql);
	if (m_mysql == 0)
	{
		return false;
	}
	if (mysql_real_connect((MYSQL *)m_mysql, csz_host, csz_user, csz_password, csz_db, un_port, 0, 0)==0)
	{
		return false;
	}
	return true;
}

void database_mysql_t::dis_connect()
{
	mysql_close((MYSQL *)m_mysql);
	m_mysql = 0;
}

bool database_mysql_t::use_db( const char *csz_db )
{
	char sz_temp[50] = {0};
	sprintf(sz_temp, "use %s", csz_db);
	return query(sz_temp);
}

bool database_mysql_t::set_char_set( const char * csz_char_set )
{
	char sz_temp[50] = {0};
	sprintf(sz_temp, "set names %s", csz_char_set);
	return query(sz_temp);
}

res_t database_mysql_t::select( const char * csz_sql )
{
	if (!query(csz_sql))
	{
		return 0;
	}
	res_t res(mysql_store_result((MYSQL *)m_mysql));
	return res;
}

int database_mysql_t::insert( const char * csz_sql, int n_length /*= 0*/ )
{
	if (!query(csz_sql, n_length))
	{
		return -1;
	}
	return get_affected_rows();
}

int database_mysql_t::update( const char * csz_sql, int n_length /*= 0*/ )
{
	if (!query(csz_sql, n_length))
	{
		return -1;
	}
	return get_affected_rows();
}

int database_mysql_t::remove( const char * csz_sql )
{
	if (!query(csz_sql))
	{
		return -1;
	}
	return get_affected_rows();
}

bool database_mysql_t::query( const char * csz_data, int n_length /*= 0*/ )
{
	if (n_length == 0)
	{
		n_length = (int)strlen(csz_data);
	}
	return !(mysql_real_query((MYSQL *)m_mysql, csz_data, n_length));        // 查询成功返回0
}

int database_mysql_t::get_affected_rows()
{
	return static_cast<int>(mysql_affected_rows((MYSQL *)m_mysql));
}

bool database_mysql_t::autocommit( bool bflag )
{
	return !(mysql_autocommit((MYSQL *)m_mysql, bflag));
}

bool database_mysql_t::commit()
{
	return !(mysql_commit((MYSQL *)m_mysql));
}

bool database_mysql_t::rollback()
{
	return !(mysql_rollback((MYSQL *)m_mysql));
}
//创建数据库
bool database_mysql_t::create_db( const char * csz_db )
{
	char sz_temp[50] = {0};
	int n_len = sprintf(sz_temp, "create database %s", csz_db);
	return query(sz_temp, n_len);
}

bool database_mysql_t::drop_db( const char * csz_db )
{
	char sz_temp[50] = {0};
	int n_len = sprintf(sz_temp, "drop database %s", csz_db);
	return query(sz_temp, n_len);
}

bool database_mysql_t::create_table( const char *csz_sql )
{
	return query(csz_sql);
}

bool database_mysql_t::drop_table( const char * csz_talbe )
{
	char sz_temp[50] = {0};
	int n_len = sprintf(sz_temp, "drop table %s", csz_talbe);
	return query(sz_temp, n_len);
}

const char * database_mysql_t::get_error()
{
	return mysql_error((MYSQL *)m_mysql);
}

unsigned int database_mysql_t::get_errno()
{
	return mysql_errno((MYSQL *)m_mysql);
}

bool database_mysql_t::has_db( const char * csz_db )
{
	res_t res = select("show databases");
	if (!res)
	{
		return false;
	}
	else
	{
		row_t row = 0;
		int num = res.row_num();
		for (int i=0; i<num; ++i)
		{
			row = res.get_next_row();
			if (!strcmp(csz_db, row[0]))
			{				
				return true;
			}
		}
		return false;
	}
}

bool database_mysql_t::has_table( const char * csz_table )
{
	res_t res = select("show tables");
	if (!res)
	{
		return false;
	}
	else
	{
		row_t row = 0;
		int num = res.row_num();
		for (int i=0; i<num; ++i)
		{
			row = res.get_next_row();
			if (!strcmp(csz_table, row[0]))
			{				
				return true;
			}
		}
		return false;
	}
}
//查询结果集
res_t::res_t() : m_data(0)
{

}

res_t::res_t( res_t& res )
{
	m_data = res.m_data;
	res.m_data = 0;
}

res_t::res_t( void *res )
{
	m_data = res;
}

res_t::~res_t()
{
	erase();
}

res_t& res_t::operator=( res_t& res)
{
	erase();
	m_data = res.m_data;
	res.m_data = 0;
	return *this;
}

int res_t::row_num()
{
	return static_cast<int>(mysql_num_rows((MYSQL_RES *)m_data));
}

int res_t::field_num()
{
	return static_cast<int>(mysql_num_fields((MYSQL_RES *)m_data));
}

unsigned long * res_t::field_lengths()
{
	return mysql_fetch_lengths((MYSQL_RES *)m_data);
}

row_t res_t::get_next_row()
{
	return mysql_fetch_row((MYSQL_RES *)m_data);
}

row_t res_t::get_row( int n_offset )
{
	mysql_data_seek((MYSQL_RES *)m_data, n_offset);
	return get_next_row();
}

row_t res_t::operator[]( int n_offset )
{
	return get_row(n_offset);
}

res_t::operator bool( void )
{
	return m_data ? true : false;
}

void res_t::erase()
{
	if (m_data)
	{
		mysql_free_result((MYSQL_RES *)m_data);
		m_data = 0;
	}
}

三、使用示例

// main.cpp : 定义控制台应用程序的入口点。

#include "stdafx.h"
#include <iostream>
#include <database_mysql.h>

int _tmain(int argc, _TCHAR* argv[])
{
	
    database_mysql_t m_MysqlDb;
    if (!m_MysqlDb.connet("127.0.0.1", "root", "123456", "epidemic_deduction"))
    {	
	    return 0;
    }
    m_MysqlDb.set_char_set("utf8");

	std::ostringstream sql;
	sql << "insert into frame_list(id,num) values(\""
		<< 1000001<<"\",\"" <<10<<"\")";
	if (m_MysqlDb.insert(sql.str().c_str())==-1)
	{
        std::cout << database.get_error();
	}

    m_MysqlDb.dis_connect();

	return 0;
}

批量插入示例

void BatchWriteExpData(std::vector<std::string> expDatas)
{
	std::cout << "start batch insert expdata[" << expDatas.size() << "]" << std::endl;
	batch_insert_adapter_t* adapter = new batch_insert_adapter_t(&m_MysqlDb);
	adapter->begin_add_field("exp_data_list");    //插入信息
	adapter->add_field("id");
	adapter->add_field("step_id");
	adapter->add_field("exp_id");
	adapter->add_field("src_id");
	adapter->add_field("tgt_id");
	adapter->add_field("behaior_type");
	adapter->add_field("time");
	adapter->end_add_field();

	for (std::vector<std::string>::iterator it = expDatas.begin(); it != expDatas.end(); ++it)
	{
		std::vector<std::string> dataList = vStringSplit(*it);
		adapter->begin_add_row();
		adapter->add_value(dataList.at(0));  
		adapter->add_value(dataList.at(1)); 
		adapter->add_value(dataList.at(2)); 
		adapter->add_value(dataList.at(3)); 
		adapter->add_value(dataList.at(4)); 
		adapter->add_value(dataList.at(5)); 
		adapter->add_value(dataList.at(6)); 
		adapter->end_add_row();
	}

	adapter->flush();
	delete adapter;
	std::cout << "Batch insert success" << std::endl;
}