QT界面中实现数据库查询结果的分页显示

 

昨天做的，今天整理了下。

数据库依然使用的是MySQL。固定每页显示的条数，然后通过SQL语句的limit属性控制查询出的结果条数。然后显示在页面上。页面上提供的功能按钮应包括上一页、下一页和转到第X页。其实本质上的实现都是一样的。还有就是控制输入不能超过最大的分页数。数据库的效率依然是个问题，对于MySQL数据库，超过100万条数据查询速度就会明显变慢。

以下是截图：

程序刚运行出来的时候：

点击下一页到尾页：

转到第X页：

代码：

头文件：

#ifndef DIVIDPAGE_H
#define DIVIDPAGE_H

#include <QtGui/QMainWindow>
#include "ui_dividpage.h"
#include <QVector>
using namespace std;
namespace sql
{
class Connection;
class ResultSet;
namespace mysql
{
   class MySQL_Driver;
}
}
class DividPage : public QMainWindow
{
Q_OBJECT

public:
DividPage(QWidget *parent = 0, Qt::WFlags flags = 0);
~DividPage();
int doConnection(char* dbAddr = "localhost",char* userName = "root" ,char* pwd = "870618",char* dbName = "mlcndbv2");
    int tableRowsNum();
void setTableItem();
int getSumPageNum();

private:
Ui::DividPageClass ui;
sql::Connection * m_Connnection;
sql::mysql::MySQL_Driver *m_SqlDriver;
    QVector<QVector<QString> > doquery(int pageNum);
int currentPageNum,sumTableNum,sumPageNum;
QVector<QVector<QString> > vec;
private slots:
void on_pushButton_3_clicked();
void on_pushButton_2_clicked();
void on_pushButton_clicked();
void on_tableWidget_clicked(const QModelIndex &);
};

#endif // DIVIDPAGE_H

 

CPP文件：

 

#include "dividpage.h"
#include <QTextCodec>
#include "mysqlconnector/mysql_connection.h"
#include "mysqlconnector/mysql_driver.h"
#include "mysqlconnector/cppconn/statement.h"
#include "mysqlconnector/cppconn/prepared_statement.h"
#include "mysqlconnector/cppconn/resultset.h"
#include "mysqlconnector/cppconn/resultset_metadata.h"
#include <QVector>
#include <QTableWidgetItem>
#include <sstream>
#include <QTextCodec>
#include <QMessageBox>
DividPage::DividPage(QWidget *parent, Qt::WFlags flags)
: QMainWindow(parent, flags)
{
ui.setupUi(this);
//设置中文编码方式
QTextCodec::setCodecForTr(QTextCodec::codecForName("gb18030"));
ui.tableWidget->horizontalHeader()->setResizeMode(QHeaderView::Stretch);
ui.tableWidget->verticalHeader()->setResizeMode(QHeaderView::Stretch);
sumTableNum = tableRowsNum();
sumPageNum = getSumPageNum();
QString labelText = QString("总共%1页").arg(QString::number(sumPageNum));
//初始默认当前页为1
currentPageNum = 1;
QString label2Text = QString("当前第%1页").arg(QString::number(currentPageNum));
ui.label->setText(tr(labelText.toStdString().c_str()));
ui.label_2->setText(tr(label2Text.toStdString().c_str()));
//初始默认不能点击上一页
ui.pushButton->setEnabled(false);
ui.pushButton_2->setEnabled(false);
if (sumPageNum > 1)
{
   ui.pushButton_2->setEnabled(true);  
}

doConnection();
setTableItem();


}

DividPage::~DividPage()
{
if(m_Connnection)
{
   m_Connnection->close();
   delete m_Connnection;
   m_Connnection = 0;
}
}

int DividPage::doConnection(char *dbAddr, char *userName , char *pwd , char *dbName ){
m_SqlDriver = sql::mysql::get_mysql_driver_instance();
if(!m_SqlDriver)
{
   return -1;
}

m_Connnection = m_SqlDriver->connect(dbAddr,userName,pwd);

if(!m_Connnection)
{
   return 0;
}
//m_Connnection->setAutoCommit(0);//关闭自动提交，事务手动提交，设置一次即可
m_Connnection->setSchema(dbName);
return 1;
}

void DividPage::on_tableWidget_clicked(const QModelIndex &)
{

}

//根据给出的页码数查询数据库中的记录
QVector<QVector<QString> > DividPage::doquery(int pageNum)
{
//数据库采用utf-8编码
QTextCodec::setCodecForTr(QTextCodec::codecForName("utf-8"));
//vec存储的是返回的5条记录，每条记录的具体信息有vec1保存
vec.clear();
string queryStr;
queryStr = "select * from student limit ";
int beginNum = (pageNum-1)*5;
int endNum = 5;
stringstream ss,ss1;
string strBeginNum;
string strEndNum;
ss<<beginNum;
ss>>strBeginNum;
ss1<<endNum;
ss1>>strEndNum;
QString qqueryStr = QString::fromStdString(queryStr);
qqueryStr.append(QString::fromStdString(strBeginNum));
qqueryStr.append(",");
qqueryStr.append(QString::fromStdString(strEndNum));

sql::Statement * query = m_Connnection->createStatement();
sql::ResultSet * res = query->executeQuery(qqueryStr.toStdString());
while (res->next())
{
   QVector<QString> vec1;
   string name = res->getString(2);
   string sex = res->getString(3);
   string age = res->getString(4);
   string department = res->getString(5);
   //将查询结果转换为QString
   QString qname = tr(name.c_str());
   QString qsex = tr(sex.c_str());
   QString qage = tr(age.c_str());
   QString qdepartment = tr(department.c_str());
   vec1.append(qname);
   vec1.append(qsex);
   vec1.append(qage);
   vec1.append(qdepartment);
   vec.append(vec1);

}
return vec;

}

void DividPage::on_pushButton_clicked()
{
//上一页
ui.tableWidget->clear();
currentPageNum--;
ui.pushButton_2->setEnabled(true);
ui.pushButton->setEnabled(true);
if (currentPageNum == 1)
{
   ui.pushButton->setEnabled(false);
}
QTextCodec::setCodecForTr(QTextCodec::codecForName("gb18030"));
QString label2Text = QString("当前第%1页").arg(QString::number(currentPageNum));
ui.label_2->setText(tr(label2Text.toStdString().c_str()));
setTableItem();

}

void DividPage::on_pushButton_2_clicked()
{
//下一页
ui.tableWidget->clear();
currentPageNum++;
ui.pushButton->setEnabled(true);
ui.pushButton_2->setEnabled(true);
if (currentPageNum == sumPageNum)
{
   ui.pushButton_2->setEnabled(false);
}
QTextCodec::setCodecForTr(QTextCodec::codecForName("gb18030"));
QString label2Text = QString("当前第%1页").arg(QString::number(currentPageNum));
ui.label_2->setText(tr(label2Text.toStdString().c_str()));
setTableItem();

}

//返回数据库表中的条数
int DividPage::tableRowsNum(){
doConnection();
sql::Statement * query = m_Connnection->createStatement();
sql::ResultSet * res = query->executeQuery("select count(*) from student");
res->next();
string strNum =res->getString(1);
int num =atoi(strNum.c_str());
return num;

}

void DividPage::setTableItem(){
doquery(currentPageNum);
for (int i = 0;i < vec.size();i++)
{
   QVector<QString> vec_row = vec.at(i);
   for (int j = 0;j < vec_row.size();j++)
   {
    QString qstr = vec_row.at(j);  
    QTableWidgetItem *tableItem = new QTableWidgetItem(qstr);
    //使文本框居中显示
    //tableItem->setTextAlignment(5);
    ui.tableWidget->setItem(i,j,tableItem);
   }
}
}

int DividPage::getSumPageNum(){
return (sumTableNum%5 == 0)?(sumTableNum/5):(sumTableNum/5+1);
}

//转到XX页
void DividPage::on_pushButton_3_clicked()
{


QString goPageNum = ui.lineEdit->text();
currentPageNum = goPageNum.toInt();
if (currentPageNum > sumPageNum)
{
   QMessageBox msg;
   QTextCodec::setCodecForTr(QTextCodec::codecForName("gb18030"));
   msg.setText(tr("输入页码超过能显示的最大页数，请重新输入"));
   msg.exec();
}
else{
   ui.tableWidget->clear();
   ui.pushButton->setEnabled(true);
   ui.pushButton_2->setEnabled(true);
   if (currentPageNum == 1)
   {
    ui.pushButton->setEnabled(false);
   }
   if (currentPageNum == sumPageNum)
   {
    ui.pushButton_2->setEnabled(false);
   }
   doquery(currentPageNum);
   QTextCodec::setCodecForTr(QTextCodec::codecForName("gb18030"));
   QString label2Text = QString("当前第%1页").arg(QString::number(currentPageNum));
   ui.label_2->setText(tr(label2Text.toStdString().c_str()));
   setTableItem();
}

}