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

首先是头文件MysqLite.h

#pragma once #include <afxcmn.h> #include "<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3.h" #include <string> #include <vector> #include <list> #pragma comment(lib,"<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3") using namespace std; typedef struct CallbackStruct { int nCount; CListCtrl* listctrl; }CallbackStruct; class C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite { public: C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite(void); ~C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite(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; // 表格<a href="https://www.jb51.cc/tag/mingcheng/" target="_blank" class="keywords">名称</a> CString m_strTableName; <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3* m_pDB; char* m_errMsg; CString m_strdbName; };

执行文件MysqLite.cpp

#include "stdafx.h" #include "<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite.h" #include <algorithm> C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite(void) : m_pDB(NULL),m_errMsg(NULL),m_strdbName(_T("")),m_strTableName(_T("")),m_nCount(0),m_strfilename(_T("")) { } C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::~C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite(void) { } bool C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::GBConvertUTF8(CString strOld,CString& strNew) { wchar_t remotedir1[MAX_PATH] ;//= {L".//图像测试<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a>夹//"}; 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 C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::MByteToWChar(LPCSTR lpcszStr,DWORD dwSize) { // Get the <a href="https://www.jb51.cc/tag/required/" target="_blank" class="keywords">required</a> 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 C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::MByteToWChar1(LPCSTR lpcszStr,DWORD dwSize) { // Get the <a href="https://www.jb51.cc/tag/required/" target="_blank" class="keywords">required</a> 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 C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::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 C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::WCharToMByte1(LPCWSTR lpcwszStr,DWORD dwSize) { DWORD dwMinSize; dwMinSize = WideCharToMultiByte(CP_ACP,FALSE); if(dwSize < dwMinSize) { return FALSE; } WideCharToMultiByte(CP_ACP,FALSE); return TRUE; } bool C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::UTF8ConvertGB(CString strOld,CString& strNew) { wchar_t remotedir1[MAX_PATH] ;//= {L".//图像测试<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a>夹//"}; char chremotedir1[MAX_PATH*2] = {0}; MByteToWChar1((LPCSTR)strOld,MAX_PATH); WCharToMByte1(remotedir1,sizeof(chremotedir1)/sizeof(chremotedir1[0])); strNew = chremotedir1; return false; } //打开一个<a href="https://www.jb51.cc/tag/shujuku/" target="_blank" class="keywords">数据库</a> bool C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::open(const char* strfilename) { m_strfilename = strfilename; CString strUnicodeName; GBConvertUTF8(strfilename,strUnicodeName); int rc = <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_open(strUnicodeName,&m_pDB); if (rc!= <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { m_errMsg = (char*)<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_errmsg(m_pDB); return false; } return true; } // <a href="https://www.jb51.cc/tag/huoqu/" target="_blank" class="keywords">获取</a>最近的出错信息 CString C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::GetLastErrorMsg(void) { CString errmsg = m_errMsg; m_errMsg = "No Error!"; return errmsg; } //创建新表格 如果已经存在则返回false bool C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::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 (<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_exec(m_pDB,buf,&m_errMsg)==<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ITE_ERROR) { return false; } return true; } //插入 TSFVD结构数据，TSFVD为自行定义数据结构 bool C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::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(<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_exec(m_pDB,strValue,&m_errMsg) !=<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_close(m_pDB); open(m_strfilename); return false; } m_nCount++; return true; } // <a href="https://www.jb51.cc/tag/guanbi/" target="_blank" class="keywords">关闭</a><a href="https://www.jb51.cc/tag/shujuku/" target="_blank" class="keywords">数据库</a> int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::close(void) { return <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_close(m_pDB); } //<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" class="keywords">查询</a>信息为QueryInfo结构体的数据 int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::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 +"/" "; //模糊<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" class="keywords">查询</a> } 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) { AfxMessage<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>("请输入<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" class="keywords">查询</a>项!"); 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 ( <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_exec(m_pDB,qStr1,Callback,(void*)(&m_callstruct),&m_errMsg)!=<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { return -1; } } return 0; } //<a href="https://www.jb51.cc/tag/huoqu/" target="_blank" class="keywords">获取</a>表名 int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::GetTableList(vector<string>& vecTableName) { CString <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>; <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>.Format("SELECT name FROM <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite_master WHERE type='table' ORDER BY name"); int row,column; char** <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>result; if (<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_get_table(m_pDB,<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>,&<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>result,&row,&column,&m_errMsg) ==<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ITE_ERROR) { return -1; } m_nCount =0; for (int i = 1; i < row+1; i++) { vecTableName.push_back(<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>result[i*column + 0]); } return 0; } //<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" class="keywords">查询</a>中的回调<a href="https://www.jb51.cc/tag/hanshu/" target="_blank" class="keywords">函数</a> int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::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; // C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite* p<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite = new C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite; // p<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite->CallbcakProc(argc,argv); return 0; } //<a href="https://www.jb51.cc/tag/shanchu/" target="_blank" class="keywords">删除</a>超过日期数据 int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::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 ( <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_exec(m_pDB,CallbackDelete,&m_errMsg)!=<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { return -1; } // CString qStr1; qStr1.Format("DELETE FROM /"%s/" WHERE CPHM != /"/"",&m_errMsg)!=<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { return -1; } } } catch( ... ) { return -1; } return 0; } //<a href="https://www.jb51.cc/tag/shanchu/" target="_blank" class="keywords">删除</a>过期数据中所用到的回调<a href="https://www.jb51.cc/tag/hanshu/" target="_blank" class="keywords">函数</a> int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::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; } // 插入列表控件指针，用于<a href="https://www.jb51.cc/tag/xianshi/" target="_blank" class="keywords">显示</a> void C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::SetClistCtrlList(CListCtrl* listctrl) { m_callstruct.listctrl = listctrl; } //事务开启 int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::begin(void) { return <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_exec(m_pDB,"BEGIN",&m_errMsg); } //事务<a href="https://www.jb51.cc/tag/guanbi/" target="_blank" class="keywords">关闭</a> int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::commit(void) { return <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ite3_exec(m_pDB,"COMMIT",&m_errMsg); } //<a href="https://www.jb51.cc/tag/huoqu/" target="_blank" class="keywords">获取</a>最近的<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" class="keywords">查询</a>数据 int C<a href="https://www.jb51.cc/tag/MysqL/" target="_blank" class="keywords">MysqL</a>ite::GetQueryCount(void) { return m_callstruct.nCount; }

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