要在c++中操作sqlite数据库，需使用sqlite的c api并通过封装简化操作。1. 包含头文件sqlite3.h并建立数据库连接，通过sqlite3_open()函数打开或创建数据库；2. 使用sqlite3_exec()执行sql语句以完成表的创建等操作；3. 对于查询，使用sqlite3_prepare_v2()、sqlite3_step()和sqlite3_finalize()逐行获取结果；4. 使用sqlite3_close()关闭数据库连接；5. 为防止sql注入，应使用参数化查询和sqlite3_bind_*()函数绑定用户输入；6. 处理blob数据时，用sqlite3_bind_blob()插入、sqlite3_column_blob()读取；7. 可通过封装一个c++类来管理数据库连接与操作，提高代码可维护性。
要在C++中操作SQLite数据库，你需要使用SQLite提供的C API，并通过一些封装来简化操作。核心在于理解如何连接数据库、执行SQL语句以及处理查询结果。
解决方案
-
包含头文件： 首先，确保你的C++代码包含了SQLite的头文件。通常是
sqlite3.h。
#include <iostream> #include <sqlite3.h>
-
连接数据库： 使用
sqlite3_open()函数连接到SQLite数据库。如果数据库不存在，该函数会创建一个新的数据库。立即学习“C++免费学习笔记（深入）”；
sqlite3 *db; int rc = sqlite3_open("mydatabase.db", &db); if (rc) { std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl; return -1; } else { std::cout << "Opened database successfully" << std::endl; } -
执行SQL语句： 使用
sqlite3_exec()函数执行SQL语句。这个函数简单直接，但不太适合处理复杂的查询结果。const char *sql = "CREATE TABLE IF NOT EXISTS COMPANY(" \ "ID INT PRIMARY KEY NOT NULL," \ "NAME TEXT NOT NULL," \ "AGE INT NOT NULL," \ "ADDRESS CHAR(50)," \ "SALARY REAL );"; rc = sqlite3_exec(db, sql, 0, 0, 0); if (rc != SQLITE_OK) { std::cerr << "SQL error: " << sqlite3_errmsg(db) << std::endl; sqlite3_close(db); return -1; } else { std::cout << "Table created successfully" << std::endl; } -
处理查询结果： 对于查询操作，通常使用
sqlite3_prepare_v2(),sqlite3_step(), 和sqlite3_finalize()这三个函数。sqlite3_prepare_v2()用于准备SQL语句，sqlite3_step()用于执行语句并逐行获取结果，sqlite3_finalize()用于释放资源。const char *sql_select = "SELECT * FROM COMPANY;"; sqlite3_stmt *stmt; rc = sqlite3_prepare_v2(db, sql_select, -1, &stmt, 0); if (rc != SQLITE_OK) { std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl; sqlite3_close(db); return -1; } while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) { int id = sqlite3_column_int(stmt, 0); const unsigned char *name = sqlite3_column_text(stmt, 1); int age = sqlite3_column_int(stmt, 2); const unsigned char *address = sqlite3_column_text(stmt, 3); double salary = sqlite3_column_double(stmt, 4); std::cout << "ID = " << id << std::endl; std::cout << "Name = " << name << std::endl; std::cout << "Age = " << age << std::endl; std::cout << "Address = " << address << std::endl; std::cout << "Salary = " << salary << std::endl; std::cout << "--------------------" << std::endl; } sqlite3_finalize(stmt); -
关闭数据库连接： 使用
sqlite3_close()函数关闭数据库连接。sqlite3_close(db);
如何避免SQL注入攻击？
使用参数化查询是防止SQL注入的关键。不要直接将用户输入拼接到SQL语句中，而是使用占位符，并通过 sqlite3_bind_*() 函数绑定参数。
const char *sql_insert = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (?, ?, ?, ?, ?);";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql_insert, -1, &stmt, 0);
if (rc != SQLITE_OK) {
std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;
sqlite3_close(db);
return -1;
}
int id = 6;
const char *name = "John Doe";
int age = 30;
const char *address = "Anytown";
double salary = 50000.0;
sqlite3_bind_int(stmt, 1, id);
sqlite3_bind_text(stmt, 2, name, -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, age);
sqlite3_bind_text(stmt, 4, address, -1, SQLITE_STATIC);
sqlite3_bind_double(stmt, 5, salary);
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
std::cerr << "Execution failed: " << sqlite3_errmsg(db) << std::endl;
} else {
std::cout << "Inserted successfully" << std::endl;
}
sqlite3_finalize(stmt);如何处理SQLite数据库中的BLOB数据？
BLOB (Binary Large Object) 用于存储二进制数据，如图像或文档。在C++中，你可以使用 sqlite3_bind_blob() 插入BLOB数据，并使用 sqlite3_column_blob() 和 sqlite3_column_bytes() 读取BLOB数据。
// 插入BLOB数据
FILE *fp = fopen("image.jpg", "rb");
fseek(fp, 0, SEEK_END);
long fsize = ftell(fp);
fseek(fp, 0, SEEK_SET); /* same as rewind(f); */
unsigned char *blob_data = (unsigned char*)malloc(fsize + 1);
fread(blob_data, fsize, 1, fp);
fclose(fp);
const char *sql_insert_blob = "INSERT INTO Images (id, data) VALUES (?, ?);";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql_insert_blob, -1, &stmt, 0);
sqlite3_bind_int(stmt, 1, 1);
sqlite3_bind_blob(stmt, 2, blob_data, fsize, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
free(blob_data);
// 读取BLOB数据
const char *sql_select_blob = "SELECT data FROM Images WHERE id = 1;";
rc = sqlite3_prepare_v2(db, sql_select_blob, -1, &stmt, 0);
if (sqlite3_step(stmt) == SQLITE_ROW) {
const void *blob = sqlite3_column_blob(stmt, 0);
int bytes = sqlite3_column_bytes(stmt, 0);
FILE *fp_out = fopen("image_out.jpg", "wb");
fwrite(blob, 1, bytes, fp_out);
fclose(fp_out);
}
sqlite3_finalize(stmt);如何使用C++封装SQLite操作？
为了简化代码并提高可维护性，可以创建一个C++类来封装SQLite操作。这个类可以处理数据库连接、SQL语句执行和结果处理。
#include <iostream>
#include <sqlite3.h>
#include <string>
#include <stdexcept>
class SQLiteDB {
public:
SQLiteDB(const std::string& db_path) {
int rc = sqlite3_open(db_path.c_str(), &db_);
if (rc) {
throw std::runtime_error("Can't open database: " + std::string(sqlite3_errmsg(db_)));
}
}
~SQLiteDB() {
if (db_) {
sqlite3_close(db_);
}
}
void execute(const std::string& sql) {
char *errMsg = 0;
int rc = sqlite3_exec(db_, sql.c_str(), 0, 0, &errMsg);
if (rc != SQLITE_OK) {
std::string errorMsg = "SQL error: " + std::string(errMsg);
sqlite3_free(errMsg);
throw std::runtime_error(errorMsg);
}
}
sqlite3* getDB() const {
return db_;
}
private:
sqlite3 *db_ = nullptr;
};
int main() {
try {
SQLiteDB db("mydatabase.db");
db.execute("CREATE TABLE IF NOT EXISTS COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);");
db.execute("INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );");
// 使用db.getDB()来执行更复杂的操作
sqlite3_stmt *stmt;
const char *sql_select = "SELECT * FROM COMPANY;";
int rc = sqlite3_prepare_v2(db.getDB(), sql_select, -1, &stmt, 0);
// ... (处理查询结果)
sqlite3_finalize(stmt);
} catch (const std::runtime_error& e) {
std::cerr << "Exception: " << e.what() << std::endl;
return -1;
}
return 0;
} 
