QTableview + QSqlTableModel + QSortFilterProxyModel实现MySQL查询和翻页(非limit语句)

最新推荐文章于 2024-07-24 16:18:52 发布

weixin_46424582

最新推荐文章于 2024-07-24 16:18:52 发布

阅读量667

点赞数

分类专栏： Qt记录文章标签： mysql qt 数据库

本文链接：https://blog.csdn.net/weixin_46424582/article/details/130781361

版权

Qt记录专栏收录该内容

22 篇文章 8 订阅

订阅专栏

1、前言

很久没写博客了，今天来总结一下。前段时间写了个MySQL的查询和翻页的demo，当时用的是mysql语句的limit，也就是说每一次翻页，都需要操作mysql，有点不流畅，特别是数据量大的情况下，于是继续改进。

QSqltablemodel+QtableView实现数据库翻页及排序(含MySQL安装及qt mysql编译)

后来再想想，其实可以用Qt的Model/View模型来实现，也就是QSortFilterProxyModel，通过重写
filterAcceptsRow来过滤需要展示的行数，通过lessThan来自定义排序。因为查询了一次之后，所有的数据信息都在model里面了，你翻页其实也只是基于view的可视项来设置。

话不多说，直接上代码。。。

2、代码

先看下重写的两个类QSqlTableModel 和 QSortFilterProxyModel
qmysqltablemodel.h

#ifndef QMYSQLTABLEMODEL_H
#define QMYSQLTABLEMODEL_H

#include <QObject>
#include <QSqlTableModel>
#include <QColor>
#include <QDebug>
#include <QFont>
#include <QSize>
class QMysqlTableModel : public QSqlTableModel
{
    Q_OBJECT
public:
    QMysqlTableModel(QObject *parent = Q_NULLPTR, QSqlDatabase db = QSqlDatabase());

    QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const override;
    QVariant headerData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const override;
};

#endif // QMYSQLTABLEMODEL_H

qmysqltablemodel.cpp

#include "qmysqltablemodel.h"
#include <QDateTime>
QMysqlTableModel::QMysqlTableModel(QObject *parent, QSqlDatabase db):
    QSqlTableModel(parent,db)
{

}

QVariant QMysqlTableModel::data(const QModelIndex &index, int role) const
{
    if (!index.isValid())
        return QVariant();
    if(role == Qt::TextColorRole){
        QString str = QSqlTableModel::data(this->index(index.row(), 10)).toString();
        if ( str=="NG"){
            return QVariant(QColor(255,0,0));
        }
    }else if(role == Qt::TextAlignmentRole){
        return Qt::AlignCenter;
    }else if(role == Qt::FontRole){
        return QFont("微软雅黑", 9, QFont::Bold);
    }else if(role == Qt::DisplayRole){
        QVariant vt = QSqlTableModel::data(index, role);
        if(QVariant::DateTime == vt.type())
            return vt.toDateTime().toString("yyyy-MM-dd hh:mm:ss");
    }
    return QSqlTableModel::data(index,role);
}

QVariant QMysqlTableModel::headerData(int section, Qt::Orientation orientation, int role) const
{
    if(Qt::Horizontal == orientation && role == Qt::DisplayRole){
        QString headerText;
        switch (section) {
        case 0:headerText="序号";break;
        case 1:headerText="时间";break;
        case 2:headerText="ID";break;
        case 3:headerText="工位";break;
        case 4:headerText="压接次数";break;
        case 5:headerText="压接结果";break;
        case 6:headerText="色偏结果";break;
        case 7:headerText="均一结果";break;
        case 8:headerText="用时";break;
        case 9:headerText="班别";break;
        case 10:headerText="结果";break;
        case 11:headerText="备注";break;
        default:
            break;
        }
        return headerText;
    }
    return QSqlTableModel::headerData(section,orientation,role);
}

qrowslimitproxymodel.h

#ifndef QROWSLIMITPROXYMODEL_H
#define QROWSLIMITPROXYMODEL_H

#include <QObject>
#include <QSortFilterProxyModel>
#include <QDebug>
#include <QBrush>
#include <QPalette>
#include <QColor>
#include <QFont>
class QRowsLimitProxyModel : public QSortFilterProxyModel
{
    Q_OBJECT
public:
    QRowsLimitProxyModel(int ePageCount = 100,QObject *parent = Q_NULLPTR );
    bool filterAcceptsRow(int source_row, const QModelIndex &source_parent) const;
    virtual bool lessThan(const QModelIndex &source_left, const QModelIndex &source_right) const Q_DECL_OVERRIDE;

    void setLimitRows(int r1,int r2);
    void setLimitRows(int startRow);
private:
    int m_eachpageCount;

    int m_RowMin=0;
    int m_RowMax=100;
};

#endif // QROWSLIMITPROXYMODEL_H

qrowslimitproxymodel.cpp

#include "qrowslimitproxymodel.h"

QRowsLimitProxyModel::QRowsLimitProxyModel(int ePageCount, QObject *parent):
     QSortFilterProxyModel(parent),m_eachpageCount(ePageCount)
{

}

bool QRowsLimitProxyModel::filterAcceptsRow(int source_row, const QModelIndex &source_parent) const
{
    Q_UNUSED(source_parent)
    return m_RowMin <= source_row && source_row < m_RowMax ;
}

void QRowsLimitProxyModel::setLimitRows(int r1, int r2)
{
    m_RowMin = r1;
    m_RowMax = r2;
    invalidateFilter();
}

void QRowsLimitProxyModel::setLimitRows(int startRow)
{
    m_RowMin = startRow;
    m_RowMax = m_RowMin + m_eachpageCount;
    invalidateFilter();
}

bool QRowsLimitProxyModel::lessThan(const QModelIndex &source_left, const QModelIndex &source_right) const
{
    return source_left.data(Qt::DisplayRole).value<int>() < source_right.data(Qt::DisplayRole).value<int>();
}

3、使用

初始化

void MainWindow::initsqlmodel()
{
    dbmodel = new QMysqlTableModel(this, db);
    dbmodel->setEditStrategy(QSqlTableModel::OnManualSubmit);

    mProxyModel = new QRowsLimitProxyModel;
    mProxyModel->setSourceModel(dbmodel);
    mProxyModel->setDynamicSortFilter(true);

    showLog("开始");
    select();
    showLog("结束");

    ui->tableView->setModel(mProxyModel);
    //    ui->tableView->setStyleSheet("background-color:rgb(0,0,0)");
//    ui->tableView->horizontalHeader()->setStretchLastSection (true);
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->setSelectionMode(QAbstractItemView::NoSelection);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectColumns);

    ui->tableView->show();
    ui->tableView->setAlternatingRowColors(true);
    ui->tableView->verticalHeader()->setHidden(true);
    ui->tableView->setColumnHidden(0,true);
    getTableHeader("table_inlineaju_datarecoed");

    setViewProperty();

    mSqlActiveTimer = new QTimer(this);
    mSqlActiveTimer->setInterval(3600000);
    connect(mSqlActiveTimer,&QTimer::timeout,[&](){
        getTableHeader("table_inlineaju_datarecoed");
    });
    mSqlActiveTimer->start();
}

查询

void MainWindow::on_pushButton_queryDB_clicked()
{
    qDebug()<<QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss:zzz")<<"start query";
    QString query;

    if(ui->radioButton_startDate->isChecked()){//按某一天查
        query+=QString("startDateTime like '%1%'").arg(ui->dateEdit->date().toString("yyyy-MM-dd"));
    }
    else if(ui->radioButton_startDate_2->isChecked()){//按日期范围查
        QString start  = ui->dateTimeEdit_start->dateTime().toString("yyyy-MM-dd hh:mm:ss");
        QString end = ui->dateTimeEdit_end->dateTime().toString("yyyy-MM-dd hh:mm:ss");
        query+=QString("startDateTime BETWEEN '%1' AND '%2'").arg(start).arg(end);
    }

    if(ui->comboBox_class->currentIndex() != 0){//班次
        if(!query.isEmpty()) query+=" and ";
        query+=QString("classType like '%1%'").arg(ui->comboBox_class->currentText());
    }
    if(ui->comboBox_pos->currentIndex() != 0){//工位
        if(!query.isEmpty()) query+=" and ";
        query+=QString("position like '%%1'").arg(ui->comboBox_pos->currentText());
    }

    if(ui->comboBox_process->currentIndex() !=0){//结果
        if(!query.isEmpty()) query+=" and ";
        int index = ui->comboBox_process->currentIndex();
        if(index == 1 || index == 2){
            query+=QString("Result like '%1%'").arg(ui->comboBox_process->currentText());
        }else if(index==3){
            query+=QString("contactResult like '%1%'").arg("NG");
        }else if(index==4){
            query+=QString("jncdResult like '%1%'").arg("NG");
        }else if(index==5){
            query+=QString("uniformityResult like '%1%'").arg("NG");
        }
    }

    if(!ui->lineEdit_IDinput->text().isEmpty()){//屏体ID
        if(!query.isEmpty()) query+=" and ";
        query+=QString("panelID like '%%1%'").arg(QString(ui->lineEdit_IDinput->text()));
    }

    mCurrFilter = query;
    qDebug()<<query;

    if(!dbmodel){
        QMessageBox::critical(this,"错误","数据库连接失败，无法查询！");
        return;
    }
    dbmodel->setFilter(query);
    while (dbmodel->canFetchMore()) {
        dbmodel->fetchMore();
    }

    double selectNum = dbmodel->rowCount();
    ui->label_6->setText(QString("投入总数:%1").arg(selectNum));
    int ngcount=0,okcount=0;
#if 0
    QSqlQuery q(QString("select Result from table_inlineaju_datarecoed where %1").arg(query));// and Result like 'NG'
    while (q.next()){
        QString data = q.value(0).toString();
        if(data==QLatin1String("OK")){
            ++okcount;
        }else if(data==QLatin1String("NG")){
            ++ngcount;
        }
    }
#else
    // #################################
    // filter之后无需再次查询数据库，直接拿模型数据来处理，提高效率
    for(int r=0;r<selectNum;++r){
        if(dbmodel->data(dbmodel->index(r,10)).toString()=="OK") ++okcount;
        else ++ngcount;
    }
    //##################################
#endif
    QString okstr,ngstr;
    if(selectNum!=0){
    double ok = okcount/selectNum;
    double ng = ngcount/selectNum;
     okstr= QString::number(ok*100,'g',8);
     ngstr= QString::number(ng*100,'g',8);
    }else{
        okstr=ngstr="0";
    }
    ui->label_8->setText(QString("OK:%1(%2%)").arg(okcount).arg(okstr));
    ui->label_10->setText(QString("NG:%1(%2%)").arg(ngcount).arg(ngstr));

    qDebug()<<QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss:zzz")<<"end query";

    updateTotalCount(selectNum);
}

翻页

void MainWindow::updateTotalCount(double count)
{
    double p = qCeil(count / mOnepageCount);
    ui->totalPage->setText(QString("/%1页").arg(p));
    mTotalPage = p;
    mCurrentPage=0;
    jumpToPage(mCurrentPage);
}

bool MainWindow::jumpToPage(int page)
{
    if(!mProxyModel) return false;
    //重新设置需要显示的行范围 [x1,x2]
    mProxyModel->setLimitRows(page * mOnepageCount , ( page + 1 ) * mOnepageCount );
    //更新当前页显示
    ui->currentPage->setText( QString::number(page+1) );
    return true;
}

void MainWindow::on_page_home_clicked()
{
    mCurrentPage = 0;
    jumpToPage(mCurrentPage);
}

void MainWindow::on_page_up_clicked()
{
    mCurrentPage -= 1;
    if(mCurrentPage < 0){
        mCurrentPage = 0;
    }
    jumpToPage(mCurrentPage);
}

void MainWindow::on_page_down_clicked()
{
    mCurrentPage += 1;
    if(mCurrentPage >= mTotalPage-1){
        mCurrentPage = mTotalPage-1;
    }
    jumpToPage(mCurrentPage);
}

void MainWindow::on_page_end_clicked()
{
    mCurrentPage = mTotalPage-1;
    jumpToPage(mCurrentPage);
}

void MainWindow::on_currentPage_returnPressed()
{
    QString input = ui->currentPage->text();
    if(input.isEmpty()) return;

    int toPage = input.toInt() -1 ;
    if(toPage < 0  || toPage >mTotalPage-1) return;
    mCurrentPage = toPage;
    jumpToPage(toPage);
}

4、效果

静态图
在这里插入图片描述
动态图

weixin_46424582

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
QTableview + QSqlTableModel + QSortFilterProxyModel实现MySQL查询和翻页(非limit语句)

很久没写博客了，今天来总结一下。前段时间写了个MySQL的查询和翻页的demo，当时用的是mysql语句的limit，也就是说每一次翻页，都需要操作mysql，有点不流畅，特别是数据量大的情况下，于是继续改进。后来再想想，其实可以用Qt的Model/View模型来实现，也就是QSortFilterProxyModel，通过重写filterAcceptsRow来过滤需要展示的行数，通过lessThan来自定义排序。
复制链接

扫一扫