c++调用数据库

文章链接:https://codemouse.online/archives/6546872158

步骤

1. 通过调用mysql_library_init 初始化MySQL库
2. 通过调用mysql_init()初始化连接句柄
3. 使用SQL语句
4. 调用mysql_close()关闭
5. 调用mysql_library_end()终止使用库

函数实现

#include "MySQLForCAPI.h"
MySQLForCAPI::MySQLForCAPI()
{
	//初始化库
	if (0 == mysql_library_init(0, nullptr, nullptr))
		cout << "mysql_library_init succeed" << endl;
	else
		cout << "mysql_library_init failed" << endl;
	//初始化对象
	if (mysql_init(&mysql) != nullptr)
		cout << "mysql_init succeed" << endl;
	else
		cout << "mysql_init failed" << endl;
	//设置字符显示
	if (0 == mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk"))
		cout << "mysql_options succeed" << endl;
	else
		cout << "mysql_options failed" << endl;
}
bool MySQLForCAPI::Query(const char* sql)
{
	if (0 == mysql_real_query(&mysql, sql, strlen(sql)))
	{
		return true;
	}
	return false;
}
bool MySQLForCAPI::Select(const char* sql, vector<vector<string>>& data)
{
	MYSQL_RES* result;//保存结果
	if (0 == mysql_real_query(&mysql, sql, strlen(sql)))
	{
		//检索一个完整的结果集给客户
		result = mysql_store_result(&mysql);
		//返回一个结果集合中的行 记录数
		int rows=mysql_num_rows(result);//会有一点丢失
		//返回一个结果集合中的列 字段数
		int fields = mysql_num_fields(result);//会有一点丢失
		MYSQL_ROW row = nullptr;
		while (row = mysql_fetch_row(result))//获取每一行数据
		{
		   vector<string> linedata;
		   for (int i = 0; i < fields; i++)
		   {
				if (row[i])
				{
					linedata.push_back(row[i]);
				}
				else
				{
					linedata.push_back("");
				}
		   }
		   data.push_back(linedata);
		}
	}
	//释放结果集占用内存
	mysql_free_result(result);
	return false;
}
bool MySQLForCAPI::CreateTable(const char* sql)
{
	if (0 == mysql_real_query(&mysql, sql, strlen(sql)))
	{
		return true;
	}
	return false;
}
bool MySQLForCAPI::CreateDB(const char* DBName)
{
	string sql = "create database if not exists ";//尾部要加空格
	sql += DBName;
	if (0 == mysql_real_query(&mysql, sql.c_str(), sql.size()))
	{
		sql = "use ";
		sql += DBName;
		if (0 == mysql_real_query(&mysql, sql.c_str(), sql.size()))
		{
			return true;
		}
	}
	return false;
}
bool MySQLForCAPI::MySQLConn(const char *host,const char *user,const char *passwd,const char *db,unsigned int port )
{
	//mysql_set_character_set(&mysql, "GBK");
	if (mysql_real_connect(&mysql, host, user, passwd, db, port, nullptr, 0) != nullptr)
	{
		return true;
	}
	return false;
}
void MySQLForCAPI::GetErrorInfo()
{
	errorNum = mysql_errno(&mysql);
	errorInfo = mysql_error(&mysql);
	cout << "errorcode:" << errorNum << " " << errorInfo << endl;
}
void MySQLForCAPI::Close()
{
	mysql_close(&mysql);
}
MySQLForCAPI::~MySQLForCAPI()
{
	Close();
	mysql_library_end();
}

调用

#include "MySQLForCAPI.h"
int main()
{
	MySQLForCAPI mysqlConn;
	if (!mysqlConn.MySQLConn("localhost", "root", "nitamab123", "jsp"))
	{
		mysqlConn.GetErrorInfo();
	}
	if (!mysqlConn.CreateDB("test"))
	{
		mysqlConn.GetErrorInfo();
	}
	string sql = "create table if not exists t1(\
				id int primary key auto_increment,\
				name varchar(20),\
				age int)engine=innodb default charset=utf8";
	if (!mysqlConn.CreateTable(sql.c_str()))
	{
		mysqlConn.GetErrorInfo();
	}
	sql = "insert into t1 values \
				(null,'夏七',25), \
				(null,'欧广',22), \
				(null,'danny',35),\
				(null,'九夏',55)";
	if (!mysqlConn.Query(sql.c_str()))
	{
		mysqlConn.GetErrorInfo();
	}
	sql = "update t1 set age=30 where id=1";
	if (!mysqlConn.Query(sql.c_str()))
	{
		mysqlConn.GetErrorInfo();
	}
	sql = "delete from t1 where id=3";
	if (!mysqlConn.Query(sql.c_str()))
	{
		mysqlConn.GetErrorInfo();
	}
	vector<vector<string>> data;
	sql = "select * from t1";
	if (!mysqlConn.Select(sql.c_str(), data))
	{
		mysqlConn.GetErrorInfo();
	}
	for (int i = 0; i < data.size(); ++i)
	{
		for (int j = 0; j < data[i].size(); ++j)
		{
		cout << data[i][j] << "\t";
		}
		//for(auto d:data[i])
		//{
		//	cout << d << "\t" ;
		//}
		cout << endl;
	}
	return 0;
}