要在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#include 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; }
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
