Sqlite3 stmt 机制操作

 

参考文献：https://blog.csdn.net/u012351051/category_8966045.html

        sqlite 提供了SQL语句执行的函数sqlite3_exec，本文介绍的是sqlite3_stmt方式对数据库进行操作。

        官方文档以及参考资料中给出的结论：如果只是单条命令，那么 使用sqlite3_exec和sqlite3_stmt效率是一样的，但是在涉及到批量操作时，推荐使用sqlite3_stmt机制。在嵌入式软件的开发中执行效率是一个关键因素，因此更加推荐使用stmt方式对数据库进行操作。同时，stmt方式提供了更加细分的步骤，因此操作也更加灵活，在实际的开发过程中数据的方式也会更加多样，更加便利。

• 操作步骤：

• 数据库打开

sqlite3 * db_stmt_open(char *dbName)
{
    sqlite3 *db = NULL;
    int ret =0;

    ret = sqlite3_open_v2(dbName,&db,SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX ,NULL);
    if(ret !=SQLITE_OK)
    {
        printf("db_stmt_open error!\n");
        return NULL;
    }
    printf("db_stmt_open successful!\n");

    return db;
}

• 指令准备

        slqite3_prepare可以实现对sql语句(模板)的解析和编译，生成了可以被执行的 sql语句实例。

SQLITE_API int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

int db_stmt_prepare(sqlite3 *db,const char *sql,sqlite3_stmt **stmt)
{
    int ret;

    ret=sqlite3_prepare_v2(db,sql,strlen(sql),stmt,NULL);

    return ret;
}

•  变量绑定

        该函数可以将变量绑定到sqlite3_prepare实力话的语句中。这个函数是非常方便的一个函数，在程序的设计过程中我们不能为每一个sql语句写一个独立的函数，通常是为同类的操作封装操作接口。在sqlite的select insert等操作中可以为每个表单封装通用接口，使用sqlite3_bind函数绑定传入的参数即可。sqlite提供了多个bind函数原型。

// 绑定二进制变量
SQLITE_API int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
// 绑定double类型变量
SQLITE_API int sqlite3_bind_double(sqlite3_stmt*, int, double);
// 绑定int类型变量
SQLITE_API int sqlite3_bind_int(sqlite3_stmt*, int, int);
// 绑定text类型（char字符串）变量
SQLITE_API int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));

        参数：
        第一个参数：sqlite3_stmt: 准备语句变量指针。 
        第二个形参： sqlite3_stmt变量参数的序号索引值，规定最左侧的SQL参数的索引值为 1，也就是说参数索引值从1开始。
        第三个形参： 是要绑定给第2个形参指向的 变量参数的 实际值。第2个形参可以指向不同的索引值。
        第四个形参： 对于有4个形参的函数，第4个形参一般是第3个形参的长度。    
        第五个形参： 是用于BLOB和字符串绑定后的 析构函数，用于在sqlite处理完blob或字符串之后处理它，一般可以设置为NULL。

关于绑定的位置关系，举一个例子说明：

const char *sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
                              "VALUES (?, ?, ?, ?, ?); ";

ret = db_stmt_prepare(db,sql,&stmt);

if(ret == SQLITE_OK)
{
    sqlite3_bind_int(stmt,1,id);   // 对应第一个? 也就是id的值，是int 类型
    sqlite3_bind_text(stmt,2,name,-1,SQLITE_STATIC); //对应第二个? 是name的值 text类型
    sqlite3_bind_int(stmt,3,age);
    sqlite3_bind_text(stmt,4,address,-1,SQLITE_STATIC);
    sqlite3_bind_int(stmt,5,salary);
}

• SQL语句执行

        sqlite3_step 函数用于执行sqlite3_prepare实例化的SQL语句，相当于sqlite3_exec的作用。

SQLITE_API int sqlite3_step(sqlite3_stmt*);

        sqlite3_step函数的参数是被实例化的sql语句，重点需要关注其返回值。 

#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */

        比较常用的返回值就上面这两个，下面做一下说明：        

        SQLITE_DONE:：意味着sql语句执行完成，并且成功。

        SQLITE_ROW：表示当前的返回结果中包含一行的结果数据。

        执行select操作时若有数据返回时返回值时SQLITE_ROW，若无返回值时为SQLITE_DONE，有个别操作返回值是NULL时，sqlite3_step操作的返回值也会是SQLITE_ROW，例如：SELECT min(DataTime) FROM MYTABLE; 其中min是聚合函数，即使表为空仍要返回，因此函数中还需使用sqlite3_column_bytes()进行判断，若返回0，则表示为NULL。

int db_stmt_exec(sqlite3_stmt *stmt)
{
    int ret;

    do{
        ret=sqlite3_step(stmt);
        if(ret==SQLITE_BUSY)
        {
            usleep(1000);
            continue;
        }
        break;
    }while(ret==SQLITE_BUSY);

    return ret;
}

• SELECT 结果获取

        在sqlite3_exec方法获取select结果时通常在callback函数中处理，在sqlite_stmt机制的处理中提供了sqlite3_count方法处理select的结果，根据数据类型的不同需要使用不同的方法，常用到的函数原型如下：

// 返回查找到的字节数
SQLITE_API int sqlite3_column_bype(sqlite3_stmt*, int iCol);
// 获取二进制数据，配合sqlite3_column_type使用
SQLITE_API const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);

// 获取double类型数据
SQLITE_API double sqlite3_column_double(sqlite3_stmt*, int iCol);
// 获取int类型的数据
SQLITE_API int sqlite3_column_int(sqlite3_stmt*, int iCol);
// 获取char* 类型数据
SQLITE_API const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);

        第一个参数是被stmt实例化的sql语句，第二个指的是select结果的第几个参数，编号从0开始，同时需要注意的是在sqlite3_colum_blob函数或者二进制结果时需要提前使用sqlite3_column_bype提前获取字节数用于后期拷贝，防止数据越界。举例说明一下：

/* const char *sql = "select address from company where id = 1;"; */

if(SQLITE_ROW == sqlite3_step(stmt)) {

    /* 获取查询到的数据的字节数 ret */
    ret = sqlite3_column_bytes(stmt,0);
    printf("select len: %d \n",ret);
    if(ret>0 && ret <100){
        /* 将查询到的数据拷贝到dataBuf中，长度为ret */        
        memcpy(dataBuf,sqlite3_column_blob(stmt,0),ret);      
        printf("select result: %s \n",dataBuf);
    }else{
        printf("select error!\n");
    }
}

        首先执行sqlite3_column_bytes(stmt,0)函数返回查询到的字节数，其中stmt是prepare后的select语句(select address from company where id = 1;)，0指的是select到的第一个结果，例子的select语句只能返回一个结果，若去掉where id = 1，则会返回多个结果，这里的第二个参数就要感觉实际情况而定。二进制类型数据的查询方法如代码备注，需要先查询长度然后再拷贝。

• free stmt语句，关闭数据库

        sqlite3_prepare_v2语句一定要对应一条sqlite3_finalize，sqlite3_open语句一定要对应一条sqlite3_close语句，这很好理解，malloc的空间一定要free释放掉。

/* 销毁prepare申请的stmt语句空间 */
void db_stmt_free(sqlite3_stmt *stmt)
{
    if(stmt!=NULL)
    {
        sqlite3_finalize(stmt);
    }
}

/* 关闭数据库 */
void db_stmt_close(sqlite3 *db)
{
    int ret;

    ret = sqlite3_close_v2(db);

    return ret;
}

• 完整的sqlite执行过程 

int db_stmt_select_int(sqlite3_stmt *stmt)
{
    int ret = 0xEE;

    while (SQLITE_ROW == sqlite3_step(stmt)) {
        ret = sqlite3_column_int(stmt, 0);
    }

    return ret;
}

int selcetConut(void)
{
    sqlite3 *db;
    sqlite3_stmt *stmt = NULL;
    int ret;
    const char *sql = "select count(*) from company;";

    // 打开数据库
    db = db_stmt_open(TestDB);
    // sql 语句准备，并返回指针
    ret = db_stmt_prepare(db,sql,&stmt);

    if(ret!=SQLITE_OK)
    {
        // 若准备失败，销毁准备语句申请的空间
        db_stmt_free(stmt);
        printf("db_exec_prepare error!\n");
        ret = -1;
    }

    // 执行查找操作 sqlite3_step sqlite3_column
	ret = db_stmt_select_int(stmt);
	printf("select conut(*): %d \n",ret);
    // 销毁prepare申请的空间
    db_stmt_free(stmt);
    // 关闭数据库
    db_stmt_close(db);

    return ret;
}