【C++数据库】SQLite3数据库连接与操作

注意：本文代码均为C++20标准下实现

一、SQLite3库安装

1.1 安装库文件

【工具】跨平台C++包管理利器vcpkg完全指南

vcpkg install sqlite3# 集成至系统目录，之前执行过此命令的无需再次执行
vcpkg integrate install

1.2 验证代码

在VS2022中新建控制台项目，测试代码如下：

#include <iostream>
#include <sqlite3.h>int main() {sqlite3* db;int rc = sqlite3_open(":memory:", &db);if (rc == SQLITE_OK) {std::cout << "SQLite3 initialized successfully!\n";std::cout << "Version: " << sqlite3_libversion() << std::endl;sqlite3_close(db);} else {std::cerr << "Open failed: " << sqlite3_errmsg(db) << std::endl;}return 0;
}

运行结果：
在这里插入图片描述

二、数据库连接管理

2.1 文件数据库创建

sqlite3* db;
const char* filename = "mydatabase.db";// 使用SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE组合标志
int rc = sqlite3_open_v2(filename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);if (rc != SQLITE_OK) {std::cerr << "Cannot open database: " << sqlite3_errmsg(db);sqlite3_close(db);return -1;
}

连接标志说明：

SQLITE_OPEN_READONLY：只读模式
SQLITE_OPEN_READWRITE：读写模式（需文件存在）
SQLITE_OPEN_CREATE：不存在时创建

2.2 连接池配置（基础版）

class DBConnection {
public:static sqlite3* get_connection() {static sqlite3* shared_db = nullptr;if (!shared_db) {int rc = sqlite3_open(":memory:", &shared_db);if (rc != SQLITE_OK) {// 错误处理...}}return shared_db;}
};

2.3 操作类基础版示例（DBConnection.h）

//DBConnection.h
#pragma once#include <iostream>
#include <sqlite3.h>class DBConnection {
public:static sqlite3* get_sqlite3_connection(const char* filename) {static sqlite3* shared_db = nullptr;// 使用SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE组合标志int rc = sqlite3_open_v2(filename, &shared_db,SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,nullptr);if (rc != SQLITE_OK) {rc = sqlite3_open(":memory:", &shared_db);}if (rc != SQLITE_OK) {std::cout << "获取 Sqlite3 数据库连接创建失败。" << std::endl;}return shared_db;}
};

测试代码修改如下：

#include <iostream>
#include "DBConnection.h"int main() {sqlite3* db = DBConnection::get_sqlite3_connection(nullptr);if (db) {std::cout << "SQLite3 initialized successfully!\n";std::cout << "Version: " << sqlite3_libversion() << std::endl;sqlite3_close(db);}else {std::cerr << "Open failed: " << sqlite3_errmsg(db) << std::endl;}return 0;
}

三、SQL执行基础

3.1 直接执行DDL语句

DDL（Data Definition Language，数据定义语言）是 SQL 中用于定义和管理数据库结构（模式）的语句集合。它不直接操作数据，而是定义数据的存储结构和规则。
简而言之：数据库操作语句

//在数据库中创建一张表
const char* create_table_sql = R"(
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT NOT NULL UNIQUE,created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);)";char* errMsg = nullptr;
rc = sqlite3_exec(db, create_table_sql, nullptr, nullptr, &errMsg);if (rc != SQLITE_OK) {std::cerr << "SQL error: " << errMsg;sqlite3_free(errMsg);
}

3.2 带参数的表删除

bool drop_table(const char* table_name) {sqlite3_stmt* stmt;const char* sql = "DROP TABLE IF EXISTS ?;";if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) != SQLITE_OK) {return false;}sqlite3_bind_text(stmt, 1, table_name, -1, SQLITE_STATIC);int rc = sqlite3_step(stmt);sqlite3_finalize(stmt);return rc == SQLITE_DONE;
}

3.3 错误代码分类处理

自行处理相关错误信息

switch(rc) {case SQLITE_BUSY:std::cerr << "Database locked";break;case SQLITE_CORRUPT:std::cerr << "Database file damaged";break;case SQLITE_TOOBIG:std::cerr << "Data exceeds limit";break;// 其他错误处理...
}

使用sqlite3库提供的对应API

sqlite3_errstr(rc);

四、完善基础DBConnection

4.1 线程安全的完整代码

#pragma once#include <iostream>
#include <string>
#include <vector>
#include <sqlite3.h>
#include <mutex>
#include <memory>class DBConnection {
public:// 获取数据库连接（线程安全版本）static sqlite3* get_connection(const char* filename = "default.db") noexcept {std::lock_guard<std::mutex> lock(s_mutex);if (!s_shared_db) {const char* effective_filename = filename ? filename : "default.db";int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;int rc = sqlite3_open_v2(effective_filename, &s_shared_db, flags, nullptr);if (rc != SQLITE_OK) {// 关闭可能的无效连接if (s_shared_db) {sqlite3_close(s_shared_db);s_shared_db = nullptr;}// 尝试内存数据库rc = sqlite3_open_v2(":memory:", &s_shared_db, flags, nullptr);if (rc == SQLITE_OK) {s_current_file = ":memory:";}else {std::cerr << "Failed to open memory database: " << sqlite3_errmsg(s_shared_db) << "\n";s_current_file.clear();}}else {s_current_file = effective_filename