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

最新推荐文章于 2022-07-07 10:44:08 发布

冷月醉雪

最新推荐文章于 2022-07-07 10:44:08 发布

阅读量1.8k

点赞数

分类专栏： QT 数据库文章标签： QSqlDatabase 多线程支持 MySQL Sqlite SQL Server

本文链接：https://blog.csdn.net/lengyuezuixue/article/details/80839405

版权

数据库同时被 2 个专栏收录

213 篇文章 3 订阅

订阅专栏

111 篇文章 9 订阅

订阅专栏

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

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

注：需要C++11的支持

#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__

.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();

参考：

https://www.yuque.com/docs/share/66f2a342-a462-494f-8406-2b21867979a2