QSqlDatabase在多线程中的使用

Qt中多线程使用数据库_qt数据库管理类支持多数据库,多线程-CSDN博客

1.

代码：

#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>
#include <QPushButton>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDriver>
#include <QThread>
#include <QTextBrowser>
#include <QVBoxLayout>

class Test : public QObject
{
    Q_OBJECT
public:
    Test(QSqlDatabase database)
        :   QObject(nullptr), m_database(database)
    {
    }
signals:
    void query_result(const QString &result);
public slots:
    void test_query()
    {
        m_database.open();
        QString str = "Thread ID: " + QString::number((int)QThread::currentThreadId(), 16) + "  result:";
        QSqlQuery query(m_database);
        query.exec("SELECT * FROM test;");
        while(query.next())
            str += query.value(0).toString() + "|";
        m_database.close();
        emit query_result(str + "\n");
    }
private:
    QSqlDatabase m_database;
};

class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = nullptr)
        :   QWidget(parent)
    {
        QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", "mydb");
        db.setPort(3306);
        db.setDatabaseName("test");
        db.setUserName("root");
        db.setPassword("root_pwd");

        test1 = new Test(db);
        test2 = new Test(db);

        test1->moveToThread(&thread1);
        test2->moveToThread(&thread2);

        thread1.start();
        thread2.start();

        QPushButton *button = new QPushButton("开始", this);
        QTextBrowser *browser = new QTextBrowser(this);
        browser->insertPlainText("UI Thread ID: " + QString::number((int)QThread::currentThreadId(), 16));
        QVBoxLayout *layout = new QVBoxLayout(this);
        layout->addWidget(button);
        layout->addWidget(browser);
        setLayout(layout);

        connect(button, &QPushButton::clicked, this, &Widget::start);
        connect(button, &QPushButton::clicked, this, [=]()
        {
           browser->insertPlainText("\n");
        });
        connect(&thread1, &QThread::finished, &thread1, &QThread::deleteLater);
        connect(&thread2, &QThread::finished, &thread2, &QThread::deleteLater);
        connect(test1, &Test::query_result, this, [=](const QString &result)
        {
           browser->insertPlainText(result);
        });
        connect(test2, &Test::query_result, this, [=](const QString &result)
        {
           browser->insertPlainText(result);
        });

        resize(500, 400);
    }

    ~Widget()
    {
        thread1.wait();
        thread2.wait();
    }

public slots:
    void start()
    {
        QMetaObject::invokeMethod(test1, "test_query");
        QMetaObject::invokeMethod(test2, "test_query");
    }

private:
    QThread thread1;
    QThread thread2;

    Test *test1;
    Test *test2;
};

#endif // WIDGET_H

数据库： 

1000条记录：内容均为123

ui： 

 

有一个线程中的数据库没有打开

 ------

2.

修改代码：

#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>
#include <QPushButton>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDriver>
#include <QThread>
#include <QTextBrowser>
#include <QVBoxLayout>
#include <QDebug>
class Test : public QObject
{
    Q_OBJECT
public:
    Test(QSqlDatabase database)
        :   QObject(nullptr), m_database(database)
    {
    }
signals:
    void query_result(const QString &result);
public slots:
    void test_query()
    {
        qDebug()<<__FILE__<<"["<<__LINE__<<"]"<<m_database;
        m_database.open();
        qDebug()<<__FILE__<<"["<<__LINE__<<"]"<<m_database;
        QString str = "Thread ID: " + QString::number((int)QThread::currentThreadId(), 16) + "  result:";
        QSqlQuery query(m_database);
        query.exec("SELECT * FROM test;");
        while(query.next())
            str += query.value(0).toString() + "|";
        m_database.close();
        emit query_result(str + "\n");
    }
private:
    QSqlDatabase m_database;
};

class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = nullptr)
        :   QWidget(parent)
    {
        QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", "mydb000");
        db.setPort(3306);
        db.setDatabaseName("test");
        db.setUserName("root");
        db.setPassword("root_pwd");

        QSqlDatabase db1 = QSqlDatabase::addDatabase("QMYSQL", "mydb111");
        db1.setPort(3306);
        db1.setDatabaseName("test");
        db1.setUserName("root");
        db1.setPassword("root_pwd");

        test1 = new Test(db);
        test2 = new Test(db1);

        test1->moveToThread(&thread1);
        test2->moveToThread(&thread2);

        thread1.start();
        thread2.start();

        QPushButton *button = new QPushButton("开始", this);
        QTextBrowser *browser = new QTextBrowser(this);
        browser->insertPlainText("UI Thread ID: " + QString::number((int)QThread::currentThreadId(), 16));
        QVBoxLayout *layout = new QVBoxLayout(this);
        layout->addWidget(button);
        layout->addWidget(browser);
        setLayout(layout);

        connect(button, &QPushButton::clicked, this, &Widget::start);
        connect(button, &QPushButton::clicked, this, [=]()
        {
           browser->insertPlainText("\n");
        });
        connect(&thread1, &QThread::finished, &thread1, &QThread::deleteLater);
        connect(&thread2, &QThread::finished, &thread2, &QThread::deleteLater);
        connect(test1, &Test::query_result, this, [=](const QString &result)
        {
           browser->insertPlainText(result);
        });
        connect(test2, &Test::query_result, this, [=](const QString &result)
        {
           browser->insertPlainText(result);
        });

        resize(500, 400);
    }

    ~Widget()
    {
        thread1.wait();
        thread2.wait();
    }

public slots:
    void start()
    {
        QMetaObject::invokeMethod(test1, "test_query");
        QMetaObject::invokeMethod(test2, "test_query");
    }

private:
    QThread thread1;
    QThread thread2;

    Test *test1;
    Test *test2;
};

#endif // WIDGET_H

不同的线程中使用不同的连接名：

发现还是不可以成功访问。

只有一个数据库可以打开。。。

3.

Qt多线程操作MySql数据库 - 补码 - 博客园 (cnblogs.com)

看了看这篇文章，在

QSqlDatabase::addDatabase和QSqlDatabase::open外部加锁，然后两个数据库都可以打开了。

（应该是在执行这两个函数时，会有数据竞争的情况发生）

#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>
#include <QPushButton>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDriver>
#include <QThread>
#include <QTextBrowser>
#include <QVBoxLayout>
#include <QDebug>
#include <QMutex>

extern QMutex mutex;
class Test : public QObject
{
    Q_OBJECT
public:
    Test(const QString &connectName)
        :   QObject(nullptr)
    {
        mutex.lock();
        m_database = QSqlDatabase::addDatabase("QMYSQL",connectName);
        m_database.setHostName("127.0.0.1");
        m_database.setPort(3306);
        m_database.setDatabaseName("test");
        m_database.setUserName("root");
        m_database.setPassword("root_pwd");
        mutex.unlock();
    }
signals:
    void query_result(const QString &result);
public slots:
    void test_query()
    {
        qDebug()<<__FILE__<<"["<<__LINE__<<"]"<<m_database;
        mutex.lock();
        if(m_database.open())
        {
            qDebug()<<__FILE__<<"["<<__LINE__<<"]"<<m_database;
            QString str = "Thread ID: " + QString::number((int)QThread::currentThreadId(), 16) + "  result:";
            QSqlQuery query(m_database);
            query.exec("SELECT * FROM test;");
            while(query.next())
                str += query.value(0).toString() + "|";
            m_database.close();
            emit query_result(str + "\n");
        }
        else
        {
            qDebug()<<__FILE__<<"["<<__LINE__<<"]"<<m_database.lastError();
        }
        mutex.unlock();
    }
private:
    QSqlDatabase m_database;
};

class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = nullptr)
        :   QWidget(parent)
    {
        test1 = new Test("db");
        test2 = new Test("db1");

        test1->moveToThread(&thread1);
        test2->moveToThread(&thread2);

        thread1.start();
        thread2.start();

        QPushButton *button = new QPushButton("开始", this);
        QTextBrowser *browser = new QTextBrowser(this);
        browser->insertPlainText("UI Thread ID: " + QString::number((int)QThread::currentThreadId(), 16));
        QVBoxLayout *layout = new QVBoxLayout(this);
        layout->addWidget(button);
        layout->addWidget(browser);
        setLayout(layout);

        connect(button, &QPushButton::clicked, this, &Widget::start);
        connect(button, &QPushButton::clicked, this, [=]()
        {
           browser->insertPlainText("\n");
        });
        connect(&thread1, &QThread::finished, &thread1, &QThread::deleteLater);
        connect(&thread2, &QThread::finished, &thread2, &QThread::deleteLater);
        connect(test1, &Test::query_result, this, [=](const QString &result)
        {
           browser->insertPlainText(result);
        });
        connect(test2, &Test::query_result, this, [=](const QString &result)
        {
           browser->insertPlainText(result);
        });

        resize(500, 400);
    }

    ~Widget()
    {
        thread1.wait();
        thread2.wait();
    }

public slots:
    void start()
    {
        QMetaObject::invokeMethod(test1, "test_query");
        QMetaObject::invokeMethod(test2, "test_query");
    }

private:
    QThread thread1;
    QThread thread2;

    Test *test1;
    Test *test2;
};

#endif // WIDGET_H

#include <QApplication>
#include "widget.h"

QMutex mutex;
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    Widget w;
    w.show();
    return a.exec();
}

 ---

在使用多线程时，特别需要注意的就是刚才出现的情况。

如：有两个线程，它们同时要访问一个共有的资源，这时候就会出现对资源的竞争，如果不添加锁，会出现什么情况将是未知的。

比如上面的例子：

线程1打开数据库成功，它的流程正常进行。

而线程2打开数据库失败，此时让流程继续走下去，结果不是我们期望的，所以要加锁。