Sqlite3数据库表内数据批量读取操作---sqlite3_stmt机制

0、引言

        在前面两篇文章已经对数据环境搭建、数据批量写入库中进行了较为详细的讲解。因此，基于前两篇文章内容的基础上，本文主要从数据库中批量数据读取操作进行梳理讲解。

        嵌入式数据库SQLite 3配置使用详细笔记教程_sqlite3-CSDN博客

        SQLite 3 优化批量数据存储操作---事务transaction机制_sqlite3 事务-CSDN博客

        数据库，顾名思义，是一个数据存储的容器、装置，既能存储用户的数据，也能将存储的数据读取出来处理。但是，要怎么做才能把数据读取出来，又能保证效率呢？基于这个问题，本文将对sqlite3_stmt机制进行说明，并实现数据的读取。

1、sqlite3_stmt机制

        sqlite3_stmt 是 SQLite 数据库在 C 语言接口中使用的一个关键数据结构，它代表了一个“准备语句对象”（prepared statement object），也是一个预编译的 SQL 语句，预编译 SQL 语句可以提高执行效率并防止 SQL 注入攻击。这个对象是对 SQL 语句的一种封装，该 SQL 语句已经被编译成字节码形式，可以直接由 SQLite 的虚拟机执行。

        在 SQLite 中，预编译 SQL 语句有如下的几个优点：

①、性能提升：预编译的语句可以被数据库引擎更快地执行，因为编译步骤已经在准备阶段完成了。

②、安全性：使用预编译语句可以有效防止 SQL 注入攻击，因为参数值在执行时是分开绑定的，不会与 SQL 代码混合。

③、减少内存占用：对于重复执行的 SQL 语句，预编译可以减少内存的使用，因为相同的 SQL 代码只需要编译一次。

        使用sqlite3_stmt基本步骤如下：

（1）、打开 SQLite 数据库连接（使用 sqlite3_open() 函数）。

（2）、准备 SQL 语句（使用 sqlite3_prepare_v2() 或 sqlite3_prepare16_v2() 函数）。

（3）、绑定参数（如果有的话，使用 sqlite3_bind_*() 系列函数）。

（4）、执行 SQL 语句（使用 sqlite3_step() 函数）。

（5）、处理结果集（如果有的话，使用 sqlite3_column_*() 系列函数）。

（6）、重置或清理 SQL 语句（使用 sqlite3_reset() 或 sqlite3_finalize() 函数）。

（7）、关闭数据库连接（使用 sqlite3_close() 函数）。

        如果只是读取一条数据时，使用sqlite3_exec和sqlite3_stmt效率是一样的，但是当涉及到大批量的操作时，此前使用的sqlite3_exec就会不是最优选择了，因此推荐使用sqlite3_stmt机制。

2、常用API说明

        从上图所示的Sqlite3的官方网站查询的数据信息可以看到，sqlite3_stmt相关的API有50多个。因此本文这一部分将对sqlite3_stmt常用的API进行介绍。

①、sqlite3_prepare_v2

int sqlite3_prepare_v2(
  sqlite3 *db,            /* 数据库连接 */
  const char *zSql,       /* SQL 文本 */
  int nByte,              /* SQL 文本的最大长度，-1 表示自动计算长度 */
  sqlite3_stmt **ppStmt,  /* 输出参数，指向预编译语句对象的指针 */
  const char **pzTail      /* 输出参数，指向 SQL 文本中未处理部分的指针 */
);

//成功返回SQLITE_OK (0)，失败返回错误码

        sqlite3_prepare_v2() 函数的主要用途是将 SQL 文本编译成一个可以执行的预编译语句对象。预编译语句在执行前可以进行参数绑定，这样可以提高执行效率并防止 SQL 注入攻击。这个函数是 sqlite3_prepare() 的改进版本，提供了更多的功能和更好的错误报告。

②、sqlite3_step

int sqlite3_step(sqlite3_stmt *pStmt);
//pStmt：一个指向预编译语句对象（sqlite3_stmt）的指针。

//返回值：
//SQLITE_ROW（1）：如果执行成功并且结果集中还有更多的行。
//SQLITE_DONE（100）：如果执行成功并且所有行都已经被处理完毕。
//如果发生错误，返回错误码

        sqlite3_step用于执行一个预编译的 SQL 语句，这个函数会执行语句，并将结果集推进到下一行（如果有的话）。当所有行都被处理完毕时，sqlite3_step() 返回 SQLITE_DONE。

③、sqlite3_bind_text

int sqlite3_bind_text(
sqlite3_stmt *stmt,  /*stmt：一个指向预编译语句对象（sqlite3_stmt）的指针。*/
int idx,             /*idx：占位符的索引，从 1 开始计数。*/
const char *zData,   /*zData：要绑定的文本数据的指针。*/
int nData,           /*nData：文本数据的长度（以字节为单位）。如果设置为 -1，SQLite 会自动计算文本的长度。*/
void (*xDel)(void *) /*一个可选的删除函数，用于在 sqlite3_finalize() 调用时释放 zData 指向的内存。如不需要释放内存，可以传递SQLITE_STATIC 或 NULL。*/
);

//成功返回SQLITE_OK (0)，失败返回错误码

        sqlite3_bind_text用于将文本类型的参数绑定到一个预编译的 SQL 语句（sqlite3_stmt）中的占位符。这个函数允许安全地将变量值插入到 SQL 语句中，从而避免 SQL 注入攻击。

④、sqlite3_column_int64

int64_t sqlite3_column_int64(sqlite3_stmt *stmt, int iCol);
//stmt：一个指向预编译语句对象（sqlite3_stmt）的指针。
//iCol：要获取的列的索引，从 0 开始计数。

//成功返回指定列的 int64_t 类型的值，失败返回0

        sqlite3_column_int64用于从结果集中获取一个 int64_t 类型的列值。这个函数通常与 sqlite3_step() 函数一起使用，用于遍历查询结果。

⑤、sqlite3_column_text

const unsigned char *sqlite3_column_text(sqlite3_stmt *stmt, int iCol);
//stmt：一个指向预编译语句对象（sqlite3_stmt）的指针。
//iCol：要获取的列的索引，从 0 开始计数。

//成功，返回指向指定列文本值的指针，失败返回NULL

        sqlite3_column_text用于从结果集中获取一个文本类型的列值。这个函数通常与 sqlite3_step() 函数一起使用，用于遍历查询结果。

        提醒：返回的指针指向的是 SQLite 内部的数据结构，不能直接修改或释放这部分内存。当 sqlite3_stmt 对象被销毁（例如通过调用 sqlite3_finalize()）时，这部分内存也会被自动释放。

⑥、sqlite3_column_double

double sqlite3_column_double(sqlite3_stmt *stmt, int iCol);
//stmt：一个指向预编译语句对象（sqlite3_stmt）的指针。
//iCol：要获取的列的索引，从 0 开始计数。

//成功，返回返回指定列的 double 类型的值，失败返回0.0

        sqlite3_column_double用于从结果集中获取一个double类型的列值。这个函数通常与 sqlite3_step() 函数一起使用，用于遍历查询结果。

⑦、sqlite3_finalize

int sqlite3_finalize(sqlite3_stmt *stmt);
//stmt：一个指向预编译语句对象（sqlite3_stmt）的指针。

//成功返回SQLITE_OK (0)，失败返回错误码

        用于清理和释放一个预编译的 SQL 语句对象（sqlite3_stmt）。

3、程序示例

        在编写此参考程序示例时，使用的是之前存储下来的一个数据库中的一个表，该表的格式如下：

        根据上图所示的内容，可以看到，该表中一共有6个数据，3种数据类型，分别是integer、text和real类型。接下来将对表中的数据进行读取解析并可视化呈现出来。

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <sqlite3.h>

typedef struct {
    long long store_time;
    char name[20];
    double detect_time;
    float x;
    float y;
    float z;
}user_data_t;

/**
  * @brief  读取Sqlite数据库中的xxx数据
  * @param  db:数据库文件描述符
  * @param  data:存储读取出来的数据
  * @retval 成功返回实际读取的数据个数,失败返回-1
  */
int sqlite3_read_xxx_data(sqlite3* db, user_data_t* data, int max_count)
{
    if (db == NULL) {
        DBUG_SHOW(DBUG_ERROR, "%s Variable db is NULL!\n", __FUNCTION__);
        return -1;
    }
    char sql_cmd[] = "SELECT * FROM detect;";
    int ret = 0;
    sqlite3_stmt* stmt = NULL;
    const char* err_msg = NULL;

    ret = sqlite3_prepare_v2(db, sql_cmd, -1, &stmt, NULL);
    if (ret != SQLITE_OK) {
        DBUG_SHOW(DBUG_ERROR, "Prepare statement err: %s\n", sqlite3_errmsg(db));
        return -1;
    }

    int count = 0;

    while ((ret = sqlite3_step(stmt)) == SQLITE_ROW && count < max_count) {
        data[count].store_time = sqlite3_column_int64(stmt, 0);
        strncpy(data[count].name, (const char*)sqlite3_column_text(stmt, 1), sizeof(data[count].name) - 1);
        data[count].name[sizeof(data[count].name) - 1] = '\0';
        data[count].detect_time = sqlite3_column_double(stmt, 2);
        data[count].x = sqlite3_column_double(stmt, 3);
        data[count].y = sqlite3_column_double(stmt, 4);
        data[count].z = sqlite3_column_double(stmt, 5);
        count++;
    }

    if (ret != SQLITE_DONE) {
        err_msg = sqlite3_errmsg(db);
        if (strcmp(err_msg, "another row available") == 0) {
            DBUG_SHOW(DBUG_WARN, "This table has another row data available!\n");
        }
        else {
            DBUG_SHOW(DBUG_ERROR, "Fetch data err: %s\n", err_msg);
            sqlite3_finalize(stmt);
            return -1;
        }
    }

    sqlite3_finalize(stmt);
    return count;
}

int main(void)
{
    sqlite3* read_db = NULL;
    char filename[256] = "2024081214134452.db";
    database_init(&read_db, filename);
    detect_database_t* data = malloc(sizeof(detect_database_t) * 512);
    int ret = sqlite3_read_xxx_data(read_db, data, 512);
    printf("data num : %d\n", ret);
    
    for (int i = 0; i < ret; i++) {
        printf(".......................................\n");
        printf("store_time:%lld\n", data[i].store_time);
        printf("name:%s\n", data[i].name);
        printf("detect_time:%.3f\n", data[i].detect_time);
        printf("x:%.3f\n", data[i].x);
        printf("y:%.3f\n", data[i].y);
        printf("z:%.3f\n", data[i].z);
    }

    free(data);
    
    while (1);
}

4、程序效果

        在读取数据库前，通过SQLite Studio程序查看了数据库中，共存放了211条有效数据。

        运行程序后，从可以看到，读取出了211条数据，且经过对比核验，读出的数据与库中的数据保持一致。