Qt 与 SQLite 嵌入式数据库开发

Qt 与 SQLite 的结合是开发轻量级、跨平台嵌入式数据库应用的理想选择。SQLite 作为一种零配置、文件型数据库，无需独立的服务器进程，非常适合集成到 Qt 应用中。本文将深入探讨 Qt 与 SQLite 的嵌入式数据库开发，包括基础操作、高级特性、性能优化和实际应用案例。

一、SQLite 基础配置与连接

1. 驱动检查与数据库连接

#include <QSqlDatabase>
#include <QSqlError>
#include <QDebug>void connectToSQLite() {// 检查 SQLite 驱动是否可用if (!QSqlDatabase::isDriverAvailable("QSQLITE")) {qDebug() << "SQLite driver not available!";return;}// 创建数据库连接QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");db.setDatabaseName("mydatabase.db");  // 数据库文件名if (!db.open()) {qDebug() << "Cannot open database:" << db.lastError().text();return;}qDebug() << "SQLite database connected successfully!";
}

2. 创建表结构

void createTables(QSqlDatabase &db) {QSqlQuery query(db);// 创建用户表query.exec(R"(CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER,email TEXT UNIQUE))");if (query.lastError().isValid()) {qDebug() << "Error creating table:" << query.lastError().text();}// 创建订单表query.exec(R"(CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,product TEXT,price REAL,order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)))");
}

二、数据操作与查询

1. 插入数据

void insertData(QSqlDatabase &db) {QSqlQuery query(db);// 插入用户数据query.prepare("INSERT INTO users (name, age, email) VALUES (:name, :age, :email)");query.bindValue(":name", "Alice");query.bindValue(":age", 30);query.bindValue(":email", "alice@example.com");if (query.exec()) {qDebug() << "User inserted successfully!";} else {qDebug() << "Error inserting user:" << query.lastError().text();}// 批量插入订单数据db.transaction();  // 使用事务提高性能QList<QVariantList> orders = {{1, "Product A", 99.99},{1, "Product B", 49.99},{1, "Product C", 199.99}};query.prepare("INSERT INTO orders (user_id, product, price) VALUES (:user_id, :product, :price)");for (const auto &order : orders) {query.bindValue(":user_id", order[0]);query.bindValue(":product", order[1]);query.bindValue(":price", order[2]);query.exec();}db.commit();
}

2. 查询数据

void queryData(QSqlDatabase &db) {// 简单查询QSqlQuery query("SELECT * FROM users", db);while (query.next()) {int id = query.value("id").toInt();QString name = query.value("name").toString();int age = query.value("age").toInt();QString email = query.value("email").toString();qDebug() << "User:" << id << name << age << email;}// 带参数的查询query.prepare("SELECT * FROM orders WHERE user_id = :user_id AND price > :min_price");query.bindValue(":user_id", 1);query.bindValue(":min_price", 50.0);if (query.exec()) {while (query.next()) {QString product = query.value("product").toString();double price = query.value("price").toDouble();QDateTime date = query.value("order_date").toDateTime();qDebug() << "Order:" << product << price << date.toString("yyyy-MM-dd");}}
}

三、高级特性与优化

1. 事务处理

bool transferMoney(QSqlDatabase &db, int fromAccount, int toAccount, double amount) {db.transaction();try {QSqlQuery query(db);// 减少源账户余额query.prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :id");query.bindValue(":amount", amount);query.bindValue(":id", fromAccount);if (!query.exec() || query.numRowsAffected() == 0) {throw std::runtime_error("Failed to debit from source account");}// 增加目标账户余额query.prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :id");query.bindValue(":amount", amount);query.bindValue(":id", toAccount);if (!query.exec() || query.numRowsAffected() == 0) {throw std::runtime_error("Failed to credit to target account");}db.commit();return true;} catch (const std::exception &e) {db.rollback();qDebug() << "Transaction failed:" << e.what();return false;}
}

2. 索引优化

void createIndex(QSqlDatabase &db) {QSqlQuery query(db);// 创建索引提高查询性能query.exec("CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders (user_id)");query.exec("CREATE INDEX IF NOT EXISTS idx_orders_price ON orders (price)");// 创建复合索引query.exec("CREATE INDEX IF NOT EXISTS idx_users_name_age ON users (name, age)");
}

3. 外键约束

void enableForeignKeys(QSqlDatabase &db) {QSqlQuery query(db);// 启用外键约束（SQLite 默认禁用）query.exec("PRAGMA foreign_keys = ON");if (query.lastError().isValid()) {qDebug() << "Failed to enable foreign keys:" << query.lastError().text();}
}

四、SQLite 特定功能

1. 数据库加密（使用 SQLCipher）

void openEncryptedDatabase(const QString &dbPath, const QString &password) {QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");db.setDatabaseName(dbPath);// 设置加密密钥db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1;QSQLITE_PWD=" + password);if (!db.open()) {qDebug() << "Cannot open encrypted database:" << db.lastError().text();return;}qDebug() << "Encrypted database opened successfully!";
}

2. 全文搜索

void createFtsTable(QSqlDatabase &db) {QSqlQuery query(db);// 创建 FTS5 全文搜索表query.exec(R"(CREATE VIRTUAL TABLE IF NOT EXISTS notes USING fts5(title,content,tags))");// 插入测试数据query.prepare("INSERT INTO notes (title, content, tags) VALUES (:title, :content, :tags)");query.bindValue(":title", "Qt Development");query.bindValue(":content", "Learn Qt with SQLite for embedded applications");query.bindValue(":tags", "Qt,SQLite,Embedded");query.exec();
}void searchFtsTable(QSqlDatabase &db, const QString &keyword) {QSqlQuery query(db);query.prepare("SELECT * FROM notes WHERE content MATCH :keyword");query.bindValue(":keyword", keyword);if (query.exec()) {while (query.next()) {QString title = query.value("title").toString();QString content = query.value("content").toString();qDebug() << "Search result:" << title << content.left(50) + "...";}}
}

五、性能优化策略

1. 批量操作优化

void batchInsertOptimized(QSqlDatabase &db, const QList<QVariantList> &data) {db.transaction();QSqlQuery query(db);query.prepare("INSERT INTO large_table (col1, col2, col3) VALUES (:val1, :val2, :val3)");// 使用预处理语句和事务进行批量插入for (const auto &row : data) {query.bindValue(":val1", row[0]);query.bindValue(":val2", row[1]);query.bindValue(":val3", row[2]);query.exec();}db.commit();
}

2. 内存管理优化

void optimizeMemoryUsage(QSqlDatabase &db) {QSqlQuery query(db);// 设置页面大小query.exec("PRAGMA page_size = 4096");// 设置缓存大小（以页面为单位）query.exec("PRAGMA cache_size = -2000");  // 负值表示 KiB，此处为 2000 KiB// 优化写入性能query.exec("PRAGMA synchronous = NORMAL");// 启用 WAL 模式query.exec("PRAGMA journal_mode = WAL");
}

六、错误处理与恢复

1. 错误处理机制

bool executeSafely(QSqlQuery &query, const QString &sql, const QVariantMap &bindValues = {}) {query.prepare(sql);// 绑定参数for (auto it = bindValues.begin(); it != bindValues.end(); ++it) {query.bindValue(it.key(), it.value());}if (!query.exec()) {qDebug() << "SQL execution error:" << query.lastError().text();qDebug() << "Failed query:" << sql;return false;}return true;
}

2. 数据库恢复

bool backupDatabase(const QString &sourceDb, const QString &targetDb) {QSqlDatabase srcDb = QSqlDatabase::addDatabase("QSQLITE", "sourceConnection");srcDb.setDatabaseName(sourceDb);if (!srcDb.open()) {qDebug() << "Cannot open source database:" << srcDb.lastError().text();return false;}QSqlDatabase destDb = QSqlDatabase::addDatabase("QSQLITE", "targetConnection");destDb.setDatabaseName(targetDb);if (!destDb.open()) {qDebug() << "Cannot open target database:" << destDb.lastError().text();srcDb.close();return false;}// 使用 SQLite 的备份 APIQSqlQuery query(srcDb);query.exec("BACKUP TO '" + targetDb + "'");srcDb.close();destDb.close();return true;
}

七、ORM 框架集成

1. 简单 ORM 实现

class Note {
public:int id;QString title;QString content;QDateTime created;// 从记录创建对象static Note fromRecord(const QSqlRecord &record) {Note note;note.id = record.value("id").toInt();note.title = record.value("title").toString();note.content = record.value("content").toString();note.created = record.value("created").toDateTime();return note;}// 保存到数据库bool save(QSqlDatabase &db) {QSqlQuery query(db);if (id <= 0) {// 插入新记录query.prepare("INSERT INTO notes (title, content, created) VALUES (:title, :content, :created)");query.bindValue(":title", title);query.bindValue(":content", content);query.bindValue(":created", created.isValid() ? created : QDateTime::currentDateTime());if (query.exec()) {id = query.lastInsertId().toInt();return true;}} else {// 更新现有记录query.prepare("UPDATE notes SET title = :title, content = :content WHERE id = :id");query.bindValue(":title", title);query.bindValue(":content", content);query.bindValue(":id", id);return query.exec();}return false;}
};

八、实际应用案例

1. 嵌入式设备数据记录

class DataLogger {
public:DataLogger(const QString &dbPath) {db = QSqlDatabase::addDatabase("QSQLITE");db.setDatabaseName(dbPath);if (!db.open()) {qDebug() << "Failed to open database:" << db.lastError().text();} else {createTables();}}void logSensorData(const QString &sensorId, double value) {if (!db.isOpen()) return;QSqlQuery query(db);query.prepare("INSERT INTO sensor_data (sensor_id, value, timestamp) VALUES (:sensor_id, :value, :timestamp)");query.bindValue(":sensor_id", sensorId);query.bindValue(":value", value);query.bindValue(":timestamp", QDateTime::currentDateTime());query.exec();}private:QSqlDatabase db;void createTables() {QSqlQuery query(db);query.exec(R"(CREATE TABLE IF NOT EXISTS sensor_data (id INTEGER PRIMARY KEY AUTOINCREMENT,sensor_id TEXT NOT NULL,value REAL,timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP))");}
};

九、总结

Qt 与 SQLite 的结合为嵌入式数据库应用开发提供了强大而灵活的解决方案。SQLite 的轻量级特性与 Qt 的跨平台能力相得益彰，使开发者能够轻松构建高效、可靠的数据驱动应用。通过合理使用事务、索引、全文搜索等高级特性，并采用适当的性能优化策略，可以充分发挥 SQLite 在嵌入式环境中的优势。