Qt编写数据可视化大屏界面电子看板12-数据库采集

#include "datadb.h"
#include "quiwidget.h"

QScopedPointer<DataDb> DataDb::self;
DataDb *DataDb::Instance()
{
    if (self.isNull()) {
        static QMutex mutex;
        QMutexLocker locker(&mutex);
        if (self.isNull()) {
            self.reset(new DataDb);
        }
    }

    return self.data();
}

DataDb::DataDb(QObject *parent) : QThread(parent)
{
    stopped = false;

    dbOkLocal = false;
    errorCount = 0;
    lastCheckTimeLocal = QDateTime::currentDateTime();

    checkConn = false;
    checkInterval = 30;

    dbTypeLocal = DbType_MySql;
    connNameLocal = "qt_sql_default_connection";
    hostNameLocal = "127.0.0.1";
    portLocal = 3306;
    dbNameLocal = "db_mysql";
    userNameLocal = "root";
    userPwdLocal = "root";

    connect(this, SIGNAL(error(QString)), this, SLOT(doError(QString)));
}

DataDb::~DataDb()
{
    this->stop();
    this->wait();
}

void DataDb::run()
{
    //打开数据库
    openDb();
    msleep(1000);

    while(!stopped) {
        //定时执行一次校验数据库连接是否正常
        QDateTime now = QDateTime::currentDateTime();
        if (checkConn && lastCheckTimeLocal.secsTo(now) >= checkInterval) {
            checkDb();
            lastCheckTimeLocal = now;
            continue;
        }

        //如果数据库连接正常则处理数据,不正常则重连数据库
        if (!dbOkLocal) {
            if (errorCount >= 3) {
                closeDb();
                msleep(3000);
                openDb();
                emit debug(QString("重连本地数据库%1").arg(dbOkLocal ? "成功" : "失败"));
                msleep(3000);
            }
        } else {
            //取出队列sql语句执行
            if (tags.count() > 0) {
                //从最前面开始取
                QMutexLocker locker(&mutex);
                QString tag = tags.takeFirst();
                QString sql = sqls.takeFirst();
                //qDebug() << TIMEMS << tag << sql;

                QSqlQuery query(dbConnLocal);
                if (query.exec(sql)) {
                    QStringList data;
                    while(query.next()) {
                        int count = query.record().count();
                        for (int i = 0; i < count; i++) {
                            data << query.value(i).toString();
                        }
                    }

                    emit receiveData(tag, data);
                }
            }

            msleep(10);
        }
    }

    stopped = false;
}

void DataDb::doError(const QString &msg)
{
    QUIHelper::showMessageBoxError(msg, 3);
}

bool DataDb::openDb()
{
    //可以自行增加其他数据库的支持
    if (dbTypeLocal == DbType_Sqlite) {
        dbConnLocal = QSqlDatabase::addDatabase("QSQLITE", connNameLocal);
        dbConnLocal.setDatabaseName(dbNameLocal);
    } else if (dbTypeLocal == DbType_MySql) {
        dbConnLocal = QSqlDatabase::addDatabase("QMYSQL", connNameLocal);
        dbConnLocal.setConnectOptions("MYSQL_OPT_RECONNECT=1;MYSQL_OPT_CONNECT_TIMEOUT=1;MYSQL_OPT_READ_TIMEOUT=1;MYSQL_OPT_WRITE_TIMEOUT=1");
        dbConnLocal.setHostName(hostNameLocal);
        dbConnLocal.setPort(portLocal);
        dbConnLocal.setDatabaseName(dbNameLocal);
        dbConnLocal.setUserName(userNameLocal);
        dbConnLocal.setPassword(userPwdLocal);
    }

    errorCount = 0;
    dbOkLocal = dbConnLocal.open();
    if (!dbOkLocal) {
        emit error("打开数据库失败!请检查用户名和密码是否正确!");
    }

    return dbOkLocal;
}

bool DataDb::checkDb()
{
    QDateTime dtStart = QDateTime::currentDateTime();

    QString sql = "select 1";
    QSqlQuery query(dbConnLocal);
    dbOkLocal = query.exec(sql);
    dbOkLocal ? (errorCount = 0) : errorCount++;

    QDateTime dtEnd = QDateTime::currentDateTime();
    double ms = dtStart.msecsTo(dtEnd);
    emit debug(QString("检查本地数据库连接(共 %1 条/用时 %2 秒)").arg(1).arg(QString::number(ms / 1000, 'f', 3)));

    return dbOkLocal;
}

void DataDb::closeDb()
{
    dbConnLocal.close();
    QSqlDatabase::removeDatabase(connNameLocal);
    dbOkLocal = false;
    emit debug("关闭本地数据库");
}

void DataDb::stop()
{
    stopped = true;
}

void DataDb::setConnInfo(DataDb::DbType dbType, const QString &connName,
                         const QString &hostName, int port, const QString &dbName,
                         const QString &userName, const QString &userPwd)
{
    this->dbTypeLocal = dbType;
    this->connNameLocal = connName;
    this->hostNameLocal = hostName;
    this->portLocal = port;
    this->dbNameLocal = dbName;
    this->userNameLocal = userName;
    this->userPwdLocal = userPwd;
}

void DataDb::setCheckConn(bool checkConn)
{
    this->checkConn = checkConn;
}

void DataDb::setCheckInterval(int checkInterval)
{
    if (checkInterval > 5 && this->checkInterval != checkInterval) {
        this->checkInterval = checkInterval;
    }
}

QSqlDatabase DataDb::getDb()
{
    return dbConnLocal;
}

bool DataDb::getOk()
{
    return dbOkLocal;
}

void DataDb::select(const QString &tableName, const QString &columnName, bool append)
{
    //超过队列中最大数量限制则无需处理
    QMutexLocker locker(&mutex);
    if (tags.count() < 100) {
        //如果是append表示追加在队列末尾,否则插到最前面优先级最高
        if (append) {
            tags << tableName;
            sqls << QString("select %1 from %2").arg(columnName).arg(tableName);
        } else {
            tags.insert(0, tableName);
            sqls.insert(0, QString("select %1 from %2").arg(columnName).arg(tableName));
        }
    }
}