C++ 连接MySQL

（一）安装MySQL和开发包

　　先安装MySQL，安装server和client……然后要安装开发包，在Ubuntu下：sudo apt-get install libMySQLclient15-dev

一开始我没有安装开发包，发现怎么都找不到mysql.h和相应的库。安装好之后，编译时包含相应的头文件以及库，即可使用MySQL的API，示例：

gcc -o mysqlDemo -I/usr/include/mysql -L/usr/lib -lmysqlclient main.c

（二）封装一个访问MySQL的库

　　本来想自己封装一个库的，可是在网上发现了一个写的很不错的例子，所以自己就把那个例子稍稍改了一下，网址在这：http://docstore.mik.ua/orelly/linux/sql/ch13_02.htm

他设计时的UML图如下：

In the MySQL and mSQL world, there are three basic concepts: the connection, the result set, and the rows in the result set. We will use these concepts as the core of the object model on which our library will be based.

Figure 13-1. Object-oriented database access library

部分代码：

Connection.h

#ifndef CONNECTION_H
#define CONNECTION_H

#include <sys/time.h>

//if you use Microsoft SQL Server
#if defined(HAS_MSQL)
#include <msql.h>
//you use mysql
#elif defined(HAS_MYSQL)
#include <mysql.h>
#endif

#include "Result.h"

class Connection
{
    public:
        Connection(const char*,const char*);
        Connection(const char*,const char*,const char*,const char*);
        ~Connection();

        void Close();
        void Connect(const char* host,const char* dataBase,
                     const char* userID,const char* password);
        int GetAffectedRows() const;
        const char* GetError();
        int IsConnected() const;
        Result* Query(const char* sqlCommand);

     private:
        int affectedRows;
     #if defined(HAS_MSQL)
        int connection;
     #elif defined(HAS_MYSQL)
        MYSQL mysql;
        MYSQL* connection;
     #else
        #error No database defined.
     #endif
};

#endif // CONNECTION_H

Connection.cpp

#include "Connection.h"

Connection::Connection(const char* host,const char* dataBase)
:affectedRows(0)
{
#if defined(HAS_MSQL)
    connection=-1;
#elif defined(HAS_MYSQL)
    connection=(MYSQL*)NULL;
#else
    #error No database linked.
#endif
    Connect(host,dataBase,(const char*)NULL,(const char*)NULL);
}

Connection::Connection(const char* host,const char* dataBase,
                       const char* userID,const char* password)
:affectedRows(0)
{
#if defined(HAS_MSQL)
    connection=-1;
#elif defined(HAS_MYSQL)
    connection=(MYSQL*)NULL;
#else
    #error No database linked.
#endif
    Connect(host,dataBase,userID,password);
}

void Connection::Connect(const char* host,const char* dataBase,
                         const char* userID,const char* password)
{
    int state;

    if(IsConnected())
        throw "Connection has been established.";
#if defined(HAS_MSQL)
    connection=msqlConnect(host);
    state=msqlSelectDB(connection,dataBase);
#elif defined(HAS_MYSQL)
    mysql_init(&mysql);
    connection=mysql_real_connect(&mysql,host,userID,password,
                                     dataBase,0,NULL,0);
#else
    #error No database linked.
#endif
    if(!IsConnected())
        throw GetError();

    if(state<0)
        throw GetError();
}

Connection::~Connection()
{
    if(IsConnected())
        Close();
}

void Connection::Close()
{
    if(!IsConnected())
        return;

#if defined HAS_MSQL
    msqlClose(connection);
    connection=-1;
#elif defined HAS_MYSQL
    mysql_close(connection);
    connection=(MYSQL*)NULL;
#else
    #error No database linked
#endif
}

Result* Connection::Query(const char* sqlCommand)
{
    T_RESULT* result;
    int state;

    if(!IsConnected())
        throw "DataBase not connected.";

#if defined(HAS_MSQL)
    state=mysqlQuery(connection,sqlCommand);
#elif defined(HAS_MYSQL)
    state=mysql_query(connection,sqlCommand);
#else
    #error No dataBase Linked.
#endif

    if(state!=0)
        throw GetError();

#if defined(HAS_MSQL)
    result=msqlStoreResult();
#elif defined(HAS_MYSQL)
    result=mysql_store_result(connection);
#else
    #error No database linked.
#endif

    //if the result was null,it was an update or an error occurred
    if(result==(T_RESULT*)NULL)
    {
    #if defined(HAS_MSQL)
        affectedRows=state;
    #elif defined(HAS_MYSQL)
        int fieldCount=mysql_field_count(connection);
        if(fieldCount!=0)
            throw GetError();
        else
            affectedRows=mysql_affected_rows(connection);
    #else
        #error No database linked.
    #endif
        //return NULL for updates
        return (Result*)NULL;
     }
     //return a Result instance for queries
     return new Result(result);
}

int Connection::GetAffectedRows() const
{
    return affectedRows;
}

const char* Connection::GetError()
{
#if defined(HAS_MSQL)
    return msqlErrMsg;
#elif defined(HAS_MYSQL)
    if(IsConnected())
        return mysql_error(connection);
    else
        return mysql_error(&mysql);
#else
    #error No database linked.
#endif
}

int Connection::IsConnected() const
{
#if defined(HAS_MSQL)
    return connection>0;
#elif defined(HAS_MYSQL)
    return (connection!=NULL);
#else
    #error No database linked.
#endif
}

Result.h

#ifndef RESULT_H
#define RESULT_H

#include <sys/time.h>

#if defined(HAS_MSQL)
#include <msql.h>
#elif defined(HAS_MYSQL)
#include <mysql.h>
#endif

#include "Row.h"

class Result
{
    public:
        Result(T_RESULT* );
        ~Result();

        void Close();
        Row* GetCurrentRow();
        int GetRowCount();
        int Next();

     private:
        int rowCount;
        T_RESULT* result;
        Row* currentRow;
};

#endif //RESULT_H

Result.cpp

#include "Result.h"

Result::Result(T_RESULT* res)
:rowCount(-1),result(res),currentRow((Row*)NULL)
{}

Result::~Result()
{
    Close();
}

int Result::Next()
{
    T_ROW row;

    if(result==(T_RESULT*)NULL)
        throw "Result set closed.";
#if defined(HAS_MSQL)
    row=msqlFetchRow(result);
#elif defined(HAS_MYSQL)
    row=mysql_fetch_row(result);
#else
    #error No database linked.
#endif
    if(!row)
    {
        currentRow=(Row*)NULL;
        return 0;
    }
    else
    {
        currentRow=new Row(result,row);
        return 1;
    }
}

Row* Result::GetCurrentRow()
{
    if(result==(T_RESULT*)NULL)
        throw "Result set closed.";

    return currentRow;
}

void Result::Close()
{
    if(result==(T_RESULT*)NULL)
        return;

#if defined(HAS_MSQL)
    msqlFreeResult(result);
#elif defined(HAS_MYSQL)
    mysql_free_result(result);
#else
    #error No database linked.
#endif
    result=(T_RESULT*)NULL;
}

int Result::GetRowCount()
{
    if(result==(T_RESULT*)NULL)
        throw "Result set closed.";

    #if defined(HAS_MSQL)
        rowCount=msqlNumRows(result);
    #elif defined(HAS_MYSQL)
        rowCount=mysql_num_rows(result);
    #else
        #error No database linked.
    #endif
    if(rowCount>-1)
        return rowCount;
    else
    return 0;
}

Row.h

#ifndef ROW_H
#define ROW_H

#include <sys/types.h>
#include <malloc.h>
#define HAS_MYSQL 1

#if defined(HAS_MSQL)
#include <msql.h>
#define T_RESULT m_result
#define T_ROW    m_row
#elif defined(HAS_MYSQL)
#include <mysql.h>
#define T_RESULT MYSQL_RES
#define T_ROW    MYSQL_ROW
#endif

class Row
{
    public:
        Row(T_RESULT* ,T_ROW);
        ~Row();
        
        char* GetField(int index);
        int GetFieldCount();
        int IsClosed() const;
        void Close();

     private:
        T_RESULT* result;
        T_ROW fields;
};

#endif // ROW_H

Row.cpp

#include "Row.h"
#include <malloc.h>

Row::Row(T_RESULT* res,T_ROW row)
:result(res),fields(row)
{}

Row::~Row()
{
    if(!IsClosed())
        Close();
}

void Row::Close()
{
    if(IsClosed())
        throw "Row closed.";

    fields=(T_ROW)NULL;
    result=(T_RESULT*)NULL;
}

int Row::GetFieldCount()
{
    if(IsClosed())
        throw "Row closed.";

#if defined(HAS_MSQL)
    return msqlNumFields(result);
#elif defined(HAS_MYSQL)
    return mysql_num_fields(result);
#else
    #error No database linked.
#endif
}

char* Row::GetField(int field)
{
    if(IsClosed())
        throw "Row closed.";

    if(field<0 || field>GetFieldCount()-1)
        throw "Field index out of bounds.";

    return fields[field];
}

int Row::IsClosed() const
{
    return (fields==(T_ROW)NULL);
}

（三）测试程序

#include <stdio.h>
#include <stdlib.h>
#include "Connection.h"

#define HAS_MYSQL 1

int main()
{
    try
    {
        Connection mysqlConnection("localhost","TestDataBase",
                                   "lei","123456");
        Result* queryResult=NULL;
        Row* row=NULL;
        int affectedRows=0;

        mysqlConnection.Query("INSERT INTO Student\
                               VALUES('2007','Jay',27);");
        affectedRows=mysqlConnection.GetAffectedRows();
        printf("Affected rows: %d row(s)\n",affectedRows);

        queryResult=mysqlConnection.Query("SELECT * FROM Student;");
        while(queryResult->Next())
        {
            row=queryResult->GetCurrentRow();
            printf("StudentID: %s\n",row->GetField(0));
            printf("StudentName: %s\n",row->GetField(1));
            printf("Age: %s\n",row->GetField(2));

            delete row;
        }
        printf("Total rows: %d row(s)\n",queryResult->GetRowCount());
        queryResult->Close();
        delete queryResult;
    }
    catch(const char* exceptionString)
    {
        printf("%s\n",exceptionString);
    }

    return EXIT_SUCCESS;
}

运行结果：

（四）利用智能指针改进程序

　　在上面的main函数中，可以看到，由于Connection::Query()和Connection::GetCurrentow()都返回了一个指针，而这些指针都需要用户自己释放，有时我们可能会忘了delete这些指针，又或者执行到某一条语句时（如row=queryResult->GetCurrentRow();）出现异常，使得delete Result和delete Row都得不到执行，最后指针Result和Row指向的内存空间没有释放。

　　那有什么办法可以在出现异常后释放指针指向的空间呢？第一种方法可以是自己在catch里面添加代码，释放内存后再让程序退出。还有一个方法是利用一个对象包装指针，达到自动释放内存的效果，这个对象就是智能指针了，好像在标准库和boost库中都有这样的东西（在boost库中是shared_ptr），这里我用以前自己写的一个类来实现，代码见我以前的一篇博文：auto_ptr的实现

　　修改后的main.cpp如下：

#include <stdio.h>
#include <stdlib.h>
#include "Connection.h"
#include "auto_ptr.h"

#define HAS_MYSQL 1

int main()
{
    try
    {
        Connection mysqlConnection("localhost","TestDataBase",
                                   "lei","123456");

        auto_ptr<Result> queryResult;
        auto_ptr<Row> row;
        int affectedRows=0;

        mysqlConnection.Query("INSERT INTO Student\
                               VALUES('2007','Jay',27);");
        affectedRows=mysqlConnection.GetAffectedRows();
        printf("Affected rows: %d row(s)\n",affectedRows);

        queryResult.reset(mysqlConnection.Query("SELECT * FROM Student;"));
        while(queryResult->Next())
        {
            row.reset(queryResult->GetCurrentRow());
            printf("StudentID: %s\n",row->GetField(0));
            printf("StudentName: %s\n",row->GetField(1));
            printf("Age: %s\n",row->GetField(2));
        }
        printf("Total rows: %d row(s)\n",queryResult->GetRowCount());
        queryResult->Close();
    }
    catch(const char* exceptionString)
    {
        printf("%s\n",exceptionString);
    }

    return EXIT_SUCCESS;
}

由于此时queryResult和row已经不是原始的指针了，而是一个完整的对象，所以程序结束时他们的析构函数会得到调用，从而自动调用delete，释放原始的指针（当然调用reset函数时也释放了指针）。如果在Result类和Row类的析构函数中打印出一条信息，可以看到这些类析构函数被调用了。

　　最后，对于MySQL还有很多很多东西没涉及到，还要继续学习……

posted @ 2012-11-20 17:35 _Lei 阅读(4590) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

_Lei

C++ 连接MySQL

公告