qt之数据库的使用二

本章主要介绍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();
    else
        QMessageBox::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());

}

欢迎大家一起交流。