QT数据库连接管理类

使用单例管理整个项目的数据库连接。

在QT中不同线程须使用不同的数据库实例。

MySql有连接超时——超过设定（默认8小时）没有活动会关闭连接。

一、头文件

#pragma once
#ifndef CSQLDATABASE_H
#define CSQLDATABASE_H

#include <QtSql>
#include <QString>
#include <QMutex>
#include <QMutexLocker>
#include <QWaitCondition>
#include <QQueue>
#include <QList>
#include <QSet>
#include <QVariant>

class CSQLDatabase;
typedef std::shared_ptr<CSQLDatabase> CSQLDatabasePtr;


class CSQLDatabase
{
public:
    /*
     * 单例模式获取
     * 参数：ip，用户名，用户密码，数据库名，数据库类型，端口号
     */
    static CSQLDatabase* GetInstance();
    //参数设置，仅执行一次
    void                initParas(QString qsHostName, QString qsUser, QString qsPassWd,
        QString qsDatabase, QString qsDatabaseType, int nPort
        );
    bool                ExecuteStmt(QString qsSql); //用于增删改
    bool                ExecuteStmt(QString qsSql,QList<QVariant> bindList); //用于增删改
    QSqlQuery           ExecuteQuery(QString qsSql);//用于查询
    QSqlQuery           ExecuteQuery(QString qsSql, QList<QVariant> bindList);//用于查询

    ~CSQLDatabase();

private:
    CSQLDatabase();

    QSqlDatabase    OpenConnection();
    //void            CloseConnection(QSqlDatabase connection);
    QSqlDatabase    CreateConnect(const QString& qsConnName);

    bool            IsOpen(QSqlDatabase db);

    class GC
    {
    public:
        ~GC()
        {
            // 可以在这里销毁所有的资源，例如：db 连接、文件句柄等
            if (m_pInstance) {
                //std::cout << "Here destroy the m_pSingleton..." << endl;
                delete m_pInstance;
                m_pInstance = nullptr;
            }
        }
        static GC gc;  // 用于释放单例
    };
private:
    int m_nPort;

    QString m_qsHostName;
    QString m_qsUser;
    QString m_qsPassWd;
    QString m_qsDatabase;
    QString m_qsDatabaseType;



    QSet<QString> m_setThreadConnNames;


    static QMutex m_Mutex;
    static CSQLDatabase* m_pInstance;
};

#endif // CSQLDATABASE_H

二、CPP

#include "CSQLDatabase.h"
#include <QThread>
//初始化静态变量
QMutex CSQLDatabase::m_Mutex;
CSQLDatabase* CSQLDatabase::m_pInstance = nullptr;
CSQLDatabase::GC CSQLDatabase::GC::gc; // 重要


CSQLDatabase* CSQLDatabase::GetInstance()
{
    //双重检测加锁
    if (!m_pInstance) {
        QMutexLocker locker(&m_Mutex);
        if (!m_pInstance)
            m_pInstance= new CSQLDatabase();
    }
    return m_pInstance;
}

CSQLDatabase::CSQLDatabase()    
{
}

CSQLDatabase::~CSQLDatabase()
{
    QMutexLocker locker(&m_Mutex);    
    for (auto s : m_setThreadConnNames) {
        QSqlDatabase::removeDatabase(s);
    }
    
    m_pInstance = nullptr;
}

QSqlDatabase CSQLDatabase::OpenConnection()
{
    QMutexLocker locker(&m_Mutex);
    quint32 nThread =(quint32) QThread::currentThread();
    QString qsConnName=QString("Connection-Thread-%1").arg((qint32)QThread::currentThread());
    qDebug() << qsConnName << " OpenConnection()  " << QDateTime::currentDateTime().toString("hh:mm:ss.zzz");
    

    if (!m_setThreadConnNames.contains(qsConnName)) {
        m_setThreadConnNames.insert(qsConnName);
    }
    QSqlDatabase db = CreateConnect(qsConnName);

    return db;
}

//void CSQLDatabase::CloseConnection(QSqlDatabase connection)
//{
//    //关闭连接
//}

QSqlDatabase CSQLDatabase::CreateConnect(const QString &qsConnName)
{
    //查看该连接名是否已经创建过连接
    if (QSqlDatabase::contains(qsConnName))
    {
        QSqlDatabase db = QSqlDatabase::database(qsConnName);
        return db;
    }

    QSqlDatabase db = QSqlDatabase::addDatabase(m_qsDatabaseType, qsConnName);
    db.setHostName(m_qsHostName);
    db.setDatabaseName(m_qsDatabase);
    db.setUserName(m_qsUser);
    db.setPassword(m_qsPassWd);
    db.setPort(m_nPort);

    if (!db.open())
    {
        qDebug() << "Open sql error" << db.lastError().text();
        return QSqlDatabase();
    }

    return db;
}

bool CSQLDatabase::IsOpen(QSqlDatabase db)
{
    bool bRes = db.isOpen();//初步判断，这个不准！！！！
    if (bRes) {
        QSqlQuery q(db);
        bRes = q.exec("SELECT 1;");//这个准
        if (!bRes) {//可能超时断开
            qDebug() << "SELECT 1;" << bRes;
            bRes = db.open();//重新连接
            QSqlQuery q(db);
            bRes = q.exec("SELECT 2;");//看看是否连接成功
            qDebug() << "SELECT 2;" << bRes;
        }
    }
    return bRes;
}

void CSQLDatabase::initParas(QString qsHostName, QString qsUser, QString qsPassWd, 
    QString qsDatabase, QString qsDatabaseType, 
    int nPort)
{
    m_qsHostName = qsHostName;
    m_qsUser = qsUser;
    m_qsPassWd=qsPassWd;
    m_qsDatabase=qsDatabase;
    m_qsDatabaseType=qsDatabaseType;
    m_nPort=nPort;

    ExecuteStmt("show TABLES;");
}

bool CSQLDatabase::ExecuteStmt(QString qsSql)
{
    QSqlDatabase db = OpenConnection();
    //if (!db.isOpen())
    if(!IsOpen(db))
        return false;

    QSqlQuery query = db.exec(qsSql);
    //CloseConnection(db);

    if (query.lastError().isValid())
    {
        qDebug() << "Sql error:" << query.lastError();
        return false;
    }

    return true;
}

bool CSQLDatabase::ExecuteStmt(QString qsSql, QList<QVariant> bindList)
{
    QSqlDatabase db = OpenConnection();
    
    if (!IsOpen(db))
        return false;

    bool bRes = false;
    QSqlQuery query = QSqlQuery(qsSql, db);
    int n = 0;
    for (auto v : bindList) {
        query.bindValue(n++, v);
    }
    bRes=query.exec();
    //CloseConnection(db);

    if (query.lastError().isValid())
    {
        qDebug() << "Sql error:" << query.lastError();
        return false;
    }

    return bRes;
}

QSqlQuery CSQLDatabase::ExecuteQuery(QString qsSql)
{
    QSqlDatabase db = OpenConnection();    
    if (!IsOpen(db))
        return QSqlQuery();

    QSqlQuery query = db.exec(qsSql);
    //CloseConnection(db);
    
    if (query.lastError().isValid())
    {
        qDebug() << "Sql error:" << query.lastError();
        return QSqlQuery();
    }

    return query;
}

QSqlQuery CSQLDatabase::ExecuteQuery(QString qsSql, QList<QVariant> bindList)
{
    QSqlDatabase db = OpenConnection();
    if (!IsOpen(db))
        return QSqlQuery();

    QSqlQuery query= QSqlQuery(qsSql,db);
    int n = 0;
    for (auto v : bindList) {
        query.bindValue(n++, v);
    }
    bool bRes=query.exec();

    //CloseConnection(db);

    if (query.lastError().isValid())
    {
        qDebug() << "Sql error:" << query.lastError();
        return QSqlQuery();
    }
    
    return query;
}

 

三、使用

先初始化：

CSQLDatabase::GetInstance()->initParas(sHost, sUserName, sPassword, sDbName, sDbType, nDbPort);