QSqlQueryModel是個好東西,卻偏偏是隻讀的。
QSqlTableModel也是個好東西,卻偏偏太死板。我學這個的時候就在想為啥QTSqlTableModel提供了setFilter(), setSort(); 卻不提供setLimit(), setFields();而且關聯查詢還得用QSqlRelationalTableModel。只好自己幹了。
首先自定義sql查詢語句封裝,web開發寫習慣了,總喜歡用Model,但為了和QSqlTableModel這樣的類區分, 就用Entity吧。
標頭檔案
#ifndef ENTITY_H#define ENTITY_H#include
實現檔案
#include “entity。h”#include
這樣基本的查詢封裝就好了,能滿足基本的需求。
那麼QSqlTableModel要使用sql語句咋辦,就只有實現自己的QSqlTableModel了。
那要怎麼實現了,看了下原始碼,裡面的select()是這樣的:
bool QSqlTableModel::select(){ Q_D(QSqlTableModel); const QString query = selectStatement(); if (query。isEmpty()) return false; beginResetModel(); d->clearCache(); QSqlQuery qu(query, d->db); setQuery(qu); if (!qu。isActive() || lastError()。isValid()) { // something went wrong - revert to non-select state d->initRecordAndPrimaryIndex(); endResetModel(); return false; } endResetModel(); return true;}
可以看出它使用的是QSqlQuery。setQuery(query),而query是從const QString query = selectStatement()這裡來的,再看看selectStatement();
QString QSqlTableModel::selectStatement() const{ Q_D(const QSqlTableModel); if (d->tableName。isEmpty()) { d->error = QSqlError(QLatin1String(“No table name given”), QString(), QSqlError::StatementError); return QString(); } if (d->rec。isEmpty()) { d->error = QSqlError(QLatin1String(“Unable to find table ”) + d->tableName, QString(), QSqlError::StatementError); return QString(); } const QString stmt = d->db。driver()->sqlStatement(QSqlDriver::SelectStatement, d->tableName, d->rec, false); if (stmt。isEmpty()) { d->error = QSqlError(QLatin1String(“Unable to select fields from table ”) + d->tableName, QString(), QSqlError::StatementError); return stmt; } return Sql::concat(Sql::concat(stmt, Sql::where(d->filter)), orderByClause());}
從返回語句看,可以看出最終返回的是一個sql字串。看吧,有where,有order,就是沒有limit, 沒有join。
現在就實現自己的QSqlTableModel,由於我需要一個checkbox,所以重寫了setData()、data()、flags()
標頭檔案
#ifndef ERPSQLTABLEMODEL_H#define ERPSQLTABLEMODEL_H#include
實現檔案
#include “erpsqltablemodel。h”#define CHECK_BOX_COLUMN 0ErpSqlTableModel::ErpSqlTableModel(QObject *parent, QSqlDatabase db) : QSqlTableModel(parent, db){}void ErpSqlTableModel::disabledCheckbox(){ needCheckBox = false;}bool ErpSqlTableModel::setData(const QModelIndex &index, const QVariant &value, int role){ if (!index。isValid()) return false; if (needCheckBox) { int actionColumn = index。column(); if (actionColumn == CHECK_BOX_COLUMN && role == Qt::CheckStateRole) { rowCheckMap[index。row()] = value。toInt() == Qt::Checked ? Qt::Checked : Qt::Unchecked; } } return true;}QVariant ErpSqlTableModel::data(const QModelIndex &idx, int role) const{ if (!idx。isValid()) return QVariant(); if (needCheckBox) { int actionColumn = idx。column(); if (actionColumn == CHECK_BOX_COLUMN && role == Qt::CheckStateRole) { return rowCheckMap[idx。row()] == Qt::Checked ? Qt::Checked : Qt::Unchecked; } } return QSqlTableModel::data(idx, role);}Qt::ItemFlags ErpSqlTableModel::flags(const QModelIndex &index) const{ Qt::ItemFlags flags = Qt::ItemIsEnabled | Qt::ItemIsSelectable; if (needCheckBox) { if (index。column() == CHECK_BOX_COLUMN) { flags |= Qt::ItemIsUserCheckable; } } return flags;}QString ErpSqlTableModel::selectStatement() const{ if (!sql。isEmpty()) { return sql; } return QSqlTableModel::selectStatement();}void ErpSqlTableModel::setSql(const QString& sqlstr){ sql = sqlstr;}
這裡添加了一個bool型別的needCheckBox,用於控制是否顯示checkbox列,一個QMap型別的rowCheckMap,用於在每行新增checkbox;一個void setSql(const QString& sqlstr)函式,用於設定sql語句,一個void disabledCheckbox()函式,用於禁用checkbox列。
前提準備工作做好了,現在就是使用,比如我要查詢erp_notices表,先建立erp_notices表的entity
NoticeEntity標頭檔案
#ifndef NOTICEENTITY_H#define NOTICEENTITY_H#include
NoticeEntity實現檔案:
#include “noticeentity。h”NoticeEntity::NoticeEntity() : Entity(“erp_notices”), mtable(“erp_notices”){}ErpSqlTableModel* NoticeEntity::getPage(int page, int pageSize){ this->fields(“id, title, status, created_at”); if (page > 0) { this->limit(page, pageSize); } QString sql = this->joinSqlStrForQueryModel(); pageList = new ErpSqlTableModel; pageList->setTable(mtable); pageList->setSql(sql); //pageList->disabledCheckbox(); pageList->select(); pageList->setHeaderData(1, Qt::Horizontal, “ID”); pageList->setHeaderData(2, Qt::Horizontal, “標題”); pageList->setHeaderData(3, Qt::Horizontal, “狀態”); pageList->setHeaderData(4, Qt::Horizontal, “新增時間”); pageList->insertColumn(0); pageList->setHeaderData(0, Qt::Horizontal, “選擇”); pageList->insertColumn(5); pageList->setHeaderData(5, Qt::Horizontal, “操作”); return pageList;}
this->fields()呼叫entity。h中的void fields()
this->limit(page, pageSize)呼叫entity。h中的void limit()以實現分頁。
如果使用join呢,我這裡只實現了leftJoin,需要了其他join到時候再說。
// select a。id, a。department_id, a。duty_id, a。name, a。birthday, a。sex, a。height, // a。weight, a。nation,b。name as dutyName, c。name as departmentName from erp_employees as a // left join erp_duties as b on a。duty_id = b。id // left join erp_departments as c on a。department_id = c。id; QString fields = “erp_employees。id, erp_departments。name as departmentName, erp_duties。name as dutyName, ” “erp_employees。name, erp_employees。birthday, erp_employees。sex, erp_employees。height, ” “erp_employees。weight, erp_employees。nation, erp_employees。created_at”; this->fields(fields); this->leftJoin(“erp_departments”, “erp_departments。id”, “erp_employees。department_id”); this->leftJoin(“erp_duties”, “erp_duties。id”, “erp_employees。duty_id”); if (page > 0) { this->limit(page, pageSize); } QString sql = this->joinSqlStrForQueryModel(); pageList = new ErpSqlTableModel; pageList->setTable(mtable); pageList->setSql(sql); pageList->select();
順便說下分頁。一般在分頁中還需要一個總頁數,以知道尾頁在哪裡,而要知道總頁數,就需要知道總條數以及每頁條數。
總條數在entity。h中使用int count()得到;然後計算總頁數
count = entity->getCount();pageCount = (count + pageSize - 1) / pageSize;if (count > 0) { ui->btnPageNext->setEnabled(true); ui->btnPageLast->setEnabled(true); } if (page > 1) { ui->btnPagePrev->setEnabled(true); ui->btnPageFirst->setEnabled(true); }
分頁按鈕
void NoticeList::on_btnPageFirst_clicked(){ page = 1; initTable();}void NoticeList::on_btnPagePrev_clicked(){ if (page >1) { page——; initTable(); }}void NoticeList::on_btnPageNext_clicked(){ if (page < pageCount) { page++; initTable(); }}void NoticeList::on_btnPageLast_clicked(){ page = pageCount; initTable();}