Qt 与 SQLite 嵌入式数据库开发

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

一、SQLite 基础配置与连接

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

#include #include #include 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 的备份 API QSqlQuery 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 在嵌入式环境中的优势。