sqlite3类

#pragma once#include <Windows.h>#include <list>#include <string>#include "sqlite3.h"typedef int (*QueryCallback) (void *para, int n_column, char **column_value, char **column_name);typedef enum _SQLITE_DATATYPE{SQLITE_DATATYPE_INTEGER = SQLITE_INTEGER,SQLITE_DATATYPE_FLOAT = SQLITE_FLOAT,SQLITE_DATATYPE_TEXT = SQLITE_TEXT,SQLITE_DATATYPE_BLOB = SQLITE_BLOB,SQLITE_DATATYPE_NULL = SQLITE_NULL,}SQLITE_DATATYPE;class SQLite;class SQLiteDataReader{public:SQLiteDataReader(sqlite3_stmt *pStmt);~SQLiteDataReader();public:// 读取一行数据   BOOL Read();// 关闭Reader，读取结束后调用   void Close();// 总的列数   int ColumnCount(void);// 获取某列的名称    LPCTSTR GetName(int nCol);// 获取某列的数据类型   SQLITE_DATATYPE GetDataType(int nCol);// 获取某列的值(字符串)   LPCTSTR GetStringValue(int nCol);// 获取某列的值(整形)   int GetIntValue(int nCol);// 获取某列的值(长整形)   long GetInt64Value(int nCol);// 获取某列的值(浮点形)   double GetFloatValue(int nCol);// 获取某列的值(二进制数据)   const BYTE* GetBlobValue(int nCol, int &nLen);private:sqlite3_stmt *m_pStmt;};class SQLiteCommand{public:SQLiteCommand(SQLite* pSqlite);SQLiteCommand(SQLite* pSqlite, LPCTSTR lpSql);~SQLiteCommand();public:// 设置命令   BOOL SetCommandText(LPCTSTR lpSql);// 绑定参数（index为要绑定参数的序号，从1开始）  BOOL BindParam(int index, LPCTSTR szValue);BOOL BindParam(int index, const int nValue);BOOL BindParam(int index, const double dValue);BOOL BindParam(int index, const unsigned char* blobValue, int nLen);// 执行命令   BOOL Excute();// 清除命令（命令不再使用时需调用该接口清除）   void Clear();private:SQLite *m_pSqlite;sqlite3_stmt *m_pStmt;};class SQLite  {  public:      SQLite(void);      ~SQLite(void);  public:      // 打开数据库       BOOL Open(LPCTSTR lpDbFlie);      // 关闭数据库       void Close();      // 执行非查询操作（更新或删除）       BOOL ExcuteNonQuery(LPCTSTR lpSql);      BOOL ExcuteNonQuery(SQLiteCommand* pCmd);        // 查询       SQLiteDataReader ExcuteQuery(LPCTSTR lpSql);      // 查询（回调方式）       BOOL ExcuteQuery(LPCTSTR lpSql,QueryCallback pCallBack);      // 开始事务       BOOL BeginTransaction();      // 提交事务       BOOL CommitTransaction();      // 回滚事务       BOOL RollbackTransaction();      // 获取上一条错误信息       LPCTSTR GetLastErrorMsg();//检查表是否存在BOOL CheckTableExist(LPCTSTR lpTableName);public:      friend class SQLiteCommand;  private:      sqlite3* m_db;  };  class SQLiteImp{public:SQLiteImp();~SQLiteImp();BOOL Init(LPCTSTR lpDbName);BOOL UnInit();BOOL CreateTable();BOOL Insert(LPCTSTR lpID, LPCTSTR lpContent);BOOL UpData(LPCTSTR lpID, LPCTSTR lpContent);INT  Query(LPCTSTR lpID, std::list<std::wstring>& contentList);INT Count(LPCTSTR lpID);BOOL UpDataGroupName(LPCTSTR lpGroupName, LPCTSTR lpNewGroupName);private:SQLite* m_pSQLite;};

#include "SQliteImp.h"#include <tchar.h>const char* WcharToUtf8(const wchar_t *pwStr){if (pwStr == NULL){return NULL;}int len = WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, NULL, 0, NULL, NULL);if (len <= 0){return NULL;}char *pStr = new char[len];WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, pStr, len, NULL, NULL);return pStr;}const wchar_t* Utf8ToWchar(const char *pStr){if (pStr == NULL){return NULL;}int len = MultiByteToWideChar(CP_UTF8, 0, pStr, -1, NULL, 0);if (len <= 0){return NULL;}wchar_t *pwStr = new wchar_t[len];MultiByteToWideChar(CP_UTF8, 0, pStr, -1, pwStr, len);return pwStr;}SQLite::SQLite(void) :m_db(NULL){}SQLite::~SQLite(void){Close();}BOOL SQLite::Open(LPCTSTR lpDbFlie){if (lpDbFlie == NULL){return FALSE;}#ifdef  UNICODE   if (sqlite3_open16(lpDbFlie, &m_db) != SQLITE_OK)#else  if (sqlite3_open(lpDbFlie, &m_db) != SQLITE_OK)#endif  {return FALSE;}return TRUE;}void SQLite::Close(){if (m_db){sqlite3_close(m_db);m_db = NULL;}}BOOL SQLite::ExcuteNonQuery(LPCTSTR lpSql){if (lpSql == NULL){return FALSE;}sqlite3_stmt* stmt;#ifdef  UNICODE   if (sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#else  if (sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#endif  {return FALSE;}sqlite3_step(stmt);return (sqlite3_finalize(stmt) == SQLITE_OK) ? TRUE : FALSE;}BOOL SQLite::ExcuteNonQuery(SQLiteCommand* pCmd){if (pCmd == NULL){return FALSE;}return pCmd->Excute();}// 查询(回调方式)  BOOL SQLite::ExcuteQuery(LPCTSTR lpSql, QueryCallback pCallBack){if (lpSql == NULL || pCallBack == NULL){return FALSE;}char *errmsg = NULL;#ifdef  UNICODE   const char *szSql = WcharToUtf8(lpSql);if (sqlite3_exec(m_db, szSql, pCallBack, NULL, &errmsg) != SQLITE_OK){delete[] szSql;return FALSE;}delete[] szSql;#else  if (sqlite3_exec(m_db, lpSql, pCallBack, NULL, &errmsg) != SQLITE_OK){return FALSE;}#endif  return TRUE;}// 查询  SQLiteDataReader SQLite::ExcuteQuery(LPCTSTR lpSql){if (lpSql == NULL){return FALSE;}sqlite3_stmt* stmt;#ifdef  UNICODE   if (sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#else  if (sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#endif  {return FALSE;}return SQLiteDataReader(stmt);}// 开始事务  BOOL SQLite::BeginTransaction(){char * errmsg = NULL;if (sqlite3_exec(m_db, "BEGIN TRANSACTION;", NULL, NULL, &errmsg) != SQLITE_OK){return FALSE;}return TRUE;}// 提交事务  BOOL SQLite::CommitTransaction(){char * errmsg = NULL;if (sqlite3_exec(m_db, "COMMIT TRANSACTION;;", NULL, NULL, &errmsg) != SQLITE_OK){return FALSE;}return TRUE;}// 回滚事务  BOOL SQLite::RollbackTransaction(){char * errmsg = NULL;if (sqlite3_exec(m_db, "ROLLBACK  TRANSACTION;", NULL, NULL, &errmsg) != SQLITE_OK){return FALSE;}return TRUE;}// 获取上一条错误信息  LPCTSTR SQLite::GetLastErrorMsg(){#ifdef UNICODE   return (LPCTSTR)sqlite3_errmsg16(m_db);#else  return sqlite3_errmsg(m_db);#endif  }BOOL SQLite::CheckTableExist(LPCTSTR lpTableName){char* sErrMsg = NULL;char sTemp[] = "select * from PCList;";if (sqlite3_exec(m_db, sTemp, NULL, NULL, &sErrMsg) != SQLITE_OK){return FALSE;}return TRUE;}SQLiteDataReader::SQLiteDataReader(sqlite3_stmt *pStmt) :m_pStmt(pStmt){}SQLiteDataReader::~SQLiteDataReader(){Close();}// 读取一行数据  BOOL SQLiteDataReader::Read(){if (m_pStmt == NULL){return FALSE;}if (sqlite3_step(m_pStmt) != SQLITE_ROW){return FALSE;}return TRUE;}// 关闭Reader，读取结束后调用  void SQLiteDataReader::Close(){if (m_pStmt){sqlite3_finalize(m_pStmt);m_pStmt = NULL;}}// 总的列数  int SQLiteDataReader::ColumnCount(void){return sqlite3_column_count(m_pStmt);}// 获取某列的名称   LPCTSTR SQLiteDataReader::GetName(int nCol){#ifdef  UNICODE   return (LPCTSTR)sqlite3_column_name16(m_pStmt, nCol);#else  return (LPCTSTR)sqlite3_column_name(m_pStmt, nCol);#endif  }// 获取某列的数据类型  SQLITE_DATATYPE SQLiteDataReader::GetDataType(int nCol){return (SQLITE_DATATYPE)sqlite3_column_type(m_pStmt, nCol);}// 获取某列的值(字符串)  LPCTSTR SQLiteDataReader::GetStringValue(int nCol){#ifdef  UNICODE   return (LPCTSTR)sqlite3_column_text16(m_pStmt, nCol);#else  return (LPCTSTR)sqlite3_column_text(m_pStmt, nCol);#endif  }// 获取某列的值(整形)  int SQLiteDataReader::GetIntValue(int nCol){return sqlite3_column_int(m_pStmt, nCol);}// 获取某列的值(长整形)  long SQLiteDataReader::GetInt64Value(int nCol){return (long)sqlite3_column_int64(m_pStmt, nCol);}// 获取某列的值(浮点形)  double SQLiteDataReader::GetFloatValue(int nCol){return sqlite3_column_double(m_pStmt, nCol);}// 获取某列的值(二进制数据)  const BYTE* SQLiteDataReader::GetBlobValue(int nCol, int &nLen){nLen = sqlite3_column_bytes(m_pStmt, nCol);return (const BYTE*)sqlite3_column_blob(m_pStmt, nCol);}SQLiteCommand::SQLiteCommand(SQLite* pSqlite) :m_pSqlite(pSqlite),m_pStmt(NULL){}SQLiteCommand::SQLiteCommand(SQLite* pSqlite, LPCTSTR lpSql) :m_pSqlite(pSqlite),m_pStmt(NULL){SetCommandText(lpSql);}SQLiteCommand::~SQLiteCommand(){}BOOL SQLiteCommand::SetCommandText(LPCTSTR lpSql){#ifdef  UNICODE   if (sqlite3_prepare16_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)#else  if (sqlite3_prepare_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)#endif  {return FALSE;}return TRUE;}BOOL SQLiteCommand::BindParam(int index, LPCTSTR szValue){#ifdef  UNICODE   if (sqlite3_bind_text16(m_pStmt, index, szValue, -1, SQLITE_TRANSIENT) != SQLITE_OK)#else  if (sqlite3_bind_text(m_pStmt, index, szValue, -1, SQLITE_TRANSIENT) != SQLITE_OK)#endif  {return FALSE;}return TRUE;}BOOL SQLiteCommand::BindParam(int index, const int nValue){if (sqlite3_bind_int(m_pStmt, index, nValue) != SQLITE_OK){return FALSE;}return TRUE;}BOOL SQLiteCommand::BindParam(int index, const double dValue){if (sqlite3_bind_double(m_pStmt, index, dValue) != SQLITE_OK){return FALSE;}return TRUE;}BOOL SQLiteCommand::BindParam(int index, const unsigned char* blobBuf, int nLen){if (sqlite3_bind_blob(m_pStmt, index, blobBuf, nLen, NULL) != SQLITE_OK){return FALSE;}return TRUE;}BOOL SQLiteCommand::Excute(){sqlite3_step(m_pStmt);return (sqlite3_reset(m_pStmt) == SQLITE_OK) ? TRUE : FALSE;}void SQLiteCommand::Clear(){if (m_pStmt){sqlite3_finalize(m_pStmt);}}SQLiteImp::SQLiteImp():m_pSQLite(NULL){}SQLiteImp::~SQLiteImp(){}BOOL SQLiteImp::Init(LPCTSTR lpDbName){if (m_pSQLite == NULL){m_pSQLite = new SQLite;}BOOL bRet = FALSE;if (m_pSQLite != NULL){bRet = m_pSQLite->Open(lpDbName);}return bRet;}BOOL SQLiteImp::UnInit(){if (m_pSQLite != NULL){m_pSQLite->Close();delete m_pSQLite;m_pSQLite = NULL;}return TRUE;}BOOL SQLiteImp::CreateTable(){if (!m_pSQLite){return FALSE;}BOOL bHas = m_pSQLite->CheckTableExist(L"PCList");if (bHas){return TRUE;}TCHAR sql[1024] = { 0 };_stprintf_s(sql, 1024, _T("%s"),_T("CREATE TABLE [PCList] (")_T("[pcid] TEXT,")_T("[content] TEXT);"));if (!m_pSQLite->ExcuteNonQuery(sql)){return FALSE;}return TRUE;}BOOL SQLiteImp::Insert(LPCTSTR lpID, LPCTSTR lpContent){if (!m_pSQLite){return FALSE;}TCHAR sql[1024] = { 0 };_stprintf_s(sql, 1024, _T("insert into PCList(pcid, content) values('%s', '%s')"), lpID, lpContent);if (!m_pSQLite->ExcuteNonQuery(sql)){return FALSE;}return TRUE;}BOOL SQLiteImp::UpData(LPCTSTR lpID, LPCTSTR lpContent){if (!m_pSQLite){return FALSE;}TCHAR sql[1024] = { 0 };_stprintf_s(sql, 1024, _T("update PCList set content = '%s' where pcid = '%s'"), lpContent, lpID);if (!m_pSQLite->ExcuteNonQuery(sql)){return FALSE;}return TRUE;}INT SQLiteImp::Query(LPCTSTR lpID, std::list<std::wstring>& contentList){if (!m_pSQLite){return FALSE;}TCHAR sql[1024] = { 0 };if (wcslen(lpID) > 0){_stprintf_s(sql, 1024, _T("select * from PCList where pcid = '%s'"), lpID);}else{_stprintf_s(sql, 1024, _T("select * from PCList"));}SQLiteDataReader Reader = m_pSQLite->ExcuteQuery(sql);int index = 0;int len = 0;while (Reader.Read()){index++;std::wstring strContent = Reader.GetStringValue(1);contentList.push_back(strContent);}return index;}INT SQLiteImp::Count(LPCTSTR lpID){if (!m_pSQLite){return FALSE;}TCHAR sql[1024] = { 0 };_stprintf_s(sql, 1024, _T("select count(*) from PCList"));SQLiteDataReader Reader = m_pSQLite->ExcuteQuery(sql);while (Reader.Read()){std::wstring strName = Reader.GetName(0);int ncount = Reader.GetIntValue(0);}}BOOL SQLiteImp::UpDataGroupName(LPCTSTR lpGroupName, LPCTSTR lpNewGroupName){if (!m_pSQLite){return FALSE;}TCHAR sql[1024] = { 0 };_stprintf_s(sql, 1024, _T("update PCList set groupname = '%s' where groupname = '%s'"), lpNewGroupName, lpGroupName);if (!m_pSQLite->ExcuteNonQuery(sql)){return FALSE;}return TRUE;}