Qt数据库操作V1.0

1、pro文件

QT += sql

2、h文件

#ifndef DATABASEOPERATION_H #define DATABASEOPERATION_H #include <QSqlDatabase> #include <QSqlQuery> #include <QSqlError> #include <QSqlRecord> #include <QDebug> #include <QVariant> class DatabaseOperation { public: enum DatabaseType { Sqlite = 0, MySql = 1, }; public: DatabaseOperation(); void SetDatabaseType(const DatabaseType& dbType); // void SetHostName(const QString& hostName); // void SetUserName(const QString& userName); // void SetPassword(const QString& password); bool Open(const QString& dbFilePath); bool IsOpen() const; void Close(); bool CreateTable(const QString& tableName, const QMap<QString, QString>& columns); bool DropTable(const QString& tableName); bool InsertData(const QString& tableName, const QMap<QString, QVariant>& values); bool SelectData(const QString& tableName, const QMap<QString, QVariant>& conditions, QList<QList<QVariant>>& results); bool DeleteData(const QString& tableName, const QMap<QString, QVariant>& conditions); private: void ExecuteQuery(const QString& sql, const QMap<QString, QVariant>& params = QMap<QString, QVariant>()); QSqlDatabase m_db; QString m_dbFilePath; }; #endif // DATABASEOPERATION_H

3、cpp文件

#include "DatabaseOperation.h" DatabaseOperation::DatabaseOperation() { } void DatabaseOperation::SetDatabaseType(const DatabaseType& dbType) { QString dbName; switch (dbType) { case DatabaseOperation::Sqlite: dbName = "QSQLITE"; break; case DatabaseOperation::MySql: dbName = "QMYSQL"; break; } m_db = QSqlDatabase::addDatabase(dbName); } bool DatabaseOperation::Open(const QString &dbFilePath) { m_dbFilePath = dbFilePath; m_db.setDatabaseName(dbFilePath); if (!m_db.open()) { throw std::runtime_error("Database open failed: " + m_db.lastError().text().toStdString()); } return true; } //void DatabaseOperation::SetHostName(const QString& hostName) //{ // m_db.setHostName(hostName); //} //void DatabaseOperation::SetUserName(const QString& userName) //{ // m_db.setUserName(userName); //} //void DatabaseOperation::SetPassword(const QString& password) //{ // m_db.setPassword(password); //} void DatabaseOperation::Close() { if (m_db.isOpen()) { m_db.close(); } } bool DatabaseOperation::CreateTable(const QString& tableName, const QMap<QString, QString>& columns) { QStringList columnDefinitions; for (auto it = columns.constBegin(); it != columns.constEnd(); ++it) { columnDefinitions << QString("%1 %2").arg(it.key(), it.value()); } QString sql = QString("CREATE TABLE IF NOT EXISTS %1 (%2)").arg(tableName, columnDefinitions.join(", ")); try { ExecuteQuery(sql); } catch (const std::exception& e) { qDebug() << "Create table failed: " << e.what(); return false; } return true; } bool DatabaseOperation::DropTable(const QString& tableName) { QString sql = QString("DROP TABLE IF EXISTS %1").arg(tableName); try { ExecuteQuery(sql); } catch (const std::exception& e) { qDebug() << "Drop table failed: " << e.what(); return false; } return true; } bool DatabaseOperation::InsertData(const QString& tableName, const QMap<QString, QVariant>& values) { QStringList keys = values.keys(); QStringList placeholders; for (const auto& key : keys) { placeholders << ":" + key; } QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)").arg(tableName, keys.join(", "), placeholders.join(", ")); try { ExecuteQuery(sql, values); } catch (const std::exception& e) { qDebug() << "Insert data failed: " << e.what(); return false; } return true; } bool DatabaseOperation::SelectData(const QString& tableName, const QMap<QString, QVariant>& conditions, QList<QList<QVariant>>& results) { QStringList conditionStrings; for (const auto& key : conditions.keys()) { conditionStrings << QString("%1 = :%1").arg(key); } QString sql = QString("SELECT * FROM %1").arg(tableName); if (!conditionStrings.isEmpty()) { sql += " WHERE " + conditionStrings.join(" AND "); } QSqlQuery query(m_db); query.prepare(sql); // 绑定条件参数 for (auto it = conditions.constBegin(); it != conditions.constEnd(); ++it) { query.bindValue(":" + it.key(), it.value()); } if (!query.exec()) { qDebug() << "Select data failed: " << query.lastError().text(); return false; } results.clear(); while (query.next()) { QList<QVariant> row; for (int i = 0; i < query.record().count(); ++i) { row.append(query.value(i)); } results.append(row); } return true; } bool DatabaseOperation::DeleteData(const QString& tableName, const QMap<QString, QVariant>& conditions) { QStringList conditionStrings; for (const auto& key : conditions.keys()) { conditionStrings << key + "=:" + key; // 构造条件字符串，例如 "id=:id" } QString sql = QString("DELETE FROM %1 WHERE %2").arg(tableName, conditionStrings.join(" AND ")); try { ExecuteQuery(sql, conditions); } catch (const std::exception& e) { qDebug() << "Delete data failed: " << e.what(); return false; } return true; } void DatabaseOperation::ExecuteQuery(const QString& sql, const QMap<QString, QVariant>& params/* = QMap<QString, QVariant>()*/) { QSqlQuery query(m_db); query.prepare(sql); for (auto it = params.constBegin(); it != params.constEnd(); ++it) { query.bindValue(":" + it.key(), it.value()); } if (!query.exec()) { throw std::runtime_error("Execute query failed: " + query.lastError().text().toStdString()); } }

4、测试文件

#include "Widget.h" #include <QApplication> #include "DatabaseOperation.h" int main(int argc, char *argv[]) { // 创建数据库对象 DatabaseOperation dbOperation; // 设置数据库类型 dbOperation.SetDatabaseType(DatabaseOperation::Sqlite); // 打开SQLite数据库 if (!dbOperation.Open("test.db")) { qDebug() << "Open database failed"; return -1; } // 删除表 if(!dbOperation.DropTable("users")) { qDebug() << "Drop table failed"; } // 创建表 QMap<QString, QString> columns { {"id", "INTEGER PRIMARY KEY AUTOINCREMENT"}, {"name", "TEXT NOT NULL"}, {"age", "INTEGER DEFAULT 18"} }; if (!dbOperation.CreateTable("users", columns)) { qDebug() << "Create table failed"; return -1; } // 插入数据1 QMap<QString, QVariant> values { {"name", "Tom"}, {"age", 21} }; if (!dbOperation.InsertData("users", values)) { qDebug() << "Insert data failed"; return -1; } // 插入数据2 QMap<QString, QVariant> values2 { {"name", "Jie"}, {"age", 22} }; if (!dbOperation.InsertData("users", values2)) { qDebug() << "Insert data failed"; return -1; } QList<QList<QVariant>> results; QMap<QString, QVariant> conditions { {"age", 20} }; // 查询数据 if (!dbOperation.SelectData("users", conditions, results)) { qDebug() << "Select data failed"; return -1; } for (const auto& row : results) { QString name = row[1].toString(); int age = row[2].toInt(); qDebug() << "name: " << name << ", age: " << age; } // // 删除数据 // if (!dbOperation.DeleteData("users", conditions)) // { // qDebug() << "Delete data failed"; // return -1; // } // // 删除表 // if (!dbOperation.DropTable("users")) // { // qDebug() << "Drop table failed"; // return -1; // } // 关闭数据库 dbOperation.Close(); QApplication a(argc, argv); Widget w; w.show(); return a.exec(); }