QT操作PostgreSQL数据库并实现增删改查功能
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、环境准备</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1. 安装 PostgreSQL</a></li><li><a href="#_lab2_0_1">2. 安装 Qt 开发环境</a></li><li><a href="#_lab2_0_2">3. 配置 Qt 连接 PostgreSQL</a></li></ul><li><a href="#_label1">二、连接 PostgreSQL 数据库</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">1. 基本连接方式</a></li><li><a href="#_lab2_1_4">2. 使用连接池(推荐)</a></li></ul><li><a href="#_label2">三、实现增删改查操作</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_5">1. 创建测试表</a></li><li><a href="#_lab2_2_6">2. 插入数据(Add)</a></li><li><a href="#_lab2_2_7">3. 查询数据(Query)</a></li><ul class="third_class_ul"><li><a href="#_label3_2_7_0">3.1 查询单条记录</a></li><li><a href="#_label3_2_7_1">3.2 查询所有记录</a></li><li><a href="#_label3_2_7_2">3.3 使用模型查询(Qt SQL 模型)</a></li></ul><li><a href="#_lab2_2_8">4. 更新数据(Update)</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_9">5. 删除数据(Delete)</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label3">四、完整示例</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_10">1. 使用控制台程序演示CRUD操作</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_11">2. 使用Qt Widgets实现GUI界面</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label4">五、高级功能</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_12">1. 事务处理</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_13">2. 批量插入</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_14">3. 使用存储过程</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label5">六、常见问题解决</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_15">1. 连接失败</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_16">2. 中文乱码</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_17">3. 性能优化</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label6">七、总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、环境准备</h2><p class="maodian"><a name="_lab2_0_0"></a></p><h3>1. 安装 PostgreSQL</h3>
<p>确保已安装 PostgreSQL 并创建了测试数据库。</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2. 安装 Qt 开发环境</h3>
<p>确保已安装 Qt 开发环境(Qt Creator 或命令行工具)。</p>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3. 配置 Qt 连接 PostgreSQL</h3>
<p>在项目文件(.pro)中添加：</p>
<div class="jb51code"><pre class="brush:cpp;">QT += sql</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>二、连接 PostgreSQL 数据库</h2>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>1. 基本连接方式</h3>
<div class="jb51code"><pre class="brush:cpp;">#include <QCoreApplication>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QDebug>
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
// 创建数据库连接
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
// 设置连接参数
db.setHostName("localhost"); // 主机名
db.setPort(5432); // 端口
db.setDatabaseName("testdb"); // 数据库名
db.setUserName("postgres"); // 用户名
db.setPassword("password"); // 密码
// 打开连接
if (!db.open()) {
qDebug() << "数据库连接失败:" << db.lastError().text();
return -1;
}
qDebug() << "成功连接到数据库";
// 关闭连接
db.close();
return a.exec();
}</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>2. 使用连接池(推荐)</h3>
<div class="jb51code"><pre class="brush:cpp;">// 创建连接池
QSqlDatabase createConnectionPool(const QString &connectionName) {
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL", connectionName);
db.setHostName("localhost");
db.setPort(5432);
db.setDatabaseName("testdb");
db.setUserName("postgres");
db.setPassword("password");
if (!db.open()) {
qCritical() << "创建连接池失败:" << db.lastError().text();
return QSqlDatabase();
}
return db;
}
// 获取连接
QSqlDatabase getConnection(const QString &connectionName) {
QSqlDatabase db = QSqlDatabase::database(connectionName);
if (!db.isOpen()) {
if (!db.open()) {
qCritical() << "获取连接失败:" << db.lastError().text();
return QSqlDatabase();
}
}
return db;
}
// 释放连接
void releaseConnection(const QString &connectionName) {
QSqlDatabase::removeDatabase(connectionName);
}</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>三、实现增删改查操作</h2>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>1. 创建测试表</h3>
<p>首先在 PostgreSQL 中创建测试表：</p>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary NUMERIC(10, 2),
hire_date DATE
);</pre></div>
<p class="maodian"><a name="_lab2_2_6"></a></p><h3>2. 插入数据(Add)</h3>
<div class="jb51code"><pre class="brush:cpp;">bool insertEmployee(QSqlDatabase &db, const QString &name,
const QString &position, double salary,
const QDate &hireDate) {
QSqlQuery query(db);
// 使用预处理语句防止SQL注入
query.prepare("INSERT INTO employees (name, position, salary, hire_date) "
"VALUES (:name, :position, :salary, :hire_date)");
query.bindValue(":name", name);
query.bindValue(":position", position);
query.bindValue(":salary", salary);
query.bindValue(":hire_date", hireDate);
if (!query.exec()) {
qDebug() << "插入数据失败:" << query.lastError().text();
return false;
}
return true;
}</pre></div>
<p class="maodian"><a name="_lab2_2_7"></a></p><h3>3. 查询数据(Query)</h3>
<p class="maodian"><a name="_label3_2_7_0"></a></p><h4>3.1 查询单条记录</h4>
<div class="jb51code"><pre class="brush:cpp;">QSqlRecord getEmployeeById(QSqlDatabase &db, int id) {
QSqlQuery query(db);
query.prepare("SELECT * FROM employees WHERE id = :id");
query.bindValue(":id", id);
if (!query.exec() || !query.next()) {
qDebug() << "查询员工失败:" << query.lastError().text();
return QSqlRecord();
}
return query.record();
}</pre></div>
<p class="maodian"><a name="_label3_2_7_1"></a></p><h4>3.2 查询所有记录</h4>
<div class="jb51code"><pre class="brush:cpp;">QList<QSqlRecord> getAllEmployees(QSqlDatabase &db) {
QList<QSqlRecord> employees;
QSqlQuery query(db);
query.exec("SELECT * FROM employees ORDER BY id");
while (query.next()) {
employees.append(query.record());
}
return employees;
}</pre></div>
<p class="maodian"><a name="_label3_2_7_2"></a></p><h4>3.3 使用模型查询(Qt SQL 模型)</h4>
<div class="jb51code"><pre class="brush:cpp;">QSqlTableModel *createEmployeeModel(QObject *parent = nullptr) {
QSqlTableModel *model = new QSqlTableModel(parent);
model->setTable("employees");
model->select();
// 设置表头
model->setHeaderData(1, Qt::Horizontal, tr("Name"));
model->setHeaderData(2, Qt::Horizontal, tr("Position"));
model->setHeaderData(3, Qt::Horizontal, tr("Salary"));
model->setHeaderData(4, Qt::Horizontal, tr("Hire Date"));
return model;
}</pre></div>
<p class="maodian"><a name="_lab2_2_8"></a></p><h3>4. 更新数据(Update)</h3>
<div class="jb51code"><pre class="brush:cpp;">bool updateEmployee(QSqlDatabase &db, int id,
const QString &name, const QString &position,
double salary, const QDate &hireDate) {
QSqlQuery query(db);
query.prepare("UPDATE employees SET name = :name, position = :position, "
"salary = :salary, hire_date = :hire_date WHERE id = :id");
query.bindValue(":name", name);
query.bindValue(":position", position);
query.bindValue(":salary", salary);
query.bindValue(":hire_date", hireDate);
query.bindValue(":id", id);
if (!query.exec()) {
qDebug() << "更新员工失败:" << query.lastError().text();
return false;
}
return true;
}</pre></div>
<p class="maodian"><a name="_lab2_2_9"></a></p><h3>5. 删除数据(Delete)</h3>
<div class="jb51code"><pre class="brush:cpp;">bool deleteEmployee(QSqlDatabase &db, int id) {
QSqlQuery query(db);
query.prepare("DELETE FROM employees WHERE id = :id");
query.bindValue(":id", id);
if (!query.exec()) {
qDebug() << "删除员工失败:" << query.lastError().text();
return false;
}
return true;
}</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>四、完整示例</h2>
<p class="maodian"><a name="_lab2_3_10"></a></p><h3>1. 使用控制台程序演示CRUD操作</h3>
<div class="jb51code"><pre class="brush:cpp;">#include <QCoreApplication>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlRecord>
#include <QDebug>
#include <QDate>
bool openDatabase(QSqlDatabase &db) {
db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("localhost");
db.setPort(5432);
db.setDatabaseName("testdb");
db.setUserName("postgres");
db.setPassword("password");
if (!db.open()) {
qDebug() << "数据库连接失败:" << db.lastError().text();
return false;
}
return true;
}
void closeDatabase(QSqlDatabase &db) {
db.close();
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
QSqlDatabase db;
if (!openDatabase(db)) {
return -1;
}
// 插入数据
QSqlQuery query(db);
query.prepare("INSERT INTO employees (name, position, salary, hire_date) "
"VALUES (:name, :position, :salary, :hire_date)");
query.bindValue(":name", "张三");
query.bindValue(":position", "开发工程师");
query.bindValue(":salary", 15000.00);
query.bindValue(":hire_date", QDate::currentDate());
if (!query.exec()) {
qDebug() << "插入失败:" << query.lastError().text();
} else {
qDebug() << "插入成功，ID:" << query.lastInsertId().toInt();
}
// 查询数据
QSqlQuery selectQuery(db);
selectQuery.exec("SELECT * FROM employees ORDER BY id");
while (selectQuery.next()) {
QSqlRecord record = selectQuery.record();
qDebug() << "ID:" << record.value("id").toInt()
<< "姓名:" << record.value("name").toString()
<< "职位:" << record.value("position").toString()
<< "薪资:" << record.value("salary").toDouble()
<< "入职日期:" << record.value("hire_date").toDate();
}
// 更新数据
query.prepare("UPDATE employees SET salary = :salary WHERE id = :id");
query.bindValue(":salary", 16000.00);
query.bindValue(":id", 1); // 假设ID为1的员工
if (!query.exec()) {
qDebug() << "更新失败:" << query.lastError().text();
} else {
qDebug() << "更新成功";
}
// 删除数据
query.prepare("DELETE FROM employees WHERE id = :id");
query.bindValue(":id", 1); // 假设要删除ID为1的员工
if (!query.exec()) {
qDebug() << "删除失败:" << query.lastError().text();
} else {
qDebug() << "删除成功";
}
closeDatabase(db);
return a.exec();
}</pre></div>
<p class="maodian"><a name="_lab2_3_11"></a></p><h3>2. 使用Qt Widgets实现GUI界面</h3>
<div class="jb51code"><pre class="brush:cpp;">// employeeform.h
#ifndef EMPLOYEEFORM_H
#define EMPLOYEEFORM_H
#include <QWidget>
#include <QSqlTableModel>
#include <QDataWidgetMapper>
QT_BEGIN_NAMESPACE
namespace Ui { class EmployeeForm; }
QT_END_NAMESPACE
class EmployeeForm : public QWidget
{
Q_OBJECT
public:
EmployeeForm(QWidget *parent = nullptr);
~EmployeeForm();
private slots:
void on_addButton_clicked();
void on_saveButton_clicked();
void on_deleteButton_clicked();
void on_refreshButton_clicked();
private:
Ui::EmployeeForm *ui;
QSqlTableModel *model;
QDataWidgetMapper *mapper;
};
#endif // EMPLOYEEFORM_H
// employeeform.cpp
#include "employeeform.h"
#include "ui_employeeform.h"
#include <QSqlDatabase>
#include <QSqlError>
#include <QMessageBox>
EmployeeForm::EmployeeForm(QWidget *parent)
: QWidget(parent)
, ui(new Ui::EmployeeForm)
{
ui->setupUi(this);
// 连接数据库
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
db.setHostName("localhost");
db.setPort(5432);
db.setDatabaseName("testdb");
db.setUserName("postgres");
db.setPassword("password");
if (!db.open()) {
QMessageBox::critical(this, "错误", "无法连接到数据库: " + db.lastError().text());
return;
}
// 创建模型
model = new QSqlTableModel(this, db);
model->setTable("employees");
model->select();
// 设置表头
model->setHeaderData(1, Qt::Horizontal, tr("姓名"));
model->setHeaderData(2, Qt::Horizontal, tr("职位"));
model->setHeaderData(3, Qt::Horizontal, tr("薪资"));
model->setHeaderData(4, Qt::Horizontal, tr("入职日期"));
// 设置视图
ui->tableView->setModel(model);
ui->tableView->setEditTriggers(QAbstractItemView::DoubleClicked);
// 设置数据映射器
mapper = new QDataWidgetMapper(this);
mapper->setModel(model);
mapper->addMapping(ui->nameEdit, 1);
mapper->addMapping(ui->positionEdit, 2);
mapper->addMapping(ui->salaryEdit, 3);
mapper->addMapping(ui->hireDateEdit, 4);
// 连接信号槽
connect(ui->tableView->selectionModel(), &QItemSelectionModel::currentRowChanged,
this, (const QModelIndex &current, const QModelIndex &) {
mapper->setCurrentModelIndex(current);
});
}
EmployeeForm::~EmployeeForm()
{
delete ui;
}
void EmployeeForm::on_addButton_clicked()
{
int row = model->rowCount();
model->insertRow(row);
ui->tableView->selectRow(row);
mapper->setCurrentIndex(row);
ui->nameEdit->setFocus();
}
void EmployeeForm::on_saveButton_clicked()
{
if (!model->submitAll()) {
QMessageBox::warning(this, "错误", "保存失败: " + model->lastError().text());
} else {
model->database().transaction();
if (model->submitAll()) {
model->database().commit();
QMessageBox::information(this, "成功", "数据保存成功");
} else {
model->database().rollback();
QMessageBox::warning(this, "错误", "保存失败: " + model->lastError().text());
}
}
}
void EmployeeForm::on_deleteButton_clicked()
{
QModelIndex index = ui->tableView->currentIndex();
if (index.isValid()) {
int ret = QMessageBox::question(this, "确认", "确定要删除这条记录吗?",
QMessageBox::Yes | QMessageBox::No);
if (ret == QMessageBox::Yes) {
model->removeRow(index.row());
if (!model->submitAll()) {
QMessageBox::warning(this, "错误", "删除失败: " + model->lastError().text());
model->revertAll();
}
}
}
}
void EmployeeForm::on_refreshButton_clicked()
{
model->select();
}</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>五、高级功能</h2>
<p class="maodian"><a name="_lab2_4_12"></a></p><h3>1. 事务处理</h3>
<div class="jb51code"><pre class="brush:cpp;">bool performTransaction(QSqlDatabase &db) {
db.transaction();
QSqlQuery query(db);
bool success = true;
// 执行多个操作
if (!query.exec("INSERT INTO employees (...) VALUES (...)" )) {
success = false;
}
if (!query.exec("UPDATE ...")) {
success = false;
}
if (success) {
db.commit();
} else {
db.rollback();
}
return success;
}</pre></div>
<p class="maodian"><a name="_lab2_4_13"></a></p><h3>2. 批量插入</h3>
<div class="jb51code"><pre class="brush:cpp;">bool batchInsertEmployees(QSqlDatabase &db, const QList<QVariantList> &employees) {
QSqlDatabase::database().transaction();
QSqlQuery query(db);
query.prepare("INSERT INTO employees (name, position, salary, hire_date) "
"VALUES (?, ?, ?, ?)");
foreach (const QVariantList &employee, employees) {
query.addBindValue(employee);
if (!query.execBatch()) {
QSqlDatabase::database().rollback();
return false;
}
}
QSqlDatabase::database().commit();
return true;
}</pre></div>
<p class="maodian"><a name="_lab2_4_14"></a></p><h3>3. 使用存储过程</h3>
<div class="jb51code"><pre class="brush:cpp;">bool callStoredProcedure(QSqlDatabase &db, int employeeId) {
QSqlQuery query(db);
query.prepare("CALL update_employee_salary(:id, :percentage)");
query.bindValue(":id", employeeId);
query.bindValue(":percentage", 10); // 增加10%
if (!query.exec()) {
qDebug() << "调用存储过程失败:" << query.lastError().text();
return false;
}
return true;
}</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>六、常见问题解决</h2>
<p class="maodian"><a name="_lab2_5_15"></a></p><h3>1. 连接失败</h3>
<ul><li>检查PostgreSQL服务是否运行</li><li>验证连接参数(主机名、端口、数据库名、用户名、密码)</li><li>检查防火墙设置</li><li>确保安装了PostgreSQL客户端库</li></ul>
<p class="maodian"><a name="_lab2_5_16"></a></p><h3>2. 中文乱码</h3>
<div class="jb51code"><pre class="brush:cpp;">// 设置编码
QTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF-8"));</pre></div>
<p>或者在连接字符串中指定编码：</p>
<div class="jb51code"><pre class="brush:cpp;">db.setConnectOptions("client_encoding=UTF8");</pre></div>
<p class="maodian"><a name="_lab2_5_17"></a></p><h3>3. 性能优化</h3>
<ul><li>使用预处理语句</li><li>批量操作代替单条操作</li><li>合理使用事务</li><li>为常用查询创建索引</li></ul>
<p class="maodian"><a name="_label6"></a></p><h2>七、总结</h2>
<p>Qt 提供了强大而灵活的数据库访问功能，通过 Qt SQL 模块可以轻松实现 PostgreSQL 数据库的增删改查操作。本文介绍了从基本连接到高级功能的实现方法，并提供了完整的代码示例。在实际开发中，可以根据项目需求选择合适的实现方式，结合事务处理、批量操作等技术提高应用性能。</p>
<p>以上就是QT操作PostgreSQL数据库并实现增删改查功能的详细内容，更多关于QT操作PostgreSQL增删改查的资料请关注琼殿技术社区其它相关文章！</p>
頁:
[1]