sqlite高效的批量數(shù)據(jù)插入sqlite3_prepare_v2

在給出操作步驟之前先簡單說明一下批量插入的概念，以幫助大家閱讀其后的示例代碼。事實上，批量插入并不是什么新的概念，在其它關(guān)系型數(shù)據(jù)庫的C接口API中都提供了一定的支持，只是接口的實現(xiàn)方式不同而已?？v觀眾多流行的數(shù)據(jù)庫接口，如OCI(Oracle API)、MySQL API和PostgreSQL API等，OCI提供的編程接口最為方便，實現(xiàn)方式也最為高效。SQLite作為一種簡單靈活的嵌入式數(shù)據(jù)庫也同樣提供了該功能，但是實現(xiàn)方式并不像其他數(shù)據(jù)庫那樣方便明顯，它只是通過一種隱含的技巧來達(dá)到批量插入的目的，其邏輯如下：

?? ?1). 開始一個事物，以保證后面的數(shù)據(jù)操作語句均在該事物內(nèi)完成。在SQLite中，如果沒有手工開啟一個事物，其所有的DML語句都是在自動提交模式下工作的，既每次操作后數(shù)據(jù)均被自動提交并寫入磁盤文件。然而在非自動提交模式下，只有當(dāng)其所在的事物被手工COMMIT之后才會將修改的數(shù)據(jù)寫入到磁盤中，之前修改的數(shù)據(jù)都是僅僅駐留在內(nèi)存中。顯而易見，這樣的批量寫入方式在效率上勢必會遠(yuǎn)遠(yuǎn)優(yōu)于多迭代式的單次寫入操作。
?? ?2). 基于變量綁定的方式準(zhǔn)備待插入的數(shù)據(jù)，這樣可以節(jié)省大量的sqlite3_prepare_v2函數(shù)調(diào)用次數(shù)，從而節(jié)省了多次將同一SQL語句編譯成SQLite內(nèi)部識別的字節(jié)碼所用的時間。事實上，SQLite的官方文檔中已經(jīng)明確指出，在很多時候sqlite3_prepare_v2函數(shù)的執(zhí)行時間要多于sqlite3_step函數(shù)的執(zhí)行時間，因此建議使用者要盡量避免重復(fù)調(diào)用sqlite3_prepare_v2函數(shù)。在我們的實現(xiàn)中，如果想避免此類開銷，只需將待插入的數(shù)據(jù)以變量的形式綁定到SQL語句中，這樣該SQL語句僅需調(diào)用sqlite3_prepare_v2函數(shù)編譯一次即可，其后的操作只是替換不同的變量數(shù)值。
?? ?3). 在完成所有的數(shù)據(jù)插入后顯式的提交事物。提交后，SQLite會將當(dāng)前連接自動恢復(fù)為自動提交模式。
?? ?
?? ?下面是示例代碼的實現(xiàn)步驟：
?? ?1). 創(chuàng)建測試數(shù)據(jù)表。
?? ?2). 通過執(zhí)行BEGIN TRANSACTION語句手工開啟一個事物。
?? ?3). 準(zhǔn)備插入語句及相關(guān)的綁定變量。
?? ?4). 迭代式插入數(shù)據(jù)。
?? ?5). 完成后通過執(zhí)行COMMIT語句提交事物。
?? ?6). 刪除測試表。

//6. 構(gòu)建基于綁定變量的插入數(shù)據(jù)。

?56? ? constchar* insertSQL = "INSERT INTO TESTTABLE VALUES(?,?,?)";

?57? ? sqlite3_stmt* stmt3 = NULL;

?58? ? if (sqlite3_prepare_v2(conn,insertSQL,strlen(insertSQL),&stmt3,NULL) != SQLITE_OK) {

?59? ? ? ? if (stmt3)

?60? ? ? ? ? ? sqlite3_finalize(stmt3);

?61? ? ? ? sqlite3_close(conn);

?62? ? ? ? return;

?63? ? }

?64? ? int insertCount = 10;

?65? ? constchar* strData = "This is a test.";

?66? ? //7. 基于已有的SQL語句，迭代的綁定不同的變量數(shù)據(jù)

?67? ? for (int i = 0; i < insertCount; ++i) {

?68? ? ? ? //在綁定時，最左面的變量索引值是1。

?69? ? ? ? sqlite3_bind_int(stmt3,1,i);

?70? ? ? ? sqlite3_bind_double(stmt3,2,i * 1.0);

?71? ? ? ? sqlite3_bind_text(stmt3,3,strData,strlen(strData),SQLITE_TRANSIENT);

?72? ? ? ? if (sqlite3_step(stmt3) != SQLITE_DONE) {

?73? ? ? ? ? ? sqlite3_finalize(stmt3);

?74? ? ? ? ? ? sqlite3_close(conn);

?75? ? ? ? ? ? return;

?76? ? ? ? }

?77? ? ? ? //重新初始化該sqlite3_stmt對象綁定的變量。

?78? ? ? ? sqlite3_reset(stmt3);

?79? ? ? ? printf("Insert Succeed.n");

?80? ? }

?81? ? sqlite3_finalize(stmt3);

?82?

?83? ? //8. 提交之前的事物。

?84? ? constchar* commitSQL = "COMMIT";

?85? ? sqlite3_stmt* stmt4 = NULL;

?86? ? if (sqlite3_prepare_v2(conn,commitSQL,strlen(commitSQL),&stmt4,NULL) != SQLITE_OK) {

?87? ? ? ? if (stmt4)

?88? ? ? ? ? ? sqlite3_finalize(stmt4);

?89? ? ? ? sqlite3_close(conn);

?90? ? ? ? return;

?91? ? }

?92? ? if (sqlite3_step(stmt4) != SQLITE_DONE) {

?93? ? ? ? sqlite3_finalize(stmt4);

?94? ? ? ? sqlite3_close(conn);

?95? ? ? ? return;

?96? ? }

?97? ? sqlite3_finalize(stmt4);

?98?

//6. 執(zhí)行SELECT語句查詢數(shù)據(jù)。

?57? ? constchar* selectSQL = "SELECT * FROM TESTTABLE";

?58? ? sqlite3_stmt* stmt3 = NULL;

?59? ? if (sqlite3_prepare_v2(conn,selectSQL,strlen(selectSQL),&stmt3,NULL) != SQLITE_OK) {

?60? ? ? ? if (stmt3)

?61? ? ? ? ? ? sqlite3_finalize(stmt3);

?62? ? ? ? sqlite3_close(conn);

?63? ? ? ? return;

?64? ? }

?65? ? int fieldCount = sqlite3_column_count(stmt3);

?66? ? do {

?67? ? ? ? int r = sqlite3_step(stmt3);

?68? ? ? ? if (r == SQLITE_ROW) {

?69? ? ? ? ? ? for (int i = 0; i < fieldCount; ++i) {

?70? ? ? ? ? ? ? ? //這里需要先判斷當(dāng)前記錄當(dāng)前字段的類型，再根據(jù)返回的類型使用不同的API函數(shù)

?71? ? ? ? ? ? ? ? //獲取實際的數(shù)據(jù)值。

?72? ? ? ? ? ? ? ? int vtype = sqlite3_column_type(stmt3,i);

?73? ? ? ? ? ? ? ? if (vtype == SQLITE_INTEGER) {

?74? ? ? ? ? ? ? ? ? ? int v = sqlite3_column_int(stmt3,i);

?75? ? ? ? ? ? ? ? ? ? printf("The INTEGER value is %d.n",v);

?76? ? ? ? ? ? ? ? } elseif (vtype == SQLITE_FLOAT) {

?77? ? ? ? ? ? ? ? ? ? double v = sqlite3_column_double(stmt3,i);

?78? ? ? ? ? ? ? ? ? ? printf("The DOUBLE value is %f.n",v);

?79? ? ? ? ? ? ? ? } elseif (vtype == SQLITE_TEXT) {

?80? ? ? ? ? ? ? ? ? ? constchar* v = (constchar*)sqlite3_column_text(stmt3,i);

?81? ? ? ? ? ? ? ? ? ? printf("The TEXT value is %s.n",v);

?82? ? ? ? ? ? ? ? } elseif (vtype == SQLITE_NULL) {

?83? ? ? ? ? ? ? ? ? ? printf("This value is NULL.n");

?84? ? ? ? ? ? ? ? }

?85? ? ? ? ? ? }

?86? ? ? ? } elseif (r == SQLITE_DONE) {

?87? ? ? ? ? ? printf("Select Finished.n");

?88? ? ? ? ? ? break;

?89? ? ? ? } else {

?90? ? ? ? ? ? printf("Failed to SELECT.n");

?91? ? ? ? ? ? sqlite3_finalize(stmt3);

?92? ? ? ? ? ? sqlite3_close(conn);

?93? ? ? ? ? ? return;

?94? ? ? ? }

?95? ? } while (true);

?96? ? sqlite3_finalize(stmt3);