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;}// 插入数据1QMap<QString, QVariant> values{{"name", "Tom"},{"age", 21}};if (!dbOperation.InsertData("users", values)){qDebug() << "Insert data failed";return -1;}// 插入数据2QMap<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();
}