SQLITE 数据库增删改查

1、添加依赖
在.pro文件添加sql模块

QT += sql
1

2、SqliteHelper头文件

#pragma once

#include 
#include 
#include 
#include 

class SqliteHelper
{
public:
    SqliteHelper();
    SqliteHelper(const QString& filePath);
    ~SqliteHelper();

    bool open(const QString& filePath);
    void close();

    bool createTable(const QString& sql);
    bool createTable(const QString& tableName, const QVariantMap& map);
    bool createTable(const QString& tableName, const QVariantHash& map);
    bool deleteTable(const QString& tableName);

    bool insertData(const QString& tableName, const QVariantMap& map);
    bool updateData(const QString& tableName, const QVariantMap& map, const QString& condition);
    bool deleteData(const QString& tableName, const QString& condition);

    QList<QVariantMap> queryData(const QString& tableName);

    bool beginTransaction();
    bool commitTransaction();
    bool rollbackTransaction();

private:

    bool execute(const QString& sql);
private:
    QSqlDatabase m_db;
};

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

3、SqliteHelper源文件

#include "SqliteHelper.h"

SqliteHelper::SqliteHelper()
{

}

SqliteHelper::SqliteHelper(const QString& filePath)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(filePath);

    if (!m_db.open())
    {
        qDebug() << "Failed to open database.";// 处理数据库打开错误
    }
}

SqliteHelper::~SqliteHelper()
{
    if (m_db.isOpen())
    {
        m_db.close();
    }
}

bool SqliteHelper::open(const QString& filePath)
{
    if (!m_db.isOpen())
    {
        m_db = QSqlDatabase::addDatabase("QSQLITE"); // QSQLITE
        m_db.setDatabaseName(filePath);

        if (!m_db.open())
        {
            qDebug() << "Failed to open database.";// 处理数据库打开错误
            return false;
        }
    }
    return true;
}

void SqliteHelper::close()
{
    if (m_db.isOpen())
    {
        m_db.close();
    }
}

bool SqliteHelper::createTable(const QString& sql)
{
    return execute(sql);
}

bool SqliteHelper::createTable(const QString& tableName, const QVariantMap& map)
{
    QStringList key_value;
    key_value.clear();
    for (const QString& key : map.keys())
    {
        key_value.append(key + " " + map[key].toString());
    }
    // deleteTable(tableName);
    QString sql = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + key_value.join(", ") + ")";
    return execute(sql);
}

bool SqliteHelper::createTable(const QString& tableName, const QVariantHash& map)
{
    QStringList key_value;
    key_value.clear();
    for (const QString& key : map.keys())
    {
        key_value.append(key + " " + map[key].toString());
    }

    //deleteTable(tableName);

    QString sql = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + key_value.join(", ") + ")";
    return execute(sql);
}

bool SqliteHelper::deleteTable(const QString& tableName)
{
    // DROP TABLE IF EXISTS [table_name];
    QString sql = QString("DROP TABLE IF EXISTS '%1'").arg(tableName);
    return execute(sql);
}

bool SqliteHelper::execute(const QString& sql)
{
    QSqlQuery sqlQuery(m_db);
    return sqlQuery.exec(sql);
}

bool SqliteHelper::insertData(const QString& tableName, const QVariantMap& map)
{
    QStringList keys;
    QStringList values;

    for (const QString& key : map.keys())
    {
        keys.append(key);
        values.append("'" + map[key].toString() + "'");
    }

    QString query = "INSERT INTO " + tableName + "(" + keys.join(", ") + ")" + " VALUES (" + values.join(", ") + ")";

    return execute(query);
}

bool SqliteHelper::updateData(const QString& tableName, const QVariantMap& map, const QString& condition)
{
    QStringList updates;

    for (const QString& key : map.keys())
    {
        updates.append(key + " = '" + map[key].toString() + "'");
    }
    QString sql = "UPDATE " + tableName + " SET " + updates.join(", ") + " WHERE " + condition;

    return execute(sql);
}

bool SqliteHelper::deleteData(const QString& tableName, const QString& condition)
{
    QString query = "DELETE FROM " + tableName + " WHERE " + condition;
    return execute(query);
}


QList<QVariantMap> SqliteHelper::queryData(const QString& tableName)
{
    QList<QVariantMap> records;

    if (!m_db.isOpen())
    {
        qDebug() << "Failed to open database.";
        return records;
    }

    // 执行查询操作
    QSqlQuery query(m_db);
    QString sql = QString("SELECT * FROM %1").arg(tableName);
    if (!query.exec(sql))
    {
        qDebug() << "Failed to execute query.";
        return records;
    }

    // 遍历结果集，将每条记录存储为 QVariantMap 并添加到列表中
    while (query.next())
    {
        QVariantMap record;
        QSqlRecord rec = query.record();
        int count = rec.count();

        for (int i = 0; i < count; ++i)
        {
            QString fieldName = rec.fieldName(i);
            QVariant value = rec.value(i);
            record[fieldName] = value;
        }

        records.append(record);
    }

    return records;
}


bool SqliteHelper::beginTransaction()
{
    return m_db.transaction();
}

bool SqliteHelper::commitTransaction()
{
    return m_db.commit();
}

bool SqliteHelper::rollbackTransaction()
{
    return m_db.rollback();
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188

4、main函数

{
	// 实例
    SqliteHelper sqlite("test.db");

    // 表名
    QString tableName("students");

    // 创建测试表    
    // 1
    QString sql = "CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, city TEXT)";
    //sqlite.createTable(sql);

    // 2
    //sqlite.deleteTable(tableName);
    QVariantMap tableHeader;
    tableHeader["id"] = "INTEGER PRIMARY KEY";
    tableHeader["name"] = "TEXT";
    tableHeader["age"] = "INTEGER";
    tableHeader["city"] = "TEXT";
    sqlite.createTable(tableName, tableHeader);

    // 3
    //QVariantHash tableHeader;
    //tableHeader["id"] = "INTEGER PRIMARY KEY";
    //tableHeader["name"] = "TEXT";
    //tableHeader["age"] = "INTEGER";
    //tableHeader["city"] = "TEXT";
    //sqlite.createTable(tableName, tableHeader);


    // 插入一条记录
    QVariantMap data;
    data["name"] = "gg";
    data["age"] = 22;
    data["city"] = "beijing";
    sqlite.insertData(tableName, data);

    // 更新记录
    QVariantMap updatedata;
    //updatedata["name"] = "lisi";
    updatedata["age"] = 32;
    updatedata["city"] = "shanghai";
    sqlite.updateData(tableName, updatedata, "name = 'gg'"); // 通过name修改
    //sqlite.updateData(tableName, updatedata, "id = 1"); // 通过id修改

    // 删除记录
    //sqlite.deleteData("students", "id = 1"); // 通过id删除
    sqlite.deleteData("students", "city = 'shanghai'"); // 通过idcity

    // 查询记录
    //QSqlQuery query(sqlite.m_db);
    //query.exec("SELECT * FROM students");
    //while (query.next())
    //{
    //    int id = query.value("id").toInt();
    //    QString name = query.value("name").toString();
    //    QString city = query.value("city").toString();
    //    int age = query.value("age").toInt();

    //    qDebug() << "id:" << id 
    //        << ", name:" << name 
    //        << ", age:" << age
    //        << ", city:" << city;
    //}


    //调用接口--查询记录
    QList<QVariantMap> mapList = sqlite.queryData(tableName);
    // 遍历结果
    int i = 0;
    for (const QVariantMap& record : mapList)
    {
        qDebug() << "-------------------------------->" + QString::number(++i);
        for (const QString& key : record.keys())
        {
            qDebug() << key + " " + record[key].toString();
        }      
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79