sqlite工作記錄-3數(shù)據庫數(shù)據高效使用

三、高效的批量數(shù)據插入：

?? ?在給出操作步驟之前先簡單說明一下批量插入的概念，以幫助大家閱讀其后的示例代碼。事實上，批量插入并不是什么新的概念，在其它關系型數(shù)據庫的C接口API中都提供了一定的支持，只是接口的實現(xiàn)方式不同而已。縱觀眾多流行的數(shù)據庫接口，如OCI(Oracle API)、MySQL API和PostgreSQL API等，OCI提供的編程接口最為方便，實現(xiàn)方式也最為高效。SQLite作為一種簡單靈活的嵌入式數(shù)據庫也同樣提供了該功能，但是實現(xiàn)方式并不像其他數(shù)據庫那樣方便明顯，它只是通過一種隱含的技巧來達到批量插入的目的，其邏輯如下：
?? ?1). 開始一個事物，以保證后面的數(shù)據操作語句均在該事物內完成。在SQLite中，如果沒有手工開啟一個事物，其所有的DML語句都是在自動提交模式下工作的，既每次操作后數(shù)據均被自動提交并寫入磁盤文件。然而在非自動提交模式下，只有當其所在的事物被手工COMMIT之后才會將修改的數(shù)據寫入到磁盤中，之前修改的數(shù)據都是僅僅駐留在內存中。顯而易見，這樣的批量寫入方式在效率上勢必會遠遠優(yōu)于多迭代式的單次寫入操作。
?? ?2). 基于變量綁定的方式準備待插入的數(shù)據，這樣可以節(jié)省大量的sqlite3_prepare_v2函數(shù)調用次數(shù)，從而節(jié)省了多次將同一SQL語句編譯成SQLite內部識別的字節(jié)碼所用的時間。事實上，SQLite的官方文檔中已經明確指出，在很多時候sqlite3_prepare_v2函數(shù)的執(zhí)行時間要多于sqlite3_step函數(shù)的執(zhí)行時間，因此建議使用者要盡量避免重復調用sqlite3_prepare_v2函數(shù)。在我們的實現(xiàn)中，如果想避免此類開銷，只需將待插入的數(shù)據以變量的形式綁定到SQL語句中，這樣該SQL語句僅需調用sqlite3_prepare_v2函數(shù)編譯一次即可，其后的操作只是替換不同的變量數(shù)值。
?? ?3). 在完成所有的數(shù)據插入后顯式的提交事物。提交后，SQLite會將當前連接自動恢復為自動提交模式。
?? ?
?? ?下面是示例代碼的實現(xiàn)步驟：
?? ?1). 創(chuàng)建測試數(shù)據表。
?? ?2). 通過執(zhí)行BEGIN TRANSACTION語句手工開啟一個事物。
?? ?3). 準備插入語句及相關的綁定變量。
?? ?4). 迭代式插入數(shù)據。
?? ?5). 完成后通過執(zhí)行COMMIT語句提交事物。

?? ?6). 刪除測試表。

#include#include#includeusing?namespace?std;
?
?void?doTest()
?{
?????sqlite3*?conn?=?NULL;
?????//1.?打開數(shù)據庫
?????int?result?=?sqlite3_open("D:/mytest.db",&conn);
?????if?(result?!=?SQLITE_OK)?{
?????????sqlite3_close(conn);
?????????return;
?????}
?????const?char*?createTableSQL?=?
?????????"CREATE?TABLE?TESTTABLE?(int_col?INT,?float_col?REAL,?string_col?TEXT)";
?????sqlite3_stmt*?stmt?=?NULL;
?????int?len?=?strlen(createTableSQL);
?????//2.?準備創(chuàng)建數(shù)據表，如果創(chuàng)建失敗，需要用sqlite3_finalize釋放sqlite3_stmt對象，以防止內存泄露。
?????if?(sqlite3_prepare_v2(conn,createTableSQL,len,&stmt,NULL)?!=?SQLITE_OK)?{
?????????if?(stmt)
?????????????sqlite3_finalize(stmt);
?????????sqlite3_close(conn);
?????????return;
?????}
?????//3.?通過sqlite3_step命令執(zhí)行創(chuàng)建表的語句。對于DDL和DML語句而言，sqlite3_step執(zhí)行正確的返回值
?????//只有SQLITE_DONE，對于SELECT查詢而言，如果有數(shù)據返回SQLITE_ROW，當?shù)竭_結果集末尾時則返回
?????//SQLITE_DONE。
?????if?(sqlite3_step(stmt)?!=?SQLITE_DONE)?{
?????????sqlite3_finalize(stmt);
?????????sqlite3_close(conn);
?????????return;
?????}
?????//4.?釋放創(chuàng)建表語句對象的資源。
?????sqlite3_finalize(stmt);
?????printf("Succeed?to?create?test?table?now.n");
?
?????//5.?顯式的開啟一個事物。
?????sqlite3_stmt*?stmt2?=?NULL;
?????const?char*?beginSQL?=?"BEGIN?TRANSACTION";
?????if?(sqlite3_prepare_v2(conn,beginSQL,strlen(beginSQL),&stmt2,NULL)?!=?SQLITE_OK)?{
?????????if?(stmt2)
?????????????sqlite3_finalize(stmt2);
?????????sqlite3_close(conn);
?????????return;
?????}
?????if?(sqlite3_step(stmt2)?!=?SQLITE_DONE)?{
?????????sqlite3_finalize(stmt2);
?????????sqlite3_close(conn);
?????????return;
?????}
?????sqlite3_finalize(stmt2);
?
?????//6.?構建基于綁定變量的插入數(shù)據。
?????const?char*?insertSQL?=?"INSERT?INTO?TESTTABLE?VALUES(?,?,?)";
?????sqlite3_stmt*?stmt3?=?NULL;
?????if?(sqlite3_prepare_v2(conn,insertSQL,strlen(insertSQL),&stmt3,NULL)?!=?SQLITE_OK)?{
?????????if?(stmt3)
?????????????sqlite3_finalize(stmt3);
?????????sqlite3_close(conn);
?????????return;
?????}
?????int?insertCount?=?10;
?????const?char*?strData?=?"This?is?a?test.";
?????//7.?基于已有的SQL語句，迭代的綁定不同的變量數(shù)據
?????for?(int?i?=?0;?i?<?insertCount;?++i)?{
?????????//在綁定時，最左面的變量索引值是1。
?????????sqlite3_bind_int(stmt3,1,i);
?????????sqlite3_bind_double(stmt3,2,i?*?1.0);
?????????sqlite3_bind_text(stmt3,3,strData,strlen(strData),SQLITE_TRANSIENT);
?????????if?(sqlite3_step(stmt3)?!=?SQLITE_DONE)?{
?????????????sqlite3_finalize(stmt3);
?????????????sqlite3_close(conn);
?????????????return;
?????????}
?????????//重新初始化該sqlite3_stmt對象綁定的變量。
?????????sqlite3_reset(stmt3);
?????????printf("Insert?Succeed.n");
?????}
?????sqlite3_finalize(stmt3);
?
?????//8.?提交之前的事物。
?????const?char*?commitSQL?=?"COMMIT";
?????sqlite3_stmt*?stmt4?=?NULL;
?????if?(sqlite3_prepare_v2(conn,commitSQL,strlen(commitSQL),&stmt4,NULL)?!=?SQLITE_OK)?{
?????????if?(stmt4)
?????????????sqlite3_finalize(stmt4);
?????????sqlite3_close(conn);
?????????return;
?????}
?????if?(sqlite3_step(stmt4)?!=?SQLITE_DONE)?{
?????????sqlite3_finalize(stmt4);
?????????sqlite3_close(conn);
?????????return;
?????}
?????sqlite3_finalize(stmt4);
?
?????//9.?為了方便下一次測試運行，我們這里需要刪除該函數(shù)創(chuàng)建的數(shù)據表，否則在下次運行時將無法
?????//創(chuàng)建該表，因為它已經存在。
?????const?char*?dropSQL?=?"DROP?TABLE?TESTTABLE";
?????sqlite3_stmt*?stmt5?=?NULL;
?????if?(sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt5,NULL)?!=?SQLITE_OK)?{
?????????if?(stmt5)
?????????????sqlite3_finalize(stmt5);
?????????sqlite3_close(conn);
?????????return;
?????}
?????if?(sqlite3_step(stmt5)?==?SQLITE_DONE)?{
?????????printf("The?test?table?has?been?dropped.n");
?????}
?????sqlite3_finalize(stmt5);
?????sqlite3_close(conn);
?}
?
?int?main()
?{
?????doTest();
?????return?0;
?}
?//輸出結果如下：
?//Succeed?to?create?test?table?now.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//Insert?Succeed.
?//The?test?table?has?been?dropped.