Qt 与 GTK 图形框架教程 / 13 - 数据库集成 / Database Integration
数据库集成 / Database Integration
构建完整的数据库驱动应用：ORM 模式、表单验证、CRUD 操作和模型-视图绑定。 Build complete database-driven apps: ORM patterns, form validation, CRUD, and Model-View binding.
13.1 架构模式 / Architecture Patterns
分层架构 / Layered Architecture
┌─────────────────────────────────────┐
│ View Layer (UI) │ ← QWidget / QML / GTK Widget
│ 表单、表格、按钮 │
├─────────────────────────────────────┤
│ Controller / ViewModel Layer │ ← 业务逻辑、数据转换
│ 验证、转换、格式化 │
├─────────────────────────────────────┤
│ Model Layer (Data) │ ← QAbstractTableModel
│ 数据结构、CRUD 接口 │
├─────────────────────────────────────┤
│ Repository Layer │ ← SQL 查询、缓存
│ 数据库访问、查询构建 │
├─────────────────────────────────────┤
│ Database (SQLite / PostgreSQL) │ ← 实际存储
└─────────────────────────────────────┘
13.2 Repository 模式 / Repository Pattern (Qt C++)
基础 Repository / Base Repository
// repository.h - 通用 Repository 基类
#ifndef REPOSITORY_H
#define REPOSITORY_H
#include <QSqlQuery>
#include <QSqlError>
#include <QVariantMap>
#include <QVariantList>
#include <QDebug>
class BaseRepository {
public:
virtual ~BaseRepository() = default;
protected:
bool executeQuery(const QString &sql, const QVariantList &bindings = {}) {
QSqlQuery query;
query.prepare(sql);
for (const auto &val : bindings) {
query.addBindValue(val);
}
if (!query.exec()) {
qCritical() << "SQL Error:" << query.lastError().text()
<< "Query:" << sql;
return false;
}
return true;
}
QVariantList queryAll(const QString &sql,
const QVariantList &bindings = {}) {
QVariantList results;
QSqlQuery query;
query.prepare(sql);
for (const auto &val : bindings) {
query.addBindValue(val);
}
if (query.exec()) {
while (query.next()) {
QVariantMap row;
for (int i = 0; i < query.record().count(); ++i) {
row[query.record().fieldName(i)] = query.value(i);
}
results.append(row);
}
}
return results;
}
QVariantMap queryOne(const QString &sql,
const QVariantList &bindings = {}) {
auto results = queryAll(sql, bindings);
return results.isEmpty() ? QVariantMap() : results.first().toMap();
}
};
#endif // REPOSITORY_H
User Repository / 用户仓库
// userrepository.h
#ifndef USERREPOSITORY_H
#define USERREPOSITORY_H
#include "repository.h"
class UserRepository : public BaseRepository {
public:
struct User {
int id = 0;
QString name;
QString email;
int age = 0;
QString createdAt;
};
// CREATE
int create(const User &user) {
QSqlQuery query;
query.prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
query.addBindValue(user.name);
query.addBindValue(user.email);
query.addBindValue(user.age);
if (!query.exec()) {
qCritical() << "Create error:" << query.lastError().text();
return -1;
}
return query.lastInsertId().toInt();
}
// READ by ID
User findById(int id) {
auto row = queryOne("SELECT * FROM users WHERE id = ?", {id});
return rowToUser(row);
}
// READ ALL with pagination
QList<User> findAll(int offset = 0, int limit = 50,
const QString &orderBy = "id DESC") {
QString sql = QStringLiteral(
"SELECT * FROM users ORDER BY %1 LIMIT ? OFFSET ?")
.arg(orderBy);
auto rows = queryAll(sql, {limit, offset});
QList<User> users;
for (const auto &row : rows) {
users.append(rowToUser(row.toMap()));
}
return users;
}
// SEARCH
QList<User> search(const QString &keyword) {
auto rows = queryAll(
"SELECT * FROM users WHERE name LIKE ? OR email LIKE ? ORDER BY id DESC",
{"%" + keyword + "%", "%" + keyword + "%"});
QList<User> users;
for (const auto &row : rows) {
users.append(rowToUser(row.toMap()));
}
return users;
}
// UPDATE
bool update(const User &user) {
return executeQuery(
"UPDATE users SET name=?, email=?, age=? WHERE id=?",
{user.name, user.email, user.age, user.id});
}
// DELETE
bool remove(int id) {
return executeQuery("DELETE FROM users WHERE id=?", {id});
}
// COUNT
int count() {
auto row = queryOne("SELECT COUNT(*) as cnt FROM users");
return row["cnt"].toInt();
}
private:
User rowToUser(const QVariantMap &row) {
User u;
u.id = row["id"].toInt();
u.name = row["name"].toString();
u.email = row["email"].toString();
u.age = row["age"].toInt();
u.createdAt = row["created_at"].toString();
return u;
}
};
#endif // USERREPOSITORY_H
13.3 表单验证 / Form Validation
验证器框架 / Validator Framework
// validator.h - 表单验证器
#ifndef VALIDATOR_H
#define VALIDATOR_H
#include <QString>
#include <QRegularExpression>
#include <QMap>
#include <QStringList>
class ValidationResult {
public:
bool isValid() const { return m_errors.isEmpty(); }
QStringList errors() const { return m_errors; }
QString errorMessage() const { return m_errors.join("\n"); }
void addError(const QString &field, const QString &message) {
m_errors.append(QStringLiteral("%1: %2").arg(field, message));
}
void addError(const QString &message) {
m_errors.append(message);
}
private:
QStringList m_errors;
};
class Validator {
public:
// 非空验证
static bool required(const QString &value, const QString &field,
ValidationResult &result) {
if (value.trimmed().isEmpty()) {
result.addError(field, "此字段不能为空 / This field is required");
return false;
}
return true;
}
// 邮箱验证
static bool email(const QString &value, const QString &field,
ValidationResult &result) {
static QRegularExpression regex(
R"(^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$)");
if (!regex.match(value).hasMatch()) {
result.addError(field, "邮箱格式无效 / Invalid email format");
return false;
}
return true;
}
// 长度范围
static bool length(const QString &value, const QString &field,
int min, int max, ValidationResult &result) {
int len = value.length();
if (len < min || len > max) {
result.addError(field,
QStringLiteral("长度必须在 %1 - %2 之间 / Length must be %1-%2")
.arg(min).arg(max));
return false;
}
return true;
}
// 数值范围
static bool range(int value, const QString &field,
int min, int max, ValidationResult &result) {
if (value < min || value > max) {
result.addError(field,
QStringLiteral("值必须在 %1 - %2 之间 / Value must be %1-%2")
.arg(min).arg(max));
return false;
}
return true;
}
// 唯一性验证（数据库检查）
static bool uniqueEmail(const QString &email, int excludeId,
ValidationResult &result) {
QSqlQuery query;
query.prepare("SELECT COUNT(*) FROM users WHERE email = ? AND id != ?");
query.addBindValue(email);
query.addBindValue(excludeId);
if (query.exec() && query.next() && query.value(0).toInt() > 0) {
result.addError("邮箱", "该邮箱已被使用 / Email already in use");
return false;
}
return true;
}
};
#endif // VALIDATOR.h
13.4 完整 CRUD 表单 / Complete CRUD Form (Qt)
// userform.h - 带验证的 CRUD 表单
#ifndef USERFORM_H
#define USERFORM_H
#include <QWidget>
#include <QFormLayout>
#include <QLineEdit>
#include <QSpinBox>
#include <QPushButton>
#include <QMessageBox>
#include <QLabel>
#include "userrepository.h"
#include "validator.h"
class UserForm : public QWidget {
Q_OBJECT
public:
explicit UserForm(QWidget *parent = nullptr) : QWidget(parent) {
auto *layout = new QFormLayout(this);
layout->setLabelAlignment(Qt::AlignRight);
// 错误提示
m_errorLabel = new QLabel;
m_errorLabel->setStyleSheet(
"color: #e74c3c; background: #fce4ec; padding: 8px; "
"border-radius: 4px; font-size: 12px;");
m_errorLabel->hide();
layout->addRow("", m_errorLabel);
// 输入字段
m_nameEdit = new QLineEdit;
m_nameEdit->setPlaceholderText("请输入姓名 (2-50 字符)");
layout->addRow("姓名 *:", m_nameEdit);
m_emailEdit = new QLineEdit;
m_emailEdit->setPlaceholderText("[email protected]");
layout->addRow("邮箱 *:", m_emailEdit);
m_ageSpin = new QSpinBox;
m_ageSpin->setRange(0, 150);
m_ageSpin->setValue(25);
layout->addRow("年龄:", m_ageSpin);
// 按钮
auto *btnLayout = new QHBoxLayout;
m_submitBtn = new QPushButton("保存 / Save");
m_submitBtn->setObjectName("submitButton");
auto *clearBtn = new QPushButton("清空 / Clear");
btnLayout->addStretch();
btnLayout->addWidget(clearBtn);
btnLayout->addWidget(m_submitBtn);
layout->addRow("", btnLayout);
// 连接信号
connect(m_submitBtn, &QPushButton::clicked, this, &UserForm::onSubmit);
connect(clearBtn, &QPushButton::clicked, this, &UserForm::onClear);
// 实时验证（输入时清除错误）
connect(m_nameEdit, &QLineEdit::textChanged, this, [this]() {
m_errorLabel->hide();
m_nameEdit->setStyleSheet("");
});
connect(m_emailEdit, &QLineEdit::textChanged, this, [this]() {
m_errorLabel->hide();
m_emailEdit->setStyleSheet("");
});
}
void loadUser(int userId) {
m_editingId = userId;
auto user = m_repo.findById(userId);
if (user.id > 0) {
m_nameEdit->setText(user.name);
m_emailEdit->setText(user.email);
m_ageSpin->setValue(user.age);
m_submitBtn->setText("更新 / Update");
}
}
signals:
void userSaved();
private slots:
void onSubmit() {
// 验证
ValidationResult validation;
Validator::required(m_nameEdit->text(), "姓名", validation);
Validator::length(m_nameEdit->text(), "姓名", 2, 50, validation);
Validator::required(m_emailEdit->text(), "邮箱", validation);
Validator::email(m_emailEdit->text(), "邮箱", validation);
Validator::range(m_ageSpin->value(), "年龄", 0, 150, validation);
if (!validation.isValid()) {
m_errorLabel->setText(validation.errorMessage());
m_errorLabel->show();
return;
}
// 构建用户对象
UserRepository::User user;
user.id = m_editingId;
user.name = m_nameEdit->text().trimmed();
user.email = m_emailEdit->text().trimmed();
user.age = m_ageSpin->value();
bool success;
if (m_editingId > 0) {
success = m_repo.update(user);
} else {
success = m_repo.create(user) > 0;
}
if (success) {
QMessageBox::information(this, "成功", "操作成功完成");
onClear();
emit userSaved();
} else {
m_errorLabel->setText("保存失败，请重试");
m_errorLabel->show();
}
}
void onClear() {
m_nameEdit->clear();
m_emailEdit->clear();
m_ageSpin->setValue(25);
m_errorLabel->hide();
m_editingId = 0;
m_submitBtn->setText("保存 / Save");
}
private:
QLineEdit *m_nameEdit;
QLineEdit *m_emailEdit;
QSpinBox *m_ageSpin;
QPushButton *m_submitBtn;
QLabel *m_errorLabel;
UserRepository m_repo;
int m_editingId = 0;
};
#endif // USERFORM_H
13.5 Python 集成示例 / Python Integration Example
"""数据库 CRUD + 表单验证 - PySide6 + SQLite"""
import re
import sqlite3
from dataclasses import dataclass, field
from pathlib import Path
from typing import Optional
from PySide6.QtWidgets import (
QWidget, QVBoxLayout, QHBoxLayout, QFormLayout,
QLineEdit, QSpinBox, QPushButton, QTableView,
QLabel, QMessageBox, QGroupBox
)
from PySide6.QtCore import Qt, Signal, Slot
from PySide6.QtGui import QStandardItemModel, QStandardItem
@dataclass
class User:
id: int = 0
name: str = ""
email: str = ""
age: int = 0
class ValidationResult:
def __init__(self):
self.errors: list[str] = []
@property
def is_valid(self) -> bool:
return len(self.errors) == 0
def add_error(self, field_name: str, message: str):
self.errors.append(f"{field_name}: {message}")
@property
def error_message(self) -> str:
return "\n".join(self.errors)
class Validator:
@staticmethod
def required(value: str, field_name: str, result: ValidationResult) -> bool:
if not value.strip():
result.add_error(field_name, "此字段不能为空")
return False
return True
@staticmethod
def email(value: str, field_name: str, result: ValidationResult) -> bool:
pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
if not re.match(pattern, value):
result.add_error(field_name, "邮箱格式无效")
return False
return True
@staticmethod
def length(value: str, field_name: str,
min_len: int, max_len: int, result: ValidationResult) -> bool:
if not (min_len <= len(value) <= max_len):
result.add_error(field_name, f"长度必须在 {min_len}-{max_len} 之间")
return False
return True
class UserRepository:
def __init__(self, db_path: str = "users.db"):
self.conn = sqlite3.connect(db_path)
self.conn.row_factory = sqlite3.Row
self._create_table()
def _create_table(self):
self.conn.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
""")
self.conn.commit()
def create(self, user: User) -> int:
cur = self.conn.execute(
"INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
(user.name, user.email, user.age))
self.conn.commit()
return cur.lastrowid
def find_all(self, limit: int = 100, offset: int = 0) -> list[User]:
rows = self.conn.execute(
"SELECT * FROM users ORDER BY id DESC LIMIT ? OFFSET ?",
(limit, offset)).fetchall()
return [User(r["id"], r["name"], r["email"], r["age"]) for r in rows]
def find_by_id(self, user_id: int) -> Optional[User]:
row = self.conn.execute(
"SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
return User(row["id"], row["name"], row["email"], row["age"]) if row else None
def update(self, user: User) -> bool:
self.conn.execute(
"UPDATE users SET name=?, email=?, age=? WHERE id=?",
(user.name, user.email, user.age, user.id))
self.conn.commit()
return True
def delete(self, user_id: int) -> bool:
self.conn.execute("DELETE FROM users WHERE id=?", (user_id,))
self.conn.commit()
return True
class UserCrudWidget(QWidget):
"""完整 CRUD 界面"""
def __init__(self, parent=None):
super().__init__(parent)
self.repo = UserRepository()
self.editing_id = 0
self._setup_ui()
self._load_data()
def _setup_ui(self):
layout = QHBoxLayout(self)
# 左：表单
form_group = QGroupBox("用户信息")
form = QFormLayout(form_group)
self.error_label = QLabel()
self.error_label.setStyleSheet(
"color: #e74c3c; background: #fce4ec; padding: 8px; border-radius: 4px;")
self.error_label.hide()
form.addRow("", self.error_label)
self.name_edit = QLineEdit()
self.name_edit.setPlaceholderText("请输入姓名 (2-50 字符)")
form.addRow("姓名 *:", self.name_edit)
self.email_edit = QLineEdit()
self.email_edit.setPlaceholderText("[email protected]")
form.addRow("邮箱 *:", self.email_edit)
self.age_spin = QSpinBox()
self.age_spin.setRange(0, 150)
form.addRow("年龄:", self.age_spin)
btn_layout = QHBoxLayout()
self.save_btn = QPushButton("保存")
self.save_btn.clicked.connect(self._on_save)
clear_btn = QPushButton("清空")
clear_btn.clicked.connect(self._on_clear)
btn_layout.addStretch()
btn_layout.addWidget(clear_btn)
btn_layout.addWidget(self.save_btn)
form.addRow("", btn_layout)
form_group.setFixedWidth(300)
layout.addWidget(form_group)
# 右：表格
right = QVBoxLayout()
self.search_edit = QLineEdit()
self.search_edit.setPlaceholderText("搜索...")
right.addWidget(self.search_edit)
self.table = QTableView()
self.model = QStandardItemModel()
self.model.setHorizontalHeaderLabels(["ID", "姓名", "邮箱", "年龄"])
self.table.setModel(self.model)
self.table.setSelectionBehavior(QTableView.SelectionBehavior.SelectRows)
self.table.setAlternatingRowColors(True)
self.table.clicked.connect(self._on_row_clicked)
right.addWidget(self.table)
# 表格操作按钮
table_btns = QHBoxLayout()
del_btn = QPushButton("删除选中")
del_btn.clicked.connect(self._on_delete)
table_btns.addStretch()
table_btns.addWidget(del_btn)
right.addLayout(table_btns)
layout.addLayout(right)
def _load_data(self):
self.model.removeRows(0, self.model.rowCount())
for user in self.repo.find_all():
row = [
QStandardItem(str(user.id)),
QStandardItem(user.name),
QStandardItem(user.email),
QStandardItem(str(user.age)),
]
self.model.appendRow(row)
@Slot()
def _on_save(self):
result = ValidationResult()
Validator.required(self.name_edit.text(), "姓名", result)
Validator.length(self.name_edit.text(), "姓名", 2, 50, result)
Validator.required(self.email_edit.text(), "邮箱", result)
Validator.email(self.email_edit.text(), "邮箱", result)
if not result.is_valid:
self.error_label.setText(result.error_message)
self.error_label.show()
return
user = User(
id=self.editing_id,
name=self.name_edit.text().strip(),
email=self.email_edit.text().strip(),
age=self.age_spin.value()
)
if self.editing_id > 0:
self.repo.update(user)
else:
self.repo.create(user)
self._on_clear()
self._load_data()
QMessageBox.information(self, "成功", "操作成功完成")
@Slot()
def _on_delete(self):
idx = self.table.currentIndex()
if not idx.isValid():
return
user_id = int(self.model.item(idx.row(), 0).text())
if QMessageBox.question(self, "确认", "确定要删除吗？") == QMessageBox.StandardButton.Yes:
self.repo.delete(user_id)
self._load_data()
@Slot()
def _on_clear(self):
self.name_edit.clear()
self.email_edit.clear()
self.age_spin.setValue(0)
self.error_label.hide()
self.editing_id = 0
self.save_btn.setText("保存")
@Slot()
def _on_row_clicked(self, index):
row = index.row()
self.editing_id = int(self.model.item(row, 0).text())
self.name_edit.setText(self.model.item(row, 1).text())
self.email_edit.setText(self.model.item(row, 2).text())
self.age_spin.setValue(int(self.model.item(row, 3).text()))
self.save_btn.setText("更新")
注意事项 / Important Notes
⚠️ 永远参数化查询 / Always Parameterize
// ❌ 错误：SQL 注入风险 query.exec("SELECT * FROM users WHERE name = '" + name + "'"); // ✅ 正确：参数化 query.prepare("SELECT * FROM users WHERE name = ?"); query.addBindValue(name);
⚠️ 事务使用 / Use Transactions 批量操作务必使用事务。SQLite 的无事务批量插入极慢。
⚠️ 连接管理 / Connection Management 长期运行的应用需定期检查连接状态，处理断连重连。
扩展阅读 / Further Reading
| 资源 / Resource | 链接 / Link |
|---|---|
| Qt SQL 文档 | https://doc.qt.io/qt-6/sql-programming.html |
| SQLite 文档 | https://www.sqlite.org/docs.html |
| Repository 模式 | https://martinfowler.com/eaaCatalog/repository.html |
← 12 - 主题与样式 | 14 - UI 测试 →