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

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

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

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

#include#include#includeusing?namespace?std;
?
?void?doTest()
?{
?????sqlite3*?conn?=?NULL;
?????//1.?打開數(shù)據(jù)庫
?????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.?準(zhǔn)備創(chuàng)建數(shù)據(jù)表，如果創(chuàng)建失敗，需要用sqlite3_finalize釋放sqlite3_stmt對象，以防止內(nèi)存泄露。
?????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ù)據(jù)返回SQLITE_ROW，當(dāng)?shù)竭_(dá)結(jié)果集末尾時(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.?顯式的開啟一個(gè)事物。
?????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.?構(gòu)建基于綁定變量的插入數(shù)據(jù)。
?????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ù)據(jù)
?????for?(int?i?=?0;?i?<?insertCount;?++i)?{
?????????//在綁定時(shí)，最左面的變量索引值是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.?為了方便下一次測試運(yùn)行，我們這里需要?jiǎng)h除該函數(shù)創(chuàng)建的數(shù)據(jù)表，否則在下次運(yùn)行時(shí)將無法
?????//創(chuàng)建該表，因?yàn)樗呀?jī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;
?}
?//輸出結(jié)果如下：
?//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.