本章主要介绍qt的数据库的查询功能。

在上一篇qt之数据库的文章基础上增加了数据库的查询功能，软件界面上增加了首记录，前一条，后一条，尾记录按钮。软件界面如下

部分程序如下

MainWindow.h如下

private:QSqlDatabase DB;//数据库连接QSqlTableModel *tabModel;//数据模型QSqlQueryModel *qryModel;QItemSelectionModel *selectModel;//选择模型QDataWidgetMapper *dataMapper;//数据映射TComboBoxDelegate delegateSex;//自定义数据代理 性别TComboBoxDelegate delegateDepart;//自定义数据代理 部门void selectData();void openTable();void showRecordCount();void refreshTableView();
private slots:void on_actFirst_triggered();void on_actPrevious_triggered();void on_actNext_triggered();void on_actLast_triggered();

第一步，进行数据库连接并查询数据库，代码如下

void MainWindow::on_actOpenDB_triggered()
{QString aFile=QFileDialog::getOpenFileName(this,"选择文件","","QSLite数据库(*.db3)");if(aFile.isEmpty())return;DB=QSqlDatabase::addDatabase("QSQLITE");DB.setDatabaseName(aFile);if(DB.open())selectData();//openTable();elseQMessageBox::warning(this,"错误","打开数据库失败");
}void MainWindow::selectData()
{QString str="SELECT empNo,Name,Gender,Birthday,Province,department,""Salary From employee ORDER BY empNo";qryModel =new QSqlQueryModel(this);qryModel->setQuery(str);if(qryModel->lastError().isValid()){QMessageBox::critical(this,"错误","数据表查询错误,错误信息\n"+qryModel->lastError().text());return;}ui->statusbar->showMessage(QString("记录条数：%1").arg(qryModel->rowCount()));QSqlRecord rec=qryModel->record();qryModel->setHeaderData(rec.indexOf("empNo"),Qt::Horizontal,"工号");qryModel->setHeaderData(rec.indexOf("Name"),Qt::Horizontal,"姓名");qryModel->setHeaderData(rec.indexOf("Gender"),Qt::Horizontal,"性别");qryModel->setHeaderData(rec.indexOf("Birthday"),Qt::Horizontal,"出生日期");qryModel->setHeaderData(rec.indexOf("Province"),Qt::Horizontal,"省份");qryModel->setHeaderData(rec.indexOf("Department"),Qt::Horizontal,"部门");qryModel->setHeaderData(rec.indexOf("Salary"),Qt::Horizontal,"工资");//创建数据模型selectModel=new QItemSelectionModel(qryModel,this);connect(selectModel,&QItemSelectionModel::currentRowChanged,this,&MainWindow::do_currentRowChanged);ui->tableView->setModel(qryModel);ui->tableView->setSelectionModel(selectModel);// //字段与widget映射dataMapper=new QDataWidgetMapper(this);dataMapper->setModel(qryModel);dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);dataMapper->addMapping(ui->spinBoxEmpNo,rec.indexOf("empNo"));dataMapper->addMapping(ui->lineEditName,rec.indexOf("Name"));dataMapper->addMapping(ui->comboBoxSex,rec.indexOf("Gender"));dataMapper->addMapping(ui->dateTimeEditBirth,rec.indexOf("Birthday"));dataMapper->addMapping(ui->comboBoxProvince,rec.indexOf("Province"));dataMapper->addMapping(ui->spinBoxSalary,rec.indexOf("Salary"));dataMapper->addMapping(ui->comboBoxDep,rec.indexOf("Department"));dataMapper->toFirst();ui->actOpenDB->setEnabled(false);}

 

记录按钮功能程序实现如下

void MainWindow::on_actFirst_triggered()
{dataMapper->toFirst();refreshTableView();
}void MainWindow::on_actPrevious_triggered()
{dataMapper->toPrevious();refreshTableView();
}void MainWindow::on_actNext_triggered()
{dataMapper->toNext();refreshTableView();
}void MainWindow::on_actLast_triggered()
{dataMapper->toLast();refreshTableView();
}
void MainWindow::refreshTableView()
{int index=dataMapper->currentIndex();QModelIndex curIndex=qryModel->index(index,1);selectModel->clearSelection();selectModel->setCurrentIndex(curIndex,QItemSelectionModel::Select);
}

refreshTableView() 作用是刷新slectModel 当前位置 。

当selectModel索引 位置发生变化时，照片和备注的的变化需要 单独一个槽函数去响应。

代码如下

void MainWindow::do_currentRowChanged(const QModelIndex &current,const QModelIndex &previous)
{Q_UNUSED(previous);if(!current.isValid()){ui->labelPic->clear();ui->plainTextEdit->clear();}dataMapper->setCurrentModelIndex(current);bool first=(current.row()==0);bool last=(current.row()==qryModel->rowCount()-1);ui->actFirst->setEnabled(!first);ui->actPrevious->setEnabled(!first);ui->actNext->setEnabled(!last);ui->actLast->setEnabled(!last);int curRecNo=selectModel->currentIndex().row();QSqlRecord curRec=qryModel->record(curRecNo);int empNo=curRec.value("EmpNo").toInt();QSqlQuery query;query.prepare("select Memo, Photo from employee where EmpNo= :ID");query.bindValue(":ID",empNo);query.exec();query.first();QVariant va=query.value("Photo");if(!va.isValid())ui->labelPic->clear();else{QByteArray data=va.toByteArray();QPixmap pic;pic.loadFromData(data);ui->labelPic->setPixmap(pic.scaledToWidth(ui->labelPic->size().width() ));}QVariant va2=query.value("Memo");ui->plainTextEdit->setPlainText(va2.toString());}

欢迎大家一起交流。