十一、Qt数据库操作

一、Sql介绍

Qt Sql模块包含多个类，实现数据库的连接，Sql语句的执行，数据获取与界面显示，数据与界面直接使用Model/View结构。

1、使用Sql模块

（1）工程加入

QT += sql

（2）添加头文件

#include <QtSel>

2、Sql相关类

1、数据库相关类

• QTableView：常用的数据库内容显示组件
• QSalQuryModel：通过设置select语句查询获取数据库内容，数据只读。
• QSqlTableModel：直接设置一个数据表的名称，可以获取苏韩剧表的全部记录，可以编辑。
• QSqlRelationalTableModel：为单张的数据库表提供了一个编辑的数据模型，支持外键。

二、QSqltableModel

1、实现程序

（1）创建项目，基于QMainWindow

（2）添加类

（3）添加组件

（4）加载数据库

void MainWindow::openTable()
{tabModel = new QSqlTableModel(this, DB);tabModel->setTable("employee"); //设置数据表名称tabModel->setSort(tabModel->fieldIndex("empNo"), Qt::AscendingOrder); //按照员工号升序tabModel->setEditStrategy(QSqlTableModel::OnManualSubmit); //手动提交数据if(!tabModel->select()){QMessageBox::critical(this, "错误", "打开数据表错误，错误信息\n"+ tabModel->lastError().text());return;}// 修改表头tabModel->setHeaderData(tabModel->fieldIndex("empNo"), Qt::Horizontal, "工号");tabModel->setHeaderData(tabModel->fieldIndex("Name"), Qt::Horizontal, "姓名");tabModel->setHeaderData(tabModel->fieldIndex("Gender"), Qt::Horizontal, "性别");tabModel->setHeaderData(tabModel->fieldIndex("Height"), Qt::Horizontal, "身高");tabModel->setHeaderData(tabModel->fieldIndex("Birthday"), Qt::Horizontal, "出生日期");tabModel->setHeaderData(tabModel->fieldIndex("Mobile"), Qt::Horizontal, "手机号");tabModel->setHeaderData(tabModel->fieldIndex("Province"), Qt::Horizontal, "省份");tabModel->setHeaderData(tabModel->fieldIndex("City"), Qt::Horizontal, "城市");tabModel->setHeaderData(tabModel->fieldIndex("Depart"), Qt::Horizontal, "部门");tabModel->setHeaderData(tabModel->fieldIndex("Education"), Qt::Horizontal, "学历");tabModel->setHeaderData(tabModel->fieldIndex("Salary"), Qt::Horizontal, "薪资");tabModel->setHeaderData(tabModel->fieldIndex("Photo"), Qt::Horizontal, "照片");tabModel->setHeaderData(tabModel->fieldIndex("Memo"), Qt::Horizontal, "备注");theSelection = new QItemSelectionModel(tabModel);ui->tableView->setModel(tabModel);ui->tableView->setSelectionModel(theSelection);connect(theSelection, SIGNAL(currentChanged(QModelIndex, QModelIndex)),this, SLOT(on_currentChanged(QModelIndex, QModelIndex)));connect(theSelection, SIGNAL(currentRowChanged(QModelIndex, QModelIndex)),this, SLOT(on_currentRowChanged(QModelIndex, QModelIndex)));// 隐藏列ui->tableView->setColumnHidden(tabModel->fieldIndex("Photo"), true);ui->tableView->setColumnHidden(tabModel->fieldIndex("Memo"), true);dataMapper = new QDataWidgetMapper;dataMapper->setModel(tabModel);dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);dataMapper->addMapping(ui->spinBoxNum, tabModel->fieldIndex("empNo"));dataMapper->addMapping(ui->lineEditName, tabModel->fieldIndex("Name"));dataMapper->addMapping(ui->comboBoxSex, tabModel->fieldIndex("Gender"));dataMapper->addMapping(ui->doubleSpinBoxHeight, tabModel->fieldIndex("Height"));dataMapper->addMapping(ui->lineEditBirthday, tabModel->fieldIndex("Birthday"));dataMapper->addMapping(ui->lineEditPhone, tabModel->fieldIndex("Mobile"));dataMapper->addMapping(ui->comboBoxProvince, tabModel->fieldIndex("Province"));dataMapper->addMapping(ui->lineEditCity, tabModel->fieldIndex("City"));dataMapper->addMapping(ui->comboBoxWork, tabModel->fieldIndex("Depart"));dataMapper->addMapping(ui->comboBoxStudy, tabModel->fieldIndex("Education"));dataMapper->addMapping(ui->textEditInfo, tabModel->fieldIndex("Memo"));getFiledNames();ui->actOpen->setEnabled(false);ui->actAppend->setEnabled(true);ui->actDelete->setEnabled(true);ui->actInsert->setEnabled(true);ui->actScan->setEnabled(true);ui->groupBoxSort->setEnabled(true);ui->groupBoxFilter->setEnabled(true);// 使用delegate实现下拉选择QStringList strList;strList << "男" << "女";bool isEditable = false;delegateSex.setItem(strList, isEditable);ui->tableView->setItemDelegateForColumn(tabModel->fieldIndex("Gender"), &delegateSex);
}void MainWindow::getFiledNames()
{QSqlRecord emptyRec = tabModel->record();for (int i = 0; i < emptyRec.count(); ++i){ui->comboBoxFields->addItem(emptyRec.fieldName(i));}
}void MainWindow::on_currentChanged(const QModelIndex &current, const QModelIndex &preivous)
{Q_UNUSED(current)Q_UNUSED(preivous)ui->actSubmit->setEnabled(tabModel->isDirty()); // 是否有数据修改ui->actRevert->setEnabled(tabModel->isDirty()); // 是否有数据修改
}void MainWindow::on_currentRowChanged(const QModelIndex &current, const QModelIndex &preivous)
{ui->actDelete->setEnabled(current.isValid());ui->actAppend->setEnabled(current.isValid());ui->actInsert->setEnabled(current.isValid());if(! current.isValid()){ui->labelPhoto->clear();return;}dataMapper->setCurrentIndex(current.row());QSqlRecord curRec = tabModel->record(current.row());if(curRec.isNull("Photo")){ui->labelPhoto->clear();}else{QByteArray data  = curRec.value("Photo").toByteArray();QPixmap pic;pic.loadFromData(data);ui->labelPhoto->setPixmap(pic.scaledToWidth(ui->labelPhoto->size().width()));}
}void MainWindow::on_actOpen_triggered()
{QString fileName = QFileDialog::getOpenFileName(this, "选择数据库文件","", "Sqlite数据库(*.db *.db3)");if(fileName.isEmpty()){return;}DB = QSqlDatabase::addDatabase("QSQLITE"); //添加数据库驱动DB.setDatabaseName(fileName); // 设置数据库名称if(!DB.open()){QMessageBox::warning(this, "错误", "打开数据库失败");return;}openTable();
}

（5）实现工具栏按钮功能

void MainWindow::on_actAppend_triggered()
{tabModel->insertRow(tabModel->rowCount(), QModelIndex());QModelIndex curIndex = tabModel->index(tabModel->rowCount() - 1, 1); // 插入后增加一行theSelection->clearSelection();theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select);tabModel->setData(tabModel->index(curIndex.row(), 0), 2000 + tabModel->rowCount());tabModel->setData(tabModel->index(curIndex.row(), 2), "男");ui->actSubmit->setEnabled(true);ui->actRevert->setEnabled(true);
}void MainWindow::on_actInsert_triggered()
{QModelIndex curIndex = theSelection->currentIndex();tabModel->insertRow(curIndex.row(), QModelIndex());theSelection->clearSelection();theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select);tabModel->setData(tabModel->index(curIndex.row(), 0), 2000 + tabModel->rowCount());tabModel->setData(tabModel->index(curIndex.row(), 2), "男");ui->actSubmit->setEnabled(true);ui->actRevert->setEnabled(true);
}void MainWindow::on_actDelete_triggered()
{QModelIndex curIndex = theSelection->currentIndex();tabModel->removeRow(curIndex.row());ui->actSubmit->setEnabled(true);ui->actRevert->setEnabled(true);
}void MainWindow::on_actSubmit_triggered()
{bool result = tabModel->submitAll();if(!result){QMessageBox::information(this, "信息", "数据提交错误，错误信息\n"+ tabModel->lastError().text());}else{ui->actSubmit->setEnabled(false);ui->actRevert->setEnabled(false);}
}void MainWindow::on_actRevert_triggered()
{tabModel->revertAll();ui->actSubmit->setEnabled(false);ui->actRevert->setEnabled(false);
}void MainWindow::on_actSetPhoto_triggered()
{QString fileName = QFileDialog::getOpenFileName(this, "选择图片", "", "照片(*.jpg *.png)");if(fileName.isEmpty()){return;}QByteArray data;QFile *file = new QFile(fileName);if(file->open(QIODevice::ReadOnly)){data = file->readAll();QModelIndex curIndex = theSelection->currentIndex();QSqlRecord curRec = tabModel->record(curIndex.row());curRec.setValue("Photo", data);tabModel->setRecord(curIndex.row(), curRec);QPixmap pic;pic.load(fileName);ui->labelPhoto->setPixmap(pic.scaledToWidth(ui->labelPhoto->width()));file->close();}delete file;
}void MainWindow::on_actClearPhoto_triggered()
{QModelIndex curIndex = theSelection->currentIndex();QSqlRecord curRec = tabModel->record(curIndex.row());curRec.setNull("Photo");tabModel->setRecord(curIndex.row(), curRec);ui->labelPhoto->clear();
}void MainWindow::on_actScan_triggered()
{if(tabModel->rowCount() != 0){for (int i = 0; i < tabModel->rowCount(); ++i){QSqlRecord aRec = tabModel->record(i);float salary = aRec.value("Salary").toFloat();salary *= 1.1;aRec.setValue("Salary", salary);tabModel->setRecord(i, aRec);}if(tabModel->submitAll()){QMessageBox::information(this, "信息", "涨工资完成");}}
}void MainWindow::on_comboBoxFields_currentIndexChanged(int index)
{if(ui->rbtnAscend->isCheckable()){tabModel->setSort(index, Qt::AscendingOrder);}else{tabModel->setSort(index, Qt::DescendingOrder);}tabModel->select(); // 重新从数据库装载
}void MainWindow::on_rbtnAscend_clicked()
{tabModel->setSort(ui->comboBoxFields->currentIndex(), Qt::AscendingOrder);tabModel->select(); // 重新从数据库装载
}void MainWindow::on_rbtnDescend_clicked()
{tabModel->setSort(ui->comboBoxFields->currentIndex(), Qt::DescendingOrder);tabModel->select(); // 重新从数据库装载
}void MainWindow::on_rbtnMan_clicked()
{tabModel->setFilter("Gender='男'");
}void MainWindow::on_rbtnWoman_clicked()
{tabModel->setFilter("Gender='女'");
}void MainWindow::on_rbtnAll_clicked()
{tabModel->setFilter("");
}

三、QSqlQueryModel

1、相关类

QAbstractTableModelQSqlQueryModel //封装了指向SELECT语句从数据库查询数据的功能QSqlTableModelQSqlRelationalTableModel

2、实现程序

1、创建项目，基于QMainWindow

2、添加图标资源文件，添加工具按钮

3、添加组件

4、实现功能

#include "mainwindow.h"
#include "ui_mainwindow.h"MainWindow::MainWindow(QWidget *parent) :QMainWindow(parent),ui(new Ui::MainWindow)
{ui->setupUi(this);qryModel = new QSqlQueryModel(this);theSelection = new QItemSelectionModel(qryModel);dataMapper = new QDataWidgetMapper(this);dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);dataMapper->setModel(qryModel);ui->tableView->setModel(qryModel);ui->tableView->setSelectionModel(theSelection);connect(theSelection, SIGNAL(currentRowChanged(QModelIndex, QModelIndex)),this, SLOT(on_currentRowChanged(QModelIndex, QModelIndex)));
}MainWindow::~MainWindow()
{delete ui;
}#include <QFileDialog>
#include <QMessageBox>
void MainWindow::on_actOpenDB_triggered()
{QString fileName = QFileDialog::getOpenFileName(this, "打开数据库", "","数据库文件(*.db *.db3)");if(fileName.isEmpty()){return;}DB = QSqlDatabase::addDatabase("QSQLITE");DB.setDatabaseName(fileName);if(!DB.open()){QMessageBox::warning(this, "错误", "打开数据库失败");return;}qryModel->setQuery("SELECT EmpNo,Name,Gender,Height,Birthday,Mobile,Province,City,Depart,Education,Salary FROM employee ORDER BY EMpNo", DB);if(qryModel->lastError().isValid()){QMessageBox::critical(this, "错误", "查询失败\n" + qryModel->lastError().text());return;}qryModel->setHeaderData(0, Qt::Horizontal, "工号");qryModel->setHeaderData(2, Qt::Horizontal, "性别");dataMapper->addMapping(ui->spinBoxNum, 0);dataMapper->addMapping(ui->lineEditName, 1);dataMapper->addMapping(ui->comboBoxSex, 2);dataMapper->addMapping(ui->doubleSpinBoxHeight, 3);dataMapper->addMapping(ui->lineEditBirthday, 4);dataMapper->addMapping(ui->lineEditPhone, 5);dataMapper->addMapping(ui->comboBoxProvince, 6);dataMapper->addMapping(ui->lineEditCity, 7);dataMapper->addMapping(ui->comboBoxWork, 8);dataMapper->addMapping(ui->comboBoxStudy, 9);dataMapper->addMapping(ui->textEditInfo, 10);// dataMapper->toFirst();ui->actOpenDB->setEnabled(false);}void MainWindow::on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{if(!current.isValid()){ui->labelPhoto->clear();return;}dataMapper->setCurrentModelIndex(current);bool first = (current.row() == 0);bool last = (current.row() == qryModel->rowCount() - 1);ui->actRecFirst->setEnabled(!first);ui->actRecPrevious->setEnabled(!first);ui->actRecNext->setEnabled(!last);ui->actRecLast->setEnabled(!last);int curRecNo = theSelection->currentIndex().row();QSqlRecord curRec = qryModel->record(curRecNo);int empNo = curRec.value("EmpNo").toInt();QSqlQuery query;query.prepare("select EmpNo,Memo,Photo from employee where EmpNo = :ID");query.bindValue(":ID", empNo); //防注入query.exec();query.first(); // 回到第一条记录if(qryModel->lastError().isValid()){QMessageBox::critical(this, "错误", "查询失败\n" + qryModel->lastError().text());return;}QVariant va = query.value("Photo");if(!va.isValid()){ui->labelPhoto->clear();}else{QPixmap pic;QByteArray data = va.toByteArray();pic.loadFromData(data);ui->labelPhoto->setPixmap(pic.scaledToWidth(ui->labelPhoto->size().width()));}QVariant va2 = query.value("Memo");ui->textEditInfo->setPlainText(va2.toString());
}void MainWindow::on_actRecFirst_triggered()
{dataMapper->toFirst();int index = dataMapper->currentIndex();QModelIndex curIndex = qryModel->index(index, 1);theSelection->clearSelection();theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select);
}void MainWindow::on_actRecPrevious_triggered()
{dataMapper->toPrevious();int index = dataMapper->currentIndex();QModelIndex curIndex = qryModel->index(index, 1);theSelection->clearSelection();theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select);
}void MainWindow::on_actRecNext_triggered()
{dataMapper->toNext();int index = dataMapper->currentIndex();QModelIndex curIndex = qryModel->index(index, 1);theSelection->clearSelection();theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select);
}void MainWindow::on_actRecLast_triggered()
{dataMapper->toLast();int index = dataMapper->currentIndex();QModelIndex curIndex = qryModel->index(index, 1);theSelection->clearSelection();theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select);
}

四、QSqlQuery

QSqlQuery是可以执行任意SQL语句的类，如SELECT、INSERT、UPDATE、DELETE等。

1、实现程序

（1）创建项目，基于QMainWindow

（2）添加工具栏按钮

（3）添加对话框

#include "dialogdata.h"
#include "ui_dialogdata.h"DialogData::DialogData(QWidget *parent) :QDialog(parent),ui(new Ui::DialogData)
{ui->setupUi(this);
}DialogData::~DialogData()
{delete ui;
}void DialogData::setUpdateRecord(QSqlRecord &recData)
{mRecord = recData;ui->spinBoxEmpNo->setEnabled(false);setWindowTitle("更新记录");// 更新界面ui->spinBoxEmpNo->setValue(recData.value("EmpNo").toInt());ui->lineEditName->setText(recData.value("Name").toString());ui->comboBoxSex->setCurrentText(recData.value("Gender").toString());ui->doubleSpinBoxHeight->setValue(recData.value("Height").toFloat());ui->lineEditBirthday->setText(recData.value("Birthday").toString());ui->lineEditPhone->setText(recData.value("Mobile").toString());ui->comboBoxProvince->setCurrentText(recData.value("Province").toString());ui->lineEditCity->setText(recData.value("City").toString());ui->comboBoxDepart->setCurrentText(recData.value("Depart").toString());ui->comboBoxEducation->setCurrentText(recData.value("Education").toString());ui->spinBoxSalary->setValue(recData.value("Salary").toInt());ui->textEditInfo->setText(recData.value("Memo").toString());QVariant va = recData.value("Photo");if(!va.isValid()){ui->labelPhoto->clear();}else{QByteArray data = va.toByteArray();QPixmap pic;pic.loadFromData(data);ui->labelPhoto->setPixmap(pic.scaledToWidth(ui->labelPhoto->size().width()));}
}void DialogData::setInsertRecord(QSqlRecord &recData)
{mRecord = recData;ui->spinBoxEmpNo->setEnabled(true);setWindowTitle("新建记录");ui->spinBoxEmpNo->setValue(recData.value("EmpNo").toInt());
}QSqlRecord DialogData::getRecordData()
{mRecord.setValue("EmpNo", ui->spinBoxEmpNo->value());mRecord.setValue("Name", ui->lineEditName->text());mRecord.setValue("Gender", ui->comboBoxSex->currentText());mRecord.setValue("Height", ui->doubleSpinBoxHeight->value());mRecord.setValue("Birthday", ui->lineEditBirthday->text());mRecord.setValue("Mobile", ui->lineEditPhone->text());mRecord.setValue("Province", ui->comboBoxProvince->currentText());mRecord.setValue("City", ui->lineEditCity->text());mRecord.setValue("Depart", ui->comboBoxDepart->currentText());mRecord.setValue("Education", ui->comboBoxEducation->currentText());mRecord.setValue("Salary", ui->spinBoxSalary->value());mRecord.setValue("Memo", ui->textEditInfo->toPlainText());return mRecord;
}#include <QFileDialog>
void DialogData::on_btnLoadPhoto_clicked()
{QString fileName = QFileDialog::getOpenFileName(this, "选择图片", "","图片(*.png *.jpg)");if(fileName.isEmpty()){return;}QByteArray data;QFile *file = new QFile(fileName);file->open(QIODevice::ReadOnly);data = file->readAll();file->close();delete file;mRecord.setValue("Photo", data);QPixmap pic;pic.loadFromData(data);ui->labelPhoto->setPixmap(pic.scaledToWidth(ui->labelPhoto->size().width()));
}void DialogData::on_btnClearPhoto_clicked()
{}

（4）工具栏按钮功能

#include "mainwindow.h"
#include "ui_mainwindow.h"#include <QMessageBox>#include "dialogdata.h"MainWindow::MainWindow(QWidget *parent) :QMainWindow(parent),ui(new Ui::MainWindow)
{ui->setupUi(this);setCentralWidget(ui->tableView);qryModel = new QSqlQueryModel;theSelection = new QItemSelectionModel(qryModel);ui->tableView->setModel(qryModel);ui->tableView->setSelectionModel(theSelection);connect(ui->tableView, SIGNAL(doubleClicked(QModelIndex)),this, SLOT(on_TableView_doubleClicked(QModelIndex)));
}MainWindow::~MainWindow()
{delete ui;
}void MainWindow::openTable()
{qryModel->setQuery("SELECT EmpNo,Name,Gender,Height,Birthday,Mobile,Province,City,Depart,Education,Salary From employee order by EmpNo");if(qryModel->lastError().isValid()){QMessageBox::warning(this, "错误", "SqlCmdError" + qryModel->lastError().text());return;}qryModel->setHeaderData(0, Qt::Horizontal, "工号");qryModel->setHeaderData(1, Qt::Horizontal, "姓名");qryModel->setHeaderData(2, Qt::Horizontal, "性别");qryModel->setHeaderData(3, Qt::Horizontal, "身高");qryModel->setHeaderData(4, Qt::Horizontal, "出生日期");qryModel->setHeaderData(5, Qt::Horizontal, "手机");qryModel->setHeaderData(6, Qt::Horizontal, "省份");qryModel->setHeaderData(7, Qt::Horizontal, "城市");qryModel->setHeaderData(8, Qt::Horizontal, "部门");qryModel->setHeaderData(9, Qt::Horizontal, "学历");qryModel->setHeaderData(10, Qt::Horizontal, "工资");//    ui->tableView->resizeColumnsToContents(); // 自动调整列宽//    ui->tableView->horizontalHeader()->setStretchLastSection(true); //拉伸最后一列ui->actOpenDB->setEnabled(false);ui->actRecInsert->setEnabled(true);ui->actRecDelete->setEnabled(true);ui->actRecEdit->setEnabled(true);ui->actScan->setEnabled(true);}#include <QFileDialog>void MainWindow::on_actOpenDB_triggered()
{QString fileName = QFileDialog::getOpenFileName(this, "选择数据库","", "SQlite数据库(*.db *.db3)");if(fileName.isEmpty()){return;}DB = QSqlDatabase::addDatabase("QSQLITE");DB.setDatabaseName(fileName);if(!DB.open()){QMessageBox::warning(this, "错误", "打开数据库失败");return;}openTable();}void MainWindow::on_actRecInsert_triggered()
{QSqlQuery query;query.exec("select * from employee where EmpNo = -1");DialogData *dataDlg = new DialogData;Qt::WindowFlags flags = dataDlg->windowFlags();dataDlg->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint); //固定大小QSqlRecord curData = query.record();curData.setValue("EmpNo", qryModel->rowCount() + 1000);dataDlg->setInsertRecord(curData);int ret = dataDlg->exec();if(ret == QDialog::Accepted){QSqlRecord recData =  dataDlg->getRecordData();query.prepare("INSERT INTO employee (EmpNo,Name,Gender,Height,Birthday,Mobile,Province,City,Depart,Education,Salary,Memo,Photo) ""VALUES(:EmpNo,:Name,:Gender,:Height,:Birthday,:Mobile,:Province,:City,:Depart,:Education,:Salary,:Memo,:Photo)");query.bindValue(":EmpNo", recData.value("EmpNo"));query.bindValue(":Name", recData.value("Name"));query.bindValue(":Gender", recData.value("Gender"));query.bindValue(":Height", recData.value("Height"));query.bindValue(":Birthday", recData.value("Birthday"));query.bindValue(":Mobile", recData.value("Mobile"));query.bindValue(":Province", recData.value("Province"));query.bindValue(":City", recData.value("City"));query.bindValue(":Depart", recData.value("Depart"));query.bindValue(":Education", recData.value("Education"));query.bindValue(":Salary", recData.value("Salary"));query.bindValue(":Memo", recData.value("Memo"));query.bindValue(":Photo", recData.value("Photo"));if(!query.exec()){QMessageBox::critical(this, "error", "Information:" + query.lastError().text());}else{qryModel->setQuery("SELECT EmpNo,Name,Gender,Height,Birthday,Mobile,Province,City,Depart,Education,Salary From employee order by EmpNo");if(qryModel->lastError().isValid()){QMessageBox::warning(this, "错误", "SqlCmdError" + qryModel->lastError().text());return;}}}delete dataDlg;
}void MainWindow::on_actRecEdit_triggered()
{int curRecNo = theSelection->currentIndex().row();QSqlRecord curRec = qryModel->record(curRecNo);int empNo = curRec.value("EmpNo").toInt();QSqlQuery query;query.prepare("select * from employee where EmpNo = :ID");query.bindValue(":ID", empNo);query.exec();query.first();if(!query.isValid()){return;}curRec = query.record();DialogData *dataDlg = new DialogData;Qt::WindowFlags flags = dataDlg->windowFlags();dataDlg->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint); //固定大小dataDlg->setUpdateRecord(curRec);int ret = dataDlg->exec();if(ret == QDialog::Accepted){QSqlRecord recData =  dataDlg->getRecordData();query.prepare("update employee set ""Name=:Name,Gender=:Gender,Height=:Height,Birthday=:Birthday,""Mobile=:Mobile,Province=:Province,City=:City,Depart=:Depart,""Education=:Education,Salary=:Salary,Memo=:Memo,Photo=:Photo where EmpNo=:ID");query.bindValue(":Name", recData.value("Name"));query.bindValue(":Gender", recData.value("Gender"));query.bindValue(":Height", recData.value("Height"));query.bindValue(":Birthday", recData.value("Birthday"));query.bindValue(":Mobile", recData.value("Mobile"));query.bindValue(":Province", recData.value("Province"));query.bindValue(":City", recData.value("City"));query.bindValue(":Depart", recData.value("Depart"));query.bindValue(":Education", recData.value("Education"));query.bindValue(":Salary", recData.value("Salary"));query.bindValue(":Memo", recData.value("Memo"));query.bindValue(":Photo", recData.value("Photo"));query.bindValue(":ID", recData.value("EmpNo"));if(!query.exec()){QMessageBox::critical(this, "error", "Information:" + query.lastError().text());}else{qryModel->setQuery("SELECT EmpNo,Name,Gender,Height,Birthday,Mobile,Province,City,Depart,Education,Salary From employee order by EmpNo");if(qryModel->lastError().isValid()){QMessageBox::warning(this, "错误", "SqlCmdError" + qryModel->lastError().text());return;}}}delete dataDlg;
}void MainWindow::on_actRecDelete_triggered()
{int curRecNo = theSelection->currentIndex().row();QSqlRecord curRec = qryModel->record(curRecNo);int empNo = curRec.value("EmpNo").toInt();QSqlQuery queryDelete;queryDelete.prepare("delete from employee where EmpNo=:ID");queryDelete.bindValue(":ID", empNo);if(!queryDelete.exec()){QMessageBox::warning(this, "错误", "SqlCmdError" + queryDelete.lastError().text());}else{qryModel->setQuery("SELECT EmpNo,Name,Gender,Height,Birthday,Mobile,Province,City,Depart,Education,Salary From employee order by EmpNo");if(qryModel->lastError().isValid()){QMessageBox::warning(this, "错误", "SqlCmdError" + qryModel->lastError().text());return;}}}void MainWindow::on_actScan_triggered()
{QSqlQuery queryEmList;queryEmList.exec("select EmpNo,Salary From employee order by EmpNo");queryEmList.first();QSqlQuery queryUpdate;queryUpdate.prepare("update employee set Salary=:Salary where EmpNo=:ID");while (queryEmList.isValid()){int empID = queryEmList.value("EmpNo").toInt();float salary = queryEmList.value("Salary").toFloat() + 1000;queryUpdate.bindValue(":Salary", salary);queryUpdate.bindValue(":ID", empID);queryUpdate.exec();if(queryUpdate.lastError().isValid()){break;}queryEmList.next();}qryModel->setQuery("SELECT EmpNo,Name,Gender,Height,Birthday,Mobile,Province,City,Depart,Education,Salary From employee order by EmpNo");if(qryModel->lastError().isValid()){QMessageBox::warning(this, "错误", "SqlCmdError" + qryModel->lastError().text());return;}
}void MainWindow::on_TableView_doubleClicked(QModelIndex index)
{Q_UNUSED(index)on_actRecEdit_triggered();
}

五、QSelRelationalTableModel

QSelRelationalTableModel类为单张的数据库提供了一个可以编辑的数据模型，它支持外键。

QAbstractTableModelQSqlQueryModel //封装了指向SELECT语句从数据库查询数据的功能QSqlTableModelQSqlRelationalTableModel

1、实现程序

（1）创建项目，基于QMainWindow

（2）添加图标资源

（3）实现工具栏功能

#include "mainwindow.h"
#include "ui_mainwindow.h"MainWindow::MainWindow(QWidget *parent) :QMainWindow(parent),ui(new Ui::MainWindow)
{ui->setupUi(this);setCentralWidget(ui->tableView);}MainWindow::~MainWindow()
{delete ui;
}#include <QFileDialog>
#include <QMessageBox>
void MainWindow::on_actOpenDB_triggered()
{QString fileName = QFileDialog::getOpenFileName(this, "打开数据库", "","Sqlite数据库(*.db *.db3)");if(fileName.isEmpty()){return;}DB = QSqlDatabase::addDatabase("QSQLITE");DB.setDatabaseName(fileName);if(!DB.open()){QMessageBox::warning(this, "错误", "打开数据库失败");}tabModel = new QSqlRelationalTableModel(this, DB);tabModel->setTable("studInfo");tabModel->setEditStrategy(QSqlTableModel::OnManualSubmit);tabModel->setSort(0, Qt::AscendingOrder);tabModel->setHeaderData(0, Qt::Horizontal, "学号");tabModel->setHeaderData(1, Qt::Horizontal, "姓名");tabModel->setHeaderData(2, Qt::Horizontal, "性别");tabModel->setHeaderData(3, Qt::Horizontal, "学院");tabModel->setHeaderData(4, Qt::Horizontal, "专业");tabModel->setRelation(3, QSqlRelation("departments", "departID", "department"));tabModel->setRelation(4, QSqlRelation("majors", "majorID", "major"));theSelection = new QItemSelectionModel(tabModel);ui->tableView->setModel(tabModel);ui->tableView->setSelectionModel(theSelection);// 表格添加代理（学院与专业下拉选）ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));tabModel->select();ui->actOpenDB->setEnabled(false);ui->actRecAppend->setEnabled(true);ui->actRecInsert->setEnabled(true);ui->actRecDelete->setEnabled(true);ui->actFields->setEnabled(true);}void MainWindow::on_actFields_triggered()
{QSqlRecord emptyRec = tabModel->record();QString str;for (int var = 0; var < emptyRec.count(); ++var){str = str + emptyRec.fieldName(var) + "\n";}QMessageBox::information(this, "字段名称", str);
}void MainWindow::on_actRecAppend_triggered()
{tabModel->insertRow(tabModel->rowCount(), QModelIndex()); // 在末尾添加一条QModelIndex curIndex = tabModel->index(tabModel->rowCount() - 1, 1); //theSelection->clearSelection(); // 清空选择项theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select); // 设置新的选项ui->actRevert->setEnabled(true);ui->actSubmit->setEnabled(true);
}void MainWindow::on_actRecInsert_triggered()
{QModelIndex curIndex = ui->tableView->currentIndex();tabModel->insertRow(curIndex.row(), QModelIndex()); // 添加一条theSelection->clearSelection(); // 清空选择项theSelection->setCurrentIndex(curIndex, QItemSelectionModel::Select); // 设置新的选项ui->actRevert->setEnabled(true);ui->actSubmit->setEnabled(true);
}void MainWindow::on_actRevert_triggered()
{tabModel->revertAll();ui->actSubmit->setEnabled(false);ui->actRevert->setEnabled(false);
}void MainWindow::on_actSubmit_triggered()
{bool res = tabModel->submitAll();if(!res){QMessageBox::information(this, "信息", "数据保存错误\n" + tabModel->lastError().text(),QMessageBox::Ok, QMessageBox::NoButton);}else{ui->actSubmit->setEnabled(false);ui->actRevert->setEnabled(false);}
}void MainWindow::on_actRecDelete_triggered()
{QModelIndex curIndex = ui->tableView->currentIndex();QModelIndex nextIndex = tabModel->index(curIndex.row() + 1, 0);tabModel->removeRow(curIndex.row()); // 删除theSelection->clearSelection(); // 清空选择项theSelection->setCurrentIndex(nextIndex, QItemSelectionModel::Select); // 设置新的选项ui->actRevert->setEnabled(true);ui->actSubmit->setEnabled(true);
}