C++ sqlite数据库增删改查（基于sqlite3)
来源：互联网 发布：首届全球程序员节 编辑：程序博客网 时间：2024/05/19 13:57
1.下载sqlite3源码编译库
下载地址：http://download.csdn.net/download/sunxianliang1/9598664
可以直接用vs2013打开，然后编译成库
2.使用sqlite3库
1) sqlitebasev.h
#ifndef SQLITEBASEV_H#define SQLITEBASEV_H#include "sqlite3.h"#include <string.h>#include <mutex>#include <iostream>using namespace std;namespace sdkdbmodule {typedef struct qureyResult{ int nRow; int nColumn; int nIndex; char** pResult; qureyResult(int row, int column, char**p) { nRow = row; nColumn = column; pResult = NULL; nIndex = 0; if(nRow > 0) { pResult = p; } } ~qureyResult() { if(pResult) { sqlite3_free_table(pResult); } } bool next() { if(nIndex == 0) { if(nRow > 0) { ++nIndex; return true; }else{ return false; } }else{ if((nRow*nColumn) >= ((nIndex+1)*nColumn)) { ++nIndex; return true; }else{ return false; } } } char* value(char *pColumnName) { int index = nIndex*nColumn; for(int i = 0; i < nColumn; ++i) { if(strcmp(pColumnName,pResult[i]) == 0) { return pResult[index]; } ++index; } return NULL; }} Result;class SqliteBaseV{public: explicit SqliteBaseV(const string &pDBName, const string &pDbPath, int pVersion = 1); sqlite3* open(); void close(sqlite3 *p); //开启事务 bool transaction(sqlite3 *p); //提交事务 bool commitTransaction(sqlite3 *p); //回滚事物 bool rollbackTransaction(sqlite3 *p); bool Update(sqlite3 *p,const string &sql); Result *Query(sqlite3 *p,const string &sql); //需要子类实现 virtual void onCreate(sqlite3 *pSqlite3) = 0; virtual void onUpdate(sqlite3 *pSqlite3, int oldVer, int newVer) = 0; private: string dbName; string dbPath; int version; mutex m_lock; //获取数据库版本号 int getDbVersion(sqlite3 *p); //创建版本表 bool createDbVerTbl(sqlite3 *p,int version); //更新版本号 bool updateDbVerTbl(sqlite3 *p,int version);};}#endif // SQLITEBASEV_H2) sqlitebaseb.cpp
#include "sqlitebasev.h"#include <thread>#include <strstream>using namespace sdkdbmodule;SqliteBaseV::SqliteBaseV(const string &pDBName, const string &pDbPath, int pVersion) : dbName(pDBName), dbPath(pDbPath), version(pVersion){}sqlite3* SqliteBaseV::open(){ //处理错误 if(dbName.empty() || dbPath.empty() || version < 0) { string errMsg; errMsg.append("Param error dbName="); errMsg.append(dbName); errMsg.append(",dbPath="); errMsg.append(dbPath); errMsg.append(",version="); errMsg.append(PublicFunction::IntToString(version)); LOGI("[sqlite] SqliteBaseV open :%s",errMsg.c_str()); return NULL; } m_lock.lock(); string dbstr = dbPath+dbName; sqlite3 *pSqlite = NULL; int nRes = sqlite3_open(dbstr.c_str(), &pSqlite); if (nRes != SQLITE_OK) { LOGI("Open database fail:%s",sqlite3_errmsg(pSqlite)); return NULL; } bool result = false; //版本号 int curVer = getDbVersion(pSqlite); //版本表不存在 if(curVer < 0) { //数据库启动一个事物，返回true成功，返回false transaction(pSqlite); //创建版本表并把版本号插入进去 createDbVerTbl(pSqlite,version); onCreate(pSqlite); result = commitTransaction(pSqlite); if(result == false) { rollbackTransaction(pSqlite); LOGI("[sqlite] SqliteBaseV open:Create failed dbName=%s,msg=%s",dbName.c_str(),sqlite3_errmsg(pSqlite)); sqlite3_close(pSqlite); pSqlite = NULL; } } else if(curVer != version) { transaction(pSqlite); updateDbVerTbl(pSqlite,version); onUpdate(pSqlite, curVer, version); result = commitTransaction(pSqlite); if(result == false) { rollbackTransaction(pSqlite); LOGI("[sqlite] SqliteBaseV open:Create failed dbName=%s,msg=%s",dbName.c_str(),sqlite3_errmsg(pSqlite)); sqlite3_close(pSqlite); pSqlite = NULL; } } m_lock.unlock(); return pSqlite;}int SqliteBaseV::getDbVersion(sqlite3 *p){ int dbver = -1; char *errmsg = NULL; char **dbResult; int nRow = 0; int nColumn = 0; int index = 0; //开始查询数据库 int result = sqlite3_get_table(p, "SELECT * FROM DB_VER", &dbResult, &nRow, &nColumn, &errmsg ); if(result == SQLITE_OK) { index = nColumn; //前面说过 dbResult 前面第一行数据是字段名称，从 nColumn索引开始才是真正的数据 for(int i = 0; i < nRow ; i++ ) { for( int j = 0 ; j < nColumn; j++ ) { dbver = atoi(dbResult [index]); ++index; // dbResult 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示 break; } } } //释放内存 sqlite3_free(errmsg); sqlite3_free_table(dbResult); return dbver;}bool SqliteBaseV::transaction(sqlite3 *p){ int result = true; char *zErrorMsg = NULL; int ret = sqlite3_exec (p, "begin transaction" , 0 , 0 , &zErrorMsg ); // 开始一个事务 if(ret != SQLITE_OK) { LOGI("start transaction failed:%s",zErrorMsg); result = false; } sqlite3_free(zErrorMsg); return result;}bool SqliteBaseV::commitTransaction(sqlite3 *p){ int result = true; char *zErrorMsg = NULL; int ret = sqlite3_exec ( p , "commit transaction" , 0 , 0 , & zErrorMsg ); // 提交事务 if(ret != SQLITE_OK) { LOGI("commit transaction failed:%s",zErrorMsg); result = false; } sqlite3_free(zErrorMsg); return result;}bool SqliteBaseV::rollbackTransaction(sqlite3 *p){ int result = true; char *zErrorMsg = NULL; int ret = sqlite3_exec ( p , "rollback transaction" , 0 , 0 , & zErrorMsg ); if(ret != SQLITE_OK) { LOGI("rollback transaction failed:%s",zErrorMsg); result = false; } sqlite3_free(zErrorMsg); return result;}bool SqliteBaseV::createDbVerTbl(sqlite3 *p, int version){ bool result = true; char *zErrorMsg = NULL; // 创建表“DB_VER” int ret = sqlite3_exec ( p , "CREATE TABLE DB_VER(VERSION VARCHAR(10))" , 0 , 0 , & zErrorMsg ); if(ret == SQLITE_OK) { // 写入“版本号” string sql = "INSERT INTO DB_VER VALUES(" + PublicFunction::IntToString(version) + ")"; ret = sqlite3_exec ( p , sql.c_str() , 0 , 0 ,0); if(ret != SQLITE_OK) { sqlite3_exec (p , "DROP TABLE DB_VER" , 0 , 0 ,0); result = false; } }else{ result = false; } sqlite3_free(zErrorMsg); return result;}bool SqliteBaseV::updateDbVerTbl(sqlite3 *p, int version){ bool result = true; char *zErrorMsg = NULL; int ret = sqlite3_exec ( p , "DELETE FROM DB_VER" , 0 , 0 , & zErrorMsg ); if(ret == SQLITE_OK) { // 写入“版本号” string sql = "INSERT INTO DB_VER VALUES(" + PublicFunction::IntToString(version) + ")"; ret = sqlite3_exec ( p , sql.c_str() , 0 , 0 ,0); if(ret != SQLITE_OK) { result = false; } }else{ result = false; } sqlite3_free(zErrorMsg); return result;}void SqliteBaseV::close(sqlite3 *p){ sqlite3_close(p); p = NULL;}bool SqliteBaseV::Update(sqlite3 *p, const string &sql){ bool result = true; char *zErrorMsg = NULL; int ret = sqlite3_exec ( p , sql.c_str() , 0 , 0 , & zErrorMsg ); if(ret == SQLITE_OK) { //do nothing }else{ LOGI("[sqlite] SqliteBaseV Update error:dbName=%s,msg=%s sql:%s",dbName.c_str(),zErrorMsg,sql.c_str()); result = false; } sqlite3_free(zErrorMsg); return result;}Result *SqliteBaseV::Query(sqlite3 *p, const string &sql){ Result *pRe = NULL; char *errmsg = NULL; char **dbResult; int nRow = 0; int nColumn = 0; //开始查询数据库 int result = sqlite3_get_table(p, sql.c_str(), &dbResult, &nRow, &nColumn, &errmsg ); if(result == SQLITE_OK) { pRe = new Result(nRow,nColumn,dbResult); }else{ LOGI("[sqlite] SqliteBaseV Query error:dbName=%s,msg=%s sql:%s",dbName.c_str(),errmsg,sql.c_str()); } //释放内存 sqlite3_free(errmsg); return pRe;}注意：在sqlitebasev.cpp中把LOGI(...)打印日志的注释掉，然后把PbPublicFunction::IntToString(...)函数换掉即可使用。类SqliteBaseV纯虚类，必须子类化然后重写纯虚函数才能使用。这个类是线程安全的。
3.SqliteBaseV使用示例：
1)dbrecord.h
#ifndef DBRECORD_H#define DBRECORD_H#include <list>#include "sqlitebasev.h"#include "dbmanager.h"namespace sdkdbmodule {class DBRecord : public SqliteBaseV{public: static DBRecord* GetInstance(const string &pPath, int version); virtual void onCreate(sqlite3 *pSqlite3); virtual void onUpdate(sqlite3 *pSqlite3, int oldVer, int newVer); //四个模拟操作增、删、改、查 bool AddOneRecord(); bool DeleteRecord(); bool updateRecord(); bool GetOneRecord();private: DBRecord(const string &dbName, const string &pPath, int version); list<string> GetCreateTableSql(); list<string> GetClearDatabaseSql(); string GetInsertSqlForRecordTBL();private: mutex lock; const string m_msgRecordTblName; string m_strDbName; static mutex m_mutex; class CGarbo { public: ~CGarbo() { if (DBRecord::instance) { delete DBRecord::instance; DBRecord::instance = NULL; } } }; static CGarbo Garbo; static DBRecord* instance;};#define DBManag DBManager::GetInstance()}#endif // DBRECORD_H2)dbrecord.cpp
#include "dbchatrecord.h"#include <strstream>using namespace sdkdbmodule;mutex DBRecord::m_mutex;DBRecord* DBCRecord::instance = NULL;DBRecord::CGarbo DBRecord::Garbo;DBRecord* DBRecord::GetInstance(const string &pPath, int version){ if(NULL == instance) { m_mutex.lock(); if(NULL == instance) { instance = new DBRecord("record.db", pPath, version); } m_mutex.unlock(); } return instance;}DBRecord::DBRecord(const string &dbName, const string &pPath, int version): SqliteBaseV(dbName, pPath, version), m_msgRecordTblName("TBL_RECORD"), m_strDbName(dbName){}void DBRecord::onCreate(sqlite3 *pSqlite3){ list<string> sqlList = GetCreateTableSql(); for (const auto& sql : sqlList) { char *errmsg = NULL; int result = sqlite3_exec(pSqlite3, sql.c_str(), 0, 0, &errmsg ); if(result != SQLITE_OK) { LOGI("onCreate failed:%s sql:%s",errmsg,sql.c_str()); } sqlite3_free(errmsg); }}void DBRecord::onUpdate(sqlite3 *pSqlite3, int /*oldVer*/, int /*newVer*/){ list<string> clearSqlList = GetClearDatabaseSql(); for (const auto& sql : clearSqlList) { char *errmsg = NULL; int result = sqlite3_exec(pSqlite3, sql.c_str(), 0, 0, &errmsg ); if(result != SQLITE_OK) { LOGI("onUpdate failed:%s sql:%s",errmsg,sql.c_str()); } sqlite3_free(errmsg); } list<string> createSqlList = GetCreateTableSql(); for (const auto& sql : createSqlList) { char *errmsg = NULL; int result = sqlite3_exec(pSqlite3, sql.c_str(), 0, 0, &errmsg ); if(result != SQLITE_OK) { LOGI("onUpdate failed:%s sql:%s",errmsg,sql.c_str()); } sqlite3_free(errmsg); }}bool DBRecord::AddOneRecord(){ bool result = false; if (m_msgRecordTblName.empty()) { return result; } lock.lock(); sqlite3 *pSqlite3 = open(); if(pSqlite3) { if(transaction(pSqlite3)) { string sqlStrs = "SELECT * FROM " + m_msgRecordTblName + " WHERE ID='t1'"; Result *query = Query(pSqlite3, sqlStrs); if(query != NULL) { if (query->next()) { Update(pSqlite3, "DELETE FROM " + m_msgRecordTblName +" WHERE ID='t1'"); } //插入一条记录 string sqlStr = GetInsertSqlForRecordTBL(); Update(pSqlite3,sqlStr); delete query; query = NULL; } //提交事务--这里耗费时间大约200ms result = commitTransaction(pSqlite3); if(result){ }else{ rollbackTransaction(pSqlite3); } } close(pSqlite3); } lock.unlock(); return result;}bool DBRecord::DeleteRecord(){ bool result = false; if (m_msgRecordTblName.empty()) { return result; } lock.lock(); sqlite3 *pSqlite3 = open(); if(pSqlite3) { if(transaction(pSqlite3)) { Update(pSqlite3, "DELETE FROM " + m_msgRecordTblName + " WHERE ID='t1'"); //提交事务--这里耗费时间大约200ms result = commitTransaction(pSqlite3); if(result){ }else{ rollbackTransaction(pSqlite3); } } close(pSqlite3); } lock.unlock(); return result;}bool DBRecord::updateRecord(){ bool result = false; string sqlStr; if (m_msgRecordTblName.empty()) { return result; } lock.lock(); sqlite3 *pSqlite3 = open(); if(pSqlite3) { if(transaction(pSqlite3)) { sqlStr = "UPDATE " + m_msgRecordTblName + " SET " + "TEST_ONE='tt'" + ",TEST_TWO='ttt'" + " WHERE ID=" + "'t1'"; Update(pSqlite3, sqlStr); //提交事务 result = commitTransaction(pSqlite3); if(result){ }else{ rollbackTransaction(pSqlite3); } } close(pSqlite3); } lock.unlock(); return result;}bool DBRecord::GetOneRecord(){ bool result = false; string sqlStr; if (m_msgRecordTblName.empty()) { return result; } lock.lock(); sqlite3* pSqlite3 = open(); if(pSqlite3) { sqlStr = "SELECT * FROM " + m_msgRecordTblName + " WHERE ID='t1'"; Result *query = Query(pSqlite3, sqlStr); if(query) { result = true; if(query->next()) { string id = query->value("ID"); ..... } delete query; } } close(pSqlite3); lock.unlock(); return result;}list<string> DBRecord::GetCreateTableSql(){ list<string> sqlist; string sqlStr=""; sqlStr = "CREATE TABLE " + m_msgRecordTblName + "(" + "ID VARCHAR PRIMARY KEY" + ",TEST_ONE VARCHAR" + ",TEST_TWO VARCHAR" + ",TEST_THREE INTEGER" + ",TEST_FOUR VARCHAR" + ")"; sqlist.push_back(sqlStr); return sqlist;}list<string> DBRecord::GetClearDatabaseSql(){ list<string> sqlist; sqlist.push_back("DROP TABLE " + m_msgRecordTblName); return sqlist;}string DBRecord::GetInsertSqlForRecordTBL(){ //插入一条记录sql string sqlStr = "INSERT INTO " + m_msgRecordTblName + " VALUES" + "(" + "'t1'" + "," + "'t2'" + "," + "'t3'" + "," + PublicFunction::IntToString(1) + "," + "t5'" + ")"; return sqlStr;}3)dbmanager.h
#ifndef DBMANAGER_H#define DBMANAGER_Hnamespace sdkdbmodule {class DBManager{public: static DBManager* GetInstance(); DBChatRecord *GetDBRecord();private: class CGarbo // 它的唯一工作就是在析构函数中删除ReceiveMsgManagerThread的实例 { public: ~CGarbo() { if (DBManager::_instance) { delete DBManager::_instance; } } }; static CGarbo Garbo; // 定义一个静态成员，在程序结束时，系统会调用它的析构函数 static DBManager* _instance;private: static mutex g_mutex; DBManager();};}#endif // DBMANAGER_H4)dbmanager.cpp
#include "dbmanager.h"#include "dbchatrecord.h"#include <vector>using namespace sdkdbmodule;mutex DBManager::g_mutex;DBManager::CGarbo DBManager::Garbo;DBManager* DBManager::_instance = nullptr;DBManager *DBManager::GetInstance(){ if(NULL == _instance) { g_mutex.lock(); if(NULL == _instance) { _instance = new DBManager(); } g_mutex.unlock(); } return _instance;}DBRecord *DBManager::GetDBRecord(){ std::string result ="D://";这里必须是utf8编码 //10标示数据库版本号 return DBRecord::GetInstance(result,10);}DBManager::DBManager(){} 0 0
- C++ sqlite数据库增删改查（基于sqlite3)
- sqlite数据库增删改查
- SQLite数据库增删改查
- SQlite---SQLite数据库增删改查操作
- C语言操作sqlite数据库(增删改查)
- SQlite 数据库增删改查-C编程简单实例
- iOS object-c语言使用SQLite数据库增删改查
- 数据库：SQLite数据库的增删改查
- SQLite数据库---数据库的增删改查
- SQLite数据库的增删改查代码
- sqlite数据库之增删改查
- SQLite数据库增删改查操作
- android sqlite数据库增删改查
- SQLite数据库创建与增删查改
- SQLite数据库增删改查操作
- Android SQLite数据库增删改查
- Android SQLite数据库增删改查
- 十、SQLite数据库增删改查操作
- Mob短信验证
- POJ 1579 Function Run Fun
- 小学数学学习笔之-总结
- content = "IE=edge,chrome=1" 详解
- 多浏览器兼容性测试整体解决方案 F2etest
- C++ sqlite数据库增删改查（基于sqlite3)
- HDU4462 Scaring the Birds
- 海哥说的未来趋势、我最近遇到的问题
- 图片比较工具这几款比较好
- php自带加密解密函数
- java-汉语转拼音
- java.lang.NoClassDefFoundError:
- 数据结构实验之求二叉树后序遍历和层次遍历
- hibernate多列关联
