sqlite工作記錄-1數(shù)據(jù)庫數(shù)據(jù)寫入

二、常規(guī)數(shù)據(jù)插入：

??? 1). 創(chuàng)建測(cè)試數(shù)據(jù)表。
??? 2). 通過INSERT語句插入測(cè)試數(shù)據(jù)。
??? 3). 刪除測(cè)試表。

????見以下代碼及關(guān)鍵性注釋：

#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對(duì)象，以防止內(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)建表的語句。對(duì)于DDL和DML語句而言，sqlite3_step執(zhí)行正確的返回值
?????//只有SQLITE_DONE，對(duì)于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)建表語句對(duì)象的資源。
?????sqlite3_finalize(stmt);
?????printf("Succeed?to?create?test?table?now.n");
?
?????int?insertCount?=?10;
?????//5.?構(gòu)建插入數(shù)據(jù)的sqlite3_stmt對(duì)象。
?????const?char*?insertSQL?=?"INSERT?INTO?TESTTABLE?VALUES(%d,%f,'%s')";
?????const?char*?testString?=?"this?is?a?test.";
?????char?sql[1024];
?????sqlite3_stmt*?stmt2?=?NULL;
?????for?(int?i?=?0;?i?<?insertCount;?++i)?{
?????????sprintf(sql,insertSQL,i,i?*?1.0,testString);
?????????if?(sqlite3_prepare_v2(conn,sql,strlen(sql),&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("Insert?Succeed.n");
?????}
?????sqlite3_finalize(stmt2);
?????//6.?為了方便下一次測(cè)試運(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*?stmt3?=?NULL;
?????if?(sqlite3_prepare_v2(conn,dropSQL,strlen(dropSQL),&stmt3,NULL)?!=?SQLITE_OK)?{
?????????if?(stmt3)
?????????????sqlite3_finalize(stmt3);
?????????sqlite3_close(conn);
?????????return;
?????}
?????if?(sqlite3_step(stmt3)?==?SQLITE_DONE)?{
?????????printf("The?test?table?has?been?dropped.n");
?????}
?????sqlite3_finalize(stmt3);
?????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.