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.1和Qt5.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);
}

如果在doSqlFunA和doSqlFunB中调用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;
}