SQLITE使用(三)&&核心API使用

標籤：

概述
     SQLite提供了一系列介面供使用者訪問資料庫，主要包括串連資料庫，處理SQL，迭代查詢結果等。本文會針對我們使用SQLite的主要情境，列出核心的API，詳細介紹API的用法並給出代碼用例。
1.開啟關閉資料庫
sqlite3_open_v2
原型：

int sqlite3_open_v2(const char *filename, /* Database filename (UTF-8) */sqlite3 **ppDb, /* OUT: SQLite db handle */int flags, /* Flags */const char *zVfs /* Name of VFS module to use */);

作用：開啟一個資料庫連接
關鍵的參數：flags
SQLITE_OPEN_NOMUTEX: 設定資料庫連接運行在多線程模式(沒有指定單線程模式的情況下)
SQLITE_OPEN_FULLMUTEX：設定資料庫連接運行在串列模式。
SQLITE_OPEN_SHAREDCACHE：設定運行在共用快取模式。
SQLITE_OPEN_PRIVATECACHE：設定運行在非共用快取模式。
SQLITE_OPEN_READWRITE：指定資料庫連接可以讀寫。
SQLITE_OPEN_CREATE：如果資料庫不存在，則建立。

sqlite3_close_v2
原型：

int sqlite3_close_v2(sqlite3*);

作用：關閉資料庫連接，若關閉時串連上有未提交的事務，該事務會自動復原。

1.1 例子：開啟關閉資料庫連接

sqlite3* pDb;char* filename="/u01/sqlite/test.db";sqlite3_open_v2(filename, &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE, NULL);........sqlite3_close_v2(pDb);

開啟資料庫檔案test.db,對應的資料庫連接可讀可寫，以多線程模式運行，並且運行在共用快取模式，執行完操作後，關閉資料庫連接。

2.更新SQL
更新SQL主要包括建立表，插入，刪除，更新記錄等，SQLite中常用的更新API有兩個，一個是sqlite3_exec，另外一個是sqlite3_prepare_v2。

2.1 sqlite3_exec
原型：

int sqlite3_exec(sqlite3*, /* An open database */const char *sql, /* SQL to be evaluated */int (*callback)(void*,int,char**,char**), /* Callback function */void *, /* 1st argument to callback */char **errmsg /* Error msg written here */);

其中參數sql可以包含多個SQL命令，語句之間以分號隔開，sqlite3_exec()將解析和執行sql字串中的每個命令，直到到達該字串的末尾或遇到錯誤。對於運行修改資料庫的命令(建立，插入，刪除，更新)非常適合，一個函數調用就可以完成全部操作。需要注意的是，雖然sqlite3_exec()可以執行多個SQL命令，但是函數不保證事務，即已執行成功的語句，不會因為後面執行失敗的語句而復原。

2.2 sqlite3_perpare_v2
原型：

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 */);

sqlite3_exec實際上是將編譯，執行進行了封裝，與之等價的一組函數是 sqlite3_prepare_v2(), sqlite3_step()和sqlite3_finalize()。sqlite3_prepare_v2()編譯SQL語句產生VDBE執行碼，sqlite3_step()執行，sqlite3_finalize()關閉語句控制代碼，釋放資源。兩種方式，都可以通過調用sqlite3_changes(pdb)，得到語句影響的行數。

2.3兩種方式比較
(1).sqlite3_exec方式介面使用很簡單，實現同樣的功能，比sqlite3_perpare_v2介面代碼量少。
(2).sqlite3_prepare方式更高效，因為只需要編譯一次，就可以重複執行N次。
(3).sqlite3_prepare方式支援參數化SQL。

鑒於兩種方式的差異，對於簡單的PRAGMA設定語句(PRAGMA cache_size=2000)，事務設定語句(BEGIN TRANSACTION,COMMIT,ROLLBACK)使用sqlite3_exec方式，更簡單；而對於批量的更新、查詢語句，則使用sqlite3_prepare方式，更高效。

2.4 例子：prepare方式執行多sql的例子，pNext初始化在sql語句首部，執行完一個sql後，移動到下一個sql首部。

const char *pNext = (const char *)sql;while (pNext && strlen(pNext) > 0) {　　rc = sqlite3_prepare_v2(pDb, pNext, -1, &pStmt, &pNext);　　if(SQLITE_OK != rc){　　　　錯誤處理　　　　break;　　}   
    rc = sqlite3_step(pStmt);    if(SQLITE_OK != rc && SQLITE_DONE != rc){　　     錯誤處理　　     break;    }
    rc = SQLITE_OK;

    /*統計影響記錄數目*/    resultCount += sqlite3_changes(pDb);

    /* 清理語句控制代碼，準備執行下一個語句*/    sqlite3_finalize(pStmt);}

3.查詢SQL
3.1 sqlite3_get_table
原型：

int sqlite3_get_table(sqlite3 *db, /* An open database */const char *zSql, /* SQL to be evaluated */char ***pazResult, /* Results of the query */int *pnRow, /* Number of result rows written here */int *pnColumn, /* Number of result columns written here */char **pzErrmsg /* Error msg written here */);

該函數接收SQL語句返回的所有記錄，使用sqlite內部分配的記憶體，將其儲存在參數resultp中，必須使用sqlite3_free_table()釋放記憶體。由於結果集可能非常大，會導致記憶體撐爆，因此對於大結果集的查詢，不建議採用這種方式。

3.2 sqlite3_prepare_v2
     prepare方式同樣支援查詢語句，主要分為3個階段，編譯，執行和結果集處理。前面更新SQL部分已經描述了prepare的基本步驟，這裡主要講結果集處理部分。首先通過sqlite3_column_count()可以得到結果集的列數目，通過sqlite3_column_type()可以得到具體某列的儲存類型，方便我們調用合適的sqlite3_column_xxx介面處理欄位值。主要有以下幾類：
sqlite3_column_int
sqlite3_column_int64
sqlite3_column_double
sqlite3_column_text
sqlite3_column_blob

3.3 例子：遍曆結果集

int rc = sqlite3_prepare_v2(pDb, sql, -1, &pStmt, NULL);//擷取列數目int n_columns = sqlite3_column_count(pStmt);do{　　ret = sqlite3_step(stmt);　　if (ret == SQLITE_ROW) 　　{　　　　//處理每一列　　　　for (i = 0; i < n_columns; i++)　　　　{
          /*擷取列儲存類型*/　　　　　　type = sqlite3_column_type(stmt,i);　　　　　　switch(type)　　　　　　{　　　　　　　　case SQLITE_INTEGER:　　　　　　　　 /*處理整型*/　　　　　　　　sqlite3_column_int(stmt,i);
　　　　　　　　break;　　　　　　　　case SQLITE_FLOAT:　　　　　　　　/*處理浮點數*/　　　　　　　　sqlite3_column_double(stmt,i);
　　　　　　　　break;　　　　　　　　case SQLITE_TEXT:　　　　　　　　/*處理字串*/　　　　　　　　sqlite3_column_text(stmt,i);
              break;　　　　　　　　case SQLITE_BLOB:　　　　　　　　/*處理二進位*/　　　　　　　　sqlite3_column_blob(stmt, i));
　　　　　　　  break;　　　　　　　　case SQLITE_NULL:　　　　　　　　/*處理空*/　　　　　　}　　　　}　　}　　else if (ret == SQLITE_DONE) //結束　　{ 　　　　break;　　}}while(true);

4.參數綁定
     SQLite通過prepare介面可以支援參數化的SQL語句，即帶問號的SQL語句。比如查詢語句select * from t where id=?，或者插入語句 insert into t(a,b,c) values(?,?,?)。通過參數化SQL，可以實現一次編譯多次執行的目的，由於問號是沒有意義的，因此需要調用sqlite3_bind_xxx介面來綁定具體的參數。主要有以下幾類：
sqlite3_bind_int
sqlite3_bind_int64
sqlite3_bind_double
sqlite3_bind_text
sqlite3_bind_blob
sqlite3_bind_null
關於綁定參數這裡提一點，對於sqlite3_bind_text和sqlite3_bind_blob介面，綁定參數佔據的儲存空間是否可以被SQLite重用。介面中通過最後一個參數指定，參數值可以為SQLITE_STATIC和SQLITE_TRANSIENT。

SQLITE_STATIC：通知bind函數，參數使用空間是常量，不會改變，sqlite內部無需拷貝副本。
SQLITE_TRANSIENT:通知bind函數，參數使用空間可能會改變，sqlite內部需要有自己的副本。

4.1 例子：大量匯入

//begin a transactionif(sqlite3_exec(pdb, "begin", NULL, NULL, &errmsg) != SQLITE_OK){　　錯誤處理　　return ERROR;}sqlite3_prepare_v2(pdb, "insert into t1 values(?,?,?);", &stmt);for (i = 0; i < n_rows; i++){　　for (j = 0; j < n_columns; j++)　　{　　　　switch(type)　　　　{　　　　　　case SQLITE_INTEGER:　　　　　　/*處理整型*/　　　　　　sqlite3_bind_int()
　　　　　　break;　　　　　　case SQLITE_FLOAT:　　　　　　/*處理浮點型*/　　　　　　sqlite3_bind_double()
　　　　　　break;　　　　　　case SQLITE_TEXT:　　　　　　/*處理字串類型*/　　　　　　sqlite3_bind_text()
　　　　　　break;
　　　　　　case SQLITE_BLOB:　　　　　　/*處理二進位類型*/　　　　　　sqlite3_bind_blob
　　　　　　break;　　　　　　case SQLITE_NULL:　　　　　　sqlite3_bind_null(stmt, index); 
　　　　　　break;   　　　　}    　　}　　sqlite3_step(stmt);  //執行　　sqlite3_reset(stmt); //將已編譯的SQL語句恢複到初始狀態，保留語句相關的資源}sqlite3_finalize(stmt); //結束語句，釋放語句控制代碼if(sqlite3_exec(pdb, "commit", NULL, NULL, &errmsg) != SQLITE_OK){　　錯誤處理　　    return ERROR; }

小結
     本文詳細描述了SQLite中實現建立，修改，查詢資料庫的介面使用，包括單SQL語句，多SQL語句和參數化SQL。主要從四個情境展開描述，開啟關閉資料庫連接，更新語句，查詢語句和參數化語句，並且對於每一種使用情境，給出了相應的代碼示範，希望能對大家熟悉使用SQLite有所協助。

SQLITE使用(三)&&核心API使用