C语言使用 sqlite数据库

sqlite 导入 使用conan 一行代码就可以

sqlite3/3.32.3

具体使用
创建基本的增删改查

#ifndef BASICC_DOWNLOADER_SQLITE_MANAGER_H_
#define BASICC_DOWNLOADER_SQLITE_MANAGER_H_#include "basics.h"
#include 
#include #define MAX_SQL_LENGTH
// 用来解析数据的  做一些赋值工作   具体操作由具体  具体数据库操作决定
typedef void (*DataRetriever)(void *, sqlite3_stmt *);typedef struct QuerySingleResult {size_t element_size;DataRetriever data_retriever;void *data;
} QuerySingleResult;typedef struct QueryManyResult {size_t element_size;DataRetriever data_retriever;GPtrArray *array;
} QueryManyResult;int OpenDataBase(char *path);/*** @param sql_format update task_info set status = $d where id = $d* @param ...  status, id* @return*/
int ExecuteSql(char *sql_format, ...);/*** @param sql_format select * from task_info where id = $d and status = $d* @param ...  id, status* @return*/
int QuerySingle(QuerySingleResult *query_single_result, char *sql_format, ...);int QueryMany(QueryManyResult *query_result, char *sql_format, ...);sqlite3_int64 GetLastInsertRowId();void CloseDataBase();#endif //BASICC_DOWNLOADER_SQLITE_MANAGER_H_

每个数据库有每个数据库不同的信息 不同的常用操作

#ifndef BASICC_DOWNLOADER_TASK_INFO_H_
#define BASICC_DOWNLOADER_TASK_INFO_H_#include 
#include typedef struct sqlite3_stmt sqlite3_stmt;#define STATUS_REMOVING -1
#define STATUS_ERROR 0
#define STATUS_READY  1
#define STATUS_PAUSED 2
#define STATUS_DOWNLOADING 3
#define STATUS_COMPLETED 4#define STATUS_READY_TEXT "Ready" //0
#define STATUS_PAUSED_TEXT "Paused" //1
#define STATUS_DOWNLOADING_TEXT "Downloading" //2
#define STATUS_COMPLETED_TEXT "Completed" //3
#define STATUS_ERROR_TEXT "Error" //4#define INVALID_ID -1
// 数据库内结构体的映射
typedef struct {gint64 id;char *filename;char *directory;char *url;gint64 size;guint64 progress;guint status;char *create_time;gboolean resume_support;
} TaskInfo;void TaskInfoDump(TaskInfo *task_info);void DestroyTaskInfoContent(TaskInfo *task_info);void DestroyTaskInfo(TaskInfo **task_info);int InsertTaskInfo(TaskInfo *task_info);void UpdateTaskInfo(TaskInfo *task_info);void DeleteTaskInfo(TaskInfo *task_info);TaskInfo *FindTaskInfoById(sqlite3_int64 id);GPtrArray *ListTaskInfos();#endif //BASICC_DOWNLOADER_TASK_INFO_H_

具体操作的实现

#include "common/sqlite_manager.h"
#include "utils/io_utils.h"
#include 
#include 
#include static sqlite3 *db;static int Sqlite3PrepareWithParameters(sqlite3_stmt **p_stmt, char const *sql_format, va_list args) {char *sql = strdup(sql_format);int sql_length = strlen(sql);//获取参数的类型char parameter_types[20];int parameter_type_count = 0;for (int i = 0; i < sql_length; ++i) {if (sql[i] == '$') {if(sql[i + 1] != '$') {parameter_types[parameter_type_count++] = sql[i + 1];sql[i] = '?';}sql[i + 1] = ' ';}}PRINTLNF("DB Execute: %s", sql);//解析第二步int err = sqlite3_prepare_v2(db, sql, -1, p_stmt, NULL);free(sql);if (err != SQLITE_OK) {PRINTLNF("prepare failed: %s", sqlite3_errmsg(db));return RESULT_FAILURE;}//读取参数 绑定进去for (int i = 0; i < parameter_type_count; ++i) {switch (parameter_types[i]) {case 'c': {char arg = va_arg(args, int);char parameter_holder[2] = {arg};sqlite3_bind_text(*p_stmt, i + 1, parameter_holder, -1, NULL);break;}case 's': {sqlite3_bind_text(*p_stmt, i + 1, va_arg(args, char *), -1, NULL);break;}case 'd': {int arg = va_arg(args, int);sqlite3_bind_int64(*p_stmt, i + 1, arg);break;}case 'f': {sqlite3_bind_double(*p_stmt, i + 1, va_arg(args, double));break;}default: {PRINTLNF("Unsupported parameter type: %c.", parameter_types[i]);exit(-1);}}}return RESULT_OK;
}int OpenDataBase(char *path) {if (sqlite3_open(path, &db) != SQLITE_OK) {PRINTLNF("Can't open database: %s", sqlite3_errmsg(db));CloseDataBase();return RESULT_FAILURE;}return RESULT_OK;
}int ExecuteSql(char *sql_format, ...) {if (db) {int result;sqlite3_stmt *stmt;va_list args;va_start(args, sql_format);if (Sqlite3PrepareWithParameters(&stmt, sql_format, args) == RESULT_OK) {if (sqlite3_step(stmt) != SQLITE_DONE) {PRINTLNF("execution failed: %s", sqlite3_errmsg(db));result = RESULT_FAILURE;} else {result = RESULT_OK;}sqlite3_finalize(stmt);} else {result = RESULT_FAILURE;}va_end(args);return result;} else {return RESULT_FAILURE;}
}int QuerySingle(QuerySingleResult *query_single_result, char *sql_format, ...) {if (db) {int result;sqlite3_stmt *stmt;va_list args;va_start(args, sql_format);if (Sqlite3PrepareWithParameters(&stmt, sql_format, args) == RESULT_OK) {if (sqlite3_step(stmt) == SQLITE_ROW) {//查询的时候需要绑定// 在C语言查询的时候 不知道返回类型多大  因为要开辟空间 所以这里也是要传值 回调后开辟空间的query_single_result->data = malloc(query_single_result->element_size);query_single_result->data_retriever(query_single_result->data, stmt);result = RESULT_OK;} else {PRINTLNF("execution failed: %s", sqlite3_errmsg(db));result = RESULT_FAILURE;}sqlite3_finalize(stmt);} else {result = RESULT_FAILURE;}va_end(args);return result;} else {return RESULT_FAILURE;}
}int QueryMany(QueryManyResult *query_result, char *sql_format, ...) {if (db) {int result;sqlite3_stmt *stmt;va_list args;va_start(args, sql_format);if (Sqlite3PrepareWithParameters(&stmt, sql_format, args) == RESULT_OK) {if (!query_result->array) {query_result->array = g_ptr_array_new_with_free_func(free);}while (1) {int sql_result = sqlite3_step(stmt);if (sql_result == SQLITE_ROW) {void *data = malloc(query_result->element_size);g_ptr_array_add(query_result->array, data);query_result->data_retriever(data, stmt);} else if(sql_result == SQLITE_ERROR){PRINTLNF("execution failed: %s", sqlite3_errmsg(db));result = RESULT_FAILURE;break;} else {result = RESULT_OK;break;}}sqlite3_finalize(stmt);} else {result  = RESULT_FAILURE;}va_end(args);return result;}return RESULT_FAILURE;
}sqlite3_int64 GetLastInsertRowId() {return sqlite3_last_insert_rowid(db);
}void CloseDataBase() {if (db) {sqlite3_close(db);db = NULL;}
}

调用复现

#include "model/task_info.h"
#include "common/sqlite_manager.h"
#include 
#include 
#include 
#include "utils/io_utils.h"
//DataRetriever 对于TaskInfo 数据库中得出的信息进行映射  也就是指向吧  （指针函数 和接口 ）
static void TaskInfoRetriever(TaskInfo *task_info, sqlite3_stmt *result) {task_info->id = sqlite3_column_int64(result, 0);task_info->url = strdup((char *) sqlite3_column_text(result, 1));task_info->filename = strdup((char *) sqlite3_column_text(result, 2));task_info->directory = strdup((char *) sqlite3_column_text(result, 3));task_info->size = sqlite3_column_int64(result, 4);task_info->progress = sqlite3_column_int64(result, 5);task_info->status = sqlite3_column_int(result, 6);task_info->resume_support = sqlite3_column_int(result, 7);task_info->create_time = strdup((char *) sqlite3_column_text(result, 8));
}int InsertTaskInfo(TaskInfo *task_info) {//这里还得传类型 不能直接问好所以传递 $+类型 以供后面判断读取可变长参数int result = ExecuteSql("insert into task_info(filename, directory, url, size, progress, status, resume_support) ""values($s, $s, $s, $d, $d, $d, $d)",task_info->filename,task_info->directory,task_info->url,task_info->size,task_info->progress,task_info->status,task_info->resume_support);if (result == RESULT_OK) {//对于默认值进行操作TaskInfo *task_info_from_db = FindTaskInfoById(GetLastInsertRowId());//返回数据库生成的默认值//复制了一份放在返回的结构体中task_info->create_time = strdup(task_info_from_db->create_time);task_info->id = task_info_from_db->id;DestroyTaskInfo(&task_info_from_db);}return result;
}void UpdateTaskInfo(TaskInfo *task_info) {ExecuteSql("update task_info set progress=$d, status=$d where id=$d",task_info->progress,task_info->status,task_info->id);
}void DeleteTaskInfo(TaskInfo *task_info) {ExecuteSql("delete from task_info where id=$d", task_info->id);task_info->id = INVALID_ID;
}TaskInfo *FindTaskInfoById(sqlite3_int64 id) {QuerySingleResult query_single_result = {// 在调用的时候传入 要开辟空间的大小.element_size = sizeof(TaskInfo),.data_retriever = (DataRetriever)TaskInfoRetriever};if (QuerySingle(&query_single_result, "select * from task_info where id=$d", id) == RESULT_OK) {return query_single_result.data;}return NULL;
}GPtrArray *ListTaskInfos() {QueryManyResult query_many_result = {.element_size = sizeof(TaskInfo),.data_retriever = (DataRetriever)TaskInfoRetriever};QueryMany(&query_many_result, "select * from task_info");return query_many_result.array;
}void DestroyTaskInfoContent(TaskInfo *task_info) {if (task_info) {free(task_info->filename);free(task_info->directory);free(task_info->url);free(task_info->create_time);memset(task_info, 0, sizeof(TaskInfo));}
}void DestroyTaskInfo(TaskInfo **task_info) {if (task_info && *task_info) {free((*task_info)->filename);free((*task_info)->directory);free((*task_info)->url);free((*task_info)->create_time);free(*task_info);*task_info = NULL;}
}void TaskInfoDump(TaskInfo *task_info) {PRINTLNF("TaskInfo(\n""            id=%lld,\n""            filename=%s,\n""            directory=%s,\n""            url=%s,\n""            size=%lld,\n""            progress=%lld,\n""            status=%d,\n""            resume_support=%d,\n""            create_time=%s\n""         )",task_info->id,task_info->filename,task_info->directory,task_info->url,task_info->size,task_info->progress,task_info->status,task_info->resume_support,task_info->create_time);
}

学习到点 或者说技术点
1 因为C语言开辟空间需要具体的malloc sizeof 不同数据库 查询结果所需要内存的大小不一样 所以在设计 查询结果的时候 对于结构体设定 给定一个element_size = sizeof(TaskInfo), 谁调用 那就开辟谁的大小。 同样不同数据库操作不一样 故而也使用了回调 谁调用传入 谁的操作一样 具体操作由对应数据库控制

代码如图

typedef struct QuerySingleResult {size_t element_size;DataRetriever data_retriever;void *data;
} QuerySingleResult;QuerySingleResult query_single_result = {// 在调用的时候传入 要开辟空间的大小.element_size = sizeof(TaskInfo),.data_retriever = (DataRetriever)TaskInfoRetriever};static void TaskInfoRetriever(TaskInfo *task_info, sqlite3_stmt *result) {task_info->id = sqlite3_column_int64(result, 0);task_info->url = strdup((char *) sqlite3_column_text(result, 1));task_info->filename = strdup((char *) sqlite3_column_text(result, 2));task_info->directory = strdup((char *) sqlite3_column_text(result, 3));task_info->size = sqlite3_column_int64(result, 4);task_info->progress = sqlite3_column_int64(result, 5);task_info->status = sqlite3_column_int(result, 6);task_info->resume_support = sqlite3_column_int(result, 7);task_info->create_time = strdup((char *) sqlite3_column_text(result, 8));
}

具体调用事宜由通用封装决定

完美的函数式编程思路

就像java function对函数进行定义一样

但是java 对象形式的编程 直接这样函数式编程就很绕

2 关于数据库查询 传递可变长参数的时候 由于C的可变长参数对于类型没有通用所以在 传递类型 后获取类型 然后赋值 在吧传递参数地方的符号改为？

  int result = ExecuteSql("insert into task_info(filename, directory, url, size, progress, status, resume_support) ""values($s, $s, $s, $d, $d, $d, $d)",task_info->filename,task_info->directory,task_info->url,task_info->size,task_info->progress,task_info->status,task_info->resume_support);

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！