Ubuntu系统MySQL嵌入式开发入门（C++）

最近在学Ubuntu软件开发，需要嵌入式数据库，作为备忘录，也希望对初学者有所帮助

首先安装MySQL

sudo apt-get install mysql-server mysql-client

再安装G++依赖的mysql.h的依赖包，见另一篇博客：fatal error： mysql.h:没有那个文件或目录

sudo apt-get install libmysqlclient-dev

运行MySQL

mysql -u root -p

创建一个简单的数据库

mysql>create database test; 
mysql>use test;    //切换到test数据库中
mysql> create table test(name varchar(255),num int(10) ); //创建一个叫test的表
mysql>show create table test;  //显示刚才创建的表信息
mysql> select * from test; 　　//查询test表中数据
mysql>quit

用vim简单写一个test.cpp 的测试文件

#include <stdio.h>
#include <mysql.h>
int main(int argc,char *argv[])
{
    MYSQL conn;
    int res;
    mysql_init(&conn);
    if(mysql_real_connect(&conn,"localhost","root","8520","test",0,NULL,CLIENT_FOUND_ROWS))
    //"root":数据库管理员 "":root密码 "test":数据库的名字
    {
        printf("connect success!\n");
        res=mysql_query(&conn,"insert into test values('user','123456')");
        if(res)
        {
            printf("error\n");
        }
        else
        {
            printf("OK\n");
        }
        mysql_close(&conn);
    }
    else
    {
        printf("Database can not connect!!!!");
    }
    return 0;
}

编译命令：注意`是跟波浪线在一起的那个符号

g++ test.cpp `mysql_config --cflags --libs` -o test

运行test

./test

就可以把这条数据存储进MySQL了

/×××××××××××××××××××××××××分割线×××××××××××××××××××××××××××××××××××××/

接下来是多文件，多线程访问数据库。目前还有bug。。。数据库总报错，连接不上

/*****************
main.cc
******************/
#include "MySQLManager.h"
#include <iostream>
#include <vector>
using namespace std;
int main()
{
    MySQLManager *mysql = new MySQLManager("127.0.0.1", "root", "xufeiyang", "mytest", (unsigned int)3306);
    mysql->initConnection();
    if(mysql->getConnectionStatus())
    {
        if(mysql->runSQLCommand("select * from student"))
        {
            vector<vector<std::string> > result = mysql->getResult();
            for(auto & vec : result)
            {
                for(auto &str : vec)
                {cout << str.c_str() << " ";}
                    cout << endl;
            }
        }
        else
        cout << "执行失败" << endl;
    }
    else
    cout << "连接未建立" << endl;
    return 0;
}

 

  

   /*    
  
  

   * MySQLManager.h    
  
  

   */
  
  

   #ifndef
    
   MYSQLMANAGER_H_
       
  
  

   #define
    
   MYSQLMANAGER_H_
        
  
  

   #include
    
   <mysql.h>
  
  

   #include
    
   <string>
  
  

   #include
    
   <iostream>
  
  

   #include
    
   <vector>
  
  

   #include
    
   <string.h>
  
  

   using 
   namespace 
   std;
  
  

   class 
   MySQLManager    
  
  

   {
  
  

   public:
  
  
        
   MySQLManager(std::string hosts, std::string userName, std::string password, std::string dbName, 
   unsigned 
   int port);
  
  
        
   ~MySQLManager();
  
  
        
   void 
   initConnection();
  
  
        
   bool 
   runSQLCommand(std::string sql);
  
  
        
   unsigned 
   int 
   insert(std::string sql);
  
  
        
   void 
   destroyConnection();
  
  
        
   bool 
   getConnectionStatus();
  
  

           vector< vector<string> > 
   getResult();
  
  

   protected:
  
  
        
   void 
   setUserName(std::string userName);
  
  
        
   void 
   setHosts(std::string hosts);
  
  
        
   void 
   setPassword(std::string password);
  
  
        
   void 
   setDBName(std::string dbName);
  
  
        
   void 
   setPort(
   unsigned 
   int port);
  
  

   private:
  
  
        
   bool IsConnected;
  
  

           vector< vector<string> > resultList;
  
  

           MYSQL mySQLClient;
  
  
        
   unsigned 
   int DEFAULTPORT;
  
  
        
   char * HOSTS;
  
  
        
   char * USERNAME;
  
  
        
   char * PASSWORD;
  
  
        
   char * DBNAME;
  
  

   };
  
  

   #endif 
   /* MYSQLMANAGER_H_ */
  
 

/***************************************
MySQLManager.cc
***************************************/
#include "MySQLManager.h"    
MySQLManager::MySQLManager(string hosts, string userName, string password, string dbName, unsigned int port)    
{    
        IsConnected = false;    
        this ->setHosts(hosts);            //    设置主机IP地址    
        this ->setUserName(userName);            //    设置登录用户名    
        this ->setPassword(password);            //    设置登录密码    
        this ->setDBName(dbName);            //    设置数据库名    
        this ->setPort(port);            //    设置端口号    
}
MySQLManager::~MySQLManager()    
{    
        this ->destroyConnection();    
}

void MySQLManager::setDBName(string dbName)    
{    
        if ( dbName.empty() )    
        {//        用户没有指定数据库名    
                std::cout << "DBName is null! Used default value: mysql" << std::endl;    
                this ->DBNAME = new char[5];    
                strcpy(this ->DBNAME, "mysql");    
        }    
        else    
        {    
                this ->DBNAME = new char[dbName.length()];    
                strcpy(this ->DBNAME, dbName.c_str());    
        }    
}
void MySQLManager::setHosts(string hosts)    
{    
        if (hosts.empty())    
        {//    用户没有指定数据库IP地址    
                std::cout << "Hosts is null! Used default value: localhost" << std::endl;    
                this ->HOSTS = new char[9];    
                strcpy(this ->HOSTS, "localhost");    
        }    
        else    
        {    
                this ->HOSTS = new char[hosts.length()];    
                strcpy(this ->HOSTS, hosts.c_str());    
        }    
}    

void MySQLManager::setPassword(string password)    
{//    用户没有指定密码    
        if ( password.empty() )    
        {    
                std::cout << "Password is null! Used default value: " << std::endl;    
                this ->PASSWORD = new char[1];    
                strcpy(this ->PASSWORD, "");    
        }    
        else    
        {    
                this ->PASSWORD = new char[password.length()];    
                strcpy(this ->PASSWORD, password.c_str());    
        }    
}    

void MySQLManager::setPort(unsigned int port)    
{//    用户没有指定端口号，使用默认端口号    
        if ( port <= 0 )    
        {    
                std::cout << "Port number is null! Used default value: 0" << std::endl;    
                this->DEFAULTPORT = 0;
        }    
        else    
        {    
                this ->DEFAULTPORT = port;    
        }    
}    

void MySQLManager::setUserName(string userName)    
{//    用户没有指定登录用户名    
        if ( userName.empty() )    
        {    
                std::cout << "UserName is null! Used default value: root" << std::endl;    
                this ->USERNAME = new char[4];    
                strcpy(this ->USERNAME, "root");    
        }    
        else    
        {    
                this ->USERNAME = new char[userName.length()];    
                strcpy(this ->USERNAME, userName.c_str());    
        }    
}    

void MySQLManager::initConnection()    
{    
        if ( IsConnected )    
        {//    已经连接到服务器    
                std::cout << "Is connected to server!" <<std::endl;    
                return;    
        } 
        mysql_init(&mySQLClient);//    初始化相关对象    
        if ( !mysql_real_connect( &mySQLClient, HOSTS, USERNAME, PASSWORD, DBNAME, DEFAULTPORT, NULL, 0) )    
        {//    连接到服务器连接到服务器
                cout << "HHHHHHHHHHHHH"<<endl;
                std::cout << "Error connection to database: \n" << mysql_error(&mySQLClient) << std::endl;    
        }    
        IsConnected = true;//    修改连接标识    
}    

bool MySQLManager::runSQLCommand(string sql)    
{    
        if (!IsConnected )
        {
                std::cout << "Not connect to database!" << std::endl;    
                return false;    
        }    
        if (sql.empty())    
        {//    SQL语句为空    
                std::cout << "SQL is null!" << std::endl;    
                return false;    
        }    

        MYSQL_RES *res;    
        MYSQL_ROW row;    

        unsigned int i,j = 0;    

        i = mysql_real_query(&mySQLClient,sql.c_str(),(unsigned int)strlen(sql.c_str()));//    执行查询    
        if ( i < 0 )    
        {    
                std::cout << "Error query from database: \n" << mysql_error(&mySQLClient) << std::endl;    
                return false;    
        }    
        res = mysql_store_result(&mySQLClient);    
        vector<string> objectValue;    
        while( (row = mysql_fetch_row(res)) )    
        {//    遍历结果集    
                objectValue.clear();    
                for ( j = 0 ; j < mysql_num_fields(res) ; j++ )    
                {    
                        objectValue.push_back(row[j]);    
                }    
                this ->resultList.push_back(objectValue);    
        }    
        mysql_free_result(res);         //free result after you get the result    

        return true;    
}
unsigned int MySQLManager::insert(std::string sql)
{
    if(!IsConnected)
    {
        cout << "" << endl;
        return -1;
    }
    if(sql.empty()){
        cout << "sql is null " << endl;
        return -1;
    }
    int rows = -1;
    int res = mysql_query(&mySQLClient, sql.c_str());
    if(res >= 0){
        // 返回受影响的行数
        rows = mysql_affected_rows(&mySQLClient);
        cout << "Inserted "<< rows << " rows\n";
        return rows;
    } else {
        cout << "Insert error " << mysql_errno(&mySQLClient) << "," << mysql_error(&mySQLClient) << endl;
        return -1;
    }
}
vector< vector<string> > MySQLManager::getResult()    
{    
        return resultList;    
}    

void MySQLManager::destroyConnection()    
{    
       mysql_close(&mySQLClient);    
       this ->IsConnected = false;    
}
bool MySQLManager::getConnectionStatus()    
{    
        return IsConnected;    
}

编译

g++ main.cc MySQLManager.cc -o main -std=c++11 -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient