在线程中执行数据库的增删改查等操作（Qt）

残梦5314

于 2024-07-15 14:32:32 发布

阅读量38

点赞数

文章标签： qt 数据库开发语言

残梦5314

本文链接：https://blog.csdn.net/qq_60797167/article/details/140438091

版权

#ifndef MYSQLTHREAD_H
#define MYSQLTHREAD_H

#include <QThread>
#include <QObject>
#include <QtSql>
#include <QDebug>
#include <functional>

class MySQLThread : public QThread
{
    Q_OBJECT

public:
    explicit MySQLThread(QObject *parent = nullptr);
    ~MySQLThread();

    // 连接数据库
    void connectToDatabase(QString hostname, QString databaseName, QString username, QString password);

    // 断开数据库连接
    void disconnectFromDatabase();

signals:
    // 数据操作完成信号，参数为操作是否成功和操作类型
    void dataOperationFinished(bool success, const QString &operation);

    // 查询数据完成信号，参数为查询结果列表
    void dataQueried(const QList<QMap<QString, QVariant>> &result);

public slots:
    // 插入数据到指定表
    void insertData(const QString &tableName, const QMap<QString, QVariant> &data);

    // 删除指定表中符合条件的数据
    void deleteData(const QString &tableName, const QString &condition);

    // 更新指定表中符合条件的数据
    void updateData(const QString &tableName, const QMap<QString, QVariant> &data, const QString &condition);

    // 查询指定表中符合条件的数据
    void queryData(const QString &tableName, const QString &condition);

protected:
    void run() override;

private:
    QSqlDatabase db; // 数据库连接对象

    // 创建数据库连接
    bool createConnection(const QString &hostname, const QString &databaseName, const QString &username, const QString &password);
};

#endif // MYSQLTHREAD_H

#include "MySQLThread.h"

MySQLThread::MySQLThread(QObject *parent) : QThread(parent)
{

}

MySQLThread::~MySQLThread()
{
    disconnectFromDatabase();
}

void MySQLThread::connectToDatabase(QString hostname, QString databaseName, QString username, QString password)
{
    if (db.isOpen()) {
        qDebug() << "数据库已经打开。";
        return;
    }

    if (!createConnection(hostname, databaseName, username, password)) {
        qDebug() << "连接数据库失败。";
        return;
    }

    qDebug() << "数据库连接成功。";
}

void MySQLThread::disconnectFromDatabase()
{
    if (db.isOpen()) {
        db.close();
        qDebug() << "数据库已关闭。";
    }
}

void MySQLThread::run()
{
    exec(); // 进入线程事件循环
}

void MySQLThread::insertData(const QString &tableName, const QMap<QString, QVariant> &data)
{
    performDatabaseOperation([=]() {
        QSqlQuery query(db);

        QString columns;
        QString values;

        // 构建插入语句的列和值部分
        for (auto it = data.constBegin(); it != data.constEnd(); ++it) {
            columns += it.key() + ", ";
            values += ":" + it.key() + ", ";
            query.bindValue(":" + it.key(), it.value());
        }

        columns.chop(2); // 移除末尾的 ", "
        values.chop(2);  // 移除末尾的 ", "

        // 构建完整的插入语句
        QString queryString = QString("INSERT INTO %1 (%2) VALUES (%3)").arg(tableName, columns, values);

        // 执行插入操作
        bool success = query.exec(queryString);
        emit dataOperationFinished(success, "Insert");

        if (success) {
            qDebug() << "数据插入成功。";
        } else {
            qDebug() << "数据插入失败：" << query.lastError().text();
        }
    });
}

void MySQLThread::deleteData(const QString &tableName, const QString &condition)
{
    performDatabaseOperation([=]() {
        QSqlQuery query(db);

        // 构建删除语句
        QString queryString = QString("DELETE FROM %1 WHERE %2").arg(tableName, condition);

        // 执行删除操作
        bool success = query.exec(queryString);
        emit dataOperationFinished(success, "Delete");

        if (success) {
            qDebug() << "数据删除成功。";
        } else {
            qDebug() << "数据删除失败：" << query.lastError().text();
        }
    });
}

void MySQLThread::updateData(const QString &tableName, const QMap<QString, QVariant> &data, const QString &condition)
{
    performDatabaseOperation([=]() {
        QSqlQuery query(db);

        // 构建更新语句
        QString updateString;

        for (auto it = data.constBegin(); it != data.constEnd(); ++it) {
            updateString += it.key() + " = :" + it.key() + ", ";
            query.bindValue(":" + it.key(), it.value());
        }

        updateString.chop(2); // 移除末尾的 ", "

        QString queryString = QString("UPDATE %1 SET %2 WHERE %3").arg(tableName, updateString, condition);

        // 执行更新操作
        bool success = query.exec(queryString);
        emit dataOperationFinished(success, "Update");

        if (success) {
            qDebug() << "数据更新成功。";
        } else {
            qDebug() << "数据更新失败：" << query.lastError().text();
        }
    });
}

void MySQLThread::queryData(const QString &tableName, const QString &condition)
{
    performDatabaseOperation([=]() {
        QSqlQuery query(db);

        // 构建查询语句
        QString queryString = QString("SELECT * FROM %1 WHERE %2").arg(tableName, condition);

        // 执行查询操作
        bool success = query.exec(queryString);

        QList<QMap<QString, QVariant>> result;
        while (query.next()) {
            QMap<QString, QVariant> row;
            for (int i = 0; i < query.record().count(); ++i) {
                row[query.record().fieldName(i)] = query.value(i);
            }
            result.append(row);
        }

        emit dataQueried(result); // 发送查询结果信号

        if (success) {
            qDebug() << "查询数据成功。";
        } else {
            qDebug() << "查询数据失败：" << query.lastError().text();
        }
    });
}

bool MySQLThread::createConnection(const QString &hostname, const QString &databaseName, const QString &username, const QString &password)
{
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(hostname);
    db.setDatabaseName(databaseName);
    db.setUserName(username);
    db.setPassword(password);

    if (!db.open()) {
        qDebug() << "打开数据库失败：" << db.lastError().text();
        return false;
    }

    return true;
}

#include <QCoreApplication>
#include "MySQLThread.h"

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    MySQLThread thread;

    // 连接数据库
    thread.connectToDatabase("localhost", "mydatabase", "username", "password");

    // 响应数据操作完成信号的槽函数
    QObject::connect(&thread, &MySQLThread::dataOperationFinished, [](bool success, const QString &operation) {
        if (success) {
            qDebug() << "数据" << operation << "成功。";
        } else {
            qDebug() << "数据" << operation << "失败。";
        }
    });

    QObject::connect(&thread, &MySQLThread::dataQueried, [](const QList<QMap<QString, QVariant>> &result) {
        qDebug() << "查询结果：";
        for (const auto &row : result) {
            qDebug() << "姓名：" << row["name"].toString() << ", 年龄：" << row["age"].toInt();
        }
    });

    // 启动线程
    thread.start();

    // 插入数据示例
    QMap<QString, QVariant> data;
    data["name"] = "John Doe";
    data["age"] = 30;
    thread.insertData("users", data);

    // 查询数据示例
    thread.queryData("users", "age > 25");

    // 等待线程退出
    thread.quit();
    thread.wait();

    // 断开数据库连接
    thread.disconnectFromDatabase();

    return a.exec();
}