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_H
2) 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_H
2)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_H
namespace 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_H
4)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()
{
}