SQLite - 接口使用

一、概要

本文简要介绍SQLite3的C/C++接口，详细用法参考各节链接。

以下是SQLite重要的2种对象：

• sqlite3：数据库链接对象，由sqlite3_open()创建，sqlite3_close()摧毁。

• sqlite3_stmt：语句处理对象（prepared statement），由sqlite3_prepare()创建，sqlite3_finalize()摧毁。

以下是SQLite重要的8种对外方法：

• sqlite3_open()：连接数据库，构造sqlite3对象。

• sqlite3_prepare()：将执行查询和更新的SQL语句编译为bytecode，构造sqlite3_stmt对象。

• sqlite3_bind()：绑定变量到SQL语句中。

• sqlite3_step()：执行sqlite3_stmt中bytecode。

• sqlite3_column()：返回执行sqlite3_stmt语句后得到行的某列值。

• sqlite3_finalize()：析构sqlite3_stmt对象。

• sqlite3_close()：析构sqlite3对象 。

• sqlite3_exec()：包装后函数，会依次执行sqlite3_prepare()、sqlite3_step()、sqlite3_column()、sqlite3_finalize()。

二、对象

2.1.sqlite3 - Database Connection Handle

sqlite3结构体用于描述sqlite数据库文件，类似于文件句柄。

2.2.sqlite3_stmt - Prepared Statement Object

sqlite3_stmt结构体用于描述编译后SQL语句。

形如，gcc会将.c文件编译为.o文件（处理器可执行二进制码），sqlite前端会将SQL语句编译为sqlite3_stmt结构体（SQLite引擎可执行代码）。

sqlite3_stmt的生命周期如下：

1. 使用sqlite3_prepre_v2()函数创建sqlite3_stmt结构体。

2. 使用sqlite3_bind_*()函数绑定参数。

3. 使用一次/多次sqlite3_step()函数执行SQL语句。

4. 使用sqlite3_reset()函数重设sqlite3_stmt，回到步骤2。

5. 析构sqlite3_stmt结构体。

三、接口

3.1.sqlite3_open()

sqlite3_open()函数用于创建与数据库文件的连接并返回sqlite3结构体。

使用实例：

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="c++" cid="n1027" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">sqlite3 *db = NULL;
int err = sqlite3_open(argv[1], &db);
if(SQLITE_OK != err) {
printf("error open sqlite database\n");
exit(1);
}</pre>

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="c++" cid="n1032" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">const char* sql = "SELECT * FROM t WHERE y=?";
const char* ret = "";
sqlite3_stmt *stmt = NULL;
err = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, &ret);
if(SQLITE_OK != err) {
printf("error construct sqlite3_stmt\n");
exit(1);
}</pre>

• ?

• ?NNN

• :VVV

• @VVV

• $VVV

  使用实例：

  <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="c++" cid="n1047" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">err = sqlite3_bind_int(stmt, 1, 2);
  if(SQLITE_OK != err) {
  printf("error bind\n");
  exit(1);
  }</pre>

  <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="c++" cid="n1051" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">err = sqlite3_step(stmt);
  if(SQLITE_DONE != err) {
  printf("error step %d\n", err);
  exit(1);
  }</pre>

  <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="c++" cid="n1055" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">int col = sqlite3_column_int(stmt, 0);
  printf("col = %d\n", col);</pre>

  <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="c++" cid="n1067" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">err = sqlite3_finalize(stmt);
  if(SQLITE_OK != err) {
  printf("error finalize\n");
  exit(1);
  }</pre>

  <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="c++" cid="n1075" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">err = sqlite3_close(db);
  if(SQLITE_OK != err) {
  printf("error close\n");
  exit(1);
  }</pre>

  <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n1062" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">$ sqlite3 test.db
  sqlite> CREATE TABLE t(x INTEGER, y INTEGER);
  sqlite> INSERT INTO t VALUES(1, 2);
  sqlite> INSERT INTO t VALUES(1, 3);
  sqlite> INSERT INTO t VALUES(1, 4);
  sqlite> .exit</pre>

  <pre mdtype="fences" cid="n1080" lang="c++" class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">/* filename: db.c */
  #include <stdlib.h>
  #include <sqlite3.h>
  #include <string.h>
  int main(int argc, char *argv[]) {
  sqlite3 *db = NULL;
  int err = sqlite3_open(argv[1], &db);
  if(SQLITE_OK != err) {
  printf("error open sqlite database\n");
  exit(1);
  }
  const char* sql = "SELECT * FROM t WHERE x=?";
  const char* ret = "";
  sqlite3_stmt *stmt = NULL;
  err = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, &ret);
  if(SQLITE_OK != err) {
  printf("error construct sqlite3_stmt\n");
  exit(1);
  }
  err = sqlite3_bind_int(stmt, 1, 1);
  if(SQLITE_OK != err) {
  printf("error bind\n");
  exit(1);
  }
  while((err = sqlite3_step(stmt)) == SQLITE_ROW) {
  int col = sqlite3_column_int(stmt, 1);
  printf("col value = %d\n", col);
  }
  if(SQLITE_DONE != err) {
  printf("error step %d\n", err);
  exit(1);
  }
  err = sqlite3_finalize(stmt);
  if(SQLITE_OK != err) {
  printf("error finalize\n");
  exit(1);
  }
  err = sqlite3_close(db);
  if(SQLITE_OK != err) {
  printf("error close\n");
  exit(1);
  }
  return 0;
  }</pre>

  <pre mdtype="fences" cid="n1088" lang="" class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;"> gcc -o db_test test.o ./db_test test.db
  col value = 2
  col value = 3
  col value = 4</pre>

  编译运行：

  源代码（测试SQL语句为SELECT * FROM t WHERE x=1;，输出所有y值）：

  创建测试数据库数据：

  四、实例

  使用实例：

  sqlite3_close()函数用于析构sqlite3结构体。

  3.7.sqlite3_close()

  使用实例：

  sqlite3_finalize()函数用于析构sqlite3_stmt结构体。

  3.6.sqlite3_finalize()

  使用实例：

  sqlite3_column()函数用于从执行sqlite3_step()函数后得到结果的sqlite3_stmt结构体中“萃取”某列值。

  3.5.sqlite3_column()

  使用实例：

  sqlite3_step()函数用于执行准备好的sqlite3_stmt（完成编译和参数绑定）。执行后会返回单行结果，多次执行可获取所有行结果。注：INSERT、UPDATE、DELETE语句只需单次运行，无返回值。

  3.4.sqlite3_step()

sqlite3_bind()函数用于绑定SQL语句中的参数，替换SQL语句形如（NNN - 整数，VVV - 参数名）：

3.3.sqlite3_bind()

使用实例：

该函数不实际执行SQL语句，仅编译SQL语句，为执行准备。

sqlite3_prepare()函数用于编译SQL语句为sqlite3_stmt结构体。

3.2.sqlite3_prepare()