74 数据库窗口程序案例

74.1 创建项目

通过QtCreator，在C:\Users\Minwei\Projects\Qt路径下，创建名为DBEditor的项目。

74.2 添加资源

C:\Users\Minwei\Projects\Qt\DBEditor\DBEditor.qrc：

1
<RCC>
2
    <qresource prefix="/">
3
        <file>images/delete.bmp</file>
4
        <file>images/exit.bmp</file>
5
        <file>images/insert.bmp</file>
6
        <file>images/open.bmp</file>
7
        <file>images/update.bmp</file>
8
    </qresource>
9
</RCC>

74.3 设计界面

74.3.1 主窗口

C:\Users\Minwei\Projects\Qt\DBEditor\dbeditorwindow.ui：

xxxxxxxxxx
134
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ui version="4.0">
3
 <class>DBEditorWindow</class>
4
 <widget class="QMainWindow" name="DBEditorWindow">
5
  <property name="geometry">
6
   <rect>
7
    <x>0</x>
8
    <y>0</y>
9
    <width>600</width>
10
    <height>500</height>
11
   </rect>
12
  </property>
13
  <property name="windowTitle">
14
   <string>数据库编辑器</string>
15
  </property>
16
  <widget class="QWidget" name="m_central">
17
   <widget class="QTableView" name="m_table">
18
    <property name="geometry">
19
     <rect>
20
      <x>10</x>
21
      <y>10</y>
22
      <width>581</width>
23
      <height>431</height>
24
     </rect>
25
    </property>
26
    <property name="frameShape">
27
     <enum>QFrame::WinPanel</enum>
28
    </property>
29
   </widget>
30
  </widget>
31
  <widget class="QToolBar" name="m_toolBar">
32
   <property name="windowTitle">
33
    <string>工具栏</string>
34
   </property>
35
   <property name="toolButtonStyle">
36
    <enum>Qt::ToolButtonTextUnderIcon</enum>
37
   </property>
38
   <attribute name="toolBarArea">
39
    <enum>TopToolBarArea</enum>
40
   </attribute>
41
   <attribute name="toolBarBreak">
42
    <bool>false</bool>
43
   </attribute>
44
   <addaction name="m_actOpen"/>
45
   <addaction name="separator"/>
46
   <addaction name="m_actAppend"/>
47
   <addaction name="m_actModify"/>
48
   <addaction name="m_actDelete"/>
49
   <addaction name="separator"/>
50
   <addaction name="m_actExit"/>
51
  </widget>
52
  <action name="m_actOpen">
53
   <property name="icon">
54
    <iconset resource="DBEditor.qrc">
55
     <normaloff>:/images/open.bmp</normaloff>:/images/open.bmp</iconset>
56
   </property>
57
   <property name="text">
58
    <string>打开</string>
59
   </property>
60
   <property name="toolTip">
61
    <string>打开</string>
62
   </property>
63
  </action>
64
  <action name="m_actAppend">
65
   <property name="icon">
66
    <iconset resource="DBEditor.qrc">
67
     <normaloff>:/images/insert.bmp</normaloff>:/images/insert.bmp</iconset>
68
   </property>
69
   <property name="text">
70
    <string>增加</string>
71
   </property>
72
   <property name="toolTip">
73
    <string>插入</string>
74
   </property>
75
  </action>
76
  <action name="m_actModify">
77
   <property name="icon">
78
    <iconset resource="DBEditor.qrc">
79
     <normaloff>:/images/update.bmp</normaloff>:/images/update.bmp</iconset>
80
   </property>
81
   <property name="text">
82
    <string>修改</string>
83
   </property>
84
   <property name="toolTip">
85
    <string>修改</string>
86
   </property>
87
  </action>
88
  <action name="m_actDelete">
89
   <property name="icon">
90
    <iconset resource="DBEditor.qrc">
91
     <normaloff>:/images/delete.bmp</normaloff>:/images/delete.bmp</iconset>
92
   </property>
93
   <property name="text">
94
    <string>删除</string>
95
   </property>
96
   <property name="toolTip">
97
    <string>删除</string>
98
   </property>
99
  </action>
100
  <action name="m_actExit">
101
   <property name="icon">
102
    <iconset resource="DBEditor.qrc">
103
     <normaloff>:/images/exit.bmp</normaloff>:/images/exit.bmp</iconset>
104
   </property>
105
   <property name="text">
106
    <string>退出</string>
107
   </property>
108
   <property name="toolTip">
109
    <string>退出</string>
110
   </property>
111
  </action>
112
 </widget>
113
 <resources>
114
  <include location="DBEditor.qrc"/>
115
 </resources>
116
 <connections>
117
  <connection>
118
   <sender>m_actExit</sender>
119
   <signal>triggered()</signal>
120
   <receiver>DBEditorWindow</receiver>
121
   <slot>close()</slot>
122
   <hints>
123
    <hint type="sourcelabel">
124
     <x>-1</x>
125
     <y>-1</y>
126
    </hint>
127
    <hint type="destinationlabel">
128
     <x>399</x>
129
     <y>299</y>
130
    </hint>
131
   </hints>
132
  </connection>
133
 </connections>
134
</ui>

74.3.2 “员工”对话框

C:\Users\Minwei\Projects\Qt\DBEditor\employeedialog.ui：

xxxxxxxxxx
176
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ui version="4.0">
3
 <class>EmployeeDialog</class>
4
 <widget class="QDialog" name="EmployeeDialog">
5
  <property name="geometry">
6
   <rect>
7
    <x>0</x>
8
    <y>0</y>
9
    <width>188</width>
10
    <height>170</height>
11
   </rect>
12
  </property>
13
  <property name="windowTitle">
14
   <string>员工</string>
15
  </property>
16
  <layout class="QVBoxLayout" name="m_layoutVer">
17
   <item>
18
    <layout class="QGridLayout" name="m_layoutGrid">
19
     <property name="sizeConstraint">
20
      <enum>QLayout::SetMaximumSize</enum>
21
     </property>
22
     <item row="0" column="0">
23
      <widget class="QLabel" name="m_labName">
24
       <property name="text">
25
        <string>姓名：</string>
26
       </property>
27
      </widget>
28
     </item>
29
     <item row="0" column="1">
30
      <widget class="QLineEdit" name="m_editName"/>
31
     </item>
32
     <item row="1" column="0">
33
      <widget class="QLabel" name="m_labGender">
34
       <property name="text">
35
        <string>性别：</string>
36
       </property>
37
      </widget>
38
     </item>
39
     <item row="1" column="1">
40
      <widget class="QComboBox" name="m_comboGender">
41
       <item>
42
        <property name="text">
43
         <string>男</string>
44
        </property>
45
       </item>
46
       <item>
47
        <property name="text">
48
         <string>女</string>
49
        </property>
50
       </item>
51
      </widget>
52
     </item>
53
     <item row="2" column="0">
54
      <widget class="QLabel" name="m_labDepartment">
55
       <property name="text">
56
        <string>部门：</string>
57
       </property>
58
      </widget>
59
     </item>
60
     <item row="2" column="1">
61
      <widget class="QComboBox" name="m_comboDepartment"/>
62
     </item>
63
     <item row="3" column="0">
64
      <widget class="QLabel" name="m_labSalary">
65
       <property name="text">
66
        <string>工资：</string>
67
       </property>
68
      </widget>
69
     </item>
70
     <item row="3" column="1">
71
      <widget class="QDoubleSpinBox" name="m_spinSalary">
72
       <property name="maximum">
73
        <double>99999.990000000005239</double>
74
       </property>
75
       <property name="value">
76
        <double>10000.000000000000000</double>
77
       </property>
78
      </widget>
79
     </item>
80
    </layout>
81
   </item>
82
   <item>
83
    <widget class="QFrame" name="m_hline">
84
     <property name="frameShape">
85
      <enum>QFrame::HLine</enum>
86
     </property>
87
     <property name="frameShadow">
88
      <enum>QFrame::Sunken</enum>
89
     </property>
90
    </widget>
91
   </item>
92
   <item>
93
    <layout class="QHBoxLayout" name="m_layoutHor">
94
     <item>
95
      <spacer name="m_spacerLeft">
96
       <property name="orientation">
97
        <enum>Qt::Horizontal</enum>
98
       </property>
99
       <property name="sizeHint" stdset="0">
100
        <size>
101
         <width>40</width>
102
         <height>20</height>
103
        </size>
104
       </property>
105
      </spacer>
106
     </item>
107
     <item>
108
      <widget class="QPushButton" name="m_btnOk">
109
       <property name="text">
110
        <string>确定</string>
111
       </property>
112
       <property name="default">
113
        <bool>true</bool>
114
       </property>
115
      </widget>
116
     </item>
117
     <item>
118
      <widget class="QPushButton" name="m_btnCancel">
119
       <property name="text">
120
        <string>取消</string>
121
       </property>
122
      </widget>
123
     </item>
124
     <item>
125
      <spacer name="m_spacerRight">
126
       <property name="orientation">
127
        <enum>Qt::Horizontal</enum>
128
       </property>
129
       <property name="sizeHint" stdset="0">
130
        <size>
131
         <width>40</width>
132
         <height>20</height>
133
        </size>
134
       </property>
135
      </spacer>
136
     </item>
137
    </layout>
138
   </item>
139
  </layout>
140
 </widget>
141
 <resources/>
142
 <connections>
143
  <connection>
144
   <sender>m_btnOk</sender>
145
   <signal>clicked()</signal>
146
   <receiver>EmployeeDialog</receiver>
147
   <slot>accept()</slot>
148
   <hints>
149
    <hint type="sourcelabel">
150
     <x>77</x>
151
     <y>271</y>
152
    </hint>
153
    <hint type="destinationlabel">
154
     <x>199</x>
155
     <y>149</y>
156
    </hint>
157
   </hints>
158
  </connection>
159
  <connection>
160
   <sender>m_btnCancel</sender>
161
   <signal>clicked()</signal>
162
   <receiver>EmployeeDialog</receiver>
163
   <slot>reject()</slot>
164
   <hints>
165
    <hint type="sourcelabel">
166
     <x>217</x>
167
     <y>271</y>
168
    </hint>
169
    <hint type="destinationlabel">
170
     <x>199</x>
171
     <y>149</y>
172
    </hint>
173
   </hints>
174
  </connection>
175
 </connections>
176
</ui>

74.4 实现功能

74.4.1 “员工”对话框

C:\Users\Minwei\Projects\Qt\DBEditor\employeedialog.h：

xxxxxxxxxx
1
#ifndef EMPLOYEEDIALOG_H
2
#define EMPLOYEEDIALOG_H
3

4
#include <QDialog>
5
#include <QMap>
6

7
namespace Ui {
8
class EmployeeDialog;
9
}
10

11
class EmployeeDialog : public QDialog
12
{
13
    Q_OBJECT
14

15
public:
16
    explicit EmployeeDialog(QMap<QString, int> const& departments,
17
        QWidget *parent = nullptr);
18
    ~EmployeeDialog();
19

20
    void set(QString const& name, QString const& gender,
21
        QString const& department, double salary);
22
    void get(QString& name, QString& gender,
23
        QString& department, double& salary);
24

25
private:
26
    Ui::EmployeeDialog *ui;
27
};
28

29
#endif // EMPLOYEEDIALOG_H

C:\Users\Minwei\Projects\Qt\DBEditor\employeedialog.cpp：

xxxxxxxxxx
1
#include "employeedialog.h"
2
#include "ui_employeedialog.h"
3

4
EmployeeDialog::EmployeeDialog(
5
    QMap<QString, int> const& departments, QWidget *parent) :
6
    QDialog(parent),
7
    ui(new Ui::EmployeeDialog)
8
{
9
    ui->setupUi(this);
10

11
    for (QString const& department : departments.keys())
12
        ui->m_comboDepartment->addItem(department);
13
}
14

15
EmployeeDialog::~EmployeeDialog()
16
{
17
    delete ui;
18
}
19

20
void EmployeeDialog::set(QString const& name, QString const& gender,
21
    QString const& department, double salary)
22
{
23
    ui->m_editName->setText(name);
24
    ui->m_comboGender->setCurrentText(gender);
25
    ui->m_comboDepartment->setCurrentText(department);
26
    ui->m_spinSalary->setValue(salary);
27
}
28

29
void EmployeeDialog::get(QString& name, QString& gender,
30
    QString& department, double& salary)
31
{
32
    name = ui->m_editName->text();
33
    gender = ui->m_comboGender->currentText();
34
    department = ui->m_comboDepartment->currentText();
35
    salary = ui->m_spinSalary->value();
36
}

74.4.2 主窗口

C:\Users\Minwei\Projects\Qt\DBEditor\dbeditorwindow.h：

xxxxxxxxxx
39
1
#ifndef DBEDITORWINDOW_H
2
#define DBEDITORWINDOW_H
3

4
#include <QMainWindow>
5
#include <QSqlQueryModel>
6
#include <QItemSelectionModel>
7

8
QT_BEGIN_NAMESPACE
9
namespace Ui { class DBEditorWindow; }
10
QT_END_NAMESPACE
11

12
class DBEditorWindow : public QMainWindow
13
{
14
    Q_OBJECT
15

16
public:
17
    DBEditorWindow(QWidget *parent = nullptr);
18
    ~DBEditorWindow();
19

20
private slots:
21
    void on_m_actOpen_triggered();
22

23
    void on_m_actAppend_triggered();
24
    void on_m_actModify_triggered();
25
    void on_m_actDelete_triggered();
26

27
    void on_m_table_doubleClicked(const QModelIndex &index);
28

29
private:
30
    void refresh(void);
31
    void update(int row);
32

33
    Ui::DBEditorWindow *ui;
34
    QSqlQueryModel* m_model;
35
    QItemSelectionModel* m_selection;
36
    QMap<QString, int> m_departments;
37
};
38

39
#endif // DBEDITORWINDOW_H

C:\Users\Minwei\Projects\Qt\DBEditor\dbeditorwindow.cpp：

xxxxxxxxxx
199
1
#include <QMessageBox>
2
#include <QSqlQuery>
3
#include <QSqlRecord>
4
#include <QSqlError>
5

6
#include "dbeditorwindow.h"
7
#include "ui_dbeditorwindow.h"
8
#include "employeedialog.h"
9

10
DBEditorWindow::DBEditorWindow(QWidget *parent)
11
    : QMainWindow(parent)
12
    , ui(new Ui::DBEditorWindow)
13
    , m_model(new QSqlQueryModel(this))
14
    , m_selection(new QItemSelectionModel(m_model))
15
{
16
    ui->setupUi(this);
17

18
    ui->m_actAppend->setEnabled(false);
19
    ui->m_actModify->setEnabled(false);
20
    ui->m_actDelete->setEnabled(false);
21

22
    setCentralWidget(ui->m_table);
23

24
    ui->m_table->setModel(m_model);
25
    ui->m_table->setSelectionModel(m_selection);
26
}
27

28
DBEditorWindow::~DBEditorWindow()
29
{
30
    delete ui;
31
}
32

33
void DBEditorWindow::on_m_actOpen_triggered()
34
{
35
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
36
    db.setHostName("localhost");
37
    db.setPort(3306);
38
    db.setUserName("root");
39
    db.setPassword("123456");
40
    db.setDatabaseName("qt_testdb");
41
    if (!db.open())
42
    {
43
        QMessageBox::critical(this, "连接qt_testdb数据库失败",
44
            db.lastError().text());
45
        return;
46
    }
47

48
    QSqlQuery query;
49
    if (!query.exec("SELECT * FROM t_department"))
50
    {
51
        QMessageBox::critical(this, "查询t_department表失败",
52
            query.lastError().text());
53
        return;
54
    }
55
    while (query.next())
56
        m_departments[query.value(1).toString()] = query.value(0).toInt();
57

58
    refresh();
59

60
    m_model->setHeaderData(0, Qt::Horizontal, "工号");
61
    m_model->setHeaderData(1, Qt::Horizontal, "姓名");
62
    m_model->setHeaderData(2, Qt::Horizontal, "性别");
63
    m_model->setHeaderData(3, Qt::Horizontal, "部门");
64
    m_model->setHeaderData(4, Qt::Horizontal, "工资");
65

66
    ui->m_actOpen->setEnabled(false);
67
    ui->m_actAppend->setEnabled(true);
68
    ui->m_actModify->setEnabled(true);
69
    ui->m_actDelete->setEnabled(true);
70
}
71

72
void DBEditorWindow::on_m_actAppend_triggered()
73
{
74
    EmployeeDialog dlg(m_departments, this);
75

76
    if (dlg.exec() == QDialog::Accepted)
77
    {
78
        QString name, gender, department;
79
        double salary;
80
        dlg.get(name, gender, department, salary);
81

82
        QSqlQuery query;
83
        query.prepare(
84
            "INSERT INTO t_employee (name, gender, department_id, salary) "
85
            "VALUES (:name, :gender, :department_id, :salary)");
86
        query.addBindValue(name);
87
        query.addBindValue(gender);
88
        query.addBindValue(m_departments[department]);
89
        query.addBindValue(salary);
90
        if (!query.exec())
91
        {
92
            QMessageBox::critical(this, "插入t_employee表失败",
93
                query.lastError().text());
94
            return;
95
        }
96

97
        refresh();
98
    }
99
}
100

101
void DBEditorWindow::on_m_actModify_triggered()
102
{
103
    update(m_selection->currentIndex().row());
104
}
105

106
void DBEditorWindow::on_m_actDelete_triggered()
107
{
108
    int id = m_model->record(m_selection->currentIndex().row()).
109
        value("id").toInt();
110

111
    QSqlQuery query;
112
    query.prepare("DELETE FROM t_employee WHERE id = :id");
113
    query.addBindValue(id);
114
    if (!query.exec())
115
    {
116
        QMessageBox::critical(this, "删除t_employee表失败",
117
            query.lastError().text());
118
        return;
119
    }
120

121
    refresh();
122
}
123

124
void DBEditorWindow::on_m_table_doubleClicked(const QModelIndex &index)
125
{
126
    update(index.row());
127
}
128

129
void DBEditorWindow::refresh(void)
130
{
131
    m_model->setQuery(
132
        "SELECT t_employee.id, t_employee.name, "
133
        "gender, t_department.name, salary "
134
        "FROM t_employee "
135
        "LEFT JOIN t_department "
136
        "ON t_employee.department_id = t_department.id "
137
        "ORDER BY t_employee.id");
138
}
139

140
void DBEditorWindow::update(int row)
141
{
142
    int id = m_model->record(row).value("id").toInt();
143

144
    QSqlQuery query;
145

146
    query.prepare(
147
        "SELECT t_employee.name, gender, "
148
        "t_department.name, salary "
149
        "FROM t_employee "
150
        "LEFT JOIN t_department "
151
        "ON t_employee.department_id = t_department.id "
152
        "WHERE t_employee.id = :id");
153
    query.bindValue(":id", id);
154
    if (!query.exec())
155
    {
156
        QMessageBox::critical(this, "查询t_employee表失败",
157
            query.lastError().text());
158
        return;
159
    }
160
    query.first();
161
    if (!query.isValid())
162
    {
163
        QMessageBox::critical(this, "查询t_employee表无效",
164
            query.lastError().text());
165
        return;
166
    }
167
    QSqlRecord record = query.record();
168

169
    EmployeeDialog dlg(m_departments, this);
170
    dlg.set(
171
        record.value("t_employee.name").toString(),
172
        record.value("gender").toString(),
173
        record.value("t_department.name").toString(),
174
        record.value("salary").toDouble());
175
    if (dlg.exec() == QDialog::Accepted)
176
    {
177
        QString name, gender, department;
178
        double salary;
179
        dlg.get(name, gender, department, salary);
180

181
        query.prepare(
182
            "UPDATE t_employee SET name = :name, gender = :gender, "
183
            "department_id = :department_id, salary = :salary "
184
            "WHERE id = :id");
185
        query.addBindValue(name);
186
        query.addBindValue(gender);
187
        query.addBindValue(m_departments[department]);
188
        query.addBindValue(salary);
189
        query.addBindValue(id);
190
        if (!query.exec())
191
        {
192
            QMessageBox::critical (this, "更新t_employee表失败",
193
                query.lastError().text());
194
            return;
195
        }
196

197
        refresh();
198
    }
199
}

74.5 测试验证

运行效果如图所示：