通过C++ API 方式,这里使用MySQL 5.7版本
环境配置
项目目录结构如下:
找到MySQL安装目录下的include和lib目录,拷贝过来。
bin目录放置libmysql.dll文件
MysqlTest目录放置源代码
配置vs项目
头文件
链接库文件
至此,VS环境配置成功。
封装数据库操作接口动态库
MySQLSvr.h文件:
/********************************************************************
*********************************************************************
**** Copyright(C) 2021 Persion ****
**** Abstract : MySQL数据库操作接口 ****
**** FileName : MySQLSvrI.h ****
**** Author : NSY ****
**** CreatedDate : 2021-11-30 14:28 ****
**** RevisedDate : xxx-xxx-xx 14:30 ****
**** RevisedAuthor: Yes ****
**** Remarks : MySQL 5.7 ****
**** Version : V1.2 ****
**** 操作MySQL数据库的最底层程序,直接驱动数据库进行操作 ****
**** 注:设置x64 ****
*********************************************************************
*********************************************************************/
#pragma once
#include<iostream>
#include "mysql.h"
#include "windows.h"
#include "winsock.h"
#include<string>
#pragma comment(lib,"libmysql.lib")
#ifdef DLL_API
#else
#define DLL_API _declspec(dllexport)
#endif
using namespace std;
//导出单独的函数
//DLL_API bool InitMySQLAPI(string IP, string user, string passwd, string DBName, int Port);
class DLL_API MySQLSvr
{
public:
MySQLSvr();
~MySQLSvr();
public:
/*
* 功能:初始化连接
* @para IP : MySQL服务IP
* @para user : MySQL连接用户名
* @para passwd : MySQL连接密码
* @para DBName : 连接数据库名
* @para Port : MySQL服务端口号
*
* return bool : 返回值,true:成功;false:失败
*/
bool InitMySQLAPI(string IP, string user, string passwd, string DBName, int Port);
/*
* 功能:插入数据
* @para TableName : 表名 string tabel = "employee";
* @para value : 插入值 string value = "22,'YYY','1',20000,'10bu',22";
*
* return int : 返回值,0:成功;-1:失败;
*/
int InsertMySQL(const string &TableName, const string &value);
/*
* 功能:删除数据
* @para TableName : 表名 string tabel = "employee";
* @para filter : 过滤条件 string filter = "name = '张三'";
*
* return int : 返回值,0:成功;-1:失败;
*/
int DeleteMySQL(const string &TableName, const string &filter);
/*
* 功能:更新数据
* @para TableName : 表名 string tabel = "employee";
* @para value : 更新值 string value = "name = '王五'";
* @para filter : 过滤条件 string filter = "name = '张三'";
*
* return int : 返回值,0:成功;-1:失败;
*/
int UpdateMySQL(const string &TableName, const string &value, const string & filter);
/*
* 功能:全表查询
* @para TableName : 表名 string tabel = "employee";
*
* return int : 返回值,0:成功;-1:失败;
*/
int ReadMySQL(const string &TableName);
/*
* 功能:查询表的记录数
* @para TableName : 表名 string tabel = "employee";
* 如: select count(*) from employee;
* return int : 返回值,记录数
*/
int CountQueryMySQL(const string &TableName);
/*
* 功能:GBK转UTF8
* @para TableName : 表名 string tabel = "employee";
* 如: select count(*) from employee;
* return int : 返回值,记录数
*/
string GBKtoUTF8(const char* strGBK);
/*
* 功能:设置编码字符集
* @para sCharacter : 编码格式 gbk或utf8
* 如:
* return int : 返回值,0:成功;-1:失败
*/
int SetCharacter(string sCharacter);
/*
* 功能:返回用于连接MySQL服务的客户端IP
* return string : 返回值 :IP:成功
*/
string ClientIP();
/*
* 功能:返回与服务端进行连接的协议
* return string : 返回值 :TCP/IP:成功
*/
string ClientProtocol();
/*
* 功能:客户端版本
* return string : 返回值 :版本号
*/
string ClientVersion();
private:
MYSQL mysql, *sock; //MySQL连接
};
MySQLSvr.cpp 文件:
#include "MySQLSvr.h"
MySQLSvr::MySQLSvr()
{
}
MySQLSvr::~MySQLSvr()
{
mysql_close(sock); //关闭连接
}
bool MySQLSvr::InitMySQLAPI(string IP, string user, string passwd, string DBName, int Port)
{
mysql_init(&mysql); //初始化
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk"); //SET NAMES GBK
if ((sock = mysql_real_connect(&mysql, IP.c_str(), user.c_str(), passwd.c_str(), DBName.c_str(), Port, NULL, 0)) == NULL)
{
cout << "连接失败,原因是:" << endl;
fprintf(stderr, "%s\n", mysql_error(&mysql));
return false;
}
else
{
cout << "连接成功!" << endl;
return true;
}
}
int MySQLSvr::InsertMySQL(const string &TableName, const string &value)
{
string SQLQuery = "insert into " + TableName + " values(" + value + "); ";
if (mysql_query(&mysql, SQLQuery.c_str()))
{
string ret = mysql_error(&mysql);
cout << ret << endl;
return -1;
}
else
{
return 0;
}
}
int MySQLSvr::DeleteMySQL(const string &TableName, const string &filter)
{
string SQLQuery = "delete from " + TableName + " where " + filter + ";";
if (mysql_query(&mysql, SQLQuery.c_str()))
{
string ret = mysql_error(&mysql);
cout << ret << endl;
return -1;
}
else
{
return 0;
}
}
int MySQLSvr::UpdateMySQL(const string &TableName, const string &value, const string & filter)
{
string SQLQuery = "update " + TableName + " set " + value + " where " + filter + ";";
if (mysql_query(&mysql, SQLQuery.c_str()))
{
string ret = mysql_error(&mysql);
cout << ret << endl;
return -1;
}
else
{
return 0;
}
}
int MySQLSvr::ReadMySQL(const string &TableName)
{
MYSQL_RES * result; //查询结果
MYSQL_ROW row;
string SQLQuery = "select * from " + TableName; //查询语句
if (mysql_query(&mysql, SQLQuery.c_str()) != 0)
{
cout << mysql_error(&mysql) << endl;
return -1;
}
else
{
if ((result = mysql_store_result(&mysql)) == NULL)
{
fprintf(stderr, "保存结果失败!\n");
return -1;
}
else
{
int lo = mysql_num_fields(result); //列数
char * str[32]; //数组
for (int i = 0; i < lo; i++)
{
str[i] = mysql_fetch_field_direct(result, i)->name; //获取表字段
cout << str[i] << " ";
}
cout << endl;
while (row = mysql_fetch_row(result)) //每成功调用一行,就滑向下一行
{
for (int i = 0; i < lo; i++)
{
cout << row[i] << " ";
}
cout << endl;
}
if ((row = mysql_fetch_row(result)) != NULL)
{
cout << "查询结果为空!" << endl;
return 2;
}
}
}
mysql_free_result(result);
}
int MySQLSvr::CountQueryMySQL(const string &TableName)
{
MYSQL_RES * result; //查询结果
string SQLQuery = "select * from " + TableName + ";"; //SQL查询语句
if (mysql_query(&mysql, SQLQuery.c_str()) != 0)
{
cout << mysql_error(&mysql) << endl;
return -1;
}
else
{
if ((result = mysql_store_result(&mysql)) == NULL)
{
fprintf(stderr, "保存结果失败!\n");
return -1;
}
return mysql_num_rows(result);
}
mysql_free_result(result);
}
string MySQLSvr::GBKtoUTF8(const char* strGBK)
{
#ifdef WIN32
int len = MultiByteToWideChar(CP_ACP, 0, strGBK, -1, NULL, 0);
wchar_t* wstr = new wchar_t[len + 1];
memset(wstr, 0, len + 1);
MultiByteToWideChar(CP_ACP, 0, strGBK, -1, wstr, len);
len = WideCharToMultiByte(CP_UTF8, 0, wstr, -1, NULL, 0, NULL, NULL);
char* str = new char[len + 1];
memset(str, 0, len + 1);
WideCharToMultiByte(CP_UTF8, 0, wstr, -1, str, len, NULL, NULL);
if (wstr) delete[] wstr;
string tempStr = str;
delete[] str;
return tempStr;
#else
return "";
#endif
}
int MySQLSvr::SetCharacter(string sCharacter)
{
int ret1 = mysql_set_character_set(&mysql, sCharacter.c_str()); //0:成功;非零:错误
if (!ret1)
{
cout << "设置客户端编码集为:" << mysql_character_set_name(&mysql) << endl;
return 0;
}
else
{
string ret = mysql_error(&mysql);
cout << ret << endl;
return -1;
}
}
string MySQLSvr::ClientIP()
{
string ret = mysql_get_host_info(&mysql);
unsigned int loc = ret.find("via", 0);
if (loc != string::npos)
{
string re = ret.substr(0, loc);
return re;
}
else
{
string ret = mysql_error(&mysql);
cout << ret << endl;
return "";
}
}
string MySQLSvr::ClientProtocol()
{
string ret = mysql_get_host_info(&mysql);
unsigned int loc = ret.find("via", 0);
if (loc != string::npos)
{
string re = ret.substr(loc + 4, ret.length());
return re;
}
else
{
return "";
}
}
string MySQLSvr::ClientVersion()
{
string version = mysql_get_client_info();
string ret = "MySQL版本:" + version + "\n";
return ret; //客户端版本号&mysql
}
编译生成如下:
调用测试
项目-》属性-》配置属性-》链接器-》附加库文件-》输入:…\bin
项目-》属性-》配置属性-》C/C+±》附加包含目录-》输入:…\include
项目-》属性-》配置属性-》常规-》输出目录-》输入:…\bin
#include<iostream>
#include<string>
#include "MySQLSvr.h"
using namespace std;
#pragma comment(lib,"MySQLDLL.lib")
int main()
{
MySQLSvr DbSvr;
string host = "127.0.0.1";
string user = "root";
string passwd = "123456";
string db = "mydb1";
unsigned int port = 3306;
DbSvr.InitMySQLAPI(host, user, passwd, db, port);
string tabel = "employee";
DbSvr.ReadMySQL(tabel);
system("pause");
return 0;
}