事实上对于操作sqlite的其他语言，写一个统一的数据库操作模型是非常容易的，比如java，c#，这些语言支持垃圾回收，支持异常捕获，支持泛型，写起来就很容易。但是对于C语言，就得另当别论了，就拿查询操作来说，c语言没有泛型，不能返回统一的泛型列表，只能返回数据模型的链表结构。

但是得益于前面讲过的通用链表)结构,我们可以尽可能的像其他语言一样封装一个通用的数据库操作模型。

回顾前面讲到的sqlite操作基本流程，查询流程是
1.打开数据库
2.准备好SQL语句。
3.绑定SQL语句参数
4.执行SQL语句
5.返回结果集，执行串链操作。
6.释放资源
7.关闭数据库句柄.
整体的流程不变，把不一样的部分抽象出来 —— 绑定参数，执行串链的过程抽象出来。

绑定参数，如

int bind_userinfo_t(sqlite3_stmt * stmt,int index, void * arg )

执行查询建链过程，如

int create_userinfo_T(sqlite3_stmt,void * arg)

然后在通用的数据库操作函数中传入函数指针，尽量的精简代码。
下面是的dbhelper.h 和 dbhelper.c 抽象的一般数据库操作函数。

#ifndef _DBHELPER_H_#define _DBHELPER_H_#include <sqlite3.h> #define DB_SQL_MAX_LEN 1024//执行没有返回的SQL语句int db_nonquery_operator(const char *sqlstr,int (*bind)(sqlite3_stmt *,int index,void * arg),void *param);//执行没有返回的SQL语句的多值传参int db_nonquery_by_varpara(const char *sql,const char *fmt,...);//执行没有返回的SQL语句int db_nonquery_transaction(int (*exec_sqls)(sqlite3 *db,void * arg),void *arg);//执行多值传参的查询语句int db_query_by_varpara(const char *sql,int (*create)(sqlite3_stmt *stmt,void *arg),void *arg,const char *fmt,...);//执行查询并返回查询结果集的条数int db_query_count_result(const char *sql);//对sqlite3_column_blob的二次封装int db_stmt_get_blob(sqlite3_stmt *stmt,int index,unsigned char *out);//对sqlite3_column_text的二次封装int db_stmt_get_text(sqlite3_stmt *stmt,int index,char *out);//对sqlite3_column_int的二次封装int db_stmt_get_int(sqlite3_stmt *stmt,int index);//对sqlite3_column_double的二次封装double db_stmt_get_double(sqlite3_stmt *stmt,int index);#endif

dbhelper.c:

#include <stdio.h>#include <string.h>#include <pthread.h>#include "na_queue.h"#include "dbhelper.h"#define DB_NAME "test.db"static pthread_mutex_t db_mutex_lock = PTHREAD_MUTEX_INITIALIZER;static int db_bind_by_var(sqlite3_stmt *stmt,const char *fmt,va_list args){    int len,npara=1;    int ret = SQLITE_OK;    if(fmt == NULL){        return ret;    }       for(;*fmt;++fmt){        if(*fmt != ‘%‘)            continue;        ++fmt;        //get length        len = 0;        while(isdigit(*fmt)){            len = len * 10 + (*fmt - ‘0‘);            ++fmt;        }        switch(*fmt){        case ‘d‘:            ret = sqlite3_bind_int(stmt,npara,va_arg(args,int));            break;        case ‘s‘:            {            char *str = va_arg(args,char *);            ret = sqlite3_bind_text(stmt,npara,str,strlen(str),NULL);            }            break;        case ‘x‘:            {            unsigned char *pdata;            pdata = http://www.mamicode.com/va_arg(args,char *);            ret = sqlite3_bind_blob(stmt,npara,pdata,len,NULL);        //    printf_phex("blob",pdata,len);            }            break;        default:            ret = SQLITE_ERROR;            break;        }        ++npara;        if(ret)            return ret;    }        return ret;}/*  * ===  FUNCTION  ====================================================================== *         Name:  db_query_by_varpara *  Description:  数据库查询操作，可以带变参绑定 *  @sql       :  sql  *  @create    :  取得数据并创建节点 *  @arg       :  用户用于create的参数 *  @fmt       :  格式字符串，%s string,%d int,%nx 长度为N的二进制串 *  ...        :  变参 *  Return     :  查询到数据的条数  * ===================================================================================== */int db_query_by_varpara(const char *sql,int (*create)(sqlite3_stmt *stmt,void *arg),void *arg,const char *fmt,...){    sqlite3 *db = NULL;    sqlite3_stmt *stmt = NULL;    if(sql == NULL){//        return SQLITE_ERROR;        return 0;    }    pthread_mutex_lock(&db_mutex_lock);    int rc = sqlite3_open(DB_NAME,&db);    if(rc != SQLITE_OK){        printf("open database failed,rc=%d",rc);        pthread_mutex_unlock(&db_mutex_lock);        return 0;    }    rc = sqlite3_prepare(db,sql,-1,&stmt,NULL);    if(rc != SQLITE_OK){        printf("database prepare fail,rc=%d",rc);        goto DB_EXEC_FAIL;    }    if(fmt){        va_list args;        va_start(args,fmt);        rc = db_bind_by_var(stmt,fmt,args);        va_end(args);        if(rc){            printf("database bind fail,rc=%d",rc);            goto DB_EXEC_FAIL;        }    }    if(create){        rc = (*create)(stmt,arg);    }else{        rc = (sqlite3_step(stmt),0);    }    sqlite3_finalize(stmt);    goto DB_EXEC_OK;DB_EXEC_FAIL:    printf("db operator failed,rc=%d",rc);    rc = 0;DB_EXEC_OK:    sqlite3_close(db);    pthread_mutex_unlock(&db_mutex_lock);    return rc;}/*  * ===  FUNCTION  ====================================================================== *         Name:  db_exec_noquery *  Description:  执行非查询操作  *  @sqlstr    :  sql,多条语句由‘;‘分开 *  @bind      :  绑定sql中的未知变量操作  *  @param     :  绑定中的参数 *  @bind.index:  sql语句序号  *  Return     :  0 or error * ===================================================================================== */int db_nonquery_operator(const char *sqlstr,int (*bind)(sqlite3_stmt *,int index,void * arg),void *param){    sqlite3 *db = NULL;    sqlite3_stmt *stmt = NULL;//    char *emsg = NULL;    if(sqlstr == NULL){        return SQLITE_ERROR;    }    pthread_mutex_lock(&db_mutex_lock);    int rc = sqlite3_open(DB_NAME,&db);    if(rc != SQLITE_OK){        printf("open database failed,rc=%d",rc);        pthread_mutex_unlock(&db_mutex_lock);        return rc;    }    rc = sqlite3_exec(db,"begin transaction",0,0,NULL);    if(rc != SQLITE_OK){        printf("begin transaction:ret=%d",rc);        goto DB_BEGIN_FAIL;    }    char sql[DB_SQL_MAX_LEN];    int index = 0,offset=0,n=0;    while(sqlstr[index] != 0){        offset = 0;        do{            if(offset >= DB_SQL_MAX_LEN){                printf("sql is too long,(%d)",offset);                rc = SQLITE_ERROR;                goto DB_EXEC_FAIL;            }            if((sqlstr[index] != ‘;‘) && (sqlstr[index] != 0)){                sql[offset++] = sqlstr[index++];            }else{                sql[offset] = ‘\0‘;                if(sqlstr[index] == ‘;‘) index++;                n++;                break;            }        }while(1);        printf("sql:%s",sql);        rc = sqlite3_prepare(db,sql,-1,&stmt,NULL);        if(rc != SQLITE_OK){            printf("prepare,error,%d",rc);            goto DB_EXEC_FAIL;        }        if(bind){            rc = (*bind)(stmt,n,param);        }else{            rc = sqlite3_step(stmt);        }        sqlite3_finalize(stmt);        if((rc != SQLITE_OK) && (rc != SQLITE_DONE)){            printf("bind");            goto DB_EXEC_FAIL;        }    }    rc = sqlite3_exec(db,"commit transaction",0,0,NULL);    if(rc){        printf("commit transaction:%d",rc);        goto DB_EXEC_FAIL;    }    goto DB_EXEC_OK;DB_EXEC_FAIL:    if(sqlite3_exec(db,"rollback transaction",0,0,NULL)){        printf("rollback transaction error");    }DB_BEGIN_FAIL://    sqlite3_free(emsg);    printf("db operator failed,rc=%d",rc);DB_EXEC_OK:    sqlite3_close(db);    pthread_mutex_unlock(&db_mutex_lock);    return rc;}int db_nonquery_by_varpara(const char *sql,const char *fmt,...){    sqlite3 *db = NULL;    sqlite3_stmt *stmt = NULL;    if(sql == NULL){        return SQLITE_ERROR;    }    pthread_mutex_lock(&db_mutex_lock);    int rc = sqlite3_open(DB_NAME,&db);    if(rc != SQLITE_OK){        printf("open database failed,rc=%d",rc);        pthread_mutex_unlock(&db_mutex_lock);        return rc;    }    printf("sql:%s",sql);    rc = sqlite3_prepare(db,sql,-1,&stmt,NULL);    if(rc != SQLITE_OK){        printf("prepare,");        goto DB_EXEC_FAIL;    }    if(fmt){        va_list args;        va_start(args,fmt);        rc = db_bind_by_var(stmt,fmt,args);        va_end(args);        if(rc){            goto DB_EXEC_FAIL;        }    }    rc = sqlite3_step(stmt);    sqlite3_finalize(stmt);    if((rc != SQLITE_OK) && (rc != SQLITE_DONE)){        printf("bind");        goto DB_EXEC_FAIL;    }    rc = 0;    goto DB_EXEC_OK;DB_EXEC_FAIL:DB_BEGIN_FAIL:    printf("db operator failed,rc=%d",rc);DB_EXEC_OK:    sqlite3_close(db);    pthread_mutex_unlock(&db_mutex_lock);    return rc;}int db_nonquery_transaction(int (*exec_sqls)(sqlite3 *db,void * arg),void *arg){    sqlite3 *db = NULL;    sqlite3_stmt *stmt = NULL;//    char *emsg = NULL;    pthread_mutex_lock(&db_mutex_lock);    int rc = sqlite3_open(DB_NAME,&db);    if(rc != SQLITE_OK){        printf("open database failed,rc=%d",rc);        pthread_mutex_unlock(&db_mutex_lock);        return rc;    }    rc = sqlite3_exec(db,"begin transaction",0,0,NULL);    if(rc != SQLITE_OK){        printf("begin transaction:%d",rc);        goto DB_BEGIN_FAIL;    }    if(exec_sqls){        rc = (*exec_sqls)(db,arg);    }else{        rc = SQLITE_ERROR;    }    if((rc != SQLITE_OK) && (rc != SQLITE_DONE)){        printf("prepare,error,%d",rc);        goto DB_EXEC_FAIL;    }    rc = sqlite3_exec(db,"commit transaction",0,0,NULL);    if(rc){        printf("commit transaction:%d",rc);        goto DB_EXEC_FAIL;    }    goto DB_EXEC_OK;DB_EXEC_FAIL:    if(sqlite3_exec(db,"rollback transaction",0,0,NULL)){        printf("rollback transaction:error");    }DB_BEGIN_FAIL://    sqlite3_free(emsg);    printf("db operator failed,rc=%d",rc);DB_EXEC_OK:    sqlite3_close(db);    pthread_mutex_unlock(&db_mutex_lock);    return rc;}static int db_get_count(sqlite3_stmt *stmt,void *arg){    int ret,*count=arg;    ret = sqlite3_step(stmt);    if(ret != SQLITE_ROW)        return SQLITE_EMPTY;    *count = db_stmt_get_int(stmt,0);    return SQLITE_OK;}/*  * ===  FUNCTION  ====================================================================== *         Name:  db_query_count_result *  Description:  查询计数结果的第一列第一行，其它忽略 *  @sql       :  查询计数的SQL语句 *  Return     :  查询到计数返回计数，否则一律返回0  * ===================================================================================== */int db_query_count_result(const char *sql){    int ret,count=0;    ret = db_query_by_varpara(sql,db_get_count,&count,NULL);    if(ret == SQLITE_OK)        return count;    return 0;}int db_stmt_get_blob(sqlite3_stmt *stmt,int index,unsigned char *out){    const char *pdata = http://www.mamicode.com/sqlite3_column_blob(stmt,index);"hljs-keyword">int len = sqlite3_column_bytes(stmt,index);    if(pdata){        memcpy(out,pdata,len);        return len;    }    return 0;}int db_stmt_get_text(sqlite3_stmt *stmt,int index,char *out){    const unsigned char *pdata = http://www.mamicode.com/sqlite3_column_text(stmt,index);"hljs-keyword">if(pdata){        int len = strlen(pdata);        strncpy(out,pdata,len);        return len;    }    return 0;}int db_stmt_get_int(sqlite3_stmt *stmt,int index){    return sqlite3_column_int(stmt,index);}double db_stmt_get_double(sqlite3_stmt *stmt,int index){    return sqlite3_column_double(stmt,index);}

对以上的代码做一下说明:
test.db为数据库文件
为了处理多线程，在对数据库进行操作的时候加了锁。

现在按照通用数据库操作方式重写上次的代码，查询和增加操作。

首先重写数据模型：

typedef struct userinfo_s{    int userid;    char username[32];    na_queue_t queue;}userinfo_t;

释放函数链表函数：

 void free_userinfo_t(na_queue_t *h){    na_queue_t *head = h,*pos,*n;    userinfo_t *p = NULL;    na_queue_for_each_safe(pos,n,head){        p = na_queue_data(pos,userinfo_t,queue);        free(p);    }    na_queue_init(head); }

绑定函数，用于绑定insert的插入函数

int bind_userinfo_t(sqlite3_stmt * stmt,int index,void * arg){        int rc;        userinfo_t * param = arg;        sqlite3_bind_int(stmt,1,param->userid);        sqlite3_bind_text(stmt,2,param->username,strlen(param->username),NULL);        rc = sqlite3_step(stmt);        if (rc != SQLITE_DONE)                return rc;        return SQLITE_OK;}

查询操作需要的串链函数：

int create_userinfo_t(sqlite3_stmt * stmt,void * arg){        na_queue_t * h = arg;        na_queue_init(h);        userinfo_t * u;        int ret,count = 0;        ret = sqlite3_step(stmt);        if(ret != SQLITE_ROW){                return 0;        }        do        {                u = calloc(sizeof(userinfo_t),1);                if(!u){                        goto CREATE_USERINFO_FAIL;                }                u->userid = db_stmt_get_int(stmt,0);                db_stmt_get_text(stmt,1,u->username);                na_queue_insert_tail(h,&(u->queue));                count ++;        } while ((ret = sqlite3_step(stmt)) == SQLITE_ROW);        return count;CREATE_USERINFO_FAIL:        free_userinfo_t(h);        return 0;}

基本的对数据表的操作就是按照这种方式来抽象代码,现在我们来按照这种方式来写一段测试代码:

//打印用户信息void printusers(na_queue_t *h){        userinfo_t * q=NULL;        na_queue_foreach(q,h,userinfo_t,queue){                printf("userid:%d username:%s\n",q->userid,q->username);        }}//得到所以用户信息int get_all_userinfo(na_queue_t * h){        return db_query_by_varpara("select * from userinfo;",create_userinfo_t,h,NULL);}//添加一个用户信息int add_a_userinfo(userinfo_t * u){        return db_nonquery_operator("insert into userinfo(userid,username) values (?,?)",bind_userinfo_t,u);}int main(int argc, char *argv[]){        printf("test get all userinfo\n");        na_queue_t h;        int ret = get_all_userinfo(&h);        printusers(&h);        free_userinfo_t(&h);        printf("test add userinfo");        userinfo_t newuser;        newuser.userid = 7;        strncpy(newuser.username,"White",10);        add_a_userinfo(&newuser);        get_all_userinfo(&h);        printusers(&h);        free_userinfo_t(&h);        return 0;}

我们还是上一次的数据库来接着运行，输出：

test get all userinfo
userid:1 username:Alex
userid:2 username:Neo
userid:3 username:Allan
userid:4 username:coby
userid:5 username:micheal
test add userinfo
userid:1 username:Alex
userid:2 username:Neo
userid:3 username:Allan
userid:4 username:coby
userid:5 username:micheal
userid:7 username:White

以上就是一个简单的C语言对SQLITE数据库操作的封装，事实上我们已经把它用在了我们的生产环境，不过生产环境版本相对于这个版本要复杂一点，加入的日志模块，线程模块，而且现在还在继续进化中，但是原理就是这样了，实现上大同小异而已。实例也同样只是写了查询和插入，至于其他操作就有读者自己在这个实例上去添加了。SQLITE小型数据库相对与其他Oracle等其他大型数据库而言，他对SQL的支持没有他们强大，但是对于小型嵌入式系统已经足够了，他们之间的差异只有改天再补充了。今天就到这了。

generated by haroopad

sqlite嵌入式数据库C语言基本操作(2)