SQLite 是 D. Richard Hipp 用 C 语言编写的开源嵌入式数据库引擎。它是完全独立的,不具有外部依赖性。 我下面提供一个Sqlite数据库包装类,供读者在相应的场合使用。
SQLite的官方网站下载地址是 http://www.sqlite.org/download.html,有兴趣的读者可以到这上面去下载最新的版本。
同时,要关注SQLite处理语句与标准SQL稍有不同的几个地方(比如Top语句等),具体的可以去网上搜索。
我的EMAIL是 wxy3064one@163.com
(一) 头文件 h
#ifndef _Q_MEMORY_DB_H #define _Q_MEMORY_DB_H
#ifdef __cplusplus extern "C" { #endif #ifdef _WIN32 #pragma comment(lib,"lib/sqlite3.lib") #include "include/sqlite3.h" #else #include <sqlite3.h> #define SQLITE_THREADSAFE 1//支持多线程 #endif
#ifdef __cplusplus } #endif
#define BS_LINE_TABLE_NAME "T_BL" #define BS_LINE_DROP_SQL "drop table T_BL;" #define BS_LINE_CREAT_SQL "create table T_BL(SQLITE_ID integer primary key,/ LON real,/ LAT real,/ CELLID integer,/ LAC integer); " #define BS_LINE_INSERT_SQL "insert into T_BL values(?,?,?); " #define BS_LINE_CREATE_INDEX_CI_LAC_SQL "create index idx_cellid_lac on T_BL(CELLID,LAC);"
class CMemDB { private: bool InMemory; int nTimeOut; bool eof; sqlite3 *db; sqlite3_stmt* stmt; int sql_code; int total_rows,total_cols; void Finalize(); void Close(); bool ExistTable(CString tbName); public:
enum MemDBFieldType{ft_INT = 1,ft_FLOAT,ft_TEXT,ft_BLOB,ft_NULL};
CMemDB(int ATimeOut=60000); virtual ~CMemDB();
void CreateMemoryDB(); const char* GetErrMsg(); //使用sqlite3_stmt //执行带绑定参数的SQL语句 void PrepareSQL(CString szSQL); bool Bind(int nCol,const char* szText); bool Bind(int nCol,const int nValue); bool Bind(int nCol,const double dwValue); bool Bind(int nCol,const BYTE* vBlob,int nLen); bool BindNULL(int nCol); int Exec(); //执行SQL语句(有返回结果) int ExecSQL(CString szSQL); //直接执行(无返回结果) int ExecCallBackSQL(CString szSQL,sqlite3_callback func = 0,void* para= NULL);//带回调函数的SQL //直接执行END //字段操作 int FindIndex(CString szField); const char* FieldName(int nCol); const int GetFieldType(CString szField); const int GetFieldType(int nCol); bool FieldIsNULL(CString szField); bool FieldIsNULL(int nCol); int GetIntFieldByName(CString szField,int nDefault=0); int GetIntFieldByIndex(int nCol,int nDefault=0); double GetDoubleFieldByName(CString szField,double fDefault=0.00); double GetDoubleFieldByIndex(int nCol,double fDefault=0.00); const char* GetTextFieldByName(CString szField,const char* sDefault=""); const char* GetTextFieldByIndex(int nCol,const char* sDefault=""); const BYTE* GetBlobFieldByName(CString szField,int *nLen); const BYTE* GetBlobFieldByIndex(int nCol,int *nLen); //行列操作 bool Eof(); void Reset(); int Next(); int GetCols(); //使用sqlite3_stmt END //Other Operation bool BeginTran(); bool CommitTran(); bool RollbackTran(); void setTimeOut(int ATimeOut); void Interrupt(); sqlite_int64 GetLastRowID(); //特殊应用 protected: CString CurTableName;
public: //针对基站和DT的拉线 bool FindBLKey(CString ACIValue,CString ALACValue); void CreateBLTable();
//读取shape文件的dbf(主要是点数据的文本内容,线和面一般不做处理) bool FindKey(CString szID); CString GetValue(CString oid,int nFieldIndex); void AddDBFromFile(CString dbFileName);
}; #endif
(二) 实现文件
#include "stdafx.h" #include "kml_baseclass.h" #include "MapFileReader.h" #include "MemDB.h"
CMemDB::CMemDB(int ATimeOut) { CurTableName = ""; db = NULL; stmt = NULL;eof = false; InMemory = false; total_cols = -1;total_rows = -1;; sql_code = sqlite3_open(":memory:",&db); if (sql_code == SQLITE_OK) InMemory = true; setTimeOut(ATimeOut); } CMemDB::~CMemDB() { Close(); }
void CMemDB::CreateMemoryDB() { Close(); sql_code = sqlite3_open(":memory:",&db); if (sql_code == SQLITE_OK) InMemory = true; } void CMemDB::Finalize() { if (stmt) { sql_code = sqlite3_finalize(stmt); stmt = NULL; } } void CMemDB::Close() { Finalize(); if (db) { sqlite3_close(db); db = NULL; } InMemory = false; }
bool CMemDB::ExistTable(CString tbName) { bool ret = false; if (db) { if (ExecSQL("select count(*) from sqlite_master where type='table' and name='"+tbName+"'") != -1) { if (GetIntFieldByIndex(0)==1) { ret =true; } } } return ret; }
const char* CMemDB::GetErrMsg() { return db?sqlite3_errmsg(db):""; }
int CMemDB::ExecCallBackSQL(CString szSQL,sqlite3_callback func,void* para) { int ret = -1; char* szMsg = NULL; if (db) { sql_code = sqlite3_exec(db,AnsiToUtf8(szSQL),func,para,&szMsg); if (sql_code == SQLITE_OK) ret = 0; if (szMsg != NULL) { sqlite3_free(szMsg); szMsg = NULL; } } return ret; } void CMemDB::PrepareSQL(CString szSQL) { Finalize(); if ((sql_code = sqlite3_prepare(db,-1,&stmt,0)) != SQLITE_OK) { stmt = NULL; } } bool CMemDB::Bind(int nCol,const char* szText) { bool bRet = false; if (stmt != NULL) { if ((sql_code =sqlite3_bind_text(stmt,nCol+1,szText,SQLITE_TRANSIENT)) == SQLITE_OK) bRet = true; } return bRet; } bool CMemDB::Bind(int nCol,const int nValue) { bool bRet = false; if (stmt != NULL) { if ((sql_code =sqlite3_bind_int(stmt,nValue)) == SQLITE_OK) bRet = true; } return bRet; } bool CMemDB::Bind(int nCol,const double dwValue) { bool bRet = false; if (stmt != NULL) { if ((sql_code =sqlite3_bind_double(stmt,dwValue)) == SQLITE_OK) bRet = true; } return bRet; } bool CMemDB::Bind(int nCol,int nLen) { bool bRet = false; if (stmt != NULL) { if ((sql_code = sqlite3_bind_blob(stmt,(const void*)vBlob,nLen,SQLITE_TRANSIENT)) == SQLITE_OK) bRet = true; } return bRet; } bool CMemDB::BindNULL(int nCol) { bool bRet = false; if (stmt != NULL) { if ((sql_code =sqlite3_bind_null(stmt,nCol+1)) == SQLITE_OK) bRet = true; } return bRet; } int CMemDB::Exec() { int ret = -1; if (stmt != NULL && db != NULL) { if ((sql_code = sqlite3_step(stmt)) == SQLITE_DONE) { ret = sqlite3_changes(db); } //sql_code = sqlite3_reset(stmt); } return ret; } int CMemDB::ExecSQL(CString szSQL) { int ret = -1; if (db) { PrepareSQL(szSQL); if (stmt) { sql_code = sqlite3_step(stmt); if (sql_code == SQLITE_DONE) { eof = true; //ret = 0; }else if (sql_code == SQLITE_ROW) { eof = false; ret = 0; }else { Finalize(); total_rows = 0; total_cols = 0; } if (stmt) { total_cols = sqlite3_column_count(stmt); } } } return ret; }
int CMemDB::FindIndex(CString szField) { int index = -1; CString tmpstr,tmpfield; if (stmt != NULL) { tmpfield = szField; tmpfield.TrimLeft(); tmpfield.TrimRight(); if (tmpfield != "") { for(int i = 0;i<total_cols;i++) { const char* sztmp = sqlite3_column_name(stmt,i); tmpstr = sztmp; tmpstr.MakeUpper(); if ( sztmp != 0 && strcmp((LPCTSTR)tmpfield,(LPCTSTR)tmpstr)==0) { index = i; break; } } } } return index; } const char* CMemDB::FieldName(int nCol) { if (!stmt) return NULL; if (!(nCol <0 || nCol > total_cols - 1)) return sqlite3_column_name(stmt,nCol); else return NULL; } const int CMemDB::GetFieldType(CString szField) { int index = -1; if (stmt == NULL) return index; index = FindIndex(szField); if (index == -1) return index; else return sqlite3_column_type(stmt,index); } const int CMemDB::GetFieldType(int nCol) { int index = -1; if (stmt == NULL) return index; if (nCol <0 || nCol > total_cols - 1) return index; else return sqlite3_column_type(stmt,nCol); } bool CMemDB::FieldIsNULL(CString szField) { int index; bool bRet = true; if ((index = GetFieldType(szField)) != -1) bRet = (index == SQLITE_NULL); return bRet; } bool CMemDB::FieldIsNULL(int nCol) { int index; bool bRet = true; if ((index = GetFieldType(nCol)) != -1) bRet = (index == SQLITE_NULL); return bRet; } int CMemDB::GetIntFieldByName(CString szField,int nDefault) { if (!stmt) return nDefault; else{ int index = FindIndex(szField); if (index == -1) return nDefault; if (FieldIsNULL(szField)) return nDefault; return sqlite3_column_int(stmt,index); } } int CMemDB::GetIntFieldByIndex(int nCol,int nDefault) { if (!stmt) return nDefault; else{ if (FieldIsNULL(nCol)) return nDefault; return sqlite3_column_int(stmt,nCol); } } double CMemDB::GetDoubleFieldByName(CString szField,double fDefault) { if (!stmt) return fDefault; else{ int index = FindIndex(szField); if (index == -1) return fDefault; if (FieldIsNULL(szField)) return fDefault; return sqlite3_column_double(stmt,index); } } double CMemDB::GetDoubleFieldByIndex(int nCol,double fDefault) { if (!stmt) return fDefault; else{ if (FieldIsNULL(nCol)) return fDefault; return sqlite3_column_double(stmt,nCol); } } const char* CMemDB::GetTextFieldByName(CString szField,const char* sDefault) { if (!stmt) return sDefault; else{ int index = FindIndex(szField); if (index == -1) return sDefault; if (FieldIsNULL(szField)) return sDefault; return (const char*)sqlite3_column_text(stmt,index); } } const char* CMemDB::GetTextFieldByIndex(int nCol,const char* sDefault) { if (!stmt) return sDefault; else{ if (FieldName(nCol) == NULL) return sDefault; if (FieldIsNULL(nCol)) return sDefault; return (const char*)sqlite3_column_text(stmt,nCol); } } const BYTE* CMemDB::GetBlobFieldByName(CString szField,int *nLen) { BYTE* pData = 0; *nLen = 0; if (!FieldIsNULL(szField)) { int index = FindIndex(szField); if (index != -1) { *nLen = sqlite3_column_bytes(stmt,index); pData = (BYTE*)sqlite3_column_blob(stmt,index); } } return pData; } const BYTE* CMemDB::GetBlobFieldByIndex(int nCol,int *nLen) { BYTE* pData = 0; *nLen = 0; if (!FieldIsNULL(nCol)) { *nLen = sqlite3_column_bytes(stmt,nCol); pData = (BYTE*)sqlite3_column_blob(stmt,nCol); } return pData; } bool CMemDB::Eof() { return eof; } void CMemDB::Reset() { if(stmt) { eof = !(sqlite3_reset(stmt)==SQLITE_OK); } } int CMemDB::Next() { int ret = -1; if (stmt) { sql_code = sqlite3_step(stmt); if (sql_code == SQLITE_DONE) { eof = true; ret = 0; }else if (sql_code == SQLITE_ROW) { eof = false; ret = 0; }else { Finalize(); } } return ret; } int CMemDB::GetCols() { return total_cols; } bool CMemDB::BeginTran() { return (ExecCallBackSQL("begin transaction;")!=-1); } bool CMemDB::CommitTran() { return (ExecCallBackSQL("commit transaction;")!=-1); } bool CMemDB::RollbackTran() { return (ExecCallBackSQL("rollback transaction;")!=-1); } void CMemDB::setTimeOut(int ATimeOut) { if (db) { nTimeOut = ATimeOut; sqlite3_busy_timeout(db,nTimeOut); } } void CMemDB::Interrupt() { if (db) { sqlite3_interrupt(db); } }
sqlite_int64 CMemDB::GetLastRowID() { return sqlite3_last_insert_rowid(db); }
void CMemDB::CreateBLTable() { if (ExistTable(BS_LINE_TABLE_NAME)) { ExecCallBackSQL(BS_LINE_DROP_SQL); } ExecCallBackSQL(BS_LINE_CREAT_SQL); ExecCallBackSQL(BS_LINE_CREATE_INDEX_CI_LAC_SQL); CurTableName = BS_LINE_TABLE_NAME; }
bool CMemDB::FindBLKey(CString ACIValue,CString ALACValue) { CString szSQL; bool ret = false; if (db && CurTableName.IsEmpty() == false) { szSQL = "select * from " + CurTableName + " where CELLID="+ACIValue+" and LAC="+ALACValue + " limit 0,1"; if (ExecSQL(szSQL) == 0) ret = true; } return ret; }
bool CMemDB::FindKey(CString szID) { CString szSQL; bool ret = false; if (db && CurTableName.IsEmpty() == false) { szSQL = "select * from " + CurTableName + " where SQLITE_ID ="+szID; if (ExecSQL(szSQL) == 0) ret = true; } return ret; }
CString CMemDB::GetValue(CString oid,int nFieldIndex) { CString ret = ""; if (FindKey(oid)) { switch(GetFieldType(nFieldIndex)) { case CMemDB::ft_TEXT: { ret = GetTextFieldByIndex(nFieldIndex); }break; case CMemDB::ft_INT: ret = IntToStr(GetIntFieldByIndex(nFieldIndex)); break; case CMemDB::ft_FLOAT:ret = FloatToStr(GetDoubleFieldByIndex(nFieldIndex)); break; default:ret = "";break; } } return ret; }
void CMemDB::AddDBFromFile(CString dbFileName) { int i,j; int NumRecs,FieldCount; CString fname,ext; CString dbSelectSQL,dbCreateSQL,dbInsertSQL; CDBFFileReader* dbFile; if (dbFileName.IsEmpty()==false) { if (!InMemory) CreateMemoryDB(); dbFile = new CDBFFileReader(); if (dbFile->Open(dbFileName)) { fname = ExtractFileName(dbFileName); ext = ExtractFileExt(dbFileName); fname = fname.Mid(0,fname.GetLength() - ext.GetLength()); FieldCount = dbFile->GetFieldCount(); dbSelectSQL = "select * from "+fname+";"; if (!ExistTable(fname)) { dbCreateSQL = "create table "+fname+" (SQLITE_ID integer primary key "; dbInsertSQL = "insert into "+fname; for(i=0;i<FieldCount;i++) { switch(dbFile->GetFieldTypeByIndex(i)) { case 'C':{ dbCreateSQL += ","+dbFile->GetFieldNameByIndex(i)+" char("+IntToStr(dbFile->GetFieldLenByIndex(i))+")"; }break; case 'F':{ dbCreateSQL += ","+dbFile->GetFieldNameByIndex(i)+" real "; }break; case 'N':{ dbCreateSQL += ","+dbFile->GetFieldNameByIndex(i)+" integer "; }break; case 'D':{ dbCreateSQL += ","+dbFile->GetFieldNameByIndex(i)+" char(8)";//date }break; case 'L':{ dbCreateSQL += ","+dbFile->GetFieldNameByIndex(i)+" char(1)"; }break; case 'M': default:break; } } dbCreateSQL += ");"; dbInsertSQL+=" values(?"; for(i=0;i<FieldCount;i++) dbInsertSQL+=",?"; dbInsertSQL += ");"; ExecCallBackSQL(dbCreateSQL); } CurTableName = fname;
j = 0;NumRecs = dbFile->GetRecordCount();BeginTran();while(j<NumRecs){PrepareSQL(dbInsertSQL);Bind(0,j+1);for(i=0;i<FieldCount;i++){CString str = Trim(dbFile->ReadAttr(j+1,i));switch(dbFile->GetFieldTypeByIndex(i)){case 'C':case 'D':case 'L':{Bind(i+1,(LPCTSTR)str);}break;case 'F':{Bind(i+1,StrToFloat(str));}break;case 'N':{Bind(i+1,StrToInt(str));}break;case 'M':default:break;} }Exec();j++;if (j%1000==0){CommitTran();BeginTran();}}CommitTran();ExecSQL(dbSelectSQL);}delete dbFile;}} (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|