1. 头文件
#pragma once #include <string> #include <sstream> using namespace std; #include "<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite/<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3.h" #pragma comment(lib,"<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite/<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3.lib") class CResultSet { public: friend class C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr; public: CResultSet(); ~CResultSet(); public: int GetColumnNum(); // 获得结果数目 int GetRecordCount(); public: int GetInt(int row,int column); string GetString(int row,int column); private: // <a href="/tag/huoqu/" target="_blank" class="keywords">获取</a>对应行列的结果 char * GetAt(int row,int column); private: char **szResult; int nRow; int nColumn; }; class C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr { public: C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr(string filename); ~C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr(); public: int Execute(const char *<a href="/tag/sql/" target="_blank" class="keywords">sql</a>); int Execute(string <a href="/tag/sql/" target="_blank" class="keywords">sql</a>); CResultSet * ExecuteQuery(string <a href="/tag/sql/" target="_blank" class="keywords">sql</a>); public: void FreeResultSet(CResultSet *rs); public: void Begin(); void Commit(); void Rollback(); private: void print_error(char *error); private: <a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3 *m_conn; };
2. 源文件
#include "stdafx.h" #include "<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr.h" CResultSet::CResultSet() { } CResultSet::~CResultSet() { ::<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3_free_table(szResult); } int CResultSet::GetColumnNum() { return nColumn; } int CResultSet::GetRecordCount() { return nRow; } int CResultSet::GetInt(int row,int column) { return atoi(GetAt(row,column)); } string CResultSet::GetString(int row,int column) { return string(GetAt(row,column)); } char * CResultSet::GetAt(int row,int column) { return szResult[(row + 1) * nColumn + column]; } C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr(string filename) { int nRet = ::<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3_open(filename.c_str(),&m_conn); if (nRet != <a href="/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { print_error("<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3_open fail"); } } C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::~C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr() { ::<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3_close(m_conn); } int C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::Execute(const char *<a href="/tag/sql/" target="_blank" class="keywords">sql</a>) { char *error_msg = NULL; int nRet = ::<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3_exec(m_conn,<a href="/tag/sql/" target="_blank" class="keywords">sql</a>,(char **) &error_msg); if (nRet != <a href="/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { print_error(error_msg); return -1; } return nRet; } int C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::Execute(string <a href="/tag/sql/" target="_blank" class="keywords">sql</a>) { return Execute(<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.c_str()); } CResultSet * C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::ExecuteQuery(string <a href="/tag/sql/" target="_blank" class="keywords">sql</a>) { char *error_msg = NULL; CResultSet *pRs = new CResultSet; int nRet = ::<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3_get_table(m_conn,<a href="/tag/sql/" target="_blank" class="keywords">sql</a>.c_str(),&pRs->szResult,&pRs->nRow,&pRs->nColumn,(char **) &error_msg); if (nRet != <a href="/tag/sql/" target="_blank" class="keywords">sql</a>ITE_OK) { print_error(error_msg); return NULL; } return pRs; } void C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::FreeResultSet(CResultSet *rs) { if (rs != NULL) { delete rs; rs = NULL; } } void C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::Begin() { Execute("BEGIN TRANSACTION"); } void C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::Commit() { Execute("COMMIT TRANSACTION"); } void C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::Rollback() { Execute("ROLLBACK TRANSACTION"); } void C<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr::print_error(char *error) { char szError[128]; memset(szError,128); sprintf(szError,"<a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite error: %s/n",error); OutputDebugStringA(szError); <a href="/tag/sql/" target="_blank" class="keywords">sql</a>ite3_free(error); }
3. 测试代码
#include <vector> #include <string> using namespace std; #include "<a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr.h" #include "Tools.h" struct cRecord { int id; string name; int age; }; int _tmain(int argc,_TCHAR* argv[]) { <a href="/tag/sql/" target="_blank" class="keywords">sql</a>iteMgr mgr("test.db"); if (false) { mgr.Execute("create table test([id] integer primary key autoincrement,[name] text,[age] int);"); char buf[256]; memset(buf,256); for (int i = 0; i < 100; ++i) { sprintf(buf,"insert into test(name,age) values(/"tom%d/",%d)",i + 1,i + 20); mgr.Execute(buf); } } else { ResultSet *rs = mgr.ExecuteQuery("select id,name,age from test"); vector<cRecord> records; for (int i = 0; i < rs->GetRecordCount(); ++i) { cRecord record; record.id = rs->GetInt(i,0); record.name = CTools::utf8_2_ansi(rs->GetString(i,1).c_str()); record.age = rs->GetInt(i,2); records.push_back(record); } mgr.FreeResultSet(rs); } return 0; }
