1. Environmental preparation
1. Install PostgreSQL
Make sure PostgreSQL is installed and the test database is created.
2. Install the Qt development environment
Make sure the Qt development environment (Qt Creator or command line tool) is installed.
3. Configure Qt connection PostgreSQL
Add in the project file (.pro):
QT += sql
2. Connect to PostgreSQL database
1. Basic connection method
#include <QCoreApplication>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QDebug>
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
// Create a database connection QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
// Set connection parameters ("localhost"); // Host name (5432); // Port ("testdb"); // Database name ("postgres"); // username ("password"); // password
// Open the connection if (!()) {
qDebug() << "Database connection failed:" << ().text();
return -1;
}
qDebug() << "Successfully connected to the database";
// Close the connection ();
return ();
}2. Use the connection pool (recommended)
// Create a connection poolQSqlDatabase createConnectionPool(const QString &connectionName) {
QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL", connectionName);
("localhost");
(5432);
("testdb");
("postgres");
("password");
if (!()) {
qCritical() << "Creating a connection pool failed:" << ().text();
return QSqlDatabase();
}
return db;
}
// Get the connectionQSqlDatabase getConnection(const QString &connectionName) {
QSqlDatabase db = QSqlDatabase::database(connectionName);
if (!()) {
if (!()) {
qCritical() << "Finished connection:" << ().text();
return QSqlDatabase();
}
}
return db;
}
// Release the connectionvoid releaseConnection(const QString &connectionName) {
QSqlDatabase::removeDatabase(connectionName);
}3. Implement the operation of adding, deleting, modifying and checking
1. Create a test table
First create a test table in PostgreSQL:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary NUMERIC(10, 2),
hire_date DATE
);2. Insert data (Add)
bool insertEmployee(QSqlDatabase &db, const QString &name,
const QString &position, double salary,
const QDate &hireDate) {
QSqlQuery query(db);
// Use preprocessing statements to prevent SQL injection ("INSERT INTO employees (name, position, salary, hire_date) "
"VALUES (:name, :position, :salary, :hire_date)");
(":name", name);
(":position", position);
(":salary", salary);
(":hire_date", hireDate);
if (!()) {
qDebug() << "Insert data failed:" << ().text();
return false;
}
return true;
}3. Query data (Query)
3.1 Query a single record
QSqlRecord getEmployeeById(QSqlDatabase &db, int id) {
QSqlQuery query(db);
("SELECT * FROM employees WHERE id = :id");
(":id", id);
if (!() || !()) {
qDebug() << "Inquiry failed:" << ().text();
return QSqlRecord();
}
return ();
}3.2 Query all records
QList<QSqlRecord> getAllEmployees(QSqlDatabase &db) {
QList<QSqlRecord> employees;
QSqlQuery query(db);
("SELECT * FROM employees ORDER BY id");
while (()) {
(());
}
return employees;
}3.3 Querying using model (Qt SQL model)
QSqlTableModel *createEmployeeModel(QObject *parent = nullptr) {
QSqlTableModel *model = new QSqlTableModel(parent);
model->setTable("employees");
model->select();
// Set the table header 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;
}4. Update data (Update)
bool updateEmployee(QSqlDatabase &db, int id,
const QString &name, const QString &position,
double salary, const QDate &hireDate) {
QSqlQuery query(db);
("UPDATE employees SET name = :name, position = :position, "
"salary = :salary, hire_date = :hire_date WHERE id = :id");
(":name", name);
(":position", position);
(":salary", salary);
(":hire_date", hireDate);
(":id", id);
if (!()) {
qDebug() << "Update employees failed:" << ().text();
return false;
}
return true;
}5. Delete data (Delete)
bool deleteEmployee(QSqlDatabase &db, int id) {
QSqlQuery query(db);
("DELETE FROM employees WHERE id = :id");
(":id", id);
if (!()) {
qDebug() << "Delete employees failed:" << ().text();
return false;
}
return true;
}4. Complete example
1. Use the console program to demonstrate CRUD operations
#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");
("localhost");
(5432);
("testdb");
("postgres");
("password");
if (!()) {
qDebug() << "Database connection failed:" << ().text();
return false;
}
return true;
}
void closeDatabase(QSqlDatabase &db) {
();
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
QSqlDatabase db;
if (!openDatabase(db)) {
return -1;
}
// Insert data QSqlQuery query(db);
("INSERT INTO employees (name, position, salary, hire_date) "
"VALUES (:name, :position, :salary, :hire_date)");
(":name", "Zhang San");
(":position", "Development Engineer");
(":salary", 15000.00);
(":hire_date", QDate::currentDate());
if (!()) {
qDebug() << "Insert failed:" << ().text();
} else {
qDebug() << "Insert successfully, ID:" << ().toInt();
}
// Query data QSqlQuery selectQuery(db);
("SELECT * FROM employees ORDER BY id");
while (()) {
QSqlRecord record = ();
qDebug() << "ID:" << ("id").toInt()
<< "Name:" << ("name").toString()
<< "Position:" << ("position").toString()
<< "Salary:" << ("salary").toDouble()
<< "Organization Date:" << ("hire_date").toDate();
}
// Update data ("UPDATE employees SET salary = :salary WHERE id = :id");
(":salary", 16000.00);
(":id", 1); // Assume that the employee with ID 1
if (!()) {
qDebug() << "Update failed:" << ().text();
} else {
qDebug() << "Update Successfully";
}
// Delete data ("DELETE FROM employees WHERE id = :id");
(":id", 1); // Assume that you want to delete an employee with ID 1
if (!()) {
qDebug() << "Delete failed:" << ().text();
} else {
qDebug() << "Delete successfully";
}
closeDatabase(db);
return ();
}2. Use Qt Widgets to implement the GUI interface
//
#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
//
#include ""
#include "ui_employeeform.h"
#include <QSqlDatabase>
#include <QSqlError>
#include <QMessageBox>
EmployeeForm::EmployeeForm(QWidget *parent)
: QWidget(parent)
, ui(new Ui::EmployeeForm)
{
ui->setupUi(this);
// Connect to the database QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
("localhost");
(5432);
("testdb");
("postgres");
("password");
if (!()) {
QMessageBox::critical(this, "mistake", "Unable to connect to database: " + ().text());
return;
}
// Create a model model = new QSqlTableModel(this, db);
model->setTable("employees");
model->select();
// Set the table header 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("Order date"));
// Set up view ui->tableView->setModel(model);
ui->tableView->setEditTriggers(QAbstractItemView::DoubleClicked);
// Set up the data mapper 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 signal slot connect(ui->tableView->selectionModel(), &QItemSelectionModel::currentRowChanged,
this, [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, "mistake", "Save failed: " + model->lastError().text());
} else {
model->database().transaction();
if (model->submitAll()) {
model->database().commit();
QMessageBox::information(this, "success", "Data saved successfully");
} else {
model->database().rollback();
QMessageBox::warning(this, "mistake", "Save failed: " + model->lastError().text());
}
}
}
void EmployeeForm::on_deleteButton_clicked()
{
QModelIndex index = ui->tableView->currentIndex();
if (()) {
int ret = QMessageBox::question(this, "confirm", "Are you sure you want to delete this record?",
QMessageBox::Yes | QMessageBox::No);
if (ret == QMessageBox::Yes) {
model->removeRow(());
if (!model->submitAll()) {
QMessageBox::warning(this, "mistake", "Delete failed: " + model->lastError().text());
model->revertAll();
}
}
}
}
void EmployeeForm::on_refreshButton_clicked()
{
model->select();
}V. Advanced features
1. Transaction processing
bool performTransaction(QSqlDatabase &db) {
();
QSqlQuery query(db);
bool success = true;
// Perform multiple operations if (!("INSERT INTO employees (...) VALUES (...)" )) {
success = false;
}
if (!("UPDATE ...")) {
success = false;
}
if (success) {
();
} else {
();
}
return success;
}2. Batch insertion
bool batchInsertEmployees(QSqlDatabase &db, const QList<QVariantList> &employees) {
QSqlDatabase::database().transaction();
QSqlQuery query(db);
("INSERT INTO employees (name, position, salary, hire_date) "
"VALUES (?, ?, ?, ?)");
foreach (const QVariantList &employee, employees) {
(employee);
if (!()) {
QSqlDatabase::database().rollback();
return false;
}
}
QSqlDatabase::database().commit();
return true;
}3. Use stored procedures
bool callStoredProcedure(QSqlDatabase &db, int employeeId) {
QSqlQuery query(db);
("CALL update_employee_salary(:id, :percentage)");
(":id", employeeId);
(":percentage", 10); // Increase 10%
if (!()) {
qDebug() << "Calling stored procedure failed:" << ().text();
return false;
}
return true;
}6. Frequently Asked Questions
1. Connection failed
- Check whether the PostgreSQL service is running
- Verify connection parameters (host name, port, database name, user name, password)
- Check firewall settings
- Make sure the PostgreSQL client library is installed
2. Chinese garbled
// Set encodingQTextCodec::setCodecForLocale(QTextCodec::codecForName("UTF-8"));Or specify the encoding in the connection string:
("client_encoding=UTF8");3. Performance optimization
- Use preprocessing statements
- Batch operation instead of single operation
- Use transactions reasonably
- Create indexes for common queries
7. Summary
Qt provides powerful and flexible database access functions, and the Qt SQL module can easily implement the addition, deletion, modification and query operations of PostgreSQL databases. This article introduces how to implement from basic connections to advanced features and provides a complete code example. In actual development, the appropriate implementation method can be selected according to project needs, and combined with transaction processing, batch operation and other technologies to improve application performance.
The above is the detailed content of QT operating PostgreSQL database and implementing the functions of adding, deleting, modifying and checking. For more information about QT operating PostgreSQL addition, deleting, modifying and checking, please pay attention to my other related articles!