一、概要
本文簡要介紹SQLite3的C/C++接口勉吻，詳細(xì)用法參考各節(jié)鏈接鸯屿。
以下是SQLite重要的2種對象：
sqlite3：數(shù)據(jù)庫鏈接對象，由sqlite3_open()創(chuàng)建窃判，sqlite3_close()摧毀。sqlite3_stmt：語句處理對象（prepared statement）喇闸，由sqlite3_prepare()創(chuàng)建袄琳，sqlite3_finalize()摧毀。
以下是SQLite重要的8種對外方法：
sqlite3_open()：連接數(shù)據(jù)庫燃乍，構(gòu)造sqlite3對象唆樊。sqlite3_prepare()：將執(zhí)行查詢和更新的SQL語句編譯為bytecode，構(gòu)造sqlite3_stmt對象橘沥。sqlite3_bind()：綁定變量到SQL語句中窗轩。sqlite3_step()：執(zhí)行sqlite3_stmt中bytecode夯秃。sqlite3_column()：返回執(zhí)行sqlite3_stmt語句后得到行的某列值座咆。sqlite3_finalize()：析構(gòu)sqlite3_stmt對象痢艺。sqlite3_close()：析構(gòu)sqlite3對象 。sqlite3_exec()：包裝后函數(shù)介陶，會依次執(zhí)行sqlite3_prepare()堤舒、sqlite3_step()、sqlite3_column()哺呜、sqlite3_finalize()舌缤。
二、對象
2.1.sqlite3 - Database Connection Handle
sqlite3結(jié)構(gòu)體用于描述sqlite數(shù)據(jù)庫文件某残，類似于文件句柄国撵。
2.2.sqlite3_stmt - Prepared Statement Object
sqlite3_stmt結(jié)構(gòu)體用于描述編譯后SQL語句。
三徐伐、接口
3.1.sqlite3_open()
sqlite3_open()函數(shù)用于創(chuàng)建與數(shù)據(jù)庫文件的連接并返回sqlite3結(jié)構(gòu)體贯钩。
<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>
-
<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值）：創(chuàng)建測試數(shù)據(jù)庫數(shù)據(jù)：
四办素、實(shí)例
sqlite3_close()函數(shù)用于析構(gòu)sqlite3結(jié)構(gòu)體魏保。3.7.sqlite3_close()
sqlite3_finalize()函數(shù)用于析構(gòu)sqlite3_stmt結(jié)構(gòu)體。3.6.sqlite3_finalize()
sqlite3_column()函數(shù)用于從執(zhí)行sqlite3_step()函數(shù)后得到結(jié)果的sqlite3_stmt結(jié)構(gòu)體中“萃取”某列值摸屠。3.5.sqlite3_column()
sqlite3_step()函數(shù)用于執(zhí)行準(zhǔn)備好的sqlite3_stmt（完成編譯和參數(shù)綁定）谓罗。執(zhí)行后會返回單行結(jié)果，多次執(zhí)行可獲取所有行結(jié)果季二。注：INSERT檩咱、UPDATE、DELETE語句只需單次運(yùn)行胯舷，無返回值刻蚯。3.4.sqlite3_step()
sqlite3_bind()函數(shù)用于綁定SQL語句中的參數(shù)，替換SQL語句形如（NNN - 整數(shù)桑嘶，VVV - 參數(shù)名）：
3.3.sqlite3_bind()
該函數(shù)不實(shí)際執(zhí)行SQL語句炊汹，僅編譯SQL語句，為執(zhí)行準(zhǔn)備逃顶。
sqlite3_prepare()函數(shù)用于編譯SQL語句為sqlite3_stmt結(jié)構(gòu)體讨便。
