C++封裝SQLite執行個體<三>

標籤：des   style   color   os   io   使用   ar   for   資料   

前一篇部落格中介紹的是怎樣依據sqlite3_get_table()函數來擷取一張表的內容，就是一股腦的把表中的內容所有儲存起來放在一個一維數組中，這其中的規則已經介紹過了。接下來講的是怎樣依據一個SQL查詢語句來挑選出合乎選擇條件的記錄，被挑選出的記錄事先是不知道的。你不知道這個查詢的結果中有幾行更不知道每一行中詳細的記錄值是什麼，所以沒辦法用給定行值和列值的方式來擷取資料，所有之前的用數組儲存表的方式即可不通了。想要靈活的管理一個查詢返回的結果這就會複雜多了，用到的SQLite原生函數也非常多。

這個CppSQLite3Query類中有四個欄位，各自是

sqlite3 *mpDB;sqlite3_stmt *mpStmt;bool mbEof;  //因為查詢的結果須要一行一行的訪問，所以設定一個bool值來表示是否達到最後一行的結尾int mnCols;  //表示這個查詢結果的列數，行數是無法得知的

第一個參數：查詢要串連的資料庫

第二個參數：

** CAPI3REF: SQL Statement Object** KEYWORDS: {prepared statement} {prepared statements}**** An instance of this object represents a single SQL statement.** This object is variously known as a "prepared statement" or a** "compiled SQL statement" or simply as a "statement".**** The life of a statement object goes something like this:**** <ol>** <li> Create the object using [sqlite3_prepare_v2()] or a related**      function.** <li> Bind values to [host parameters] using the sqlite3_bind_*()**      interfaces.** <li> Run the SQL by calling [sqlite3_step()] one or more times.** <li> Reset the statement using [sqlite3_reset()] then go back**      to step 2.  Do this zero or more times.** <li> Destroy the object using [sqlite3_finalize()].** </ol>

是一個SQL語句對象，或是說一條原生的SQL查詢語句select * from XXX;經過SQLite處理後就產生了一個sqlite3_stmt對象。以後就能夠不用原生的語句來做查詢，就直接使用這個sqlite3_stmt*就能夠查詢了，當查詢結束後，要將sqlite3_stmt*指標對象釋放掉。

釋放函數sqlite3_finalize()和sqlite3_free();函數功能類似，僅僅只是後者是釋放sqlite3*對象的，而前者是釋放sqlite3_stmt*對象的。

函數原型   SQLITE_API int sqlite3_finalize(sqlite3_stmt *pStmt);

函數說明：

/*** CAPI3REF: Destroy A Prepared Statement Object**** ^The sqlite3_finalize() function is called to delete a [prepared statement].** ^If the most recent evaluation of the statement encountered no errors** or if the statement is never been evaluated, then sqlite3_finalize() returns** SQLITE_OK.  ^If the most recent evaluation of statement S failed, then** sqlite3_finalize(S) returns the appropriate [error code] or** [extended error code].**** ^The sqlite3_finalize(S) routine can be called at any point during** the life cycle of [prepared statement] S:** before statement S is ever evaluated, after** one or more calls to [sqlite3_reset()], or after any call** to [sqlite3_step()] regardless of whether or not the statement has** completed execution.**** ^Invoking sqlite3_finalize() on a NULL pointer is a harmless no-op.**** The application must finalize every [prepared statement] in order to avoid** resource leaks.  It is a grievous error for the application to try to use** a prepared statement after it has been finalized.  Any use of a prepared** statement after it has been finalized can result in undefined and** undesirable behavior such as segfaults and heap corruption.*/

用來初始化mnCols欄位的函數是：

SQLITE_API int sqlite3_column_count(sqlite3_stmt *pStmt);

函數說明：

/*** CAPI3REF: Number Of Columns In A Result Set**** ^Return the number of columns in the result set returned by the** [prepared statement]. ^This routine returns 0 if pStmt is an SQL** statement that does not return data (for example an [UPDATE]).**** See also: [sqlite3_data_count()]*/

在依據列索引值擷取當前列的列名用到的函數：

函數原型：SQLITE_API const char *sqlite3_column_name(sqlite3_stmt*, int N);

在推斷資料是否為空白的時候要用到檢測資料類型的函數：

函數原型：SQLITE_API int sqlite3_column_type(sqlite3_stmt*, int iCol);

這個函數返回的是一個整型，當中不同的數值代表不同的已定義好的宏，以下是各種類型：

#define SQLITE_INTEGER  1#define SQLITE_FLOAT    2#define SQLITE_BLOB     4#define SQLITE_NULL     5#ifdef SQLITE_TEXT# undef SQLITE_TEXT#else# define SQLITE_TEXT     3#endif#define SQLITE3_TEXT     3

另一種查詢資料類型的函數

SQLITE_API const char *sqlite3_column_decltype(sqlite3_stmt*,int);

他返回的直接是類型名稱。

在擷取各種不同類型資料的時候用到各種函數，他們返回不同的資料類型的值：

SQLITE_API const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);SQLITE_API int sqlite3_column_bytes(sqlite3_stmt*, int iCol);SQLITE_API int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);SQLITE_API double sqlite3_column_double(sqlite3_stmt*, int iCol);SQLITE_API int sqlite3_column_int(sqlite3_stmt*, int iCol);SQLITE_API sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);SQLITE_API const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);SQLITE_API const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);SQLITE_API int sqlite3_column_type(sqlite3_stmt*, int iCol);SQLITE_API sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);

上面的一些函數可能沒有出如今這個封裝的C++類中。

以下直接貼代碼：

class CppSQLite3Query{private:sqlite3 *mpDB;sqlite3_stmt *mpStmt;bool mbEof;int mnCols;void CheckStmt();public:CppSQLite3Query();CppSQLite3Query(sqlite3 *pdb, sqlite3_stmt *pStmt, bool bEof);CppSQLite3Query(const CppSQLite3Query &rQuery);CppSQLite3Query& operator= (const CppSQLite3Query &rQuery);~CppSQLite3Query();int FieldNums();int FieldIndex(const char* szField);const char* FieldName(int nField);int FieldDataType(int nField);const char* FieldDeclType(int nField);const char* FieldValue(int nField);const char* FieldValue(const char *szField);bool FieldIsNull(int nField);bool FieldIsNull(const char *szField);bool GetIntValue(int nField, int &rDest);bool GetIntValue(const char *szField, int &rDest);bool GetFloatValue(int nField, double &rDest);bool GetFloatValue(const char *szField, double &rDest);bool GetStringValue(int nField, char *&rDest);bool GetStringValue(const char *szField, char *&rDest);bool Eof();void NextRow();void Finalize();};

CppSQLite3Query::CppSQLite3Query(){mpDB = 0;mpStmt = 0;mnCols = 0;mbEof = true;}CppSQLite3Query::CppSQLite3Query(sqlite3 *pdb, sqlite3_stmt *pStmt, bool bEof){mpDB = pdb;mpStmt = pStmt;mbEof = bEof;mnCols = sqlite3_column_count(pStmt);}CppSQLite3Query::CppSQLite3Query(const CppSQLite3Query &rQuery){mpStmt = rQuery.mpStmt;const_cast<CppSQLite3Query &>(rQuery).mpStmt = 0;mnCols = rQuery.mnCols;mbEof = rQuery.mbEof;}CppSQLite3Query& CppSQLite3Query::operator =(const CppSQLite3Query &rQuery){mpStmt = rQuery.mpStmt;const_cast<CppSQLite3Query &>(rQuery).mpStmt = 0;mnCols = rQuery.mnCols;mbEof = rQuery.mbEof;return *this;}CppSQLite3Query::~CppSQLite3Query(){Finalize();}void CppSQLite3Query::CheckStmt(){if (mpStmt == 0){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid Stmt Pointer",DONT_DELETE_MSG);}}int CppSQLite3Query::FieldNums(){CheckStmt();return mnCols;}//依據欄位名返回列索引int CppSQLite3Query::FieldIndex(const char* szField){CheckStmt();if (szField){for (int nField = 0; nField < mnCols; nField++){//後面還有非常多相似的函數，參數差點兒相同，須要一個sqlite3_stmt*和列索引值，這應該是內部查詢了之後返回的結果，而不是事先儲存的const char *szTemp = sqlite3_column_name(mpStmt, nField);if (strcmp(szTemp, szField) == 0){return nField;}}}throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field name requested",DONT_DELETE_MSG);}const char* CppSQLite3Query::FieldName(int nField){CheckStmt();if (nField < 0 || nField > mnCols-1){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field index requested",DONT_DELETE_MSG);}return sqlite3_column_name(mpStmt, nField);}int CppSQLite3Query::FieldDataType(int nField){CheckStmt();if (nField < 0 || nField > mnCols-1){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field index requested",DONT_DELETE_MSG);}return sqlite3_column_type(mpStmt, nField);}const char* CppSQLite3Query::FieldDeclType(int nField){CheckStmt();if (nField < 0 || nField > mnCols-1){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field index requested",DONT_DELETE_MSG);}return sqlite3_column_decltype(mpStmt, nField);}const char* CppSQLite3Query::FieldValue(int nField){CheckStmt();if (nField < 0 || nField > mnCols-1){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field index requested",DONT_DELETE_MSG);}return (const char*)sqlite3_column_text(mpStmt, nField);}const char* CppSQLite3Query::FieldValue(const char *szField){int nField = FieldIndex(szField);return FieldValue(nField);}bool CppSQLite3Query::FieldIsNull(int nField){return (FieldDataType(nField) == SQLITE_NULL);}bool CppSQLite3Query::FieldIsNull(const char *szField){int nField = FieldIndex(szField);return (FieldDataType(nField) == SQLITE_NULL);}bool CppSQLite3Query::GetIntValue(int nField, int &rDest){if (FieldDataType(nField) == SQLITE_NULL){return false;}else{rDest = sqlite3_column_int(mpStmt, nField);return true;}}bool CppSQLite3Query::GetIntValue(const char *szField, int &rDest){int nField = FieldIndex(szField);return GetIntValue(nField, rDest);}bool CppSQLite3Query::GetFloatValue(int nField, double &rDest){if (FieldDataType(nField) == SQLITE_NULL){return false;}else{rDest = sqlite3_column_double(mpStmt, nField);return true;}}bool CppSQLite3Query::GetFloatValue(const char *szField, double &rDest){int nField = FieldIndex(szField);return GetFloatValue(nField, rDest);}bool CppSQLite3Query::GetStringValue(int nField, char *&rDest){if (FieldDataType(nField) == SQLITE_NULL){return false;}else{rDest = const_cast<char *>((const char*)sqlite3_column_text(mpStmt, nField));return true;}}bool CppSQLite3Query::GetStringValue(const char *szField, char *&rDest){int nField = FieldIndex(szField);return GetStringValue(nField, rDest);}bool CppSQLite3Query::Eof(){CheckStmt();return mbEof;}void CppSQLite3Query::NextRow(){CheckStmt();int nRet = sqlite3_step(mpStmt);if (nRet == SQLITE_DONE){// no rowsmbEof = true;}else if (nRet == SQLITE_ROW){// more rows, nothing to do}else{nRet = sqlite3_finalize(mpStmt);mpStmt = 0;const char* szError = sqlite3_errmsg(mpDB);throw CppSQLite3Exception(nRet, szError, DONT_DELETE_MSG);}}void CppSQLite3Query::Finalize(){if (mpStmt){int nRet = sqlite3_finalize(mpStmt);mpStmt = 0;if (nRet != SQLITE_OK){const char* szError = sqlite3_errmsg(mpDB);throw CppSQLite3Exception(nRet, szError, DONT_DELETE_MSG);}}}

C++封裝SQLite執行個體<三>