Linux下使用C/C++访问数据库

Linux下使用C/C++访问数据库——MySQL篇

最近打算写一套OA系统，为了方便进行数据库操作，特意抽出一周的时间来研究C/C++访问各种数据库的方法，并打算封装一套数据库操作类，现在奉上最简单的一部分：在Linux下访问MySQL数据库。
本文中使用的MySQL API代码为C语言，如果各位对C++有兴趣，可以考虑使用mysql++。
一、配置开发环境
首先需要编译、安装MySQL，安装完成后，将MySQL目录中的lib目录添加到环境变量中。
新建C/C++工程，把$MYSQL_ROOT/include添加到编译环境的包含路径下面。在编译选项中，增加$MYSQL_ROOT/lib目录。在Link选项中增加-lmysqlclient（已经把lib目录增加到系统环境变量中），或者直接引用libmysqlclient.so文件。
二、程序代码
不多说了，直接上代码，注释都很详细。

/*    
* MySQLManager.h    
*    
*    Created on: Feb 18, 2009    
*            Author: Steven Wee    
*/    

#ifndef MYSQLMANAGER_H_    
#define MYSQLMANAGER_H_    

#include "../Common/CheckStringTools.h"    

#include <mysql.h>    

#include <string>    
#include <iostream>    
#include <vector>    

#include <string.h>    

using namespace std;    

class MySQLManager    
{    
public:    
        /*    
         * Init MySQL    
         * @param hosts:         Host IP address    
         * @param userName:        Login UserName    
         * @param password:        Login Password    
         * @param dbName:        Database Name    
         * @param port:                Host listen port number    
         */    
        MySQLManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port);    
        ~MySQLManager();    
        void initConnection();    
        /*    
         * Making query from database    
         * @param mysql:        MySQL Object    
         * @param sql:                Running SQL command    
         */    
        bool runSQLCommand(std::string sql);    
        /**    
         * Destroy MySQL object    
         * @param mysql                MySQL object    
         */    
        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.cpp    
*    
*    Created on: Feb 18, 2009    
*            Author: Steven Wee    
*/    
#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 )    
        {    
                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) )    
        {//    连接到服务器    
                std::cout << "Error connection to database: %s\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;    

        StringTools stringTools;    
        sql = stringTools.filterString(sql);    

        i = mysql_real_query(&mySQLClient,sql.c_str(),(unsigned int)strlen(sql.c_str()));//    执行查询    
        if ( i )    
        {    
                std::cout << "Error query from database: %s\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;    
}    

vector< vector<string> > MySQLManager::getResult()    
{    
        return resultList;    
}    

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

bool MySQLManager::getConnectionStatus()    
{    
        return IsConnected;    
} 

三、修改建议
本人在以后的完善中，打算把runSQLCommand(char * sql)函数分解成两个或者三个函数，分别执行select和insert等语句。
在程序中，我并没有强制要求参数必须为const，可能会出现一些安全问题。
本文仅起抛砖引玉的作用，希望有高手可以指点我程序中的问题。

敬请期待下一篇文章：Linux下使用C/C++访问数据库——SQL Server篇



一个例子

#include <iostream>   
#include <string>   
#include <strstream>   
using namespace std;   

class CBaseX
      {
      public:
		  int x;
		  CBaseX() { x = 10; }
		  void foo() { printf("CBaseX::foo() x=%d\n", x); }
 };
      class CBaseY
        {
			public:
				int y;
				int* py;
				CBaseY() { y = 20; py = &y; }
				void bar() { printf("CBaseY::bar() y=%d, *py=%d\n", y, *py); 
			}
        };
      class CDerived : public CBaseX, public CBaseY
			{
			public:
				int z;
			 CDerived() { z = 10; }
			 void test() { printf("test z=%d\n", z); }
          };

int main()   
{   
    int n = 65535;   
    strstream ss;   
	    strstream ss1;   
			    strstream ss2;   
    string s;   
	string s1;
	string s2;
    ss << n;   
    ss >> s;   
    cout << s << endl;   
   double t_d = 10.6;
  float t_i= static_cast<float>(t_d);
// cout<<t_i<endl;
  ss1<<t_i;
  ss1>>s1;
  cout << s1 << endl;   

  char* c="iloveyou";

  char * p = reinterpret_cast<char *>(c);//
 ss2<<p;
  ss2>>s2;
    cout << s2 << endl;  


       CDerived* pD = new CDerived();
	  //  pD->bar();
        printf("CDerived* pD = %x\n", (int)pD);
          CBaseY* pY = pD; // 成功编译, pY = pD + 4
		//  pY->bar();

		  CBaseY* pD3 = dynamic_cast<CBaseY*>(pD);//

         pD3->bar();

		 	  CDerived* pD4 = static_cast<CDerived*>(pD3);//

         pD4->bar();

		   CBaseY* pD5 = static_cast<CBaseY*>(pD4);//

         pD5->bar();

		  CDerived* pD6 = reinterpret_cast<CDerived*>(pD4);// 出错

         pD6->bar();


        printf("CBaseY* pY = %x\n", (int)pY);
            void* pV1 = pY; //成功编译, pV1 = pY
        printf("void* pV1 = %x\n", (int)pV1);
               // pD2 = pY, 但是我们预期 pD2 = pY - 4
        CDerived* pD2 = static_cast<CDerived*>(pV1);
        printf("CDerived* pD2 = %x\n", (int)pD2);
        // 系统崩溃
		pD2->test();



/*
class B {};  
class D: public B {}
void f( B* pb )
{
    D* pd1 = dynamic_cast<D*>(pb); 
    D* pd2 = static_cast<D*>(pb);  
}*/


    return 0;   
}  


/*

dynamic_cast:向下转型
static_cast :这个好用 ,可向上也可向下
reinterpret_cast: 这个还有待进一步体会
const_cast: 这个也要时一步体会真实用法

*/