C/C++ 通過SQLiteSDK增刪改查
2023-11-25 21:00:35
SQLite，作為一款嵌入式關係型資料庫管理系統，一直以其輕量級、零設定以及跨平臺等特性而備受青睞。不同於傳統的資料庫系統，SQLite是一個庫，直接與應用程式一同編譯和連結，無需單獨的資料庫伺服器程序，實現了資料庫的零設定管理。這種設計理念使得SQLite成為許多嵌入式系統、移動應用和小型專案中的首選資料庫引擎。
SQLite的特點包括：
- 嵌入式資料庫引擎： SQLite 是一個嵌入式資料庫引擎，意味著它是一個庫，與應用程式一同編譯和連結，而不是作為獨立的程序執行。
- 零設定： 無需設定或管理。SQLite 不需要一個獨立的資料庫伺服器程序，所有的操作都是直接在儲存在檔案中的資料庫上執行。
- 輕量級： SQLite 是一個輕量級的資料庫，相對於一些其他資料庫管理系統來說，它的記憶體佔用和資源消耗相對較小。
- 支援 SQL： SQLite 支援大部分標準的 SQL 語法，並提供了事務支援，包括隔離級別和回滾。
- 跨平臺： SQLite 可以在各種作業系統上執行，包括 Windows、Linux、macOS 和其他嵌入式系統。
- 自給自足： SQLite 資料庫是一個單一的磁碟檔案，整個資料庫被儲存在一個檔案中，這使得備份、複製或傳輸資料庫變得非常容易。
- 開源： SQLite 是一個開源專案，採用公共領域授權（Public Domain License），可以在商業和非商業專案中免費使用。
SQLite 資料庫以其獨特的自給自足特性脫穎而出，整個資料庫被儲存在一個單一的磁碟檔案中，使得備份、複製或傳輸資料庫變得異常簡單。而作為一款開源專案，SQLite採用了公共領域授權，可以在商業和非商業專案中免費使用。
由於該資料庫的小巧和簡潔所以在使用上也非常容易，當讀者下載好附件以後會看到如下圖所示的檔案；
使用時只需要將sqlite3.h與sqlite3.c檔案匯入到專案中並使用#include "sqlite3.h"即可，無需做其他設定，圖中的sqlite3.dll是動態庫，sqlite3.exe則是一個命令列版本的資料庫可在測試時使用它。
開啟與關閉庫
sqlite3_open 用於開啟或建立一個 SQLite 資料庫檔案。該函數的原型如下：
int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
filename: 要開啟或建立的 SQLite 資料庫檔案的路徑。如果檔案不存在，將會建立一個新的資料庫檔案。ppDb: 用於儲存 SQLite 資料庫控制程式碼（handle）的指標。SQLite 資料庫控制程式碼是與一個開啟的資料庫關聯的結構，它在後續的 SQLite 操作中用作標識。
該函數返回一個整數值，代表函數的執行狀態。如果函數成功執行，返回 SQLITE_OK。如果有錯誤發生，返回一個表示錯誤程式碼的整數值。可以通過 sqlite3_errmsg 函數獲取更詳細的錯誤資訊。
sqlite3_close 用於關閉資料庫連線的函數。其原型如下：
int sqlite3_close(sqlite3*);
sqlite3: 要關閉的 SQLite 資料庫連線的控制程式碼。
該函數返回一個整數值，用於表示函數的執行狀態。如果函數成功執行，返回 SQLITE_OK。如果有錯誤發生，返回一個表示錯誤程式碼的整數值。
使用 sqlite3_close 函數可以釋放與資料庫連線相關的資源，並確保資料庫檔案被正確關閉。在關閉資料庫連線之前，應該確保已經完成了所有需要執行的 SQL 語句，並在需要的情況下檢查執行結果。
// 開啟資料庫並返回控制程式碼
sqlite3* open_database(std::string database_name)
{
int ref =-1;
sqlite3 *db = 0;
ref = sqlite3_open(database_name.c_str(), &db);
if (ref == SQLITE_OK)
return db;
return false;
}
// 關閉資料庫
bool close_database(sqlite3 *db)
{
int ref = sqlite3_close(db);
if (ref == SQLITE_OK)
return true;
return false;
}
執行查詢語句
sqlite3_exec 用於執行 SQL 語句的高階介面函數。它的原型如下：
int sqlite3_exec(
sqlite3* db, /* Database handle */
const char* sql, /* SQL statement, UTF-8 encoded */
int (*callback)( /* Callback function */
void*, /* Callback parameter */
int, /* Number of columns in the result set */
char**, /* Array of column values */
char** /* Array of column names */
),
void* callback_param, /* 1st argument to callback function */
char** errmsg /* Error msg written here */
);
db: SQLite 資料庫連線的控制程式碼。sql: 要執行的 SQL 語句，以 UTF-8 編碼。callback: 回撥函數，用於處理查詢結果的每一行資料。callback_param: 傳遞給回撥函數的引數。errmsg: 用於儲存錯誤訊息的指標。
sqlite3_exec 函數執行一個或多個 SQL 語句，並對每一條語句的執行結果呼叫指定的回撥函數。回撥函數的原型如下：
int callback(
void* callback_param, /* 引數，由 sqlite3_exec 傳遞給回撥函數 */
int num_columns, /* 結果集中的列數 */
char** column_values, /* 指向結果集中當前行的列值的陣列 */
char** column_names /* 指向結果集中列名的陣列 */
);
callback_param: 回撥函數的引數，由sqlite3_exec傳遞給回撥函數。num_columns: 結果集中的列數。column_values: 指向結果集中當前行的列值的陣列。column_names: 指向結果集中列名的陣列。
回撥函數返回一個整數，用於指示是否繼續執行後續的 SQL 語句。如果回撥函數返回非零值，sqlite3_exec 將停止執行 SQL，並立即返回。
sqlite3_prepare_v2 用於準備 SQL 語句的介面函數。它的原型如下：
int sqlite3_prepare_v2(
sqlite3* db, /* Database handle */
const char* sql, /* SQL statement, UTF-8 encoded */
int sql_len, /* Length of SQL statement in bytes, or -1 for zero-terminated */
sqlite3_stmt** stmt, /* OUT: Statement handle */
const char** tail /* OUT: Pointer to unused portion of SQL statement */
);
db: SQLite 資料庫連線的控制程式碼。sql: 要準備的 SQL 語句，以 UTF-8 編碼。sql_len: SQL 語句的長度，如果為 -1，則表示 SQL 語句以 null 結尾。stmt: 用於儲存準備好的語句控制程式碼的指標。tail: 用於儲存未使用的 SQL 語句的指標。
sqlite3_prepare_v2 函數用於將 SQL 語句編譯成一個 SQLite 語句物件（prepared statement）。這個物件可以被多次執行，每次執行時可以繫結不同的引數。stmt 引數將用於儲存編譯後的語句的控制程式碼，以供後續的操作。
sqlite3_step 執行預編譯 SQL 語句的介面函數。它的原型如下：
int sqlite3_step(sqlite3_stmt*);
sqlite3_stmt*: 由sqlite3_prepare_v2預編譯的 SQL 語句的控制程式碼。
sqlite3_step 函數用於執行由 sqlite3_prepare_v2 預編譯的 SQL 語句。在執行過程中，可以通過不斷呼叫 sqlite3_step 來逐行獲取查詢結果，直到結果集結束。對於非查詢語句（如 INSERT、UPDATE、DELETE），sqlite3_step 函數執行一次即可完成操作。
該函數的返回值表示執行的結果，可能的返回值包括：
SQLITE_ROW: 成功獲取一行資料。SQLITE_DONE: 執行完成，沒有更多的資料可用（用於非查詢語句）。- 其他錯誤碼，表示執行過程中出現了錯誤。
sqlite3_column_text 用於獲取查詢結果集中某一列的文字值。其原型為：
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
sqlite3_stmt*: 由sqlite3_prepare_v2預編譯的 SQL 語句的控制程式碼。int iCol: 列的索引，從0開始。
該函數返回指向字串值的指標，該字串值是查詢結果集中指定列的文字表示。需要注意的是，返回的指標指向 SQLite 內部的儲存區，應該在使用完之後儘早釋放資源。
sqlite3_column_int 用於獲取查詢結果集中某一列的整數值。其原型為：
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite3_stmt*: 由sqlite3_prepare_v2預編譯的 SQL 語句的控制程式碼。int iCol: 列的索引，從0開始。
該函數返回查詢結果集中指定列的整數表示。需要注意的是，如果該列不是整數型別，或者包含的資料無法轉換為整數，那麼返回的結果可能不是有效的整數值。
sqlite3_finalize 用於釋放一個預備語句物件（prepared statement）。在使用 sqlite3_prepare_v2 函數準備 SQL 語句後，需要使用 sqlite3_finalize 來釋放相應的語句物件。
該函數的原型為：
int sqlite3_finalize(sqlite3_stmt *pStmt);
sqlite3_stmt *pStmt: 指向要釋放的語句物件的指標。
該函數返回 SQLITE_OK 表示成功，返回其他錯誤碼錶示失敗。
// 執行SQL語句
bool exec_sql(sqlite3 *db, char *sql)
{
char *error_code = 0;
int ref = sqlite3_exec(db, sql, 0, 0, &error_code);
if (ref == SQLITE_OK)
{
return true;
}
return false;
}
// 插入資料
bool insert_data(sqlite3 *db, char *sql)
{
sqlite3_stmt *stmt = 0;
// 插入前檢查語句合法性, -1自動計算SQL長度
int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (ref == SQLITE_OK)
{
sqlite3_step(stmt); // 執行語句
sqlite3_finalize(stmt); // 清理語句控制程式碼
return true;
}
sqlite3_finalize(stmt);
return false;
}
// 查詢資料集
bool select_data(sqlite3 *db, char *sql)
{
sqlite3_stmt *stmt = 0;
int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (ref == SQLITE_OK)
{
// 每調一次sqlite3_step()函數，stmt就會指向下一條記錄
while (sqlite3_step(stmt) == SQLITE_ROW)
{
// 取出第1列欄位的值
const unsigned char *name = sqlite3_column_text(stmt, 1);
// 取出第2列欄位的值
int age = sqlite3_column_int(stmt, 2);
std::cout << "姓名: " << name << " 年齡: " << age << std::endl;
}
}
else
{
sqlite3_finalize(stmt);
return false;
}
sqlite3_finalize(stmt);
return true;
}
呼叫查詢語句
建立資料庫
首先開啟了名為 "database.db" 的 SQLite 資料庫，並建立了一個名為 "LySharkDB" 的表，該表格包含了id、name、age 和 msg四個欄位。隨後，通過執行 SQL 語句建立了這個表格。最後，關閉了資料庫連線。這段程式碼主要用於資料庫初始化操作，確保了資料庫中包含了指定的表格結構。
int main(int argc, char *argv[])
{
sqlite3* open_db = open_database("database.db");
if (open_db != false)
{
bool create_table_ref;
std::string sql =
"create table LySharkDB("
"id int auto_increment primary key,"
"name char(30) not null,"
"age int not null,"
"msg text default null"
")";
// 執行建立表操作
char run_sql[1024] = { 0 };
strcpy(run_sql, sql.c_str());
create_table_ref = exec_sql(open_db, run_sql);
}
close_database(open_db);
std::system("pause");
return 0;
}
上述程式碼執行後則可以建立一個資料庫database.db表名為LySharkDB讀者可以使用資料庫工具開啟該表，其結構如下所示；
插入資料測試
建立資料庫後，接著就是插入資料測試，插入時可以使用insert_data，如下程式碼項資料庫中插入5條記錄；
int main(int argc, char *argv[])
{
sqlite3* open_db = open_database("./database.db");
if (open_db != false)
{
bool create_table_ref;
// 執行插入記錄
if (create_table_ref == true)
{
bool insert_ref = 0;
insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(1,'lyshark',1,'hello lyshark');");
insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(2,'guest',2,'hello guest');");
insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(3,'admin',3,'hello admin');");
insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(4,'wang',4,'hello wang');");
insert_ref = insert_data(open_db, "insert into LySharkDB(id,name,age,msg) values(5,'sqlite',5,'hello sql');");
if (insert_ref == true)
{
std::cout << "插入完成" << std::endl;
}
}
}
close_database(open_db);
std::system("pause");
return 0;
}
插入後，開啟資料庫管理軟體，可看到插入後的記錄；
查詢與刪除資料
而查詢刪除與增加記錄，我們這裡直接使用exec_sql()函數，通過傳入不同的SQL語句實現。
int main(int argc, char *argv[])
{
sqlite3* open_db = open_database("./database.db");
if (open_db != false)
{
// 刪除記錄
bool delete_ref = exec_sql(open_db, "delete from LySharkDB where id = 5;");
if (delete_ref == true)
{
std::cout << "刪除完成." << std::endl;
}
// 更新記錄
bool update_ref = exec_sql(open_db, "update LySharkDB set name='lyshark' where id = 4;");
if (update_ref == true)
{
std::cout << "更新完成." << std::endl;
}
// 查詢資料
bool select_ref = select_data(open_db, "select * from LySharkDB;");
if (select_ref == true)
{
std::cout << "查詢完畢." << std::endl;
}
}
close_database(open_db);
std::system("pause");
return 0;
}
執行更新後的表記錄如下所示；
查詢區間資料
首先建立一些資料集，這裡通過迴圈生成並插入資料，如下程式碼中新建一個TimeDB資料表，其中有三個欄位uid,host_time,cpu_value；
#include <iostream>
#include <string>
#include <map>
#include <vector>
#include <time.h>
#include "sqlite3.h"
#include <boost/lexical_cast.hpp>
#include <boost/format.hpp>
using namespace std;
using namespace boost;
// 獲取本地時間日期
std::string get_local_datetime()
{
char ct[80];
time_t tt;
struct tm *tblock;
tt = time(NULL);
tblock = localtime(&tt);
strftime(ct, sizeof(ct), "%Y-%m-%d %H:%M:%S", tblock);
return ct;
}
// 初始化建立表結構
void Init_Database()
{
sqlite3* open_db = open_database("./database.db");
if (open_db != false)
{
bool create_table_ref;
std::string sql =
"create table TimeDB("
"uid primary key,"
"host_time char(128) not null,"
"cpu_value int not null"
");";
char run_sql[1024] = { 0 };
strcpy(run_sql, sql.c_str());
exec_sql(open_db, run_sql);
}
close_database(open_db);
}
// 批次生成時間字串並插入資料表
void Insert_Test()
{
sqlite3* open_db = open_database("./database.db");
for (int x = 0; x < 1000; x++)
{
// 獲取本地日期時間
std::string local_times = get_local_datetime();
std::string format_string = boost::str(boost::format("insert into TimeDB(uid,host_time,cpu_value) values(%d,'%s',%d);") % x %local_times %x);
std::cout << "執行SQL: " << format_string << std::endl;
char run_sql[1024] = { 0 };
strcpy(run_sql, format_string.c_str());
insert_data(open_db, run_sql);
_sleep(1000);
}
close_database(open_db);
}
int main(int argc, char *argv[])
{
sqlite3* open_db = open_database("./database.db");
Init_Database();
Insert_Test();
std::system("pause");
return 0;
}
如下是五分鐘的模擬資料；
當有了資料則再查詢，程式碼中Select_Time_List函數演示瞭如何通過時間查詢一個區間的資料，並返回一個容器列表給被呼叫者使用，查詢程式碼如下所示；
#include <iostream>
#include <string>
#include <map>
#include <vector>
#include <time.h>
#include "sqlite3.h"
#include <boost/lexical_cast.hpp>
#include <boost/format.hpp>
using namespace std;
using namespace boost;
// 開啟資料庫並返回控制程式碼
sqlite3* open_database(std::string database_name)
{
int ref = -1;
sqlite3 *db = 0;
ref = sqlite3_open(database_name.c_str(), &db);
if (ref == SQLITE_OK)
return db;
return false;
}
// 關閉資料庫
bool close_database(sqlite3 *db)
{
int ref = sqlite3_close(db);
if (ref == SQLITE_OK)
return true;
return false;
}
// 執行SQL語句
bool exec_sql(sqlite3 *db, char *sql)
{
char *error_code = 0;
int ref = sqlite3_exec(db, sql, 0, 0, &error_code);
if (ref == SQLITE_OK)
{
return true;
}
return false;
}
// 插入資料
bool insert_data(sqlite3 *db, char *sql)
{
sqlite3_stmt *stmt = 0;
// 插入前檢查語句合法性, -1自動計算SQL長度
int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (ref == SQLITE_OK)
{
sqlite3_step(stmt); // 執行語句
sqlite3_finalize(stmt); // 清理語句控制程式碼
return true;
}
sqlite3_finalize(stmt);
return false;
}
// 查詢資料集
bool select_data(sqlite3 *db, char *sql)
{
sqlite3_stmt *stmt = 0;
int ref = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (ref == SQLITE_OK)
{
// 每調一次sqlite3_step()函數，stmt就會指向下一條記錄
while (sqlite3_step(stmt) == SQLITE_ROW)
{
// 取出第1列欄位的值
const unsigned char *name = sqlite3_column_text(stmt, 1);
// 取出第2列欄位的值
int age = sqlite3_column_int(stmt, 2);
std::cout << "姓名: " << name << " 年齡: " << age << std::endl;
}
}
else
{
sqlite3_finalize(stmt);
return false;
}
sqlite3_finalize(stmt);
return true;
}
// 獲取本地時間日期
std::string get_local_datetime()
{
char ct[80];
time_t tt;
struct tm *tblock;
tt = time(NULL);
tblock = localtime(&tt);
strftime(ct, sizeof(ct), "%Y-%m-%d %H:%M:%S", tblock);
return ct;
}
// 初始化建立表結構
void Init_Database()
{
sqlite3* open_db = open_database("./database.db");
if (open_db != false)
{
bool create_table_ref;
std::string sql =
"create table TimeDB("
"uid primary key,"
"host_time char(128) not null,"
"cpu_value int not null"
");";
char run_sql[1024] = { 0 };
strcpy(run_sql, sql.c_str());
exec_sql(open_db, run_sql);
}
close_database(open_db);
}
// 批次生成時間字串並插入資料表
void Insert_Test()
{
sqlite3* open_db = open_database("./database.db");
for (int x = 0; x < 1000; x++)
{
// 獲取本地日期時間
std::string local_times = get_local_datetime();
std::string format_string = boost::str(boost::format("insert into TimeDB(uid,host_time,cpu_value) values(%d,'%s',%d);") % x %local_times %x);
std::cout << "執行SQL: " << format_string << std::endl;
char run_sql[1024] = { 0 };
strcpy(run_sql, format_string.c_str());
insert_data(open_db, run_sql);
_sleep(1000);
}
close_database(open_db);
}
// 查詢時間區間並返回 傳入開始時間與結束時間,過濾出特定的記錄
bool Select_Time_List(sqlite3 *db, std::vector<std::map<std::string, int>> &time_ref, std::string start_time, std::string end_time)
{
sqlite3_stmt *stmt = 0;
std::string format_string = boost::str(boost::format("select * from TimeDB where host_time >= '%s' and host_time <= '%s';") % start_time %end_time);
char run_sql[1024] = { 0 };
strcpy(run_sql, format_string.c_str());
int ref = sqlite3_prepare_v2(db, run_sql, -1, &stmt, 0);
if (ref == SQLITE_OK)
{
while (sqlite3_step(stmt) == SQLITE_ROW)
{
std::map < std::string, int > ptr;
// 取出第一個和第二個欄位
const unsigned char *time_text = sqlite3_column_text(stmt, 1);
const int cpu_value = sqlite3_column_int(stmt, 2);
// 放入一個map容器中
ptr[boost::lexical_cast<std::string>(time_text)] = cpu_value;
time_ref.push_back(ptr);
}
sqlite3_finalize(stmt);
return true;
}
sqlite3_finalize(stmt);
return false;
}
int main(int argc, char *argv[])
{
sqlite3* open_db = open_database("./database.db");
//Init_Database();
//Insert_Test();
// 查詢 2023-11-25 19:52:31 - 2023-11-25 19:53:35 區間內的所有的負載情況
std::vector<std::map<std::string, int>> db_time;
bool is_true = Select_Time_List(open_db, db_time, "2023-11-25 19:52:31", "2023-11-25 19:53:35");
if (is_true == true)
{
for (int x = 0; x < db_time.size(); x++)
{
// 輸出該區間內的資料
std::map < std::string, int>::iterator ptr;
for (ptr = db_time[x].begin(); ptr != db_time[x].end(); ptr++)
{
std::cout << "時間區間: " << ptr->first << " CPU利用率: " << ptr->second << std::endl;
}
}
}
std::system("pause");
return 0;
}
例如程式碼中我們查詢2023-11-25 19:52:31 - 2023-11-25 19:53:35這個區間內的資料資訊，並返回一個map容器給被呼叫者，執行效果如下所示；
