C++连接Mysql数据库的几种方式

描述

mysql数据库是一种关系型数据库， 近年来的用户迅速增加，下面介绍几种C++连接mysql的方式

• 通过Mysql原生lib连接， 为方便使用， 通过原生接口再次封装

.h

#ifndef MYSQLDATABASE_MYSQLDATABASE_H_
#define MYSQLDATABASE_MYSQLDATABASE_H_

#include <string>
#include <vector>
#include <list>
#include <thread>
#include <mutex>
#include "mysql.h"

class MysqlDataBase
{
public:
	MysqlDataBase();
	void SetConfigInfo(std::string db_ip, int db_port, std::string db_name, std::string db_user, std::string db_password);
	bool ConnectDataBase();
	void CloseDataBase();
	bool OperationDataBase(std::vector<std::string>operations);
	bool OperationDataBase(std::vector<std::string>operations,int& index); //用于初始化时插入数据库失败返回错误语句
	MYSQL_RES *SelectDataBase(std::string select);
	bool FreeSelect(MYSQL_RES *result);
	int GetSqlOptSize();
private:
	bool OperationDataBase(std::string operation);
	void OperationDataBaseLoop();
private:
	MYSQL * con;
	bool connect_status_;
	std::string db_ip_;
	int db_port_;
	std::string db_name_;
	std::string db_user_;
	std::string db_password_;


	std::mutex mutex_opt_;
	std::list<std::string>opt_sql_;
	int sql_size_=0;
};

#endif

.cpp

#include "MysqlOperation.h"
#include "logger.h"
#include <time.h>
#include <iostream>
MysqlDataBase::MysqlDataBase()
{
	connect_status_ = false;
	con = mysql_init((MYSQL*)0);
}
void MysqlDataBase::SetConfigInfo(std::string db_ip, int db_port, std::string db_name, std::string db_user, std::string db_password)
{
	db_ip_ = db_ip;
	db_port_ = db_port;
	db_name_ = db_name;
	db_user_ = db_user;
	db_password_ = db_password;
}

bool MysqlDataBase::ConnectDataBase()
{
	if (connect_status_ == true){
		return true;
	}

	if (con != NULL && mysql_real_connect(con, db_ip_.c_str(), db_user_.c_str(),
		db_password_.c_str(), db_name_.c_str(), (unsigned)db_port_/*3306 TCP IP端口*/, NULL/*Unix Socket 连接类型*/, CLIENT_MULTI_STATEMENTS/*运行成ODBC数据库标志*/))
	{
		if (!mysql_select_db(con, db_name_.c_str()))
		{
			//printf("Select successfully the database!\n");
			char reconnect = 1;
			mysql_options(con, MYSQL_OPT_RECONNECT, (char *)&reconnect);
			std::string query = "set names \'GBK\'";
			int rt = mysql_query(con, query.c_str());
			if (rt)
			{
				printf("Error making query: %s !!!\n", mysql_error(con));
			}
		}
		std::thread th(std::bind(&MysqlDataBase::OperationDataBaseLoop,this));
		th.detach();

		connect_status_ = true;
	}
	else
	{
		connect_status_ = false;
	}
	return connect_status_;
}

void MysqlDataBase::CloseDataBase()
{
	if (connect_status_ == false){
		return;
	}
	mysql_close(con);
}
void MysqlDataBase::OperationDataBaseLoop()
{
	int i = 0;
	while (true)
	{
		{
			std::lock_guard<decltype(mutex_opt_)> lock(mutex_opt_);
			//new add by liu
			//output_debug_information("mysqlping", base::__kInfo) << "start ping";
			if (mysql_ping(con) == 0)
			{
				//output_debug_information("mysqlping", base::__kInfo) << "end ping with ok";
				//output_debug_information("mysqlping", base::__kInfo) << "opt_sql_.size:"<< opt_sql_.size();
				std::list<std::string>::iterator iter;
				for (iter = opt_sql_.begin(); iter != opt_sql_.end();)
				{
					if (OperationDataBase(*iter) == true)
					{
						iter = opt_sql_.erase(iter);
						output_debug_information("mysqlping", base::__kInfo) << "sql_excueed success,then opt_sql_.size:" << opt_sql_.size();
					}
					else
					{
						iter++;
						output_debug_information("mysqlping", base::__kInfo) << "sql_excueed failed ,then opt_sql_.size:" << opt_sql_.size();
					}
				}
			}
			else
			{
				output_debug_information("mysqlping", base::__kError) << "end ping with error";
			}	
		}
		Sleep(100);
	}
}
bool MysqlDataBase::OperationDataBase(std::vector<std::string>operations)
{
	std::lock_guard<decltype(mutex_opt_)> lock(mutex_opt_);
	for (int i = 0; i < operations.size(); i++){
		opt_sql_.push_back(operations[i]);
		output_debug_information("mysqlping", base::__kInfo) << "add sql opt_sql_.size1:" << opt_sql_.size();
	}
	return true;
}

bool MysqlDataBase::OperationDataBase(std::vector<std::string>operations, int& index) //用于初始化时插入数据库失败返回错误语句
{
	std::lock_guard<decltype(mutex_opt_)> lock(mutex_opt_);
	for (int i = 0; i < operations.size(); i++){
		opt_sql_.push_back(operations[i]);
		output_debug_information("mysqlping", base::__kInfo) << "add sql opt_sql_.size:" << opt_sql_.size();
	}
	return true;
}
MYSQL_RES * MysqlDataBase::SelectDataBase(std::string select)
{
	if (connect_status_ == false){
		return false;
	}
	std::lock_guard<decltype(mutex_opt_)> lock(mutex_opt_);
	bool select_status = false;
	int rt = mysql_real_query(con, select.c_str(), strlen(select.c_str()));
	if (rt)
	{
		select_status = false;
		return NULL;
	}
	else
	{
		select_status = true;
		return mysql_store_result(con);//将结果保存在res结构体中
	}
	//return select_status;
}

bool MysqlDataBase::FreeSelect(MYSQL_RES *result)
{
	mysql_free_result(result);
	return true;
}
int MysqlDataBase::GetSqlOptSize( )
{
	output_debug_information("mysqlping", base::__kInfo) << "current opt_sql_.size:" << opt_sql_.size();
	return opt_sql_.size();
}
bool MysqlDataBase::OperationDataBase(std::string operation)
{
	if (connect_status_ == false){
		output_debug_information("mysqlping", base::__kInfo) << "connect_status_==false";
		return false;
	}
	bool operation_status = false;
	int rt = mysql_query(con, operation.c_str());
	if (rt != 0)
	{
		operation_status = false;
		mysql_rollback(con);
		//把失败的语句弹出，然后放在队尾 add by liu 1206
		/*opt_sql_.pop_front();
		opt_sql_.push_back(operation);*/
		
		output_debug_information("MysqlDataBase",base::__kError) << "error sql:" << operation;
	}
	else
	{
		/*output_debug_information("mysqlping", kLogLevel_Info, kLogTarget_Filesystem
			, "execute sql success,%s", operation);*/
		operation_status = true;
	}
	return operation_status;
}

• 通过Qt原生库连接Mysql
  需要在Qt的构建文件中加入

QT += sql

//如下代码段为连接Mysql数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setDatabaseName("tradesystem");
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("123456");
    bool ok = db.open();

//对数据库的增删查改
//1、查询
	QSqlQuery query;
    bool b=query.exec(selectordersSql);//填入SQL语句
    while(query.next())
    {
            OrderInfo order;
            order.order_trade_hour = query.value(0).toString();
            order.order_weight = query.value(1).toString();
            goods_type = query.value(2).toString();
    }

//2、非查询
	QSqlQuery query;
    QString insertSql = "delete from weatherinfo where weather_id = %1";
    QString sql = insertSql.arg(5);
    bool b=query.exec(sql);
    if(b)
    {
        cout << "successed" << endl;
    }
    else
    {
        cout << sql.toStdString() << "failed" << endl;
    }