Qt数据库多线程操作注意事项

将项目从Qt5.10.1迁移到Qt5.12.4,发现封装的数据库相关代码报错,主要是以下两种:

  1. QSqlDatabasePrivate::database: requested database does not belong to the calling thread.
  2. QSqlDatabasePrivate::removeDatabase: connection ‘xxx’ is still in use, all queries will cease to work.

Qt5.10.1Qt5.12.4的官方帮助文档中搜索 Thread-Support in Qt Modules ,都可以看到如下说明,但是同样的代码在Qt5.10.1中却没有报错,连警告都没有。

Threads and the SQL Module
A connection can only be used from within the thread that created it. Moving connections between threads or creating queries from a different thread is not supported.
In addition, the third party libraries used by the QSqlDrivers can impose further restrictions on using the SQL Module in a multithreaded program. Consult the manual of your database client for more information.

现在开始分别分析在Qt5.12.4中出现上述问题的原因和解决方法。
问题一:

QSqlDatabasePrivate::database: requested database does not belong to the calling thread.

出现此问题,是由于在线程A中创建了数据库中的连接,在线程B中使用此数据库连接去执行增、删、、改查等操作。所以,避免此问题,只要在操作数据库查询的线程中创建连接即可。

问题二:

QSqlDatabasePrivate::removeDatabase: connection ‘xxx’ is still in use, all queries will cease to work.

Qt5.12.4帮助文档中关于removeDatabase的使用方法如下,强调了在调用removeDatabase前,需要把QSqlDatabase和QSqlQuery销毁。否则,就会出现上述警告。

[static] void QSqlDatabase::removeDatabase(const QString &connectionName)
Removes the database connection connectionName from the list of database connections.
Warning: There should be no open queries on the database connection when this function is called, otherwise a resource leak will occur.
Example:
// WRONG
QSqlDatabase db = QSqlDatabase::database(“sales”);
QSqlQuery query(“SELECT NAME, DOB FROM EMPLOYEES”, db);
QSqlDatabase::removeDatabase(“sales”); // will output a warning
// “db” is now a dangling invalid database connection,
// “query” contains an invalid result set
The correct way to do it:
{
QSqlDatabase db = QSqlDatabase::database(“sales”);
QSqlQuery query(“SELECT NAME, DOB FROM EMPLOYEES”, db);
}
// Both “db” and “query” are destroyed because they are out of scope
QSqlDatabase::removeDatabase(“sales”); // correct
To remove the default connection, which may have been created with a call to addDatabase() not specifying a connection name, you can retrieve the default connection name by calling connectionName() on the database returned by database(). Note that if a default database hasn’t been created an invalid database will be returned.

根据文档的提示,可以修改下代码,简单封装下。

QSqlDatabase DBUtil::openDataBase(QString connectionnName)
{
    QSqlDatabase db;
    if ( true == QSqlDatabase::contains(connectionnName)
    {
        db = QSqlDatabase::database(connectionnName);
    }
    else
    {
        db = QSqlDatabase::addDatabase("QSQLITE", connectionnName);
        db.setDatabaseName("xxx.db");
    }

    if (false == db.open())
    {
        qDebug() << db.lastError().text();
    }
    return db;
}

void DBUtil::closeDataBase(QString connectionName)
{
    QSqlDatabase::removeDatabase(connectionName);
}

void DBUtil::executeSql()
{
    QString connectionName;
    {
        QSqlDatabase db = DBUtil::openDataBase(”your db connect name“);
        connectionName = db.connectionName();
        QSqlQuery query(db);
        query.prepare(sql);
        /* do your sql */
    }
    DBUtil::closeDataBase(connectionName);
}

拓展问题
如果你的代码中不使用QtConcurrent,那上述代码应该也够用了。不巧的时,由于有大量数据需要并行处理,所以我的代码中使用了QtConcurrent::run,如下。

void procData()
{
	QFuture<void> groupFuture = QtConcurrent::run(doSqlFunA);
	QFuture<void> cameraFuture = QtConcurrent::run(doSqlFunB);
}

如果在doSqlFunAdoSqlFunB中调用DBUtil::openDataBase使用了相同的数据库连接名称,那就会出现问题一的错误了。原因是doSqlFunA在调用DBUtil::openDataBase时,创建了数据库连接A;紧接着,doSqlFunB在调用DBUtil::openDataBase时,由于数据库连接A已经创建,且没有被销毁,所以doSqlFunB直接使用了数据库连接A,而没有新建,导致使用了其他线程创建的数据库连接而报错。
当然,你可以在每次调用DBUtil::openDataBase时,传入不同的名称,避免这个问题。更高效的方法是改进下DBUtil::openDataBase封装,用随机数作为数据库的连接名称。

QSqlDatabase DBUtil::openDataBase()
{
    quint32 value = QRandomGenerator::global()->generate();
    QSqlDatabase db;
    if ( true == QSqlDatabase::contains(QString::number(value)))
    {
        db = QSqlDatabase::database(QString::number(value));
    }
    else
    {
        db = QSqlDatabase::addDatabase("QSQLITE", QString::number(value));
        db.setDatabaseName("xxx.db");
    }

    if (false == db.open())
    {
        qDebug() << db.lastError().text();
    }
    return db;
}
发布了16 篇原创文章 · 获赞 1 · 访问量 1439
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览