注意：本文代码均为C++20标准下实现
一、SQLite3库安装
1.1 安装库文件
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