头文件
#pragma once
#define SQLITE_HAS_CODEC
#include "sqlite\sqlite3.h"
#include "CParamBind.h"
#include <string>
#include <iostream>
#include <map>
#include <vector>
#include <unordered_map>
#include <codecvt>
using std::map;
using std::vector;
using std::unordered_map;
using std::string;
using std::cout;
using std::endl;
typedef vector<std::map<std::string, std::string>> SqliteSelectResult;
class Sqlite
{
public:
Sqlite(const std::string& strFileName);
~Sqlite();
bool Exec(const std::string& sql);
bool Query(const std::string& sql, SqliteSelectResult& selectResult);
bool Begin();
bool Commit();
bool Vacuum();
public:
bool Open();
bool Close();
static int select_cb(void* data, int cNum, char* column_values[], char* column_names[]);
public:
static std::string utf8_to_gbk(const std::string& str);
static std::string gbk_to_utf8(const std::string& str);
private:
sqlite3* m_sqlite;
std::string m_passwd;
std::string dbFileName;
};
class SigleSqlite
{
public:
~SigleSqlite() { instance = nullptr; }
static Sqlite* GetInstance()
{
if (instance == nullptr)
instance = new Sqlite("xxxxxxx.db");
return instance;
}
static void Destroy()
{
if (instance != nullptr)
{
delete instance;
}
}
private:
static Sqlite* instance;
};
cpp
#include <Windows.h>
#include "Sqlite.h"
#include "CGoogleLog.h"
#pragma comment(lib,"sqlite3_x64.lib")
Sqlite* SigleSqlite::instance = nullptr;
Sqlite::Sqlite(const std::string& strFileName)
{
m_passwd = "20230427";
dbFileName = strFileName;
Open();
}
Sqlite::~Sqlite()
{
Close();
}
bool Sqlite::Open()
{
string errMsg = "";
int ret = sqlite3_open(dbFileName.c_str(), &m_sqlite);
if (SQLITE_OK == ret)
{
if (SQLITE_OK == sqlite3_key(m_sqlite, m_passwd.c_str(), m_passwd.size()))
{
LOG(INFO) << "sqlite3_key success!";
return true;
}
}
errMsg = sqlite3_errmsg(m_sqlite);
LOG(FATAL) << "sqlite3_open error: " << errMsg;
return false;
}
bool Sqlite::Exec(const std::string& sql)
{
LOG(INFO) << utf8_to_gbk(sql);
string errMsg = "";
char* cerrMsg = nullptr;
int ret = sqlite3_exec(m_sqlite, sql.c_str(), 0, 0, &cerrMsg);
if (SQLITE_OK == ret)
{
return true;
}
errMsg = cerrMsg;
sqlite3_free(cerrMsg);
int retryTimes = 0;
while (retryTimes < 5 && errMsg == "database is locked")
{
Sleep(100 * (retryTimes + 1));
int ret = sqlite3_exec(m_sqlite, sql.c_str(), 0, 0, &cerrMsg);
if (SQLITE_OK == ret)
{
errMsg = " retryTimes: " + std::to_string(retryTimes + 1);
LOG(ERROR) << utf8_to_gbk(sql) << ": " << errMsg;
return true;
}
errMsg = cerrMsg;
sqlite3_free(cerrMsg);
retryTimes++;
}
return false;
}
int Sqlite::select_cb(void* data, int cNum, char* column_values[], char* column_names[])
{
SqliteSelectResult* selectResult = static_cast<SqliteSelectResult*>(data);
map<string, string> record;
for (int i = 0; i < cNum; i++)
{
if (column_values[i])
{
record[column_names[i]] = column_values[i];
}
else
{
record[column_names[i]] = "NULL";
}
}
selectResult->push_back(record);
return 0;
}
std::string Sqlite::utf8_to_gbk(const std::string& str)
{
std::wstring_convert<std::codecvt_utf8<wchar_t> > conv;
std::wstring tmp_wstr = conv.from_bytes(str);
const char* GBK_LOCALE_NAME = ".936";
std::wstring_convert<std::codecvt_byname<wchar_t, char, mbstate_t>> convert(new std::codecvt_byname<wchar_t, char, mbstate_t>(GBK_LOCALE_NAME));
return convert.to_bytes(tmp_wstr);
}
std::string Sqlite::gbk_to_utf8(const std::string& str)
{
const char* GBK_LOCALE_NAME = ".936";
std::wstring_convert<std::codecvt_byname<wchar_t, char, mbstate_t>> convert(new std::codecvt_byname<wchar_t, char, mbstate_t>(GBK_LOCALE_NAME));
std::wstring tmp_wstr = convert.from_bytes(str);
std::wstring_convert<std::codecvt_utf8<wchar_t>> cv2;
return cv2.to_bytes(tmp_wstr);
}
bool Sqlite::Query(const std::string& sql, SqliteSelectResult& selectResult)
{
LOG(INFO) << sql;
std::string errMsg = "";
char* cerrMsg = nullptr;
int ret = sqlite3_exec(m_sqlite, sql.c_str(), Sqlite::select_cb, (void*)&selectResult, &cerrMsg);
if (SQLITE_OK == ret)
{
return true;
}
errMsg = cerrMsg;
if (cerrMsg != nullptr)
{
sqlite3_free(cerrMsg);
}
int retryTimes = 0;
while (retryTimes < 5 && errMsg == "database is locked")
{
Sleep(100 * (retryTimes + 1));
int ret = sqlite3_exec(m_sqlite, sql.c_str(), Sqlite::select_cb, (void*)&selectResult, &cerrMsg);
if (SQLITE_OK == ret)
{
errMsg = " retryTimes: " + std::to_string(retryTimes + 1);
LOG(ERROR) << sql << ": " << errMsg;
return true;
}
errMsg = cerrMsg;
sqlite3_free(cerrMsg);
retryTimes++;
}
LOG(ERROR) << sql << ": " << errMsg;
return false;
}
bool Sqlite::Close()
{
if (SQLITE_OK == sqlite3_close(m_sqlite))
{
LOG(INFO) << "sqlite3_close success";
return true;
}
else
{
LOG(INFO) << "sqlite3_close failed";
return false;
}
}
bool Sqlite::Begin()
{
std::string errMsg;
char* cerrMsg = nullptr;
int rc = sqlite3_exec(m_sqlite, "begin;", NULL, 0, &cerrMsg);
if (rc != SQLITE_OK)
{
errMsg = cerrMsg;
sqlite3_free(cerrMsg);
LOG(ERROR) << "begin: " << errMsg;
return false;
}
return true;
}
bool Sqlite::Commit()
{
std::string errMsg;
char* cerrMsg = nullptr;
int rc = sqlite3_exec(m_sqlite, "commit;", NULL, 0, &cerrMsg);
if (rc != SQLITE_OK)
{
errMsg = cerrMsg;
sqlite3_free(cerrMsg);
LOG(ERROR) << "commit: " << errMsg;
return false;
}
return true;
}
bool Sqlite::Vacuum()
{
return Exec("VACUUM");
}
sql绑定代码CParamBind.h
#pragma once
#ifndef __C_PARAM_BIND_H__
#define __C_PARAM_BIND_H__
#include <iostream>
#include <string>
using namespace std;
namespace ParamBind
{
template <typename T>
typename std::enable_if<std::is_same<char*, T>::value || std::is_same<const char*, T>::value>::type
ReplaceSlot(std::string& strStateMent, T t)
{
int nPos = strStateMent.find_first_of('?');
strStateMent.replace(nPos, 1, "'" + std::string(t) + "'");
}
template <typename T>
typename std::enable_if<std::is_integral<T>::value>::type ReplaceSlot(std::string& strStateMent, T t)
{
int nPos = strStateMent.find_first_of('?');
std::string strValue = std::to_string(t);
strStateMent.replace(nPos, 1, strValue);
}
inline int BindParams(std::string& strStateMent)
{
return 0;
}
template <typename T, typename... Args>
inline std::string BindParams(std::string& strStateMent, T&& first, Args&&... args)
{
string str1 = strStateMent;
ReplaceSlot(str1, first);
strStateMent = str1;
BindParams(strStateMent, std::forward<Args>(args)...);
std::string res = strStateMent;
return res;
}
}
#endif
**示例:
std::string sql = insert into table(?,?,?,?);
std::string sqlRes = BindParams(sql, "dfs",3, "df",5);**