在表中显示数据
在许多情况下,以表格式的视图为用户显示数据集是最简单的方法。本节给出 Staff Manager应用程序的主窗体,它由两个呈主一从关系的 QTableView 组成(窗体如图 13.4所示)。主视图是一个单位部门的列表,从视图则为当前部门中的雇员列表。两个视图都使用了 QSqlRelationalTableModels,因为它们呈现出的两个数据库表都有外键字段。
我们依然使用 enum 枚举类型变量为表的列索引号给出有具体含义的命名:
enum {
Department_Id = 0,
Department_Name = 1,
Department_LocationId = 2
};
首先看看头文件中 MainForm 类的定义:
class MainForm : public QWidget
{
Q_OBJECT
public:
MainForm();
private slots:
void updateEmployeeView();
void addDepartment();
void deleteDepartment();
void editEmployees();
private:
void createDepartmentPanel();
void createEmployeePanel();
QSqlRelationalTableModel *departmentModel;
QSqlRelationalTableModel *employeeModel;
QWidget *departmentPanel;
QWidget *employeePanel;
...
QDialogButtonBox *buttonBox;
};
为了设立主-从关系必须确保当用户浏览到主视图中不同的记录(行)时更新从视图中的表并显示相关的记录。这可以通过私有updateEmployeeView()槽实现。其他三个槽[addDepartment()、deleteDepartment()和editEmployees()槽]的功能则可望文生义,两个私有函数[createDepartmentPanel()和createEmployeePanel()]则是构造函数的帮助函数。
大多数构造函数的代码都与创建用户界面接口相关,并且会设立合适的信号-槽联系。而我们只关注那些与数据库编程相关的部分。
MainForm::MainForm()
{
createDepartmentPanel();
createEmployeePanel();
构造函数首先调用两个帮助函数 createDepartmentPanel()和 createEmployeePanel()。第一个函数创建并设立部门模型和视图,第二个函数则创建并设立雇员的模型和视图。在查看完构造函数以后,我们将看看这两个函数的有关部分。
构造函数的下一个部分设立了包含两个表视图的切分窗口,同时还设立窗体按钮。我们将省略所有这方面的代码。
...
connect(addButton, SIGNAL(clicked()), this, SLOT(addDepartment()));
connect(deleteButton, SIGNAL(clicked()),
this, SLOT(deleteDepartment()));
connect(editButton, SIGNAL(clicked()), this, SLOT(editEmployees()));
connect(quitButton, SIGNAL(clicked()), this, SLOT(close()));
...
departmentView->setCurrentIndex(departmentModel->index(0, 0));
}
我们建立对话框中按钮和槽的连接,同时确保第一个部门就是当前的项。
既然看过了构造函数,接下来就看看createDepartmentPanel()帮助函数中的代码,它设立了部门的模型和视图:
void MainForm::createDepartmentPanel()
{
departmentPanel = new QWidget;
departmentModel = new QSqlRelationalTableModel(this);
departmentModel->setTable("department");
departmentModel->setRelation(Department_LocationId,
QSqlRelation("location", "id", "name"));
departmentModel->setSort(Department_Name, Qt::AscendingOrder);
departmentModel->setHeaderData(Department_Name, Qt::Horizontal,
tr("Dept."));
departmentModel->setHeaderData(Department_LocationId,
Qt::Horizontal, tr("Location"));
departmentModel->select();
departmentView = new QTableView;
departmentView->setModel(departmentModel);
departmentView->setItemDelegate(new QSqlRelationalDelegate(this));
departmentView->setSelectionMode(
QAbstractItemView::SingleSelection);
departmentView->setSelectionBehavior(QAbstractItemView::SelectRows);
departmentView->setColumnHidden(Department_Id, true);
departmentView->resizeColumnsToContents();
departmentView->horizontalHeader()->setStretchLastSection(true);
departmentLabel = new QLabel(tr("Depar&tments"));
departmentLabel->setBuddy(departmentView);
connect(departmentView->selectionModel(),
SIGNAL(currentRowChanged(const QModelIndex &,
const QModelIndex &)),
this, SLOT(updateEmployeeView()));
...
}
这段代码与前一节中设立employee雇员表模型时所见的代码相似。这个视图是一个标准QTableView,但是因为外键的存在,必须使用 QSqlRelationalDelegate,这样外键的文本才能正常出现在视图中并能通过组合框进行更改,而不只是更改原始的回号。
我们已经选择隐藏部门的ID字段,因为它对用户来说没有什么意义。我们也拉伸主视图中最后一列可视字段,也就是部门地址宇段,以填满视图中水平方向上的可用空间。
部门主视图有它自己的针对AbstractItemView::SingleSelection 的模式设置选择以及针对 AbstractItemView::SelectRows 的状态设置选择。模式设置意味着用户可以浏览到表中单独的单元格,而状态设置则表示当用户浏览记录项时,整行都是高亮显示的。
我们从视图的选择模型来建立 updateEmployeeView()槽与 currentRowChanged() 信号的连接。这个连接是使主-从关系起作用的关键,它同时也确保了雇员从视图总是能显示部门主视图中被高亮选中的部门所对应的雇员信息。
createEmployeePanel()帮助函数内部的代码与,createDepart:mentPanel ( )帮助函数的相似,但是也有一些重要的区别:
void MainForm::createEmployeePanel()
{
employeePanel = new QWidget;
employeeModel = new QSqlRelationalTableModel(this);
employeeModel->setTable("employee");
employeeModel->setRelation(Employee_DepartmentId,
QSqlRelation("department", "id", "name"));
employeeModel->setSort(Employee_Name, Qt::AscendingOrder);
employeeModel->setHeaderData(Employee_Name, Qt::Horizontal,
tr("Name"));
employeeModel->setHeaderData(Employee_Extension, Qt::Horizontal,
tr("Ext."));
employeeModel->setHeaderData(Employee_Email, Qt::Horizontal,
tr("Email"));
employeeView = new QTableView;
employeeView->setModel(employeeModel);
employeeView->setSelectionMode(QAbstractItemView::SingleSelection);
employeeView->setSelectionBehavior(QAbstractItemView::SelectRows);
employeeView->setEditTriggers(QAbstractItemView::NoEditTriggers);
employeeView->horizontalHeader()->setStretchLastSection(true);
employeeView->setColumnHidden(Employee_Id, true);
employeeView->setColumnHidden(Employee_DepartmentId, true);
employeeView->setColumnHidden(Employee_StartDate, true);
employeeLabel = new QLabel(tr("E&mployees"));
employeeLabel->setBuddy(employeeView);
...
}
雇员视图的编辑触发器被设置为 QAbstractItemView::NoEditTriggers,它能有效确保视图的只读属性。在这个应用程序中,通过单击Edit Employees 按钮,用户可以添加、编辑和删除雇员记录,而该按钮调用了前一节中开发的EmployeeForm。
这次,我们隐藏三列,而不是仅隐藏一列。我们隐藏 id 列,因为它对于用户来说还是没有什么意义。还需要隐藏 departmentid 列,因为每一次显示的雇员信息都是属于当前选中部门的。最后,隐藏 startdate 列,因为它几乎没有任何实质作用,而且还可以通过单击 Edit Employees 按钮读取它。
void MainForm::updateEmployeeView()
{
QModelIndex index = departmentView->currentIndex();
if (index.isValid()) {
QSqlRecord record = departmentModel->record(index.row());
int id = record.value("id").toInt();
employeeModel->setFilter(QString("departmentid = %1").arg(id));
employeeLabel->setText(tr("E&mployees in the %1 Department")
.arg(record.value("name").toString()));
} else {
employeeModel->setFilter("departmentid = -1");
employeeLabel->setText(tr("E&mployees"));
}
employeeModel->select();
employeeView->horizontalHeader()->setVisible(
employeeModel->rowCount() > 0);
}
只要当前部门发生更改(包括启动的时候) ,这个槽就会被调用。如果,它是一个有效的当前部门,函数会重新找回该部门的 ID 号并且对其雇员模型建立一个过滤器程序。这就迫使雇员信息只对那些与其匹配的部门ID外键才显示。(过滤器程序就是没有 WHERE 关键字的 WHERE子句)。我们还需要更新 employee 表上的标签文本以显示雇员所属部门的名称。
如果没有有效的部门名(比如数据库为空时) ,就将过滤器程序设置为与一个不存在的部门相匹配,这样就没有与其匹配的记录。
然后,对模型调用select() 函数以应用这个过滤器程序。这将依次发射信号;而视图将通过自我更新对该信号做出回应。最后,显示或者隐藏 employee 表的列标题,这取决于是否有任何雇员信息被显示。
void MainForm::addDepartment()
{
int row = departmentModel->rowCount();
departmentModel->insertRow(row);
QModelIndex index = departmentModel->index(row, Department_Name);
departmentView->setCurrentIndex(index);
departmentView->edit(index);
}
如果用户单击"Add Dept"按钮,就会在 department 表的最后插入一个新行,并让这一行成为当前行,同时启动部门名那一列的编辑功能,就好像用户按下了F2键或者双击了该列一样。如果需要提供一些默认值,则将在调用 insertRow()后立即调用 setData()来完成。
我们不必考虑为新记录创建独特的键,因为使用的是一个自动增加列。如果这科方法不能或者不适合使用,则可以连接模型的 beforeInsert()信号。这个信号在用户编辑后将发射,正好在数据库的插入发生之前。这是放入ID或者处理用户数据的最佳时间。 beforeDelete()和 beforeUpdate()信号很类似,它们对创建审计追踪非常有用。
void MainForm::deleteDepartment()
{
QModelIndex index = departmentView->currentIndex();
if (!index.isValid())
return;
QSqlDatabase::database().transaction();
QSqlRecord record = departmentModel->record(index.row());
int id = record.value(Department_Id).toInt();
int numEmployees = 0;
QSqlQuery query(QString("SELECT COUNT(*) FROM employee "
"WHERE departmentid = %1").arg(id));
if (query.next())
numEmployees = query.value(0).toInt();
if (numEmployees > 0) {
int r = QMessageBox::warning(this, tr("Delete Department"),
tr("Delete %1 and all its employees?")
.arg(record.value(Department_Name).toString()),
QMessageBox::Yes | QMessageBox::No);
if (r == QMessageBox::No) {
QSqlDatabase::database().rollback();
return;
}
query.exec(QString("DELETE FROM employee "
"WHERE departmentid = %1").arg(id));
}
departmentModel->removeRow(index.row());
departmentModel->submitAll();
QSqlDatabase::database().commit();
updateEmployeeView();
departmentView->setFocus();
}
如果想删除部门且该部门没有任何雇员记录,这个操作就可以直接执行而没有其他任何确认提示信息。如果该部门中有雇员,就要求用户确认其删除操作。而如果用户确认其删除操作,就执行级联删除以确保数据库的关系完整性。为了实现这个功能,特别是针对那些并不会为我们保证关系完整性的数据库(如 SQLite 3) ,必须使用事务处理。
一旦启动事务,就执行一个查询以查明该部门中有多少雇员。只要有一名雇员,就弹出一个消息框要求其确认操作。如果用户单击 No,就回滚该事务并返回。否则,就删除该部门以及该部门中的所有雇员的信息,并且提交事务。
void MainForm::editEmployees()
{
int employeeId = -1;
QModelIndex index = employeeView->currentIndex();
if (index.isValid()) {
QSqlRecord record = employeeModel->record(index.row());
employeeId = record.value(Employee_Id).toInt();
}
EmployeeForm form(employeeId, this);
form.exec();
updateEmployeeView();
}
只要用户单击Edit Employees 按钮,就会调用editEmployees()槽。首先分配一个无效的雇员ID,然后用当前的雇员回复写这个无效的雇员ID。接着,构建EmployeeForm 并让它在形式上显示出来。最后,调用 updateEmployeeView()槽,以使主窗体的从表视图自我刷新,因为此时雇员信息可能已经发生了更改。
mainform.h
#ifndef MAINFORM_H
#define MAINFORM_H
#include <QWidget>
class QDialogButtonBox;
class QLabel;
class QModelIndex;
class QPushButton;
class QSplitter;
class QSqlRelationalTableModel;
class QTableView;
enum {
Department_Id = 0,
Department_Name = 1,
Department_LocationId = 2
};
class MainForm : public QWidget
{
Q_OBJECT
public:
MainForm();
private slots:
void updateEmployeeView();
void addDepartment();
void deleteDepartment();
void editEmployees();
private:
void createDepartmentPanel();
void createEmployeePanel();
QSqlRelationalTableModel *departmentModel;
QSqlRelationalTableModel *employeeModel;
QWidget *departmentPanel;
QWidget *employeePanel;
QLabel *departmentLabel;
QLabel *employeeLabel;
QTableView *departmentView;
QTableView *employeeView;
QSplitter *splitter;
QPushButton *addButton;
QPushButton *deleteButton;
QPushButton *editButton;
QPushButton *quitButton;
QDialogButtonBox *buttonBox;
};
#endif
mainform.cpp
#include <QtGui>
#include <QtSql>
#include "employeeform.h"
#include "mainform.h"
MainForm::MainForm()
{
createDepartmentPanel();
createEmployeePanel();
splitter = new QSplitter(Qt::Vertical);
splitter->setFrameStyle(QFrame::StyledPanel);
splitter->addWidget(departmentPanel);
splitter->addWidget(employeePanel);
addButton = new QPushButton(tr("&Add Dept."));
deleteButton = new QPushButton(tr("&Delete Dept."));
editButton = new QPushButton(tr("&Edit Employees..."));
quitButton = new QPushButton(tr("&Quit"));
buttonBox = new QDialogButtonBox;
buttonBox->addButton(addButton, QDialogButtonBox::ActionRole);
buttonBox->addButton(deleteButton, QDialogButtonBox::ActionRole);
buttonBox->addButton(editButton, QDialogButtonBox::ActionRole);
buttonBox->addButton(quitButton, QDialogButtonBox::AcceptRole);
connect(addButton, SIGNAL(clicked()), this, SLOT(addDepartment()));
connect(deleteButton, SIGNAL(clicked()),
this, SLOT(deleteDepartment()));
connect(editButton, SIGNAL(clicked()), this, SLOT(editEmployees()));
connect(quitButton, SIGNAL(clicked()), this, SLOT(close()));
QVBoxLayout *mainLayout = new QVBoxLayout;
mainLayout->addWidget(splitter);
mainLayout->addWidget(buttonBox);
setLayout(mainLayout);
setWindowTitle(tr("Staff Manager"));
departmentView->setCurrentIndex(departmentModel->index(0, 0));
}
void MainForm::updateEmployeeView()
{
QModelIndex index = departmentView->currentIndex();
if (index.isValid()) {
QSqlRecord record = departmentModel->record(index.row());
int id = record.value("id").toInt();
employeeModel->setFilter(QString("departmentid = %1").arg(id));
employeeLabel->setText(tr("E&mployees in the %1 Department")
.arg(record.value("name").toString()));
} else {
employeeModel->setFilter("departmentid = -1");
employeeLabel->setText(tr("E&mployees"));
}
employeeModel->select();
employeeView->horizontalHeader()->setVisible(
employeeModel->rowCount() > 0);
}
void MainForm::addDepartment()
{
int row = departmentModel->rowCount();
departmentModel->insertRow(row);
QModelIndex index = departmentModel->index(row, Department_Name);
departmentView->setCurrentIndex(index);
departmentView->edit(index);
}
void MainForm::deleteDepartment()
{
QModelIndex index = departmentView->currentIndex();
if (!index.isValid())
return;
QSqlDatabase::database().transaction();
QSqlRecord record = departmentModel->record(index.row());
int id = record.value(Department_Id).toInt();
int numEmployees = 0;
QSqlQuery query(QString("SELECT COUNT(*) FROM employee "
"WHERE departmentid = %1").arg(id));
if (query.next())
numEmployees = query.value(0).toInt();
if (numEmployees > 0) {
int r = QMessageBox::warning(this, tr("Delete Department"),
tr("Delete %1 and all its employees?")
.arg(record.value(Department_Name).toString()),
QMessageBox::Yes | QMessageBox::No);
if (r == QMessageBox::No) {
QSqlDatabase::database().rollback();
return;
}
query.exec(QString("DELETE FROM employee "
"WHERE departmentid = %1").arg(id));
}
departmentModel->removeRow(index.row());
departmentModel->submitAll();
QSqlDatabase::database().commit();
updateEmployeeView();
departmentView->setFocus();
}
void MainForm::editEmployees()
{
int employeeId = -1;
QModelIndex index = employeeView->currentIndex();
if (index.isValid()) {
QSqlRecord record = employeeModel->record(index.row());
employeeId = record.value(Employee_Id).toInt();
}
EmployeeForm form(employeeId, this);
form.exec();
updateEmployeeView();
}
void MainForm::createDepartmentPanel()
{
departmentPanel = new QWidget;
departmentModel = new QSqlRelationalTableModel(this);
departmentModel->setTable("department");
departmentModel->setRelation(Department_LocationId,
QSqlRelation("location", "id", "name"));
departmentModel->setSort(Department_Name, Qt::AscendingOrder);
departmentModel->setHeaderData(Department_Name, Qt::Horizontal,
tr("Dept."));
departmentModel->setHeaderData(Department_LocationId,
Qt::Horizontal, tr("Location"));
departmentModel->select();
departmentView = new QTableView;
departmentView->setModel(departmentModel);
departmentView->setItemDelegate(new QSqlRelationalDelegate(this));
departmentView->setSelectionMode(
QAbstractItemView::SingleSelection);
departmentView->setSelectionBehavior(QAbstractItemView::SelectRows);
departmentView->setColumnHidden(Department_Id, true);
departmentView->resizeColumnsToContents();
departmentView->horizontalHeader()->setStretchLastSection(true);
departmentLabel = new QLabel(tr("Depar&tments"));
departmentLabel->setBuddy(departmentView);
connect(departmentView->selectionModel(),
SIGNAL(currentRowChanged(const QModelIndex &,
const QModelIndex &)),
this, SLOT(updateEmployeeView()));
QVBoxLayout *layout = new QVBoxLayout;
layout->addWidget(departmentLabel);
layout->addWidget(departmentView);
departmentPanel->setLayout(layout);
}
void MainForm::createEmployeePanel()
{
employeePanel = new QWidget;
employeeModel = new QSqlRelationalTableModel(this);
employeeModel->setTable("employee");
employeeModel->setRelation(Employee_DepartmentId,
QSqlRelation("department", "id", "name"));
employeeModel->setSort(Employee_Name, Qt::AscendingOrder);
employeeModel->setHeaderData(Employee_Name, Qt::Horizontal,
tr("Name"));
employeeModel->setHeaderData(Employee_Extension, Qt::Horizontal,
tr("Ext."));
employeeModel->setHeaderData(Employee_Email, Qt::Horizontal,
tr("Email"));
employeeView = new QTableView;
employeeView->setModel(employeeModel);
employeeView->setSelectionMode(QAbstractItemView::SingleSelection);
employeeView->setSelectionBehavior(QAbstractItemView::SelectRows);
employeeView->setEditTriggers(QAbstractItemView::NoEditTriggers);
employeeView->horizontalHeader()->setStretchLastSection(true);
employeeView->setColumnHidden(Employee_Id, true);
employeeView->setColumnHidden(Employee_DepartmentId, true);
employeeView->setColumnHidden(Employee_StartDate, true);
employeeLabel = new QLabel(tr("E&mployees"));
employeeLabel->setBuddy(employeeView);
QVBoxLayout *layout = new QVBoxLayout;
layout->addWidget(employeeLabel);
layout->addWidget(employeeView);
employeePanel->setLayout(layout);
}
main.cpp
#include <QtGui>
#include <QtSql>
#include <cstdlib>
#include "mainform.h"
bool createConnection()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("personnel.dat");
if (!db.open()) {
QMessageBox::warning(0, QObject::tr("Database Error"),
db.lastError().text());
return false;
}
return true;
}
void createFakeData()
{
QStringList names;
names << "Eabha Biddell" << "Prentice Hutchison"
<< "Rameesha Davidge" << "Digby Roson" << "Nettah Newarch"
<< "Lewisha Middleton" << "Ahmed Hanmer"
<< "Jordyn-Leigh Lamant" << "Lindsay Bigham"
<< "Kaylay Weir" << "Sofia Weir" << "Coel Spurlock"
<< "Youcef Culpan" << "Lucy-Jasmine Blanchard"
<< "Ally Hodgkin" << "Ara Collinge" << "Luka Dempster"
<< "Samanta Winster" << "Keri Palin" << "Ruiridh Bisset"
<< "Norman Epworth" << "Kezia Raw"
<< "Kaylan-Thomas Swynford" << "Kashaf Benning"
<< "Norma Yair" << "Edan Bassett" << "Akshat Mcglasson"
<< "Philippa Upton" << "Tylor Rockliff" << "Aqdas Buckman"
<< "Briana Dowell" << "Querida North" << "Chelsay Botts"
<< "Kishanth Calloway" << "Jan Covington"
<< "Teighan Monson" << "Claudia Mendel" << "Kerra Doe"
<< "Kara Depp" << "Harlie Soole" << "Viggo Streeter"
<< "Ava Cofel" << "Catherine Balderston"
<< "Brendan Gosnay" << "Zhaoyun Haygarth" << "Deri Pepler"
<< "Vicki Hopwood" << "Amitra Bindless" << "Cerhys Hayton"
<< "Gwendoline Westall";
QProgressDialog progress;
progress.setWindowModality(Qt::WindowModal);
progress.setWindowTitle(QObject::tr("Staff Manager"));
progress.setLabelText(QObject::tr("Creating database..."));
progress.setMinimum(0);
progress.setMaximum(names.count() + 6);
progress.setValue(1);
qApp->processEvents();
QSqlQuery query;
query.exec("DROP TABLE department");
query.exec("DROP TABLE employee");
query.exec("DROP TABLE location");
progress.setValue(2);
qApp->processEvents();
query.exec("CREATE TABLE location ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR(40) NOT NULL)");
progress.setValue(3);
qApp->processEvents();
query.exec("CREATE TABLE department ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR(40) NOT NULL, "
"locationid INTEGER NOT NULL, "
"FOREIGN KEY (locationid) REFERENCES location)");
progress.setValue(4);
qApp->processEvents();
query.exec("CREATE TABLE employee ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR(40) NOT NULL, "
"departmentid INTEGER NOT NULL, "
"extension INTEGER NOT NULL, "
"email VARCHAR(40) NOT NULL, "
"startdate DATE NOT NULL, "
"FOREIGN KEY (departmentid) REFERENCES department)");
progress.setValue(5);
qApp->processEvents();
query.exec("INSERT INTO location (name) VALUES ("
"'Floor 18, 1129 Evanston Heights, New York, NY')");
query.exec("INSERT INTO location (name) VALUES ("
"'The Shed, Elmtree Drive, Boston, MA')");
query.exec("INSERT INTO location (name) VALUES ("
"'14 Valentine Buildings, Amor Street, Cambridge, MA')");
query.exec("INSERT INTO location (name) VALUES ("
"'Bunker Building, Silo Avenue, Los Angeles, CA')");
query.exec("INSERT INTO department (name, locationid) VALUES ("
"'Sales', 1)");
query.exec("INSERT INTO department (name, locationid) VALUES ("
"'Marketing', 2)");
query.exec("INSERT INTO department (name, locationid) VALUES ("
"'Processing', 1)");
query.exec("INSERT INTO department (name, locationid) VALUES ("
"'Support', 4)");
query.exec("INSERT INTO department (name, locationid) VALUES ("
"'Research', 3)");
progress.setValue(6);
qApp->processEvents();
int count = 0;
query.prepare("INSERT INTO employee (name, departmentid, "
"extension, email, startdate) "
"VALUES (:name, :departmentid, :extension, "
":email, :startdate)");
foreach (QString name, names) {
query.bindValue(":name", name);
query.bindValue(":departmentid", 1 + (std::rand() % 5));
query.bindValue(":extension", 400 + (std::rand() % 100));
query.bindValue(":email", name.toLower().replace(" ", ".") +
"@company.com");
query.bindValue(":startdate",
QDate::currentDate().addDays(-(std::rand() % 3600)));
query.exec();
++count;
progress.setValue(count + 6);
}
progress.setValue(progress.maximum());
qApp->processEvents();
}
int main(int argc, char *argv[])
{
QApplication app(argc, argv);
bool existingData = QFile::exists("personnel.dat");
if (!createConnection())
return 1;
if (!existingData)
createFakeData();
MainForm form;
form.resize(500, 600);
form.show();
return app.exec();
}