SQLite教程（十四）：C語言編程實例代碼（2）
三、高效的批量數(shù)據(jù)插入：
在給出操作步驟之前先簡單說明一下批量插入的概念，以幫助大家閱讀其后的示例代碼。事實上，批量插入并不是什么新的概念，在其它關(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ù)庫那樣方便明顯，它只是通過一種隱含的技巧來達到批量插入的目的，其邏輯如下：
1). 開始一個事物，以保證后面的數(shù)據(jù)操作語句均在該事物內(nèi)完成。在SQLite中，如果沒有手工開啟一個事物，其所有的DML語句都是在自動提交模式下工作的，既每次操作后數(shù)據(jù)均被自動提交并寫入磁盤文件。然而在非自動提交模式下，只有當其所在的事物被手工COMMIT之后才會將修改的數(shù)據(jù)寫入到磁盤中，之前修改的數(shù)據(jù)都是僅僅駐留在內(nèi)存中。顯而易見，這樣的批量寫入方式在效率上勢必會遠遠優(yōu)于多迭代式的單次寫入操作。
2). 基于變量綁定的方式準備待插入的數(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í)行時間，因此建議使用者要盡量避免重復調(diào)用sqlite3_prepare_v2函數(shù)。在我們的實現(xiàn)中，如果想避免此類開銷，只需將待插入的數(shù)據(jù)以變量的形式綁定到SQL語句中，這樣該SQL語句僅需調(diào)用sqlite3_prepare_v2函數(shù)編譯一次即可，其后的操作只是替換不同的變量數(shù)值。
3). 在完成所有的數(shù)據(jù)插入后顯式的提交事物。提交后，SQLite會將當前連接自動恢復為自動提交模式。
下面是示例代碼的實現(xiàn)步驟：
1). 創(chuàng)建測試數(shù)據(jù)表。
2). 通過執(zhí)行BEGIN TRANSACTION語句手工開啟一個事物。
3). 準備插入語句及相關(guān)的綁定變量。
4). 迭代式插入數(shù)據(jù)。
5). 完成后通過執(zhí)行COMMIT語句提交事物。
6). 刪除測試表。
見以下代碼及關(guān)鍵性注釋：
復制代碼 代碼如下:
#include <sqlite3.h>
#include <string>
#include <stdio.h>
using 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. 準備創(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，當?shù)竭_結(jié)果集末尾時則返回
//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. 構(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) {
//在綁定時，最左面的變量索引值是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ù)據(jù)表，否則在下次運行時將無法
//創(chuàng)建該表，因為它已經(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.
該結(jié)果和上一個例子(普通數(shù)據(jù)插入)的結(jié)果完全相同，只是在執(zhí)行效率上明顯優(yōu)于前者。
四、數(shù)據(jù)查詢：
數(shù)據(jù)查詢是每個關(guān)系型數(shù)據(jù)庫都會提供的最基本功能，下面的代碼示例將給出如何通過SQLite API獲取數(shù)據(jù)。
1). 創(chuàng)建測試數(shù)據(jù)表。
2). 插入一條測試數(shù)據(jù)到該數(shù)據(jù)表以便于后面的查詢。
3). 執(zhí)行SELECT語句檢索數(shù)據(jù)。
4). 刪除測試表。
見以下示例代碼和關(guān)鍵性注釋：
復制代碼 代碼如下:
#include <sqlite3.h>
#include <string>
#include <stdio.h>
using 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. 準備創(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，當?shù)竭_結(jié)果集末尾時則返回
//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. 為后面的查詢操作插入測試數(shù)據(jù)。
sqlite3_stmt* stmt2 = NULL;
const char* insertSQL = "INSERT INTO TESTTABLE VALUES(20,21.0,"this is a test.")";
if (sqlite3_prepare_v2(conn,insertSQL,strlen(insertSQL),&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;
}
printf("Succeed to insert test data.\n");
sqlite3_finalize(stmt2);
//6. 執(zhí)行SELECT語句查詢數(shù)據(jù)。
const char* selectSQL = "SELECT * FROM TESTTABLE";
sqlite3_stmt* stmt3 = NULL;
if (sqlite3_prepare_v2(conn,selectSQL,strlen(selectSQL),&stmt3,NULL) != SQLITE_OK) {
if (stmt3)
sqlite3_finalize(stmt3);
sqlite3_close(conn);
return;
}
int fieldCount = sqlite3_column_count(stmt3);
do {
int r = sqlite3_step(stmt3);
if (r == SQLITE_ROW) {
for (int i = 0; i < fieldCount; ++i) {
//這里需要先判斷當前記錄當前字段的類型，再根據(jù)返回的類型使用不同的API函數(shù)
//獲取實際的數(shù)據(jù)值。
int vtype = sqlite3_column_type(stmt3,i);
if (vtype == SQLITE_INTEGER) {
int v = sqlite3_column_int(stmt3,i);
printf("The INTEGER value is %d.\n",v);
} else if (vtype == SQLITE_FLOAT) {
double v = sqlite3_column_double(stmt3,i);
printf("The DOUBLE value is %f.\n",v);
} else if (vtype == SQLITE_TEXT) {
const char* v = (const char*)sqlite3_column_text(stmt3,i);
printf("The TEXT value is %s.\n",v);
} else if (vtype == SQLITE_NULL) {
printf("This value is NULL.\n");
}
}
} else if (r == SQLITE_DONE) {
printf("Select Finished.\n");
break;
} else {
printf("Failed to SELECT.\n");
sqlite3_finalize(stmt3);
sqlite3_close(conn);
return;
}
} while (true);
sqlite3_finalize(stmt3);
//7. 為了方便下一次測試運行，我們這里需要刪除該函數(shù)創(chuàng)建的數(shù)據(jù)表，否則在下次運行時將無法
//創(chuàng)建該表，因為它已經(jīng)存在。
const char* dropSQL = "DROP TABLE TESTTABLE";
sqlite3_stmt* stmt4 = NULL;
if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt4,NULL) != SQLITE_OK) {
if (stmt4)
sqlite3_finalize(stmt4);
sqlite3_close(conn);
return;
}
if (sqlite3_step(stmt4) == SQLITE_DONE) {
printf("The test table has been dropped.\n");
}
sqlite3_finalize(stmt4);
sqlite3_close(conn);
}
int main()
{
doTest();
return 0;
}
//輸出結(jié)果如下：
//Succeed to create test table now.
//Succeed to insert test data.
//The INTEGER value is 20.
//The DOUBLE value is 21.000000.
//The TEXT value is this is a test..
//Select Finished.
//The test table has been dropped.
相關(guān)文章：
1. MySQL基礎(chǔ)教程11 —— 函數(shù)之Cast函數(shù)和操作符2. MariaDB的安裝與配置教程3. 一篇文章帶你掌握SQLite3基本用法4. SQLite中的WAL機制詳細介紹5. SQLServer導出數(shù)據(jù)庫字典的詳細圖文教程6. sqlserver數(shù)據(jù)庫導入方法的詳細圖文教程7. centos 7下安裝mysql（MariaDB）的教程8. SQLite教程（一）：SQLite數(shù)據(jù)庫介紹9. SQLite數(shù)據(jù)庫常用語句及MAC上的SQLite可視化工具MeasSQLlite使用方法10. SQLite 實現(xiàn)if not exist 類似功能的操作
網(wǎng)公網(wǎng)安備