← 返回库列表

🗄️ SQLiteCpp 使用指南

优雅的 C++ SQLite 数据库封装

项目地址：https://github.com/SRombauts/SQLiteCpp

📖 什么是 SQLiteCpp？

SQLiteCpp 是一个对 SQLite 的 C++ 封装库，提供 RAII 和异常安全的接口，简化了 SQLite 数据库的操作。

🚀 安装方式

使用 vcpkg（推荐）

vcpkg install sqlitecpp

使用 CMake

git clone https://github.com/SRombauts/SQLiteCpp.git
cd SQLiteCpp
mkdir build && cd build
cmake .. -DCMAKE_BUILD_TYPE=Release
make -j4
sudo make install

💡 基础使用

连接和创建数据库

#include <iostream>
#include <SQLiteCpp/SQLiteCpp.h>

int main() {
    try {
        // 打开或创建数据库
        SQLite::Database db("example.db", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);

        std::cout << "数据库文件: " << db.getFilename() << std::endl;
        std::cout << "是否打开: " << db.isOpen() << std::endl;

    } catch (std::exception& e) {
        std::cerr << "错误: " << e.what() << std::endl;
    }

    return 0;
}

创建表

SQLite::Database db("example.db", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);

// 创建表
db.exec("CREATE TABLE IF NOT EXISTS users ("
       "id INTEGER PRIMARY KEY AUTOINCREMENT, "
       "name TEXT NOT NULL, "
       "age INTEGER, "
       "email TEXT)");

// 检查表是否存在
bool exists = db.tableExists("users");
std::cout << "表 exists: " << exists << std::endl;

插入数据

// 直接执行 SQL
db.exec("INSERT INTO users (name, age, email) VALUES ('John', 25, 'john@example.com')");

// 使用预编译语句（推荐）
SQLite::Statement query(db, "INSERT INTO users (name, age, email) VALUES (?, ?, ?)");

query.bind(1, "Alice");
query.bind(2, 30);
query.bind(3, "alice@example.com");
query.exec();

query.bind(1, "Bob");
query.bind(2, 28);
query.bind(3, "bob@example.com");
query.exec();

// 使用事务
SQLite::Transaction transaction(db);
try {
    db.exec("INSERT INTO users (name, age) VALUES ('Charlie', 35)");
    db.exec("INSERT INTO users (name, age) VALUES ('David', 40)");
    transaction.commit();
} catch (...) {
    transaction.rollback();
}

查询数据

// 查询单条记录
SQLite::Statement query(db, "SELECT * FROM users WHERE id = ?");
query.bind(1, 1);

if (query.executeStep()) {
    int id = query.getColumn(0);
    std::string name = query.getColumn(1);
    int age = query.getColumn(2);

    std::cout << "ID: " << id << ", Name: " << name << ", Age: " << age << std::endl;
}

// 查询所有记录
SQLite::Statement queryAll(db, "SELECT * FROM users");

while (queryAll.executeStep()) {
    int id = queryAll.getColumn("id");
    std::string name = queryAll.getColumn("name");
    int age = queryAll.getColumn("age");
    std::string email = queryAll.getColumn("email");

    std::cout << id << ": " << name << ", " << age << " 岁, " << email << std::endl;
}

更新和删除

// 更新数据
SQLite::Statement update(db, "UPDATE users SET age = ? WHERE id = ?");
update.bind(1, 26);
update.bind(2, 1);
update.exec();

int updated = db.execAndGet("SELECT changes()");
std::cout << "更新了 " << updated << " 行" << std::endl;

// 删除数据
SQLite::Statement del(db, "DELETE FROM users WHERE id = ?");
del.bind(1, 2);
del.exec();

int deleted = db.execAndGet("SELECT changes()");
std::cout << "删除了 " << deleted << " 行" << std::endl;

使用返回值

// 获取单个值
int count = db.execAndGet("SELECT COUNT(*) FROM users");
std::cout << "用户总数: " << count << std::endl;

// 获取平均年龄
double avgAge = db.execAndGet("SELECT AVG(age) FROM users");
std::cout << "平均年龄: " << avgAge << std::endl;

批量插入

SQLite::Statement insert(db, "INSERT INTO users (name, age) VALUES (?, ?)");

// 批量插入（在事务中）
SQLite::Transaction transaction(db);

for (int i = 0; i < 100; i++) {
    insert.bind(1, "User" + std::to_string(i));
    insert.bind(2, 20 + (i % 50));
    insert.exec();
    insert.reset();
}

transaction.commit();

std::cout << "插入了 100 条记录" << std::endl;

内存数据库

// 使用内存数据库
SQLite::Database db(":memory:", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);

db.exec("CREATE TABLE test (id INTEGER, name TEXT)");

// 快速操作，无磁盘 I/O

错误处理

try {
    SQLite::Database db("example.db", SQLite::OPEN_READWRITE);

    // 执行可能失败的 SQL
    SQLite::Statement query(db, "SELECT * FROM nonexistent_table");

    if (!query.executeStep()) {
        std::cout << "查询失败" << std::endl;
    }

} catch (SQLite::Exception& e) {
    std::cerr << "SQLite 错误: " << e.what() << std::endl;
    std::cerr << "错误代码: " << e.getErrorCode() << std::endl;
    std::cerr << "扩展错误: " << e.getExtendedErrorCode() << std::endl;
}

🎯 常见操作速查

操作 方法 说明
执行 SQL db.exec() 执行无返回的 SQL
获取值 db.execAndGet() 执行并获取单个值
预编译 SQLite::Statement 预编译 SQL 语句
绑定参数 query.bind() 绑定参数
事务 SQLite::Transaction 事务管理
检查表 db.tableExists() 检查表是否存在

📚 更多资源