自己写的一个关于Sqlite3 操作的 的C++封装

这个封装主要包括 插入数据 删除过期数据，和查询指定数据，列出代码目的是为了 让刚接触的快速熟悉Sqlite的操作，和其中回调函数的使用

首先是头文件MySqlite.h

#pragma once #include <afxcmn.h> #include "sqlite3.h" #include <string> #include <vector> #include <list> #pragma comment(lib,"sqlite3") using namespace std; typedef struct CallbackStruct { int nCount; CListCtrl* listctrl; }CallbackStruct; class CMySqlite { public: CMySqlite(void); ~CMySqlite(void); public: bool GBConvertUTF8(CString strOld,CString& strNew); bool UTF8ConvertGB(CString strOld,CString& strNew); bool open(const char* strfilename); CString GetLastErrorMsg(void); bool CreateNewTable(const char* strTable); bool SetStructValue(TSFVD tsfvd); int close(void); int ExecuteQuery(QueryInfo qInfo); int GetTableList(vector<string>& vecTableName); int DeleteOutdateData(int nSaveDay); void SetClistCtrlList(CListCtrl* listctrl); int begin(void); //事务开启 int commit(void);// 事务结束 int GetQueryCount(void); static int Callback(void* NotUsed,int argc,char** argv,char** azColName); static int CallbackDelete(void* NotUsed,char** azColName); private: BOOL MByteToWChar(LPCSTR lpcszStr,LPWSTR lpwszStr,DWORD dwSize); BOOL MByteToWChar1(LPCSTR lpcszStr,DWORD dwSize); BOOL WCharToMByte(LPCWSTR lpcwszStr,LPSTR lpszStr,DWORD dwSize); BOOL WCharToMByte1(LPCWSTR lpcwszStr,DWORD dwSize); private: CallbackStruct m_callstruct; CString m_strfilename; // 表格名称 CString m_strTableName; sqlite3* m_pDB; char* m_errMsg; CString m_strdbName; };

执行文件MySqlite.cpp

#include "stdafx.h" #include "MySqlite.h" #include <algorithm> CMySqlite::CMySqlite(void) : m_pDB(NULL),m_errMsg(NULL),m_strdbName(_T("")),m_strTableName(_T("")),m_nCount(0),m_strfilename(_T("")) { } CMySqlite::~CMySqlite(void) { } bool CMySqlite::GBConvertUTF8(CString strOld,CString& strNew) { wchar_t remotedir1[MAX_PATH] ;//= {L".//图像测试文件夹//"}; char chremotedir1[MAX_PATH*2] = {0}; MByteToWChar((LPCSTR)strOld,remotedir1,MAX_PATH); WCharToMByte(remotedir1,chremotedir1,sizeof(chremotedir1)/sizeof(chremotedir1[0])); strNew = chremotedir1; return true; } BOOL CMySqlite::MByteToWChar(LPCSTR lpcszStr,DWORD dwSize) { // Get the required size of the buffer that receives the Unicode // string. DWORD dwMinSize; dwMinSize = MultiByteToWideChar (CP_ACP,lpcszStr,-1,NULL,0); if(dwSize < dwMinSize) { return FALSE; } // Convert headers from ASCII to Unicode. MultiByteToWideChar (CP_ACP,lpwszStr,dwMinSize); return TRUE; } BOOL CMySqlite::MByteToWChar1(LPCSTR lpcszStr,DWORD dwSize) { // Get the required size of the buffer that receives the Unicode // string. DWORD dwMinSize; dwMinSize = MultiByteToWideChar (CP_UTF8,0); if(dwSize < dwMinSize) { return FALSE; } // Convert headers from ASCII to Unicode. MultiByteToWideChar (CP_UTF8,dwMinSize); return TRUE; } BOOL CMySqlite::WCharToMByte(LPCWSTR lpcwszStr,DWORD dwSize) { DWORD dwMinSize; dwMinSize = WideCharToMultiByte(CP_UTF8,lpcwszStr,FALSE); if(dwSize < dwMinSize) { return FALSE; } WideCharToMultiByte(CP_UTF8,lpszStr,dwSize,FALSE); return TRUE; } BOOL CMySqlite::WCharToMByte1(LPCWSTR lpcwszStr,DWORD dwSize) { DWORD dwMinSize; dwMinSize = WideCharToMultiByte(CP_ACP,FALSE); if(dwSize < dwMinSize) { return FALSE; } WideCharToMultiByte(CP_ACP,FALSE); return TRUE; } bool CMySqlite::UTF8ConvertGB(CString strOld,CString& strNew) { wchar_t remotedir1[MAX_PATH] ;//= {L".//图像测试文件夹//"}; char chremotedir1[MAX_PATH*2] = {0}; MByteToWChar1((LPCSTR)strOld,MAX_PATH); WCharToMByte1(remotedir1,sizeof(chremotedir1)/sizeof(chremotedir1[0])); strNew = chremotedir1; return false; } //打开一个数据库 bool CMySqlite::open(const char* strfilename) { m_strfilename = strfilename; CString strUnicodeName; GBConvertUTF8(strfilename,strUnicodeName); int rc = sqlite3_open(strUnicodeName,&m_pDB); if (rc!= SQLITE_OK) { m_errMsg = (char*)sqlite3_errmsg(m_pDB); return false; } return true; } // 获取最近的出错信息 CString CMySqlite::GetLastErrorMsg(void) { CString errmsg = m_errMsg; m_errMsg = "No Error!"; return errmsg; } //创建新表格 如果已经存在则返回false bool CMySqlite::CreateNewTable(const char* strTable) { CString buf; buf.Format("CREATE TABLE /"%s/"(ID INTEGER PRIMARY KEY,CPHM TEXT,CPLX INTEGER," "TGSJ TEXT,TCCBH TEXT,JCKBH INTEGER,JCKLX INTEGER,ZXD INTEGER,TPID1 TEXT,GZML TEXT)",strTable); m_strTableName.Format("/"%s/"(CPHM,CPLX,TGSJ,TCCBH,JCKBH,JCKLX,ZXD,TPID1,GZML)",strTable);//(CPHM,GZML) if (sqlite3_exec(m_pDB,buf,&m_errMsg)==SQLITE_ERROR) { return false; } return true; } //插入 TSFVD结构数据，TSFVD为自行定义数据结构 bool CMySqlite::SetStructValue(TSFVD tsfvd) { CString strValue; strValue.Format("INSERT INTO %s VALUES('%s','%s','%d','%s')",m_strTableName,tsfvd.cphm,tsfvd.cplx,tsfvd.tgsj,tsfvd.tccid,tsfvd.jckbh,tsfvd.jcklx,tsfvd.zxd,tsfvd.tpid1,tsfvd.bl); if(sqlite3_exec(m_pDB,strValue,&m_errMsg) !=SQLITE_OK) { sqlite3_close(m_pDB); open(m_strfilename); return false; } m_nCount++; return true; } // 关闭数据库 int CMySqlite::close(void) { return sqlite3_close(m_pDB); } //查询信息为QueryInfo结构体的数据 int CMySqlite::ExecuteQuery(QueryInfo qInfo) { vector<string> vecTableName; vector<string> vecFinalTable; GetTableList(vecTableName); vector<string>::iterator iterTable = vecTableName.begin(); CString qStr; int nQFlag = 0; for (;iterTable !=vecTableName.end();iterTable++ ) { if (qInfo.m_bFlagTD) { if (iterTable->compare(qInfo.m_strDateStart)>=0 && iterTable->compare(qInfo.m_strDateEnd) <=0) { vecFinalTable.push_back(*iterTable); } } else { vecFinalTable.push_back(*iterTable); } } CString buffer1; if (qInfo.m_nTDBH>0) { buffer1.Format("%d",qInfo.m_nTDBH); qStr += "JCKBH=" + buffer1; nQFlag =1; } if (qInfo.m_bFlagTD) { qStr += "AND TGSJ > /"" +qInfo.m_strTimeStart + "/" AND TGSJ < /""+ qInfo.m_strTimeEnd +"/""; nQFlag =1; } if (qInfo.m_nJCKLX>0) { buffer1.Format("%d",qInfo.m_nJCKLX-1); qStr += "AND JCKLX=" + buffer1 ; nQFlag =1; } if (qInfo.m_strCPHM.GetLength() !=0) { if (qInfo.m_strCPHM.Find("%")>=0) { qStr += "AND CPHM LIKE /"" + qInfo.m_strCPHM +"/" "; //模糊查询 } else { qStr += "AND CPHM=/"" + qInfo.m_strCPHM +"/""; } nQFlag =1; } int nLXsize = qInfo.m_vecCPLX.size(); if (nLXsize <=10 && nLXsize>0) { nQFlag =1; qStr +="AND ("; for (int k = 0; k< nLXsize; k++) { qStr.Format("%sCPLX=%d OR ",qStr,qInfo.m_vecCPLX[k]); } } if (nLXsize==0) { qStr += "CPLX=-1 OR "; } if (nQFlag ==0) { AfxMessageBox("请输入查询项!"); return -3; } else { qStr.Delete(qStr.GetLength()-4,4); qStr += ")"; } if (qStr.Left(3) =="AND") { qStr.Delete(0,3); } m_callstruct.nCount = 0; for (int i =0; i < vecFinalTable.size(); i++) { CString qStr1; qStr1.Format("SELECT * FROM /"%s/" WHERE %s ORDER BY TGSJ",vecFinalTable[i].c_str(),qStr); if ( sqlite3_exec(m_pDB,qStr1,Callback,(void*)(&m_callstruct),&m_errMsg)!=SQLITE_OK) { return -1; } } return 0; } //获取表名 int CMySqlite::GetTableList(vector<string>& vecTableName) { CString sql; sql.Format("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"); int row,column; char** sqlresult; if (sqlite3_get_table(m_pDB,sql,&sqlresult,&row,&column,&m_errMsg) ==SQLITE_ERROR) { return -1; } m_nCount =0; for (int i = 1; i < row+1; i++) { vecTableName.push_back(sqlresult[i*column + 0]); } return 0; } //查询中的回调函数 int CMySqlite::Callback(void* NotUsed,char** azColName) { CallbackStruct* callstruct = (CallbackStruct*)NotUsed; callstruct->listctrl->InsertItem(callstruct->nCount,"",0); callstruct->listctrl->SetItemText(callstruct->nCount,argv[3]);//识别时间 CString jckbh; jckbh.Format("%s通道%s",argv[5],g_strChannelType[atoi(argv[6])]); callstruct->listctrl->SetItemText(callstruct->nCount,1,jckbh);//进出口编号 callstruct->listctrl->SetItemText(callstruct->nCount,2,argv[1]);//车牌号码 callstruct->listctrl->SetItemText(callstruct->nCount,3,g_strCarType[atoi(argv[2])]);//车牌类型 callstruct->listctrl->SetItemText(callstruct->nCount,4,argv[3]);//置信度 callstruct->listctrl->SetItemText(callstruct->nCount,5,argv[9]);//图像路径 callstruct->nCount++; // CListCtrl listctrl; // CMySqlite* psqlite = new CMySqlite; // psqlite->CallbcakProc(argc,argv); return 0; } //删除超过日期数据 int CMySqlite::DeleteOutdateData(int nSaveDay) { if (nSaveDay ==0) { return 0; } nSaveDay++; try { vector<string> vec_Talbename; vector<int> vec_nStartEnd; GetTableList(vec_Talbename); int nsize = vec_Talbename.size(); sort(vec_Talbename.begin(),vec_Talbename.end()); vector<string>::iterator itTabIndex = vec_Talbename.end()-1; while(nSaveDay) { if (itTabIndex != vec_Talbename.begin()) { itTabIndex--; } nSaveDay--; if (itTabIndex ==vec_Talbename.begin()&& nSaveDay>0) { return 0; } } itTabIndex++; for (vector<string>::iterator it = vec_Talbename.begin(); it != itTabIndex ;it++) { CString qStr1; qStr1.Format("SELECT * FROM /"%s/" WHERE CPHM != /"/"",it->c_str()); if ( sqlite3_exec(m_pDB,CallbackDelete,&m_errMsg)!=SQLITE_OK) { return -1; } // CString qStr1; qStr1.Format("DELETE FROM /"%s/" WHERE CPHM != /"/"",&m_errMsg)!=SQLITE_OK) { return -1; } } } catch( ... ) { return -1; } return 0; } //删除过期数据中所用到的回调函数 int CMySqlite::CallbackDelete(void* NotUsed,char** azColName) { CString strPicPath = argv[9]; CString strID = argv[8]; strID = strID.Left(8) + "//"; CString strTXTPath = strPicPath; strTXTPath.Replace(strID,"TXT//"); strTXTPath.Replace(".jpg",".txt"); if (remove(strTXTPath)==0) { remove(strPicPath); } return 0; } // 插入列表控件指针，用于显示 void CMySqlite::SetClistCtrlList(CListCtrl* listctrl) { m_callstruct.listctrl = listctrl; } //事务开启 int CMySqlite::begin(void) { return sqlite3_exec(m_pDB,"BEGIN",&m_errMsg); } //事务关闭 int CMySqlite::commit(void) { return sqlite3_exec(m_pDB,"COMMIT",&m_errMsg); } //获取最近的查询数据 int CMySqlite::GetQueryCount(void) { return m_callstruct.nCount; }

在执行操作时候并不会调用回调函数，所以利用查询到的数据删除对应的文本和图像，再利用删除SQL删除数据库中数据

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!