Qt Model View框架
QSqlQueryModel基于QSqlQuery 提供了执行SQL语句并返回执行结果的便利接口:setQuery;默认情况下是只读的,倘若需要读写数据库,则需要继续并实现其基类QAbstractItemModel的setData()和flags()方法,或者使用其子类QSqlTableModel关于同步异步、阻塞非阻塞:
1.阻塞调用:调用者等待被调用者的执行结果,期间什么都不做
2.非阻塞调用:调用者并不等待被调用者,而是之后通过轮询或者消息等方式获取被调用者的执行结果
3.同步:进程里的两个线程之间的同步
4.异步:进程里的一个线程使用了非阻塞式调用
QSqlQueryModel
背景
需要在表格里显示线上数据库中的图片,网速较慢的情况下拉取数据库期间界面会处于冻结状态,造成不好的用户体验;另起线程查询数据库、使用自定义model来显示查询结果也可行,但既有项目里用的是QSqlQueryModel,改写QSqlModel工作量会更少
一、QSqlQueryModel源码解析
QSqlQueryModel是通过setQuery方法来更新model里的数据的
1.void setQuery(const QSqlQuery &query)
void QSqlQueryModel::setQuery(const QSqlQuery &query)
{
Q_D(QSqlQueryModel);
beginResetModel();
QSqlRecord newRec = query.record();
bool columnsChanged = (newRec != d->rec);
if (d->colOffsets.size() != newRec.count() || columnsChanged)
d->initColOffsets(newRec.count());
d->bottom = QModelIndex();
d->error = QSqlError();
d->query = query;
d->rec = newRec;
d->atEnd = true;
if (query.isForwardOnly()) {
d->error = QSqlError(QLatin1String("Forward-only queries "
"cannot be used in a data model"),
QString(), QSqlError::ConnectionError);
endResetModel();
return;
}
if (!query.isActive()) {
d->error = query.lastError();
endResetModel();
return;
}
if (query.driver()->hasFeature(QSqlDriver::QuerySize) && d->query.size() > 0) {
d->bottom = createIndex(d->query.size() - 1, d->rec.count() - 1);
} else {
d->bottom = createIndex(-1, d->rec.count() - 1);
d->atEnd = false;
}
// fetchMore does the rowsInserted stuff for incremental models
fetchMore();
endResetModel();
queryChange();
}
2.void setQuery(const QString &query, const QSqlDatabase &db = QSqlDatabase())
void QSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)
{
setQuery(QSqlQuery(query, db));
}
二、思路
setQuery本质是将QSqlQuery对象拷贝至QSqlQueryModelPrivate的成员变量query,真正查询数据库的步骤是在构造QSqlQuery对象时进行的:
QSqlQuery::QSqlQuery(const QString& query, QSqlDatabase db)
{
d = QSqlQueryPrivate::shared_null();
qInit(this, query, db);
}
static void qInit(QSqlQuery *q, const QString& query, QSqlDatabase db)
{
QSqlDatabase database = db;
if (!database.isValid())
database = QSqlDatabase::database(QLatin1String(QSqlDatabase::defaultConnection), false);
if (database.isValid()) {
*q = QSqlQuery(database.driver()->createResult());
}
if (!query.isEmpty())
q->exec(query);
}
重定义1setQuery(const QString &query, const QSqlDatabase &db = QSqlDatabase()),将QSqlQuery对象的构造放到另一个线程即可
三、实现
void AsyncSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)
{
auto threadFetchSql = [&](const QString sql )->int{
auto result = QSqlQuery( sql ,nullptr == p_databaseSource ? db : p_databaseSource() );
setQuery(result );
return 0;
};
std::thread queryWorker(threadFetchSql ,query);
queryWorker.detach();
}
四、完整示例1
#ifndef ASYNCSQLQUERYMODEL_H
#define ASYNCSQLQUERYMODEL_H
#include <QtSql/qtsqlglobal.h>
#include <QtSql/qsqldatabase.h>
#include <QtSql/qsqlquerymodel.h>
#include <QSqlQuery>
#include <functional>
#include <mutex>
#include <QFuture>
#include <QtConcurrent>
#include <memory>
class QSqlRecord;
class QSqlQueryModelPrivate;
class QAbstractItemView;
class QSqlError;
class QSqlQuery;
class AsyncSqlQueryModel : public QSqlQueryModel
{
Q_OBJECT
Q_DECLARE_PRIVATE(QSqlQueryModel)
public:
explicit AsyncSqlQueryModel(QObject *parent = nullptr);
virtual ~AsyncSqlQueryModel();
void setQuery(const QSqlQuery &query);
void setQuery(const QString &query, const QSqlDatabase &db = QSqlDatabase());
void registerThreadStorageDbSource(std::function <QSqlDatabase()> callback);
protected:
QAbstractItemView* m_view;
private:
std::function < QSqlDatabase()> p_databaseSource;
QSqlQuery m_query;
std::mutex m_queryLock;
signals:
};
#endif // ASYNCSQLQUERYMODEL_H
#include "AsyncSqlQueryModel.h"
#include "private/qsqlquerymodel_p.h"
#include <QtSql/private/qtsqlglobal_p.h>
#include "private/qabstractitemmodel_p.h"
#include "qabstractitemmodel.h"
#include <qdebug.h>
#include <qsqldriver.h>
#include <qsqlfield.h>
#include <QDebug>
#include <thread>
#include <QSqlQuery>
#include <QTableView>
QT_BEGIN_NAMESPACE
#define QSQL_PREFETCH 255
void QSqlQueryModelPrivate::prefetch(int limit)
{
Q_Q(QSqlQueryModel);
if (atEnd || limit <= bottom.row() || bottom.column() == -1)
return;
QModelIndex newBottom;
const int oldBottomRow = qMax(bottom.row(), 0);
// try to seek directly
if (query.seek(limit)) {
newBottom = q->createIndex(limit, bottom.column());
} else {
// have to seek back to our old position for MS Access
int i = oldBottomRow;
if (query.seek(i)) {
while (query.next())
++i;
newBottom = q->createIndex(i, bottom.column());
} else {
// empty or invalid query
newBottom = q->createIndex(-1, bottom.column());
}
atEnd = true; // this is the end.
}
if (newBottom.row() >= 0 && newBottom.row() > bottom.row()) {
q->beginInsertRows(QModelIndex(), bottom.row() + 1, newBottom.row());
bottom = newBottom;
q->endInsertRows();
} else {
bottom = newBottom;
}
}
QSqlQueryModelPrivate::~QSqlQueryModelPrivate()
{
}
void QSqlQueryModelPrivate::initColOffsets(int size)
{
colOffsets.resize(size);
memset(colOffsets.data(), 0, colOffsets.size() * sizeof(int));
}
int QSqlQueryModelPrivate::columnInQuery(int modelColumn) const
{
if (modelColumn < 0 || modelColumn >= rec.count() || !rec.isGenerated(modelColumn) || modelColumn >= colOffsets.size())
return -1;
return modelColumn - colOffsets[modelColumn];
}
AsyncSqlQueryModel::AsyncSqlQueryModel(QObject *parent) : QSqlQueryModel(parent),
p_databaseSource(nullptr),m_view(nullptr)
{
}
AsyncSqlQueryModel::~AsyncSqlQueryModel()
{
}
void AsyncSqlQueryModel::setQuery(const QSqlQuery &query)
{
Q_D(QSqlQueryModel);
QAbstractItemView* view = nullptr;
if(m_view)
view = m_view;
else
if(parent() && parent()->inherits("QAbstractItemView"))
view = static_cast<QAbstractItemView*>(parent());
if(view)
view->reset();
beginResetModel();
QSqlRecord newRec = query.record();
bool columnsChanged = (newRec != d->rec);
if (d->colOffsets.size() != newRec.count() || columnsChanged)
d->initColOffsets(newRec.count());
d->bottom = QModelIndex();
d->error = QSqlError();
d->query = query;
d->rec = newRec;
d->atEnd = true;
if (query.isForwardOnly()) {
d->error = QSqlError(QLatin1String("Forward-only queries "
"cannot be used in a data model"),
QString(), QSqlError::ConnectionError);
endResetModel();
return;
}
if (!query.isActive()) {
d->error = query.lastError();
endResetModel();
return;
}
if (query.driver()->hasFeature(QSqlDriver::QuerySize) && d->query.size() > 0) {
d->bottom = createIndex(d->query.size() - 1, d->rec.count() - 1);
} else {
d->bottom = createIndex(-1, d->rec.count() - 1);
d->atEnd = false;
}
// fetchMore does the rowsInserted stuff for incremental models
fetchMore();
endResetModel();
queryChange();
if(view)
view->viewport()->update();
}
void AsyncSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)
{
auto threadFetchSql = [&](const QString sql )->int{
m_query = QSqlQuery( sql ,nullptr == p_databaseSource ? db : p_databaseSource() );
setQuery(m_query);
return 0;
};
std::thread queryWorker(threadFetchSql ,query);
queryWorker.detach();
}
void AsyncSqlQueryModel::registerThreadStorageDbSource(std::function<QSqlDatabase ()> callback)
{
p_databaseSource = callback;
}
QT_END_NAMESPACE