# SQLite學習手冊(實例代碼<二>)
三、高效的批量數據插入：
在給出操作步驟之前先簡單說明一下批量插入的概念，以幫助大家閱讀其后的示例代碼。事實上，批量插入并不是什么新的概念，在其它關系型數據庫的C接口API中都提供了一定的支持，只是接口的實現方式不同而已。縱觀眾多流行的數據庫接口，如OCI(Oracle API)、MySQL API和PostgreSQL API等，OCI提供的編程接口最為方便，實現方式也最為高效。SQLite作為一種簡單靈活的嵌入式數據庫也同樣提供了該功能，但是實現方式并不像其他數據庫那樣方便明顯，它只是通過一種隱含的技巧來達到批量插入的目的，其邏輯如下：
1). 開始一個事物，以保證后面的數據操作語句均在該事物內完成。在SQLite中，如果沒有手工開啟一個事物，其所有的DML語句都是在自動提交模式下工作的，既每次操作后數據均被自動提交并寫入磁盤文件。然而在非自動提交模式下，只有當其所在的事物被手工COMMIT之后才會將修改的數據寫入到磁盤中，之前修改的數據都是僅僅駐留在內存中。顯而易見，這樣的批量寫入方式在效率上勢必會遠遠優于多迭代式的單次寫入操作。
2). 基于變量綁定的方式準備待插入的數據，這樣可以節省大量的sqlite3_prepare_v2函數調用次數，從而節省了多次將同一SQL語句編譯成SQLite內部識別的字節碼所用的時間。事實上，SQLite的官方文檔中已經明確指出，在很多時候sqlite3_prepare_v2函數的執行時間要多于sqlite3_step函數的執行時間，因此建議使用者要盡量避免重復調用sqlite3_prepare_v2函數。在我們的實現中，如果想避免此類開銷，只需將待插入的數據以變量的形式綁定到SQL語句中，這樣該SQL語句僅需調用sqlite3_prepare_v2函數編譯一次即可，其后的操作只是替換不同的變量數值。
3). 在完成所有的數據插入后顯式的提交事物。提交后，SQLite會將當前連接自動恢復為自動提交模式。
下面是示例代碼的實現步驟：
1). 創建測試數據表。
2). 通過執行BEGIN TRANSACTION語句手工開啟一個事物。
3). 準備插入語句及相關的綁定變量。
4). 迭代式插入數據。
5). 完成后通過執行COMMIT語句提交事物。
6). 刪除測試表。
見以下代碼及關鍵性注釋：
```
1 #include <sqlite3.h>
2 #include <string>
3 #include <stdio.h>
4
5 using namespace std;
6
7 void doTest()
8 {
9 sqlite3* conn = NULL;
10 //1\. 打開數據庫
11 int result = sqlite3_open("D:/mytest.db",&conn);
12 if (result != SQLITE_OK) {
13 sqlite3_close(conn);
14 return;
15 }
16 const char* createTableSQL =
17 "CREATE TABLE TESTTABLE (int_col INT, float_col REAL, string_col TEXT)";
18 sqlite3_stmt* stmt = NULL;
19 int len = strlen(createTableSQL);
20 //2\. 準備創建數據表，如果創建失敗，需要用sqlite3_finalize釋放sqlite3_stmt對象，以防止內存泄露。
21 if (sqlite3_prepare_v2(conn,createTableSQL,len,&stmt,NULL) != SQLITE_OK) {
22 if (stmt)
23 sqlite3_finalize(stmt);
24 sqlite3_close(conn);
25 return;
26 }
27 //3\. 通過sqlite3_step命令執行創建表的語句。對于DDL和DML語句而言，sqlite3_step執行正確的返回值
28 //只有SQLITE_DONE，對于SELECT查詢而言，如果有數據返回SQLITE_ROW，當到達結果集末尾時則返回
29 //SQLITE_DONE。
30 if (sqlite3_step(stmt) != SQLITE_DONE) {
31 sqlite3_finalize(stmt);
32 sqlite3_close(conn);
33 return;
34 }
35 //4\. 釋放創建表語句對象的資源。
36 sqlite3_finalize(stmt);
37 printf("Succeed to create test table now.\n");
38
39 //5\. 顯式的開啟一個事物。
40 sqlite3_stmt* stmt2 = NULL;
41 const char* beginSQL = "BEGIN TRANSACTION";
42 if (sqlite3_prepare_v2(conn,beginSQL,strlen(beginSQL),&stmt2,NULL) != SQLITE_OK) {
43 if (stmt2)
44 sqlite3_finalize(stmt2);
45 sqlite3_close(conn);
46 return;
47 }
48 if (sqlite3_step(stmt2) != SQLITE_DONE) {
49 sqlite3_finalize(stmt2);
50 sqlite3_close(conn);
51 return;
52 }
53 sqlite3_finalize(stmt2);
54
55 //6\. 構建基于綁定變量的插入數據。
56 const char* 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 const char* strData = "This is a test.";
66 //7\. 基于已有的SQL語句，迭代的綁定不同的變量數據
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 const char* 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
99 //9\. 為了方便下一次測試運行，我們這里需要刪除該函數創建的數據表，否則在下次運行時將無法
100 //創建該表，因為它已經存在。
101 const char* dropSQL = "DROP TABLE TESTTABLE";
102 sqlite3_stmt* stmt5 = NULL;
103 if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt5,NULL) != SQLITE_OK) {
104 if (stmt5)
105 sqlite3_finalize(stmt5);
106 sqlite3_close(conn);
107 return;
108 }
109 if (sqlite3_step(stmt5) == SQLITE_DONE) {
110 printf("The test table has been dropped.\n");
111 }
112 sqlite3_finalize(stmt5);
113 sqlite3_close(conn);
114 }
115
116 int main()
117 {
118 doTest();
119 return 0;
120 }
121 //輸出結果如下：
122 //Succeed to create test table now.
123 //Insert Succeed.
124 //Insert Succeed.
125 //Insert Succeed.
126 //Insert Succeed.
127 //Insert Succeed.
128 //Insert Succeed.
129 //Insert Succeed.
130 //Insert Succeed.
131 //Insert Succeed.
132 //Insert Succeed.
133 //The test table has been dropped.
```
該結果和上一個例子(普通數據插入)的結果完全相同，只是在執行效率上明顯優于前者。
四、數據查詢：
數據查詢是每個關系型數據庫都會提供的最基本功能，下面的代碼示例將給出如何通過SQLite API獲取數據。
1). 創建測試數據表。
2). 插入一條測試數據到該數據表以便于后面的查詢。
3). 執行SELECT語句檢索數據。
4). 刪除測試表。
見以下示例代碼和關鍵性注釋：
```
1 #include <sqlite3.h>
2 #include <string>
3 #include <stdio.h>
4
5 using namespace std;
6
7 void doTest()
8 {
9 sqlite3* conn = NULL;
10 //1\. 打開數據庫
11 int result = sqlite3_open("D:/mytest.db",&conn);
12 if (result != SQLITE_OK) {
13 sqlite3_close(conn);
14 return;
15 }
16 const char* createTableSQL =
17 "CREATE TABLE TESTTABLE (int_col INT, float_col REAL, string_col TEXT)";
18 sqlite3_stmt* stmt = NULL;
19 int len = strlen(createTableSQL);
20 //2\. 準備創建數據表，如果創建失敗，需要用sqlite3_finalize釋放sqlite3_stmt對象，以防止內存泄露。
21 if (sqlite3_prepare_v2(conn,createTableSQL,len,&stmt,NULL) != SQLITE_OK) {
22 if (stmt)
23 sqlite3_finalize(stmt);
24 sqlite3_close(conn);
25 return;
26 }
27 //3\. 通過sqlite3_step命令執行創建表的語句。對于DDL和DML語句而言，sqlite3_step執行正確的返回值
28 //只有SQLITE_DONE，對于SELECT查詢而言，如果有數據返回SQLITE_ROW，當到達結果集末尾時則返回
29 //SQLITE_DONE。
30 if (sqlite3_step(stmt) != SQLITE_DONE) {
31 sqlite3_finalize(stmt);
32 sqlite3_close(conn);
33 return;
34 }
35 //4\. 釋放創建表語句對象的資源。
36 sqlite3_finalize(stmt);
37 printf("Succeed to create test table now.\n");
38
39 //5\. 為后面的查詢操作插入測試數據。
40 sqlite3_stmt* stmt2 = NULL;
41 const char* insertSQL = "INSERT INTO TESTTABLE VALUES(20,21.0,'this is a test.')";
42 if (sqlite3_prepare_v2(conn,insertSQL,strlen(insertSQL),&stmt2,NULL) != SQLITE_OK) {
43 if (stmt2)
44 sqlite3_finalize(stmt2);
45 sqlite3_close(conn);
46 return;
47 }
48 if (sqlite3_step(stmt2) != SQLITE_DONE) {
49 sqlite3_finalize(stmt2);
50 sqlite3_close(conn);
51 return;
52 }
53 printf("Succeed to insert test data.\n");
54 sqlite3_finalize(stmt2);
55
56 //6\. 執行SELECT語句查詢數據。
57 const char* 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 //這里需要先判斷當前記錄當前字段的類型，再根據返回的類型使用不同的API函數
71 //獲取實際的數據值。
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 } else if (vtype == SQLITE_FLOAT) {
77 double v = sqlite3_column_double(stmt3,i);
78 printf("The DOUBLE value is %f.\n",v);
79 } else if (vtype == SQLITE_TEXT) {
80 const char* v = (const char*)sqlite3_column_text(stmt3,i);
81 printf("The TEXT value is %s.\n",v);
82 } else if (vtype == SQLITE_NULL) {
83 printf("This value is NULL.\n");
84 }
85 }
86 } else if (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);
97
98 //7\. 為了方便下一次測試運行，我們這里需要刪除該函數創建的數據表，否則在下次運行時將無法
99 //創建該表，因為它已經存在。
100 const char* dropSQL = "DROP TABLE TESTTABLE";
101 sqlite3_stmt* stmt4 = NULL;
102 if (sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt4,NULL) != SQLITE_OK) {
103 if (stmt4)
104 sqlite3_finalize(stmt4);
105 sqlite3_close(conn);
106 return;
107 }
108 if (sqlite3_step(stmt4) == SQLITE_DONE) {
109 printf("The test table has been dropped.\n");
110 }
111 sqlite3_finalize(stmt4);
112 sqlite3_close(conn);
113 }
114
115 int main()
116 {
117 doTest();
118 return 0;
119 }
120 //輸出結果如下：
121 //Succeed to create test table now.
122 //Succeed to insert test data.
123 //The INTEGER value is 20.
124 //The DOUBLE value is 21.000000.
125 //The TEXT value is this is a test..
126 //Select Finished.
127 //The test table has been dropped.
```
- SQLite學習手冊
- SQLite學習手冊(目錄)
- SQLite學習手冊(開篇)
- SQLite學習手冊(C/C++接口簡介)
- SQLite學習手冊(數據表和視圖)
- SQLite學習手冊(內置函數)
- SQLite學習手冊(索引和數據分析/清理)
- SQLite學習手冊(數據庫和事務)
- SQLite學習手冊(表達式)
- SQLite學習手冊(數據類型)
- SQLite學習手冊(命令行工具)
- SQLite學習手冊(在線備份)
- SQLite學習手冊(內存數據庫)
- SQLite學習手冊(臨時文件)
- SQLite學習手冊(鎖和并發控制)
- SQLite學習手冊(實例代碼&lt;一&gt;)
- SQLite學習手冊(實例代碼&lt;二&gt;)