MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

SQLite核心C API查询封装与参数化查询技巧

2024-02-045.2k 阅读

SQLite核心C API查询封装基础

SQLite是一款轻量级的嵌入式数据库，其核心C API提供了丰富的功能来操作数据库。在实际开发中，对查询操作进行封装可以提高代码的可维护性和复用性。

首先，让我们来看一个简单的SQLite查询操作的基本流程。使用SQLite C API进行查询，通常需要以下几个步骤：打开数据库连接、准备SQL语句、绑定参数（如果有参数化查询）、执行查询、处理查询结果、清理资源（关闭语句和数据库连接）。

打开数据库连接

在C语言中，我们使用sqlite3_open函数来打开一个SQLite数据库。该函数的原型如下：

int sqlite3_open(
  const char *filename,   /* 数据库文件名 */
  sqlite3 **ppDb          /* 输出参数，指向打开的数据库连接的指针 */
);

示例代码如下：

sqlite3 *db;
int rc = sqlite3_open("test.db", &db);
if (rc) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    return rc;
} else {
    printf("Opened database successfully\n");
}

准备SQL语句

准备SQL语句使用sqlite3_prepare_v2函数，它将SQL语句编译成一个可执行的对象。函数原型为：

int sqlite3_prepare_v2(
  sqlite3*,                /* 数据库连接 */
  const char *zSql,       /* SQL语句 */
  int nByte,              /* SQL语句的长度， -1表示自动计算 */
  sqlite3_stmt **ppStmt,  /* 输出参数，指向准备好的语句对象的指针 */
  const char **pzTail     /* 未使用的SQL语句部分 */
);

例如，准备一个简单的查询语句：

sqlite3_stmt *stmt;
const char *sql = "SELECT id, name FROM users;";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return rc;
}

执行查询并处理结果

执行查询使用sqlite3_step函数，它会推进语句执行到下一个结果行。函数返回值表示执行状态，常见的有SQLITE_ROW表示有结果行，SQLITE_DONE表示查询完成。

while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    int id = sqlite3_column_int(stmt, 0);
    const unsigned char *name = sqlite3_column_text(stmt, 1);
    printf("ID: %d, Name: %s\n", id, name);
}
if (rc != SQLITE_DONE) {
    fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
}

清理资源

查询完成后，需要关闭语句和数据库连接，以释放资源。

sqlite3_finalize(stmt);
sqlite3_close(db);

简单查询封装

为了简化上述操作流程，我们可以对查询操作进行封装。下面是一个简单的封装示例，封装了查询并打印结果的功能。

#include <stdio.h>
#include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\t", azColName[i], argv[i]? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int execute_query(sqlite3 *db, const char *sql) {
    char *zErrMsg = 0;
    int rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    }
    return rc;
}

int main() {
    sqlite3 *db;
    int rc = sqlite3_open("test.db", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return rc;
    }
    const char *sql = "SELECT id, name FROM users;";
    rc = execute_query(db, sql);
    sqlite3_close(db);
    return rc;
}

在上述代码中，execute_query函数封装了执行SQL语句并通过回调函数处理结果的过程。sqlite3_exec函数会自动处理语句的准备、执行和清理等操作，适用于简单的查询场景。

参数化查询的重要性

参数化查询是一种在SQL语句中使用占位符来代替实际值的技术。在SQLite中，参数化查询具有以下几个重要优点：

  1. 安全性：防止SQL注入攻击。如果直接将用户输入拼接到SQL语句中，恶意用户可能会通过输入特殊字符来改变SQL语句的逻辑，从而获取敏感数据或执行恶意操作。使用参数化查询，SQLite会将参数作为数据处理，而不是SQL语句的一部分，有效避免了这种风险。
  2. 性能优化：SQLite可以对带有参数的SQL语句进行缓存和优化。当相同的SQL语句结构（只是参数值不同）多次执行时，SQLite可以重用之前编译好的执行计划，提高执行效率。

SQLite核心C API中的参数化查询

在SQLite C API中，参数化查询通过在SQL语句中使用占位符，并使用相应的函数绑定实际参数值来实现。常见的占位符有两种形式：问号（?）和命名参数（如:name）。

使用问号占位符

使用问号占位符时，参数按照顺序从1开始编号。下面是一个示例，查询用户表中特定ID的用户信息：

sqlite3_stmt *stmt;
const char *sql = "SELECT id, name FROM users WHERE id =?;";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return rc;
}
int user_id = 1;
rc = sqlite3_bind_int(stmt, 1, user_id);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return rc;
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    int id = sqlite3_column_int(stmt, 0);
    const unsigned char *name = sqlite3_column_text(stmt, 1);
    printf("ID: %d, Name: %s\n", id, name);
}
if (rc != SQLITE_DONE) {
    fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);

在上述代码中，sqlite3_bind_int函数将user_id的值绑定到SQL语句中的第一个问号占位符。

使用命名参数

命名参数使用冒号加参数名的形式（如:name）。示例如下：

sqlite3_stmt *stmt;
const char *sql = "SELECT id, name FROM users WHERE name = :username;";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return rc;
}
const char *username = "John";
rc = sqlite3_bind_text(stmt, ":username", username, -1, SQLITE_STATIC);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return rc;
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    int id = sqlite3_column_int(stmt, 0);
    const unsigned char *name = sqlite3_column_text(stmt, 1);
    printf("ID: %d, Name: %s\n", id, name);
}
if (rc != SQLITE_DONE) {
    fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);

这里使用sqlite3_bind_text函数将username的值绑定到命名参数:username。sqlite3_bind_text函数的最后一个参数SQLITE_STATIC表示SQLite可以直接使用传入的字符串指针，而不需要复制字符串。如果传入SQLITE_TRANSIENT，SQLite会复制字符串。

参数化查询封装

为了方便使用参数化查询，我们可以对其进行封装。下面是一个封装示例，支持使用问号占位符的参数化查询：

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

typedef struct {
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int param_count;
    int *param_types;
    void **param_values;
} QueryContext;

QueryContext* create_query_context(sqlite3 *db, const char *sql) {
    QueryContext *ctx = (QueryContext*)malloc(sizeof(QueryContext));
    if (!ctx) {
        return NULL;
    }
    ctx->db = db;
    int rc = sqlite3_prepare_v2(db, sql, -1, &ctx->stmt, 0);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        free(ctx);
        return NULL;
    }
    ctx->param_count = 0;
    ctx->param_types = NULL;
    ctx->param_values = NULL;
    return ctx;
}

int bind_parameter(QueryContext *ctx, int type, void *value) {
    if (ctx->param_count >= sqlite3_bind_parameter_count(ctx->stmt)) {
        fprintf(stderr, "Too many parameters\n");
        return -1;
    }
    ctx->param_count++;
    ctx->param_types = (int*)realloc(ctx->param_types, ctx->param_count * sizeof(int));
    ctx->param_values = (void**)realloc(ctx->param_values, ctx->param_count * sizeof(void*));
    ctx->param_types[ctx->param_count - 1] = type;
    ctx->param_values[ctx->param_count - 1] = value;
    return 0;
}

int execute_query(QueryContext *ctx) {
    for (int i = 0; i < ctx->param_count; i++) {
        int param_index = i + 1;
        switch (ctx->param_types[i]) {
            case SQLITE_INTEGER:
                sqlite3_bind_int(ctx->stmt, param_index, *((int*)ctx->param_values[i]));
                break;
            case SQLITE_TEXT:
                sqlite3_bind_text(ctx->stmt, param_index, (const char*)ctx->param_values[i], -1, SQLITE_STATIC);
                break;
            // 可以根据需要添加更多类型
            default:
                fprintf(stderr, "Unsupported parameter type\n");
                return -1;
        }
    }
    int rc;
    while ((rc = sqlite3_step(ctx->stmt)) == SQLITE_ROW) {
        int col_count = sqlite3_column_count(ctx->stmt);
        for (int i = 0; i < col_count; i++) {
            const char *col_name = sqlite3_column_name(ctx->stmt, i);
            const unsigned char *col_value = sqlite3_column_text(ctx->stmt, i);
            printf("%s = %s\t", col_name, col_value? (const char*)col_value : "NULL");
        }
        printf("\n");
    }
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(ctx->db));
    }
    sqlite3_finalize(ctx->stmt);
    free(ctx->param_types);
    free(ctx->param_values);
    free(ctx);
    return rc;
}

int main() {
    sqlite3 *db;
    int rc = sqlite3_open("test.db", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return rc;
    }
    const char *sql = "SELECT id, name FROM users WHERE id =? AND name =?";
    QueryContext *ctx = create_query_context(db, sql);
    if (!ctx) {
        sqlite3_close(db);
        return -1;
    }
    int user_id = 1;
    const char *username = "John";
    bind_parameter(ctx, SQLITE_INTEGER, &user_id);
    bind_parameter(ctx, SQLITE_TEXT, (void*)username);
    execute_query(ctx);
    sqlite3_close(db);
    return 0;
}

在上述代码中，create_query_context函数创建一个查询上下文，bind_parameter函数用于绑定参数，execute_query函数执行查询并处理结果。这种封装方式使得参数化查询更加简洁和易用。

复杂参数化查询技巧

批量插入数据

在实际应用中，经常需要批量插入数据到数据库中。使用参数化查询可以提高插入效率并保证数据的一致性。下面是一个批量插入用户数据的示例：

sqlite3_stmt *stmt;
const char *sql = "INSERT INTO users (id, name) VALUES (?,?);";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return rc;
}
typedef struct {
    int id;
    const char *name;
} User;
User users[] = {
    {1, "Alice"},
    {2, "Bob"},
    {3, "Charlie"}
};
for (int i = 0; i < sizeof(users) / sizeof(users[0]); i++) {
    rc = sqlite3_bind_int(stmt, 1, users[i].id);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
        break;
    }
    rc = sqlite3_bind_text(stmt, 2, users[i].name, -1, SQLITE_STATIC);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
        break;
    }
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to insert data: %s\n", sqlite3_errmsg(db));
        break;
    }
    sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);

在这个示例中，我们使用一个结构体数组来存储要插入的用户数据，通过循环绑定参数并执行插入操作。每次执行完插入后，使用sqlite3_reset函数重置语句，以便再次使用。

动态构建参数化查询

有时候，我们需要根据不同的条件动态构建SQL语句和参数。例如，根据用户输入的不同条件查询数据。下面是一个示例：

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

int main() {
    sqlite3 *db;
    int rc = sqlite3_open("test.db", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return rc;
    }
    char sql[256] = "SELECT id, name FROM users WHERE 1 = 1";
    char condition[128];
    printf("Enter condition (e.g., id > 10): ");
    fgets(condition, sizeof(condition), stdin);
    condition[strcspn(condition, "\n")] = '\0';
    strncat(sql, " AND ", sizeof(sql) - strlen(sql) - 1);
    strncat(sql, condition, sizeof(sql) - strlen(sql) - 1);
    sqlite3_stmt *stmt;
    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return rc;
    }
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        int id = sqlite3_column_int(stmt, 0);
        const unsigned char *name = sqlite3_column_text(stmt, 1);
        printf("ID: %d, Name: %s\n", id, name);
    }
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return 0;
}

在上述代码中，我们首先构建一个基本的SQL语句SELECT id, name FROM users WHERE 1 = 1，然后根据用户输入的条件动态添加到SQL语句中。虽然这种方式没有直接使用参数化查询来处理条件，但可以通过进一步改进，将条件中的变量部分使用参数化查询来实现，以提高安全性。

处理查询结果集的高级技巧

大型结果集的分页处理

当查询结果集非常大时，一次性获取所有数据可能会导致内存不足或性能问题。此时，需要进行分页处理。在SQLite中，可以使用LIMIT和OFFSET关键字来实现分页。例如，每页显示10条记录：

int page_num = 1;
int page_size = 10;
int offset = (page_num - 1) * page_size;
const char *sql = "SELECT id, name FROM users LIMIT? OFFSET?";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return rc;
}
rc = sqlite3_bind_int(stmt, 1, page_size);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return rc;
}
rc = sqlite3_bind_int(stmt, 2, offset);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return rc;
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    int id = sqlite3_column_int(stmt, 0);
    const unsigned char *name = sqlite3_column_text(stmt, 1);
    printf("ID: %d, Name: %s\n", id, name);
}
if (rc != SQLITE_DONE) {
    fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);

通过调整page_num和page_size的值，可以获取不同页的数据。

结果集的排序和聚合操作

SQLite支持丰富的排序和聚合操作。例如，对用户表按年龄进行降序排序，并统计用户数量：

const char *sql = "SELECT COUNT(*), AVG(age) FROM users ORDER BY age DESC;";
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return rc;
}
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
    int count = sqlite3_column_int(stmt, 0);
    double avg_age = sqlite3_column_double(stmt, 1);
    printf("User count: %d, Average age: %.2f\n", count, avg_age);
}
if (rc != SQLITE_DONE) {
    fprintf(stderr, "Failed to execute statement: %s\n", sqlite3_errmsg(db));
}
sqlite3_finalize(stmt);

在上述代码中，COUNT(*)用于统计用户数量，AVG(age)用于计算平均年龄，ORDER BY age DESC用于按年龄降序排序。

事务处理与查询的结合

事务是一组数据库操作的集合，这些操作要么全部成功执行，要么全部回滚。在SQLite中，事务处理对于保证数据的一致性非常重要，特别是在涉及多个查询操作的场景下。

开始事务

使用BEGIN语句开始一个事务。在SQLite C API中，可以使用sqlite3_exec函数执行BEGIN语句：

rc = sqlite3_exec(db, "BEGIN", 0, 0, &zErrMsg);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to start transaction: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
    sqlite3_close(db);
    return rc;
}

执行查询操作

在事务中可以执行多个查询操作，例如插入多条数据：

sqlite3_stmt *stmt;
const char *sql = "INSERT INTO users (id, name) VALUES (?,?);";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
    sqlite3_exec(db, "ROLLBACK", 0, 0, &zErrMsg);
    sqlite3_free(zErrMsg);
    sqlite3_close(db);
    return rc;
}
User users[] = {
    {1, "Alice"},
    {2, "Bob"},
    {3, "Charlie"}
};
for (int i = 0; i < sizeof(users) / sizeof(users[0]); i++) {
    rc = sqlite3_bind_int(stmt, 1, users[i].id);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
        sqlite3_exec(db, "ROLLBACK", 0, 0, &zErrMsg);
        sqlite3_free(zErrMsg);
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return rc;
    }
    rc = sqlite3_bind_text(stmt, 2, users[i].name, -1, SQLITE_STATIC);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to bind parameter: %s\n", sqlite3_errmsg(db));
        sqlite3_exec(db, "ROLLBACK", 0, 0, &zErrMsg);
        sqlite3_free(zErrMsg);
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return rc;
    }
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "Failed to insert data: %s\n", sqlite3_errmsg(db));
        sqlite3_exec(db, "ROLLBACK", 0, 0, &zErrMsg);
        sqlite3_free(zErrMsg);
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return rc;
    }
    sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);

提交或回滚事务

如果所有查询操作都成功，使用COMMIT语句提交事务；如果有任何操作失败，使用ROLLBACK语句回滚事务。

rc = sqlite3_exec(db, "COMMIT", 0, 0, &zErrMsg);
if (rc != SQLITE_OK) {
    fprintf(stderr, "Failed to commit transaction: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
    sqlite3_close(db);
    return rc;
}

通过将查询操作放在事务中，可以确保数据的一致性和完整性，避免部分操作成功而部分失败导致的数据不一致问题。

错误处理与调试技巧

在使用SQLite C API进行查询封装和参数化查询时，良好的错误处理和调试技巧至关重要。

错误处理

SQLite C API函数通常返回一个状态码来表示操作的结果。常见的状态码有SQLITE_OK表示成功，其他值表示不同类型的错误。当函数返回非SQLITE_OK时，应该使用sqlite3_errmsg函数获取错误信息。例如：

int rc = sqlite3_open("test.db", &db);
if (rc) {
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    return rc;
}

在准备语句、绑定参数和执行查询等操作中，同样要检查返回状态码并处理错误。

调试技巧

  1. 打印SQL语句：在执行SQL语句之前，打印出完整的SQL语句（包括参数值），以便检查SQL语句的正确性。对于参数化查询，可以在绑定参数后构建一个包含实际参数值的字符串来模拟最终执行的SQL语句。
  2. 单步调试：使用调试工具（如GDB）对代码进行单步调试，检查变量的值和函数的执行流程。特别是在处理复杂的查询逻辑和参数绑定过程中，单步调试可以帮助发现问题。
  3. 日志记录：在代码中添加日志记录功能，记录重要的操作和事件，如数据库连接的打开和关闭、SQL语句的执行等。日志可以帮助追踪程序的运行过程，快速定位问题。

通过合理的错误处理和有效的调试技巧，可以提高代码的稳定性和可维护性，确保SQLite查询操作的正确性和高效性。

在实际应用中，根据具体的需求和场景，灵活运用SQLite核心C API的查询封装和参数化查询技巧，可以开发出高效、安全、可靠的数据库应用程序。无论是小型嵌入式系统还是大型桌面应用，SQLite都能提供强大的数据库支持，而掌握这些技术将使开发者能够充分发挥SQLite的优势。