C++封装SQLite实例<二>

这一篇博客主要讲如何使用SQLite有关库函数去管理数据库中的一张表。

主要用到的函数：

SQLITE_API int sqlite3_get_table

(  sqlite3 *db,                /* The database on which the SQL executes */  const char *zSql,           /* The SQL to be executed */  char ***pazResult,          /* Write the result table here */  int *pnRow,                 /* Write the number of rows in the result here */  int *pnColumn,              /* Write the number of columns of result here */  char **pzErrMsg             /* Write error messages here */)

比较难理解的是第三个参数，是一个三重指针。pazResult指向的是一个一维指针数组，char*result[Num];在SQLite中所有的字段都是被解释为字符串类型存储的，采用的是UTF8编码方式。并且将一个二维表存储在了一维数组中，就是将一个一维数组分割成了mnRows个段，每一段代表二维表中的一行，一行有mnCols个元素。只要给出确切的行值与列值，就能根据公式算出在一位数组中的索引下标。

class CppSQLite3Table{private:int mnCurrentRow;int mnRows;int mnCols;char **mpaszResults;void CheckResluts();public:CppSQLite3Table();CppSQLite3Table(const CppSQLite3Table &rTable);CppSQLite3Table(char **paszResults, int nRows, int nCols);~CppSQLite3Table();CppSQLite3Table& operator= (const CppSQLite3Table &rTable);int NumOfFields();int NumOfRows();const char* NameOfField(int nField);const char* ValueOfField(int nField);const char* ValueOfField(const char *szField);bool FieldIsNull(int nField);bool FieldIsNull(const char *szField);bool GetIntField(int nField, int &rDest);bool GetIntField(const char *szField, int &rDest);bool GetFloatField(int nField, double &rDest);bool GetFloatField(const char *szField, double &rDest);bool GetStringField(int nField, char *&rDest);bool GetStringField(const char *szField, char *&rDset);void SetRow(int nRow);void finalize();};

CppSQLite3Table::CppSQLite3Table(){mnCols = 0;mnRows = 0;mnCurrentRow = 0;mpaszResults = 0;}CppSQLite3Table::CppSQLite3Table(const CppSQLite3Table &rTable){mnCols = rTable.mnCols;mnRows = rTable.mnRows;mnCurrentRow = rTable.mnCurrentRow;mpaszResults = rTable.mpaszResults;const_cast<CppSQLite3Table&>(rTable).mpaszResults = 0; //当表复制时，销毁原来的表，防止丢失修改}CppSQLite3Table::CppSQLite3Table(char **paszResults, int nRows, int nCols){mpaszResults = paszResults; //给出一个一维指针数组，初始化一个表mnCols = nCols;mnRows = nRows;mnCurrentRow = 0;}CppSQLite3Table::~CppSQLite3Table(){finalize();}CppSQLite3Table& CppSQLite3Table::operator= (const CppSQLite3Table &rTable){finalize();mpaszResults = rTable.mpaszResults;const_cast<CppSQLite3Table &>(rTable).mpaszResults = 0;mnCols = rTable.mnCols;mnRows = rTable.mnRows;mnCurrentRow = rTable.mnCurrentRow;return *this;}void CppSQLite3Table::finalize(){if (mpaszResults){sqlite3_free_table(mpaszResults);  //利用库函数销毁表存储内容mpaszResults = 0;}}int CppSQLite3Table::NumOfFields(){CheckResluts();return mnCols;}int CppSQLite3Table::NumOfRows(){CheckResluts();return mnRows;}const char* CppSQLite3Table::NameOfField(int nField){CheckResluts();if (nField < 0 || nField > mnCols-1){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field index requested",DONT_DELETE_MSG);}return mpaszResults[nField]; //一位数组的头mnCols个元素存放的是表的字段名称，存储具体位置是mpaszResults[0,,,mnCols-1]。}const char* CppSQLite3Table::ValueOfField(int nField){CheckResluts();if (nField < 0 || nField > mnCols-1){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field index requested",DONT_DELETE_MSG);}//根据要查询的当前行与列值算出在一位数组中的索引下标，额外加一个mnCols是第一行存储的是字段名int nIndex = mnCurrentRow*mnCols + mnCols + nField;return mpaszResults[nIndex];}//根据字段名称来访问某一列的数据const char* CppSQLite3Table::ValueOfField(const char *szField){CheckResluts();if (szField){for (int nField = 0; nField < mnCols; nField++){if (strcmp(szField, mpaszResults[nField]) == 0){int nIndex = mnCurrentRow*mnCols + mnCols + nField;return mpaszResults[nIndex];}}}throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid field name requested",DONT_DELETE_MSG);}bool CppSQLite3Table::FieldIsNull(int nField){CheckResluts();return (ValueOfField(nField) == 0);}bool CppSQLite3Table::FieldIsNull(const char* szField){CheckResluts();return (ValueOfField(szField) == 0);}//这里的获取具体类型数值函数，需要用户对数据库中的表有一定的了解，知道哪些字段存储的是什么内容//并且使用的是外部传递引用的形式bool CppSQLite3Table::GetIntField(int nField, int &rDest){if (FieldIsNull(nField)){return false;} else{//atoi()函数是C库函数，讲数值型字符串转换为整型值rDest = atoi(ValueOfField(nField));return true;}}bool CppSQLite3Table::GetIntField(const char *szField, int &rDest){if (FieldIsNull(szField)){return false;} else{rDest = atoi(ValueOfField(szField));return true;}}bool CppSQLite3Table::GetFloatField(int nField, double &rDest){if (FieldIsNull(nField)){return false;} else{//C库函数，将数值型字符串转换为浮点数rDest = atof(ValueOfField(nField));return true;}}bool CppSQLite3Table::GetFloatField(const char *szField, double &rDest){if (FieldIsNull(szField)){return false;} else{rDest = atof(ValueOfField(szField));return true;}}bool CppSQLite3Table::GetStringField(int nField, char *&rDest){if (FieldIsNull(nField)){return false;} else{rDest = const_cast<char *>(ValueOfField(nField));return true;}}bool CppSQLite3Table::GetStringField(const char *szField, char *&rDset){if (FieldIsNull(szField)){return false;}else{rDset = const_cast<char *>(ValueOfField(szField));return true;}}//在每一次需要获取数据的时候都要设置要访问的行值void CppSQLite3Table::SetRow(int nRow){CheckResluts();if (nRow < 0 || nRow > mnCols-1){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Invalid row index requested",DONT_DELETE_MSG);}mnCurrentRow = nRow;}void CppSQLite3Table::CheckResluts(){if (mpaszResults == 0){throw CppSQLite3Exception(CPPSQLITE_ERROR,"Null Results pointer",DONT_DELETE_MSG);}}

与select查询相比，获取并维护一整张表会容易很多，对表的访问全转化为对一位数组的访问了，只需要做一个简单的逻辑地址(就是下标)转化。后面会讲如何获取一个查询并维护这个查询的结果，这个结果可能只有一条记录，也可能有很多条记录。