【QT学习】——表格的分页、上下翻页操作（读取数据库后的数据界面显示）

表格读取数据库代码

表格先通过读取数据库，获取到需要读取并显示在界面表格内的数据信息
分页操作的数据库语句：“select * from tb_video limit 3 offset 0”

strcpy(sql,"select * from tb_video limit 3 offset 0");
SignalDB::getInstance()->getTableBySelect(sql,row,col,datares);

SinalDB类里面代码：

#include "signaldb.h"

SignalDB *SignalDB::db=nullptr;
SignalDB::SignalDB()
{
    openDataBase("../data/videoDemo.db");
}

SignalDB::~SignalDB()
{
    closeDataBase();
    delete this->sqldb;
}

SignalDB *SignalDB::getInstance()
{
    //单例设计模式

    if(SignalDB::db==nullptr)
    {
        SignalDB::db=new SignalDB();
    }
    return SignalDB::db;
}

void SignalDB::ReleseInstance()
{

}

int SignalDB::openDataBase(char *path)
{
    int res;
    res=sqlite3_open(path,&this->sqldb);
    if(res==SQLITE_OK)
    {
        qDebug()<<"open sucess";
    }
    else
    {
        qDebug()<<sqlite3_errcode(this->sqldb);
        qDebug()<<sqlite3_errmsg(this->sqldb);
    }
    return res;
}

void SignalDB::closeDataBase()
{
    int res=sqlite3_close(this->sqldb);
    if(res==SQLITE_OK)
    {
        qDebug()<<"open sucess";
    }
    else
    {
        qDebug()<<sqlite3_errcode(this->sqldb);
        qDebug()<<sqlite3_errmsg(this->sqldb);
    }
}

int SignalDB::getTableBySelect(char *sql, int &row, int &col, char **&datares)
{
    int res=sqlite3_get_table(this->sqldb,sql,&datares,&row,&col,&this->errmsg);
    if(res==SQLITE_OK)
    {
        qDebug()<<"open sucess";
    }
    else
    {
        qDebug()<<sqlite3_errcode(this->sqldb);
        qDebug()<<sqlite3_errmsg(this->sqldb);
    }
    return res;
}

表格向上翻页

代码：

void Widget::on_prevBtn_click()
{
    if(this->curPageLab->text().toInt()>1)
    {
        this->model->setRowCount(0);

        int cur=this->curPageLab->text().toInt();
        cur--;
        int index=(cur-1)*3;

        sprintf(sql,"select * from tb_video limit 3 offset %d",index);
        SignalDB::getInstance()->getTableBySelect(sql,row,col,datares);
        for(int i=0;i<row;i++)
        {
            for(int j=0;j<col;j++)
            {
                this->model->setItem(i,j,new QStandardItem(datares[(i+1)*col+j]));
            }
        }
        this->curPageLab->setText(QString::number(cur));
    }
}

表格向下翻页操作代码

代码：

void Widget::on_nextBtn_click()
{
    if(this->curPageLab->text().toInt()<this->countPageLab->text().toInt())
    {
        this->model->setRowCount(0);

        int cur=this->curPageLab->text().toInt();
        cur++;
        int index=(cur-1)*3;

        sprintf(sql,"select * from tb_video limit 3 offset %d",index);
        SignalDB::getInstance()->getTableBySelect(sql,row,col,datares);
        for(int i=0;i<row;i++)
        {
            for(int j=0;j<col;j++)
            {
                this->model->setItem(i,j,new QStandardItem(datares[(i+1)*col+j]));
            }
        }
        this->curPageLab->setText(QString::number(cur));
    }
}

全部代码展示

.h头文件：

#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>
#include<QTableView>
#include<QStandardItemModel>
#include<QHeaderView>
#include<QPushButton>
#include<QLabel>
#include<sqlite3.h>
#include<QString>
class Widget : public QWidget
{
    Q_OBJECT

public:
    Widget(QWidget *parent = 0);
    ~Widget();
    void DataBaseInit();
private:
    QTableView *tv;
    QStandardItemModel *model;
    QPushButton *prevBtn;
    QPushButton *nextBtn;
    QLabel * curPageLab;
    QLabel * segmentLab;
    QLabel * countPageLab;
    sqlite3 * psqlite3;
    char *errmsg;//接受错误信息
    char **datares;//接收结果集
    int row;//接收结果集有多少行
    int col;//接收结果集有多少列
    char sql[100];//sql语句

    int countPage;

 public slots:
    void on_prevBtn_click();
    void on_nextBtn_click();
};

#endif // WIDGET_H

.cpp文件：

#include "widget.h"
#include"tools/signaldb.h"
Widget::Widget(QWidget *parent)
    : QWidget(parent)
{
    resize(900,300);
    this->tv=new QTableView(this);
    this->tv->setFixedSize(900,200);

    this->model=new QStandardItemModel(3,7,this);
    this->tv->setModel(this->model);
    //水平和垂直方向自适应窗口大小
    this->tv->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    this->tv->verticalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    //隐藏行号
    this->tv->verticalHeader()->hide();
    //关闭单元格编辑
    this->tv->setEditTriggers(QAbstractItemView::NoEditTriggers);

    //暂时使用绝对布局
    this->tv->setGeometry(0,0,900,200);

    this->prevBtn=new QPushButton("上一页",this);
    this->prevBtn->setGeometry(200,225,100,50);
    this->nextBtn=new QPushButton("下一页",this);
    this->nextBtn->setGeometry(600,225,100,50);
    this->curPageLab=new QLabel("1",this);
    this->curPageLab->setGeometry(400,225,10,50);
    this->segmentLab=new QLabel("/",this);
    this->segmentLab->setGeometry(450,225,100,50);
    this->countPageLab=new QLabel("1",this);
    this->countPageLab->setGeometry(500,225,100,50);
    DataBaseInit();
    connect(this->prevBtn,SIGNAL(clicked(bool)),this,SLOT(on_prevBtn_click()));
    connect(this->nextBtn,SIGNAL(clicked(bool)),this,SLOT(on_nextBtn_click()));

}

Widget::~Widget()
{

}

void Widget::DataBaseInit()
{
    //拼接sql语句
    strcpy(sql,"select * from tb_video");


    SignalDB::getInstance()->getTableBySelect(sql,row,col,datares);

    if(row%3==0)
    {
        this->countPage=row/3;

    }
    else
    {
        this->countPage=row/3+1;
    }
    this->countPageLab->setText(QString::number(countPage));

    //根据数据库数据设置列数
    this->model->setColumnCount(col);
    this->model->setRowCount(3);


    strcpy(sql,"select * from tb_video limit 3 offset 0");


    SignalDB::getInstance()->getTableBySelect(sql,row,col,datares);

    for(int i=0;i<col;i++)
    {
        this->model->setHeaderData(i,Qt::Horizontal,datares[i]);
    }

    for(int i=0;i<row;i++)
    {
        for(int j=0;j<col;j++)
        {
            this->model->setItem(i,j,new QStandardItem(datares[(i+1)*col+j]));
        }
    }
}

void Widget::on_prevBtn_click()
{
    if(this->curPageLab->text().toInt()>1)
    {
        this->model->setRowCount(0);

        int cur=this->curPageLab->text().toInt();
        cur--;
        int index=(cur-1)*3;

        sprintf(sql,"select * from tb_video limit 3 offset %d",index);
        SignalDB::getInstance()->getTableBySelect(sql,row,col,datares);
        for(int i=0;i<row;i++)
        {
            for(int j=0;j<col;j++)
            {
                this->model->setItem(i,j,new QStandardItem(datares[(i+1)*col+j]));
            }
        }
        this->curPageLab->setText(QString::number(cur));
    }
}

void Widget::on_nextBtn_click()
{
    if(this->curPageLab->text().toInt()<this->countPageLab->text().toInt())
    {
        this->model->setRowCount(0);

        int cur=this->curPageLab->text().toInt();
        cur++;
        int index=(cur-1)*3;

        sprintf(sql,"select * from tb_video limit 3 offset %d",index);
        SignalDB::getInstance()->getTableBySelect(sql,row,col,datares);
        for(int i=0;i<row;i++)
        {
            for(int j=0;j<col;j++)
            {
                this->model->setItem(i,j,new QStandardItem(datares[(i+1)*col+j]));
            }
        }
        this->curPageLab->setText(QString::number(cur));
    }
}

结果图展示