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;
};

CPP 文件

    #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 实际上设置为正确的文件/参数。它基于此处的解决方案 http://www.linuxjournal.com/article/9602。 mMessage 类是一个 qObject，它也可以正确传递给 m_worker 对象。

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

Answer 1

你很接近。

由于您在 addDatabase() 中为数据库指定了名称（“WorkerDatabase”），因此生成的连接不是应用程序默认连接，并且 QSqlDatabase() 不会返回该连接。

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

m_accelerometerQuery = new QSqlQuery(m_database);

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

m_accelerometerQuery = new QSqlQuery("WorkerDatabase");

是：

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

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