MySQL-C++封装类

参考：https://blog.csdn.net/daoming1112/article/details/54710743

 

MySQLInterface.h：

 

1.  
   // MySQLInterface
2.  
    
3.  
   // 功能描述：实现对MySQL访问操作的封装
4.  
    
5.  
   #ifndef __MYSQL_INTERFACE_H__
6.  
   #define __MYSQL_INTERFACE_H__
7.  
    
8.  
   #include <string>
9.  
   #include <vector>
10.  
     
11.  
    #include <winsock.h> // 远程访问
12.  
    #include "MySQL/include/mysql.h"
13.  
     
14.  
    // 引入相关库
15.  
    #pragma comment(lib, "ws2_32.lib")
16.  
    #pragma comment(lib, "MySQL/lib/libmysql.lib")
17.  
     
18.  
    #define ERROR_QUERY_FAIL -1 // 操作失败
19.  
     
20.  
     
21.  
    // 定义MySQL连接信息
22.  
    typedef struct
23.  
    {
24.  
    char* server;
25.  
    char* user;
26.  
    char* password;
27.  
    char* database;
28.  
    int port;
29.  
    }MySQLConInfo;
30.  
     
31.  
    class MySQLInterface
32.  
    {
33.  
    public:
34.  
    MySQLInterface();
35.  
    virtual ~MySQLInterface();
36.  
     
37.  
    void SetMySQLConInfo(char* server, char* username, char* password, char* database, int port);// 设置连接信息
38.  
    bool Open(); // 打开连接
39.  
    void Close(); // 关闭连接
40.  
     
41.  
    bool Select(const std::string& Querystr, std::vector<std::vector<std::string> >& data); // 读取数据
42.  
    bool Query(const std::string& Querystr); // 其他操作
43.  
    int GetInsertID(const std::string& Querystr);// 插入并获取插入的ID,针对自动递增ID
44.  
    void ErrorIntoMySQL(); // 错误消息
45.  
     
46.  
    public:
47.  
    int ErrorNum; // 错误代号
48.  
    const char* ErrorInfo; // 错误提示
49.  
     
50.  
    private:
51.  
    MySQLConInfo MysqlConInfo; // 连接信息
52.  
    MYSQL MysqlInstance; // MySQL对象
53.  
    MYSQL_RES *Result; // 用于存放结果
54.  
    };
55.  
     
56.  
    #endif

MySQLInterface.cpp:

 

1.  
   #include "stdafx.h"
2.  
   #include "MySQLInterface.h"
3.  
    
4.  
    
5.  
   MySQLInterface::MySQLInterface() :
6.  
   ErrorNum(0), ErrorInfo("ok")
7.  
   {
8.  
   mysql_library_init(0, NULL, NULL);
9.  
   mysql_init(&MysqlInstance);
10.  
     
11.  
    // 设置字符集，否则无法处理中文
12.  
    mysql_options(&MysqlInstance, MYSQL_SET_CHARSET_NAME, "gbk");
13.  
    }
14.  
     
15.  
    MySQLInterface::~MySQLInterface()
16.  
    {
17.  
    }
18.  
     
19.  
    // 设置连接信息
20.  
    void MySQLInterface::SetMySQLConInfo(char* server, char* username, char* password, char* database, int port)
21.  
    {
22.  
    MysqlConInfo.server = server;
23.  
    MysqlConInfo.user = username;
24.  
    MysqlConInfo.password = password;
25.  
    MysqlConInfo.database = database;
26.  
    MysqlConInfo.port = port;
27.  
    }
28.  
     
29.  
    // 打开连接
30.  
    bool MySQLInterface::Open()
31.  
    {
32.  
    if (mysql_real_connect(&MysqlInstance, MysqlConInfo.server, MysqlConInfo.user,
33.  
    MysqlConInfo.password, MysqlConInfo.database, MysqlConInfo.port, 0, 0) != NULL)
34.  
    {
35.  
    return true;
36.  
    }
37.  
    else
38.  
    {
39.  
    ErrorIntoMySQL();
40.  
    return false;
41.  
    }
42.  
    }
43.  
     
44.  
    // 断开连接
45.  
    void MySQLInterface::Close()
46.  
    {
47.  
    mysql_close(&MysqlInstance);
48.  
    }
49.  
     
50.  
    //读取数据
51.  
    bool MySQLInterface::Select(const std::string& Querystr, std::vector<std::vector<std::string> >& data)
52.  
    {
53.  
    if (0 != mysql_query(&MysqlInstance, Querystr.c_str()))
54.  
    {
55.  
    ErrorIntoMySQL();
56.  
    return false;
57.  
    }
58.  
     
59.  
    Result = mysql_store_result(&MysqlInstance);
60.  
     
61.  
    // 行列数
62.  
    int row = mysql_num_rows(Result);
63.  
    int field = mysql_num_fields(Result);
64.  
     
65.  
    MYSQL_ROW line = NULL;
66.  
    line = mysql_fetch_row(Result);
67.  
     
68.  
    int j = 0;
69.  
    std::string temp;
70.  
    std::vector<std::vector<std::string> >().swap(data);
71.  
    while (NULL != line)
72.  
    {
73.  
    std::vector<std::string> linedata;
74.  
    for (int i = 0; i < field; i++)
75.  
    {
76.  
    if (line[i])
77.  
    {
78.  
    temp = line[i];
79.  
    linedata.push_back(temp);
80.  
    }
81.  
    else
82.  
    {
83.  
    temp = "";
84.  
    linedata.push_back(temp);
85.  
    }
86.  
    }
87.  
    line = mysql_fetch_row(Result);
88.  
    data.push_back(linedata);
89.  
    }
90.  
    return true;
91.  
    }
92.  
     
93.  
    // 其他操作
94.  
    bool MySQLInterface::Query(const std::string& Querystr)
95.  
    {
96.  
    if (0 == mysql_query(&MysqlInstance, Querystr.c_str()))
97.  
    {
98.  
    return true;
99.  
    }
100.  
     ErrorIntoMySQL();
101.  
     return false;
102.  
     }
103.  
      
104.  
     // 插入并获取插入的ID,针对自动递增ID
105.  
     int MySQLInterface::GetInsertID(const std::string& Querystr)
106.  
     {
107.  
     if (!Query(Querystr))
108.  
     {
109.  
     ErrorIntoMySQL();
110.  
     return ERROR_QUERY_FAIL;
111.  
     }
112.  
     // 获取ID
113.  
     return mysql_insert_id(&MysqlInstance);
114.  
     }
115.  
      
116.  
     //错误信息
117.  
     void MySQLInterface::ErrorIntoMySQL()
118.  
     {
119.  
     ErrorNum = mysql_errno(&MysqlInstance);
120.  
     ErrorInfo = mysql_error(&MysqlInstance);
121.  
     }

Example

#include <iostream>

using namespace std;

 

#include "MySQLInterface.h" 

 

int _tmain(int argc, _TCHAR* argv[])

{

         MySQLInterface MySQLInterface;

         MySQLInterface.SetMySQLConInfo("localhost", "root", "123456", "world", 337);

         if (!MySQLInterface.Open())

         {

                  std::cout << MySQLInterface.ErrorNum << " : " << MySQLInterface.ErrorInfo << std::endl;

         }

 

         // 读取数据

         std::vector<std::vector<std::string> > data;

         std::string sqlstr = "SELECT `ID`,`Name`,`CountryCode`,`District` FROM `world`.`city` LIMIT 10";

         MySQLInterface.Select(sqlstr, data);

 

         // 显示数据

         for (unsigned int i = 0; i < data.size(); ++i)

         {

                  for (unsigned int j = 0; j < data[0].size(); ++j)

                  {

                          cout << data[i][j] << "\t\t";

                  }

                  cout << endl;

         }

 

         // 其他操作

         sqlstr = "CREATE TABLE IF NOT EXISTS `new_paper` (";

         sqlstr += " `NewID` int(11) NOT NULL AUTO_INCREMENT,";

         sqlstr += " `NewCaption` varchar(40) NOT NULL,";

         sqlstr += " `NewContent` text,";

         sqlstr += " `NewTime` datetime DEFAULT NULL,";

         sqlstr += " PRIMARY KEY(`NewID`)";

         sqlstr += " ) ENGINE = InnoDB DEFAULT CHARSET = utf8";

 

         if (!MySQLInterface.Query(sqlstr))

         {

                  std::cout << MySQLInterface.ErrorNum << " : " << MySQLInterface.ErrorInfo << std::endl;

         }

 

         MySQLInterface.Close();

 

         system("pause");

         return 0;

}

 

 

错误提示

MFC (CString)

if (!m_MySQLInter.Open()) // 连接失败

    {

         CString strError = _T("");

         USES_CONVERSION;

         strError.Format(_T("打开数据库失败...\n%d : %s"), m_MySQLInter.ErrorNum, A2W(m_MySQLInter.ErrorInfo));

         ::MessageBox(GetSafeHwnd(), strError, _T("系统提示"), MB_ICONEXCLAMATION | MB_OK);

         return;

    }

 

Win32

         if(!m_MySQLInter.Open())

         {

                  std::cout<< m_MySQLInter.ErrorNum << " : " <<m_MySQLInter.ErrorInfo << std::endl;

                  return;

         }

 

 

类型转换

Std::string to char*

         // 类型转换

         char* pDatabase = new char[strlen(database.c_str()) + 1];

         strcpy(pDatabase, database.c_str());

         char* pPassword = new char[strlen(password.c_str()) + 1];

         strcpy(pPassword, password.c_str());

         char* pUserName = new char[strlen(usename.c_str()) + 1];

         strcpy(pUserName, usename.c_str());

         char* pServer = new char[strlen(server.c_str()) + 1];

         strcpy(pServer, server.c_str());

 

         m_MySQLInter.SetMySQLConInfo(pServer, pUserName, pPassword, pDatabase, port);

 

CString to char*

USES_CONVERSION;

    m_MySQLInter.SetMySQLConInfo(W2A(m_strServer), W2A(m_strUserName), W2A(m_strPassword), "log", _ttoi(m_strPort));

 

    if (!m_MySQLInter.Open()) // 连接失败

    {

         CString strError = _T("");

         USES_CONVERSION;

         strError.Format(_T("打开数据库失败...\n%d : %s"), m_MySQLInter.ErrorNum, A2W(m_MySQLInter.ErrorInfo));

         ::MessageBox(GetSafeHwnd(), strError, _T("系统提示"), MB_ICONEXCLAMATION | MB_OK);

         return;

    }