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 1 //如下代码段为连接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; }