Qt SQL 多线程数据库查询

Question

我正在构建一个基于Qt的应用程序，用于监视和捕获从串行端口流传输的数据。数据实时绘图，通过TCP发送，并存储到SQLite数据库中。不幸的是，我发现SQLite插入会导致GUI变得无响应，因为我正在主循环的上下文中执行串行数据处理、绘图、TCP传输和数据库插入。我研究了将数据库插入分离到另一个线程的方法，并编写了以下代码。

 #include <QObject>
#include <QDebug>
#include <QStringList>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QObject>
#include <QList>
#include <QThread>
#include <QMutex>
#include <QWaitCondition>
#include <QSqlDatabase>
#include <QSqlRecord>
#include <QString>
#include "mMessage.h"
// The class that does all the work with the database. This class will
// be instantiated in the thread object's run() method.
class Worker : public QObject
{
  Q_OBJECT
   public:
    Worker( QObject* parent = 0);
    ~Worker();
    bool insertADC(mMessage* insertMessage);
  public slots:
    void slotExecute( mMessage* insertMessage );
  signals:
    void queryResult(bool);
   private:
     QSqlDatabase m_database;
     bool m_databaseOpen;
     void prepareQueries();
     QSqlQuery *m_accelerometerQuery;
     QSqlQuery *m_adcQuery;
     QSqlQuery *m_metricsQuery;
     QSqlQuery *m_rssiQuery;
};

class mDatabaseThread : public QThread
{
  Q_OBJECT
  public:
    mDatabaseThread(QObject *parent = 0);
    ~mDatabaseThread();
    void executeInsertion( mMessage* insertMessage );
  signals:
    void progress( const QString& msg );
void ready(bool);
  protected:
    void run();
  signals:
    void executefwd( mMessage* insertMessage );
  private:
     Worker* m_worker;
};

C++文件

    #include "mDatabaseThread.h"
    //

    Worker::Worker( QObject* parent )
        : QObject( parent )
    {
        // thread-specific connection, see db.h
        m_database = QSqlDatabase::addDatabase( "QSQLITE",
                                                "WorkerDatabase" ); // named connection
        m_database.setDatabaseName("trainingX.db3");
        if ( !m_database.open() )
        {
            qWarning() << "Unable to connect to database, giving up:" << m_database.lastError().text();
            m_databaseOpen = false;
            return;
        }
     m_databaseOpen = true;

    }

    Worker::~Worker()
    {
        //close the database
       // m_database.close();
       // m_database.removeDatabase("trainingX.db3");
    }
    void Worker::prepareQueries()
    {
        if (m_databaseOpen)
        {
        m_accelerometerQuery->prepare("INSERT INTO accelerometer (key, timestamp, nickname, unitid,"
                                   "sectorid, acc_x, acc_y, acc_z) "
                                   "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                                   ":acc_x, :acc_y, :acc_z)");

        m_adcQuery->prepare("INSERT INTO adc (key, timestamp, nickname, unitid, sectorid,"
                         "adc0, adc1, adc2, adc3, adc4, adc5, adc6, adc7) "
                         "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                         ":adc0, :adc1, :adc2, :adc3, :adc4, :adc5, :adc6, :adc7)");

        m_metricsQuery->prepare("INSERT INTO metrics (key, timestamp, nickname, unitid, sectorid, "
                             "acc_temp, unit_temp, unit_pressure, bpm_instant, bpm_average, base_pressure, base_temp) "
                             "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                             ":acc_temp, :unit_temp, :unit_pressure, :bpm_instant, :bpm_average, :base_pressure, :base_temp)");

        m_rssiQuery->prepare("INSERT INTO rssi (key, timestamp, nickname, unitid, sectorid, "
                          "rssi_1, rssi_2, rssi_3, rssi_4, rssi_avg) "
                          "VALUES (NULL, :timestamp, :nickname, :unitid, :sectorid,"
                          ":rssi_1, :rssi_2, :rssi_3, :rssi_4, :rssi_avg)");
        }
    }

    void Worker::slotExecute( mMessage* insertMessage )
    {
        m_accelerometerQuery = new QSqlQuery("WorkerDatabase");
        m_adcQuery = new QSqlQuery("WorkerDatabase");
        m_metricsQuery = new QSqlQuery("WorkerDatabase");
        m_rssiQuery = new QSqlQuery("WorkerDatabase");

        prepareQueries();

        insertADC(insertMessage);
        //insertRSSI(insertMessage);
        //insertAccelerometer(insertMessage);
        //insertMetrics(insertMessage);
        emit queryResult( true );
    }
bool Worker::insertADC(mMessage *insertMessage)
{
    if (m_databaseOpen)
    {
           // m_adcQuery->bindValue(":key",0);
            m_adcQuery->bindValue(":timestamp",insertMessage->m_timestamp);
            m_adcQuery->bindValue(":nickname",insertMessage->m_nickname);
            m_adcQuery->bindValue(":unitid",insertMessage->m_unitId.toInt());
            m_adcQuery->bindValue(":sectorid",insertMessage->m_sectorId.toInt());
            m_adcQuery->bindValue(":adc0",insertMessage->m_adc0.toInt());
            m_adcQuery->bindValue(":adc1",insertMessage->m_adc1.toInt());
            m_adcQuery->bindValue(":adc2",insertMessage->m_adc2.toInt());
            m_adcQuery->bindValue(":adc3",insertMessage->m_adc3.toInt());
            m_adcQuery->bindValue(":adc4",insertMessage->m_adc4.toInt());
            m_adcQuery->bindValue(":adc5",insertMessage->m_adc5.toInt());
            m_adcQuery->bindValue(":adc6",insertMessage->m_adc6.toInt());
            m_adcQuery->bindValue(":adc7",insertMessage->m_adc7.toInt());
            if (m_adcQuery->exec())
            {
                return true;
            }
            else
            {
                qDebug() << "SQL ADC failed.";
                qDebug() << m_adcQuery->lastError();
                return false;
            }
    }
    else
    {
    //database isn't open
    return false;
    }
}
////database thread

mDatabaseThread::mDatabaseThread(QObject *parent)
    : QThread(parent)
{
}
mDatabaseThread::~mDatabaseThread()
{
    delete m_worker;
}

void mDatabaseThread::executeInsertion( mMessage* insertMessage )
{
    emit executefwd( insertMessage ); // forwards to the worker
}

void mDatabaseThread::run()
{
    emit ready(false);
     // Create worker object within the context of the new thread
    m_worker = new Worker();
    connect( this, SIGNAL( executefwd( mMessage* ) ),
             m_worker, SLOT( slotExecute( mMessage* ) ) );
    connect( m_worker, SIGNAL(queryResult(bool)), this, SIGNAL(ready(bool)));
    emit ready(true);

    exec();  // our event loop
}

mDatabaseThread类有一个工作对象，它将数据库工作发送到该对象。信号和槽都能正常工作并正确触发。然而，实际的QSqlQuery出现错误，报告数据库未打开 - 但是当我尝试调试时，我看到m_database实际上已经设置为正确的文件/参数。这是基于此处的解决方案。mMessage类是一个qObject，也被正确地传递给了m_worker对象。

从主类中，在构造函数中调用m_databaseThread->start()，然后调用executeInsertion(mMessage*)函数。我尝试改变初始化m_database的方式以及QSqlQueries，但无论我做什么，它都会抱怨QSqlQuery找不到数据库连接。

- mhilmi

Answer 1

你离正确答案很近。

因为你在 addDatabase() 中给数据库命名了 ("WorkerDatabase")，所以得到的连接不是应用程序默认连接，也不会被 QSqlDatabase() 返回。

因此，你需要将数据库对象传递给你的 QSqlQuery 构造函数：

m_accelerometerQuery = new QSqlQuery(m_database);

您在这里使用的构造函数是：

m_accelerometerQuery = new QSqlQuery("WorkerDatabase");

is:

QSqlQuery (const QString & query = QString(), QSqlDatabase db = QSqlDatabase())

当你将“WorkerDatabase”传递给它时，它被存储为SQL查询，并且由QSqlDatabase()返回的默认（不存在的）数据库被存储。

- Brian Roach

---

现在会尝试一下，谢谢你的提示。我知道这是些愚蠢的事情！ - mhilmi