类似站酷的设计网站,varnish wordpress,深圳谷歌网络推广公司,网页设计与网站建设在线考试1一、Sql介绍
Qt Sql模块包含多个类#xff0c;实现数据库的连接#xff0c;Sql语句的执行#xff0c;数据获取与界面显示#xff0c;数据与界面直接使用Model/View结构。1、使用Sql模块
#xff08;1#xff09;工程加入
QT sql#xff08;2#xff09;添加头文件 …一、Sql介绍
Qt Sql模块包含多个类实现数据库的连接Sql语句的执行数据获取与界面显示数据与界面直接使用Model/View结构。1、使用Sql模块
1工程加入
QT sql2添加头文件
#include QtSel2、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语句从数据库查询数据的功能QSqlTableModelQSqlRelationalTableModel2、实现程序 1、创建项目基于QMainWindow
2、添加图标资源文件添加工具按钮
3、添加组件 4、实现功能
#include mainwindow.h
#include ui_mainwindow.hMainWindow::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.hDialogData::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.hMainWindow::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 QFileDialogvoid 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语句从数据库查询数据的功能QSqlTableModelQSqlRelationalTableModel1、实现程序 1创建项目基于QMainWindow 2添加图标资源
3实现工具栏功能
#include mainwindow.h
#include ui_mainwindow.hMainWindow::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);
}
