QSqlDatabase的进一步封装（多线程支持+更加简单的操作）——同时支持MySQL, SQL Server和Sqlite

开发背景：

1.直接用QSqlDatabase我觉得太麻烦了；

2.对于某些数据库，多个线程同时使用一个QSqlDatabase的时候会崩溃；

3.这段时间没什么干货放出来觉得浑身不舒服，就想写一个。

于是，我就封装了一下

只要简单的实例化，然后通过query()就可以拿到QSqlQuery的实例化对象。

还自带计时，一段时间不用可自动关闭，既保证效率也不占用无用资源。

注：需要C++11的支持

不多说，上代码：

JasonQt_Database.h

#ifndef __JasonQt_Database_h__
#define __JasonQt_Database_h__

// C++ lib import
#include <functional>

// Qt lib import
#include <QtCore>
#include <QtSql>

#define PropertyDeclare(Type, Name, setName, ...)
    private:
    Type m_ ## Name __VA_ARGS__;
    public:
    inline const Type &Name(void) const { return m_ ## Name; }
    inline void setName(const Type &Name) { m_ ## Name = Name; }
    private:

namespace JasonQt_Database
{

enum DatabaseModeEnum{ DatabaseNameMode, DatabaseHostMode };

enum QueryMode { QueryAutoMode, QueryMultiMode, QuerySingleMode };

class DatabaseSettings
{
private:
    PropertyDeclare(DatabaseModeEnum, databaseMode, setDatabaseMode)

    PropertyDeclare(QString, databaseType, setDatabaseType)
    PropertyDeclare(QString, connectionName, setConnectionName)

    // File mode
    PropertyDeclare(QString, nameModeName, setNameModeName)

    // Host mode
    PropertyDeclare(QString, hostModeHostName, setHostModeHostName)
    PropertyDeclare(QString, hostModeDatabaseName, setHostModeDatabaseName)
    PropertyDeclare(QString, hostModeUserName, setHostModeUserName)
    PropertyDeclare(QString, hostModePassword, setHostModePassword)

private:
    DatabaseSettings(const DatabaseModeEnum &databastMode, const QString &databaseType, const QString &connectionName);

public:
    DatabaseSettings(const QString &databaseType, const QString &connectionName, const QString &nameModeName);

    DatabaseSettings(const QString &databaseType, const QString &connectionName, const QString &hostModeHostName, const QString &hostModeDatabaseName, const QString &hostModeUserName, const QString &hostModePassword);
};

class ConnectSettings
{
private:
    PropertyDeclare(int, maxOpenTime, setMaxOpenTime)
    PropertyDeclare(QueryMode, queryMode, setQueryMode)
    PropertyDeclare(int, minWaitTime, setMinWaitTime)

public:
    ConnectSettings(const int &maxOpenTime = 60 * 1000, const QueryMode &queryMode = QueryAutoMode, const int &minWaitTime = -1);
};

class Query
{
private:
    QSqlQuery *m_query;
    QMutex *m_mutex = NULL;

public:
    Query(QSqlDatabase &dataBase, QMutex *mutex = NULL);

    Query(Query &&other);

    ~Query(void);

    inline QSqlQuery *operator->(void) { return m_query; }

    inline QSqlQuery *operator*(void) { return m_query; }

    QSqlQuery *takeQuery(void);

    QMutex *takeMutex(void);
};

class ConnectNode: public QObject
{
     Q_OBJECT

private:
    QSqlDatabase *m_database = NULL;
    QString m_connectionName;

    DatabaseSettings m_dataBaseSettings;
    ConnectSettings m_connectSettings;

    QTimer *m_autoClose = NULL;
    QMutex *m_mutex = NULL;

public:
    ConnectNode(const DatabaseSettings &dataBaseSettings, const ConnectSettings &connectSettings);

    ~ConnectNode(void);

public:
    Query query(void);

public slots:
    bool addDataBase(void);

    void removeDataBase(void);

    bool open(void);

    void close(void);

signals:
    void controlStartAutoClose(void);

    void controlStopAutoClose(void);
};

class Control
{
private:
    DatabaseSettings m_databaseSettings;
    ConnectSettings m_connectSettings;

    QMap<qint64, ConnectNode *> m_node;

    QMutex m_mutex;
    QTime *m_wait = NULL;

public:
    Control(const DatabaseSettings &databaseSettings, const ConnectSettings &connectSettings = ConnectSettings());

    Control(const Control &) = delete;

    ~Control(void);

public:
    void removeAll(void);

    Query query(void);

private:
    void insert(const qint64 &key);
};

}

#endif//__JasonQt_Database_h__

JasonQt_Database.cpp

#include "JasonQt_Database.h"

using namespace JasonQt_Database;

// DatabaseSettings
DatabaseSettings::DatabaseSettings(const DatabaseModeEnum &databastMode, const QString &databaseType, const QString &connectionName)
{
    m_databaseMode = databastMode;
    m_databaseType = databaseType;
    m_connectionName = connectionName;
}

DatabaseSettings::DatabaseSettings(const QString &databaseType, const QString &connectionName, const QString &nameModeName):
    DatabaseSettings(DatabaseNameMode, databaseType, connectionName)
{
    m_nameModeName = nameModeName;
}

DatabaseSettings::DatabaseSettings(const QString &databaseType, const QString &connectionName, const QString &hostModeHostName, const QString &hostModeDatabaseName, const QString &hostModeUserName, const QString &hostModePassword):
    DatabaseSettings(DatabaseHostMode, databaseType, connectionName)
{
    m_hostModeHostName = hostModeHostName;
    m_hostModeDatabaseName = hostModeDatabaseName;
    m_hostModeUserName = hostModeUserName;
    m_hostModePassword = hostModePassword;
}

// ConnectSettings
ConnectSettings::ConnectSettings(const int &maxOpenTime, const QueryMode &queryMode, const int &minWaitTime)
{
    m_maxOpenTime = maxOpenTime;
    m_queryMode = queryMode;
    m_minWaitTime = minWaitTime;
}

// Query
Query::Query(QSqlDatabase &dataBase, QMutex *mutex):
    m_query(new QSqlQuery(dataBase))
{
    m_mutex = mutex;
}

Query::Query(Query &&other)
{
    m_query = other.takeQuery();
    m_mutex = other.takeMutex();
}

Query::~Query(void)
{
    if(m_query)
    {
        delete m_query;
    }
    if(m_mutex)
    {
        m_mutex->unlock();
    }
}

QSqlQuery *Query::takeQuery(void)
{
    auto buf = m_query;
    m_query = NULL;
    return buf;
}

QMutex *Query::takeMutex(void)
{
    auto buf = m_mutex;
    m_mutex = NULL;
    return buf;
}

// ConnectNode
ConnectNode::ConnectNode(const DatabaseSettings &dataBaseSettings, const ConnectSettings &connectSettings):
    m_dataBaseSettings(dataBaseSettings),
    m_connectSettings(connectSettings)
{
    m_connectionName = QString("%1(%2)").arg(m_dataBaseSettings.connectionName()).arg(QString::number(qint64(QThread::currentThread()), 16));

    m_mutex = new QMutex(QMutex::Recursive);

    if(m_connectSettings.maxOpenTime())
    {
        m_autoClose = new QTimer;
        m_autoClose->setSingleShot(true);
        m_autoClose->setInterval(m_connectSettings.maxOpenTime());
        m_autoClose->moveToThread(qApp->thread());
        m_autoClose->setParent(qApp);

        connect(m_autoClose, SIGNAL(timeout()), this, SLOT(close()), Qt::DirectConnection);
        connect(this, SIGNAL(controlStartAutoClose()), m_autoClose, SLOT(start()));
        connect(this, SIGNAL(controlStopAutoClose()), m_autoClose, SLOT(stop()));
    }

    this->addDataBase();
}

ConnectNode::~ConnectNode(void)
{
    if(m_mutex){ m_mutex->lock(); }

    if(m_autoClose)
    {
        m_autoClose->deleteLater();
    }

    this->removeDataBase();

    if(m_mutex){ m_mutex->unlock(); }
    if(m_mutex){ delete m_mutex; }
}

Query ConnectNode::query(void)
{
    if(!m_database)
    {
        this->addDataBase();
    }

    if(!m_database->isOpen())
    {
        m_database->open();
    }

    if(m_mutex){ m_mutex->lock(); }

    Query buf(*m_database, m_mutex);
    emit controlStartAutoClose();
    return buf;
}

bool ConnectNode::addDataBase(void)
{
    if(m_mutex){ m_mutex->lock(); }

    if(m_database)
    {
        this->removeDataBase();
    }

    m_database = new QSqlDatabase(QSqlDatabase::addDatabase(m_dataBaseSettings.databaseType(), m_connectionName));

    switch(m_dataBaseSettings.databaseMode())
    {
        case DatabaseNameMode:
        {
            m_database->setDatabaseName(m_dataBaseSettings.nameModeName());
            break;
        }
        case DatabaseHostMode:
        {
            m_database->setHostName(m_dataBaseSettings.hostModeHostName());
            m_database->setDatabaseName(m_dataBaseSettings.hostModeDatabaseName());
            m_database->setUserName(m_dataBaseSettings.hostModeUserName());
            m_database->setPassword(m_dataBaseSettings.hostModePassword());
            break;
        }
        default:
        {
            if(m_mutex){ m_mutex->unlock(); }
            return false;
        }
    }

    const auto &&flag = this->open();

    if(m_mutex){ m_mutex->unlock(); }

    return flag;
}

void ConnectNode::removeDataBase(void)
{
    if(m_mutex){ m_mutex->lock(); }

    delete m_database;
    m_database = NULL;
    QSqlDatabase::removeDatabase(m_connectionName);

    if(m_mutex){ m_mutex->unlock(); }
}

bool ConnectNode::open(void)
{
    if(!m_database)
    {
        this->addDataBase();
    }

    if(m_mutex){ m_mutex->lock(); }

    emit controlStartAutoClose();
    const auto &&Flag = m_database->open();

    if(m_mutex){ m_mutex->unlock(); }

    return Flag;
}

void ConnectNode::close(void)
{
    if(m_mutex)
    {
        if(m_mutex->tryLock())
        {
            m_mutex->unlock();
            emit controlStopAutoClose();
            m_database->close();
        }
        else
        {
            emit controlStartAutoClose();
        }
    }
    else
    {
        emit controlStopAutoClose();
        m_database->close();
    }
}

// Control
Control::Control(const DatabaseSettings &databaseSettings, const ConnectSettings &connectSettings):
    m_databaseSettings(databaseSettings),
    m_connectSettings(connectSettings)
{
    if(m_connectSettings.queryMode() == QueryAutoMode)
    {
        if(databaseSettings.databaseType() == "QMYSQL")
        {
            m_connectSettings.setQueryMode(QueryMultiMode);
        }
        else if(databaseSettings.databaseType() == "QODBC")
        {
            m_connectSettings.setQueryMode(QueryMultiMode);
        }
        else
        {
            m_connectSettings.setQueryMode(QuerySingleMode);
        }
    }
    if(m_connectSettings.queryMode() == QuerySingleMode)
    {
        this->insert(qint64(QThread::currentThread()));
    }

    if(m_connectSettings.minWaitTime() == -1)
    {
        if(databaseSettings.databaseType() == "QMYSQL")
        {
            m_connectSettings.setMinWaitTime(0);
        }
        else if(databaseSettings.databaseType() == "QODBC")
        {
            m_connectSettings.setMinWaitTime(0);
        }
        else
        {
            m_connectSettings.setMinWaitTime(5);
            m_wait = new QTime;
            m_wait->start();
        }
    }
    else
    {
        m_wait = new QTime;
        m_wait->start();
    }
}

Control::~Control(void)
{
    for(auto &now: m_node)
    {
        now->deleteLater();
    }
    if(m_wait)
    {
        delete m_wait;
    }
}

void Control::removeAll(void)
{
    m_mutex.lock();

    for(auto &Now: m_node)
    {
        Now->removeDataBase();
    }

    m_mutex.unlock();
}

Query Control::query(void)
{
    if(m_wait)
    {
        const auto &&flag = m_connectSettings.minWaitTime() - m_wait->elapsed();
        m_wait->restart();
        if(flag > 0)
        {
            QThread::msleep(flag);
        }
    }

    if(m_connectSettings.queryMode() == QueryMultiMode)
    {
        m_mutex.lock();

        const auto &¤tThread = qint64(QThread::currentThread());
        const auto &&now = m_node.find(currentThread);
        if(now != m_node.end())
        {
            auto buf((*now)->query());

            m_mutex.unlock();
            return buf;
        }
        else
        {
            this->insert(qint64(QThread::currentThread()));

            m_mutex.unlock();
            return this->query();
        }
    }
    else
    {
        return (*m_node.begin())->query();
    }
}

void Control::insert(const qint64 &key)
{
    m_node[key] = new ConnectNode(m_databaseSettings, m_connectSettings);
}

使用：

// Qt lib import
#include <QCoreApplication>
#include <QtConcurrent>
#include <QSqlError>

// JasonQt lib import
#include "JasonQt/JasonQt_Database.h"

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    /*
     * 注：关于附加参数
     * 这是可以不写的，如果要写的话，可以参考这个：
     *
     * 单次打开数据库最大时间：也就是最大open的时间，对于某些数据库，长时间open但不使用，不仅会造成资源浪费还会意外断开。在设置了60 * 1000后，且60秒内未进行查询，就自动断开。
     * 多线程支持：简单的说就是高级点的数据库，比如 MySql 写 JasonQt_Database::QueryMultiMode ；低级的，比如 Sqlite ，写 JasonQt_Database::QuerySingleMode ,就可以了。
     * 最小等待时间：对于某些数据库，比如Sqlite，密集查询时可能出错，此时可以适当的提升两次查询之间的最小等待时间，比如10ms
     */

    // Sqlite的连接方式                    类型        连接名        Sqlite文件路径      单次打开数据库最大时间                多线程支持           最小等待时间
    JasonQt_Database::Control control({ "QSQLITE", "TestDB", "/Users/Jason/test.db" }, { 60 * 1000,        JasonQt_Database::QuerySingleMode, 10});

    // MySql的连接方式                      类型      连接名        IP        数据库    用户名        密码
//  JasonQt_Database::Control control({ "QMYSQL", "TestDB", "localhost", "JasonDB", "root", "YourPassword" });


    // SqlServer的连接方式                  类型      连接名                                    数据库              数据库名   用户名         密码
//  JasonQt_Database::Control control({ "QODBC", "TestDB", "Driver={SQL SERVER};server=iZ23kn6vmZ\TEST;database=test;uid=sa;pwd=YourPassword;" });

    auto insert = [&]()
    {
        auto query(control.query()); // 这里的query在解引用（ -> 或者 * ）后返回的是 QSqlQuery ，直接用就可以了，不需要单独打开数据库或者其他的初始化

        query->prepare("insert into Test1 values(?)"); // 模拟插入操作

        query->addBindValue(rand() % 1280);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }

        query->clear();

        query->prepare("insert into Test2 values(NULL, ?, ?)");

        query->addBindValue(rand() % 1280);
        QString buf;
        for(int now = 0; now < 50; now++)
        {
            buf.append('a' + (rand() % 26));
        }
        query->addBindValue(buf);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }
    };
    auto delete_ = [&]()
    {
        auto query(control.query());

        query->prepare("delete from Test1 where data = ?");

        query->addBindValue(rand() % 1280);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }

        query->clear();

        query->prepare("delete from Test2 where data1 = ?");

        query->addBindValue(rand() % 1280);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }
    };
    auto update = [&]()
    {
        auto query(control.query());

        query->prepare("update Test1 set data = ? where data = ?");

        query->addBindValue(rand() % 1280);
        query->addBindValue(rand() % 1280);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }

        query->clear();

        query->prepare("update Test2 set data1 = ?, data2 = ? where data1 = ?");

        query->addBindValue(rand() % 1280 + 1);
        QString buf;
        for(int now = 0; now < 50; now++)
        {
            buf.append('a' + (rand() % 26));
        }
        query->addBindValue(buf);
        query->addBindValue(rand() % 1280 + 1);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }
    };
    auto select = [&]()
    {
        auto query(control.query());

        query->prepare("select * from Test1 where data = ?");

        query->addBindValue(rand() % 1280);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }

        query->clear();

        query->prepare("select * from Test2 where data1 = ?");

        query->addBindValue(rand() % 1280);

        if(!query->exec())
        {
            qDebug() << "Error" << __LINE__;
        }
    };

    volatile int count = 0, last = 0;

    QTime time;
    time.start();

    QThreadPool::globalInstance()->setMaxThreadCount(10);

    for(int now = 0; now < 3; now++) // 开启3个线程测试
    {
        QtConcurrent::run([&]()
        {
            while(true)
            {
                count++;
                if(!(count % 1000))
                {
                    const auto &&now = time.elapsed();
                    qDebug() << now - last; // 打印每完成1000语句的时间
                    last = now;
                }

                switch(rand() % 20)
                {
                    case 0:  { insert(); break; }
                    case 1:  { delete_(); break; }
                    case 2:  { update(); break; }
                    default: { select(); break; }
                }
            }
            QThread::msleep(10);
        });
    }

    return a.exec();
}

我也写了一个示例工程，可以前往这里下载

http://download.csdn.net/detail/wsj18808050/8566497

http://blog.csdn.net/wsj18808050/article/details/44891715