自己写的一个关于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, int argc, char** argv , char** azColName);private:BOOL MByteToWChar(LPCSTR lpcszStr, LPWSTR lpwszStr, DWORD dwSize);BOOL MByteToWChar1(LPCSTR lpcszStr, LPWSTR lpwszStr, DWORD dwSize);BOOL WCharToMByte(LPCWSTR lpcwszStr, LPSTR lpszStr, DWORD dwSize);BOOL WCharToMByte1(LPCWSTR lpcwszStr, LPSTR lpszStr, 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, LPWSTR lpwszStr, DWORD dwSize){// Get the required size of the buffer that receives the Unicode // string. DWORD dwMinSize;dwMinSize = MultiByteToWideChar (CP_ACP , 0, lpcszStr, -1, NULL, 0);if(dwSize < dwMinSize){return FALSE;}// Convert headers from ASCII to Unicode.MultiByteToWideChar (CP_ACP, 0, lpcszStr, -1, lpwszStr, dwMinSize); return TRUE;}BOOL CMySqlite::MByteToWChar1(LPCSTR lpcszStr, LPWSTR lpwszStr, DWORD dwSize){// Get the required size of the buffer that receives the Unicode // string. DWORD dwMinSize;dwMinSize = MultiByteToWideChar (CP_UTF8, 0, lpcszStr, -1, NULL, 0);if(dwSize < dwMinSize){return FALSE;}// Convert headers from ASCII to Unicode.MultiByteToWideChar (CP_UTF8, 0, lpcszStr, -1, lpwszStr, dwMinSize); return TRUE;}BOOL CMySqlite::WCharToMByte(LPCWSTR lpcwszStr, LPSTR lpszStr, DWORD dwSize){DWORD dwMinSize;dwMinSize = WideCharToMultiByte(CP_UTF8,NULL,lpcwszStr,-1,NULL,0,NULL,FALSE);if(dwSize < dwMinSize){return FALSE;}WideCharToMultiByte(CP_UTF8,NULL,lpcwszStr,-1,lpszStr,dwSize,NULL,FALSE);return TRUE;}BOOL CMySqlite::WCharToMByte1(LPCWSTR lpcwszStr, LPSTR lpszStr, DWORD dwSize){DWORD dwMinSize;dwMinSize = WideCharToMultiByte(CP_ACP,NULL,lpcwszStr,-1,NULL,0,NULL,FALSE);if(dwSize < dwMinSize){return FALSE;}WideCharToMultiByte(CP_ACP,NULL,lpcwszStr,-1,lpszStr,dwSize,NULL,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,remotedir1,MAX_PATH);WCharToMByte1(remotedir1,chremotedir1,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;}//创建新表格 如果已经存在则返回falsebool 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,CPLX,TGSJ,TCCBH,JCKBH,JCKLX,ZXD,TPID1,GZML)if (sqlite3_exec(m_pDB,buf, 0, 0,&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','%s', '%s','%s','%s', '%d','%s','%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, 0, 0, &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++){CStringqStr1;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, int argc, char** argv, char** azColName){CallbackStruct* callstruct = (CallbackStruct*)NotUsed;callstruct->listctrl->InsertItem(callstruct->nCount ,"",0);callstruct->listctrl->SetItemText(callstruct->nCount ,0,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++){CStringqStr1;qStr1.Format("SELECT * FROM /"%s/" WHERE CPHM != /"/"",it->c_str());if ( sqlite3_exec(m_pDB, qStr1, CallbackDelete, (void*)(&m_callstruct), &m_errMsg)!=SQLITE_OK){return -1;}//CStringqStr1;qStr1.Format("DELETE FROM /"%s/" WHERE CPHM != /"/"",it->c_str());if ( sqlite3_exec(m_pDB, qStr1, NULL, 0, &m_errMsg)!=SQLITE_OK){return -1;}}}catch( ... ){return -1;}return 0;}//删除过期数据中所用到的回调函数int CMySqlite::CallbackDelete(void* NotUsed, int argc, char** argv , 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",0,0,&m_errMsg);}//事务关闭int CMySqlite::commit(void){return sqlite3_exec(m_pDB,"COMMIT",0,0,&m_errMsg);}//获取最近的查询数据int CMySqlite::GetQueryCount(void){return m_callstruct.nCount;}

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