最近看到关于qt 中mysql 浏览数据分页,有人用到了qtablewidget 翻页时手动添加
就在想,既然qt中提供了view和model,为什么放着不用哪?
简单实现了一把,记录一下
首先重新继承一下QSqlTableModel,翻页时修改记录数
userSqlTableModel.h
#ifndef USERSQLTABLEMODEL_H
#define USERSQLTABLEMODEL_H
#include <QSqlTableModel>
class UserSqlTableModel : public QSqlTableModel
{
Q_OBJECT
public:
explicit UserSqlTableModel(QObject *parent = 0,QSqlDatabase db = QSqlDatabase() );
void setStart(int start){this->m_nStart = start;}
int getStart(){return this->m_nStart;}
public:
QVariant headerData(int section, Qt::Orientation orientation, int role) const;
private:
int m_nStart; //记录数据起始id
};
#endif // USERSQLTABLEMODEL_H
userSqlTableModel.cpp
#include "usersqltablemodel.h"
UserSqlTableModel::UserSqlTableModel(QObject *parent,QSqlDatabase db) :
QSqlTableModel(parent,db)
{
m_nStart = 0;
}
QVariant UserSqlTableModel::headerData(int section, Qt::Orientation orient, int role) const
{
if (orient == Qt::Vertical && role == Qt::DisplayRole)
return section + m_nStart;
return QSqlTableModel::headerData(section,orient,role);
}
在对话框类中声明几个变量:
private:
UserSqlTableModel *userModel;
int m_nTotal; //总记录数
int m_nPageSize; //每页显示条数
int m_nCurPageSize; //当前页数据条数
int m_nStart; //起始记录数
初始化变量:
this->m_nStart = -1;
this->m_nPageSize = 6;
this->m_nCurPageSize = 0;
this->m_nTotal = 0;
userModel = new UserSqlTableModel(this);
userModel->setTable("user");
userModel->setSort(User_Name, Qt::AscendingOrder);
userModel->setHeaderData(User_Name, Qt::Horizontal,tr("账 号"));
userModel->setHeaderData(User_GroupChName, Qt::Horizontal,tr("所属组"));
userModel->setHeaderData(User_Description,Qt::Horizontal, tr("备注"));
userView->setModel(userModel);
userView->setSelectionMode(QAbstractItemView::SingleSelection);
userView->setSelectionBehavior(QAbstractItemView::SelectRows);
userView->setColumnHidden(User_Id, true);
userView->setColumnHidden(User_Pwd, true);
userView->setColumnHidden(User_Group, true);
userView->resizeColumnsToContents();
userView->horizontalHeader()->setStretchLastSection(true);
this->m_nTotal = this->getTotal();
on_pushButton_first_clicked();
void UserDialog::on_pushButton_first_clicked() //首页
{
if(this->m_nStart == 0 && this->m_nCurPageSize == this->m_nPageSize)
return;
this->m_nStart = 0;
this->m_nCurPageSize = this->m_nTotal > this->m_nPageSize ? this->m_nPageSize : this->m_nTotal;
updateInfo();
}
void UserDialog::on_pushButton_previous_clicked() //上一页
{
if(this->m_nStart == 0 || this->m_nTotal <= 0)
return;
this->m_nStart -= this->m_nPageSize;
this->m_nCurPageSize = this->m_nPageSize;
updateInfo();
}
void UserDialog::on_pushButton_next_clicked() //下一页
{
if(this->m_nCurPageSize < this->m_nPageSize || this->m_nTotal <= 0)
return;
this->m_nStart += this->m_nPageSize;
this->m_nCurPageSize = this->m_nTotal - this->m_nStart > this->m_nPageSize ? this->m_nPageSize : this->m_nTotal - this->m_nStart;
updateInfo();
}
void UserDialog::on_pushButton_last_clicked() //尾页
{
if(this->m_nCurPageSize < this->m_nPageSize || this->m_nTotal <= 0)
return;
this->m_nCurPageSize = this->m_nTotal % this->m_nPageSize;
this->m_nStart = this->m_nTotal - this->m_nCurPageSize;
updateInfo();
}
void UserDialog::updateInfo()
{
QString strFilter = QString(" 1=1 limit %1,%2").arg(this->m_nStart).arg(this->m_nCurPageSize);
userModel->setStart(this->m_nStart + 1);
userModel->setFilter(strFilter);
userModel->select();
}
总结来关键的就有两点:
1、重载QSqlTableModel,在翻页时重置记录条数
2、设置model的过滤条件
当时直接 setFilter("limit 0,1;")始终不行 ,但是直接执行带limit 的 sql 语句就没有问题。
最终在手册中看到这样一句话
The filter is a SQL WHERE clause without the keyword WHERE (for example, name='Josephine').
就是说过滤条件即为省略where的条件语句
select * from table where limit 0,6; 肯定是不行的
加一个永真条件就哦了
同时注意一下最后的‘;’若没有用qt 2.3.0会报执行时错误:
D:\qt\1010\tengxun\fontsampler-build-desktop\debug\fontsampler.exe 启动中...
ASSERT: "idx >= 0 && idx < s" in file ..\..\include/QtCore/../../../../../../ndk_buildrepos/qt-desktop/src/corelib/tools/qvarlengtharray.h, line 107