sqlite在VC环境下的使用涉及到单例模式、SQLite的C++接口、编码转换等,这里提供一个自以为比较完善的解决方案以供参考,欢迎拍砖指导。
注:编程环境VS2012,以动态链接方式使用sqlite(参考20110512 SQLite下载与SQLite.lib生成),本文也没有考虑多线程的问题。
1、sqlite操作类
sqliteHelper.h
/* ** FileName : DBHelper.h ** Author : pigautumn ** Date : 2016/8/22 ** Description : sqlite操作单例类 */ #pragma once #include "sqlite3.h" #include <string> #include <vector> using std::string; using std::vector; class sqliteHelper { public: static sqliteHelper& GetInstance(); ~sqliteHelper(void); public: bool IsTableExisted(const string& table_name); //表是否存在 bool CreateTable(const string& sql,const string& table_name); //创建表 bool DropTable(const string& table_name); //删除表 bool InsertData(const string& sql); //插入数据 bool UpdateData(const string& sql); //更新数据 bool DeleteData(const string& sql); //删除数据 bool GetDataCount(const string& sql,int& count); //获取记录个数 bool GetDataInfo(const string& sql,vector<vector<string>>& data); //获取记录 bool GetMaxId(const string& table_name,int& row_id); //获取最新插入数据的ID const string& GetLastError() const { return m_ssErrMsg; } //获取错误信息 public: void SetDBFileName(const string& file_name); //设置数据库文件名 void SetDBFileFolder(const string& file_path); //设置数据库路径 private: sqliteHelper(void); sqliteHelper(const sqliteHelper&); //拷贝构造函数只声明不实现 sqliteHelper& operator=(const sqliteHelper&); //赋值函数只声明不实现 bool OpenDB(); //打开数据库 void CloseDB(); //关闭数据库 bool Excutesql(const string& sql); //执行sql语句 static int IsTableExisted(void* para,int n_column,char** column_value,char** column_name); //判断表是否存在 private: static string m_ssErrMsg; //错误信息 static int m_snResult; //错误码 string m_sDBFileName; //数据库文件名 string m_sDBFilePath; //数据库文件路径 sqlite3* m_pDB; //数据库指针 string m_sDBFileNameUtf8; //UTF8编码数据库文件名 string m_sDBFilePathUtf8; //UTF8编码数据库文件路径 bool m_bConvertDBFile; //是否转换数据库名称编码 };
sqliteHelper.cpp
#include "stdafx.h" #include "sqliteHelper.h" #include "CodeConverter.h" #include <windows.h> #include <sstream> using std::stringstream; //Init static class member string sqliteHelper::m_ssErrMsg = ""; int sqliteHelper::m_snResult = 0; sqliteHelper::sqliteHelper(void) : m_pDB(NULL),m_sDBFileName("data.db"),m_sDBFilePath("."),m_bConvertDBFile(true) { m_sDBFilePath = string("./") + m_sDBFileName; } sqliteHelper::~sqliteHelper(void) { } sqliteHelper& sqliteHelper::GetInstance() { static sqliteHelper s_manager; return s_manager; } //设置数据库文件名 void sqliteHelper::SetDBFileName(const string& file_name) { m_sDBFileName = file_name; m_sDBFilePath = string("./") + m_sDBFileName; m_bConvertDBFile = true; } //设置数据库路径 void sqliteHelper::SetDBFileFolder(const string& folder) { if (folder.length() <= 0) { return; } if ('\\' == folder[folder.length() - 1] || '/' == folder[folder.length() - 1]) { m_sDBFilePath = folder + m_sDBFileName; } else { m_sDBFilePath = folder + string("/") + m_sDBFileName; } m_bConvertDBFile = true; } //打开数据库,如果该数据库不存在,就创建一个数据库 bool sqliteHelper::OpenDB() { if (m_bConvertDBFile) { //将ACSII转换为UTF8(由于经常要打开数据库,因此将数据库文件名称转换为UTF8编码后保存) m_sDBFilePathUtf8 = CodeConverter::AcsiiToUtf8(m_sDBFilePath); m_bConvertDBFile = false; } m_snResult = sqlite3_open(m_sDBFilePathUtf8.c_str(),&m_pDB); if (m_snResult != sqlITE_OK) { stringstream ss; ss << "Open database " << m_sDBFilePath << " Failed."; m_ssErrMsg = ss.str(); return false; } return true; } //关闭数据库 void sqliteHelper::CloseDB() { sqlite3_close(m_pDB); } //判断表是否存在 //para:传进回调函数的参数,n_column:字段个数 //column_value:char*数组(保存每一个字段的值),column_name:字段名称 int sqliteHelper::IsTableExisted(void* para,char** column_name) { bool* existed = (bool*)para; *existed = (**column_value) != '0'; //字段的值为空时表示不存在数据 return 0; } //表是否存在 bool sqliteHelper::IsTableExisted(const string& table_name) { //打开数据库 if (!OpenDB()) { return false; } bool existed = false; string sql_acsii = "SELECT count(type) FROM sqlite_master WHERE type = 'table' and name = '" + table_name + "'"; string sql_utf8 = CodeConverter::AcsiiToUtf8(sql_acsii); m_snResult = sqlite3_exec(m_pDB,sql_utf8.c_str(),sqliteHelper::IsTableExisted,&existed,NULL); //关闭数据库 CloseDB(); return existed; } //创建表 bool sqliteHelper::CreateTable(const string& sql,const string& table_name) { //判断表是否存在 string table_name_utf8 = CodeConverter::AcsiiToUtf8(table_name); if (IsTableExisted(table_name_utf8)) { return true; } //执行sql语句 return Excutesql(sql); } //删除表 bool sqliteHelper::DropTable(const string& table_name) { //判断表是否存在 string table_name_utf8 = CodeConverter::AcsiiToUtf8(table_name); if (!IsTableExisted(table_name_utf8)) { return true; } //执行sql语句 string sql = "DROP TABLE " + table_name; return Excutesql(sql); } //插入数据 bool sqliteHelper::InsertData(const string& sql) { return Excutesql(sql); } //删除数据 bool sqliteHelper::DeleteData(const string& sql) { return Excutesql(sql); } //更新数据 bool sqliteHelper::UpdateData(const string& sql) { return Excutesql(sql); } //执行sql语句 bool sqliteHelper::Excutesql(const string& sql) { //打开数据库 if (!OpenDB()) { return false; } string sql_utf8 = CodeConverter::AcsiiToUtf8(sql); char* pErrMsg = NULL; m_snResult = sqlite3_exec(m_pDB,NULL,&pErrMsg); m_ssErrMsg = (pErrMsg == NULL ? "" : pErrMsg); //关闭数据库 CloseDB(); return (m_snResult == sqlITE_OK); } //获取记录个数 bool sqliteHelper::GetDataCount(const string& sql,int& count) { //打开数据库 if (!OpenDB()) { return false; } char* pErrMsg = NULL; char** pDBResult = NULL; //查询结果 int nColumn = 0; int nRow = 0; string sql_utf8 = CodeConverter::AcsiiToUtf8(sql); m_snResult = sqlite3_get_table(m_pDB,&pDBResult,&nRow,&nColumn,&pErrMsg); m_ssErrMsg = (pErrMsg == NULL ? "" : pErrMsg); bool ret = (m_snResult == sqlITE_OK); if (ret && pDBResult != NULL && pDBResult[nColumn] != NULL) { count = atoi(pDBResult[nColumn]); } //释放查询结果(无论查询是否成功) sqlite3_free_table(pDBResult); //关闭数据库 CloseDB(); return ret; } //获取记录 bool sqliteHelper::GetDataInfo(const string& sql,vector<vector<string>>& data) { //打开数据库 if (!OpenDB()) { return false; } //清空数据 data.clear(); char* pErrMsg = NULL; char** pDBResult = NULL; //查询结果,前面是字段名称,后面才是字段的值 int nRow = 0; int nColumn = 0; string sql_utf8 = CodeConverter::AcsiiToUtf8(sql); string acsii_string; string val; m_snResult = sqlite3_get_table(m_pDB,&pErrMsg); m_ssErrMsg = (pErrMsg == NULL ? "" : pErrMsg); bool ret = (m_snResult == sqlITE_OK); if (ret) { int index = nColumn; //pDBResult前面是列名 for (int i=0; i<nRow; i++) { vector<string> vecRow; for (int j=0; j<nColumn; j++) { val = pDBResult[index++]; acsii_string = CodeConverter::Utf8ToAcsii(val); vecRow.push_back(acsii_string); } data.push_back(vecRow); } } //释放查询结果(无论查询是否成功) sqlite3_free_table(pDBResult); //关闭数据库 CloseDB(); return ret; } //获取最新插入数据的ID bool sqliteHelper::GetMaxId(const string& table_name,int& row_id) { bool ret = false; //打开数据库 if (!OpenDB()) { return ret; } char* pErrMsg = NULL; char** pDBResult = NULL; //查询结果,前面是字段名称,后面才是字段的值 int nRow = 0; int nColumn = 0; string sql_acsii = "SELECT MAX(RID) FROM " + table_name; string sql_utf8 = CodeConverter::AcsiiToUtf8(sql_acsii); string acsii_string; string val; m_snResult = sqlite3_get_table(m_pDB,&pErrMsg); m_ssErrMsg = (pErrMsg == NULL ? "" : pErrMsg); ret = (m_snResult == sqlITE_OK); if (ret && pDBResult != NULL && pDBResult[nColumn] != NULL) { val = pDBResult[nColumn]; acsii_string = CodeConverter::Utf8ToAcsii(val); row_id = atoi(acsii_string.c_str()); } //释放查询结果(无论查询是否成功) sqlite3_free_table(pDBResult); //关闭数据库 CloseDB(); return ret; }
2、编码转换类
CodeConverter.h
/* ** FileName : DBHelper.h ** Author : pigautumn ** Date : 2016/8/23 ** Description : 编码转换类(提供静态方法) */ #pragma once #include <string> #include <xstring> using std::string; using std::wstring; class CodeConverter { public: static wstring AcsiiToUnicode(const string& acsii_string); //ACSII转Unicode static string AcsiiToUtf8(const string& acsii_string); //ACSII转UTF8 static string UnicodeToAcsii(const wstring& unicode_string); //Unicode转ACSII static string UnicodeToUtf8(const wstring& unicode_string); //Unicode转UTF8 static string Utf8ToAcsii(const string& utf8_string); //UTF8转ACSII static wstring Utf8ToUnicode(const string& utf8_string); //UTF8转Unicode };
CodeConverter.cpp
#include "stdafx.h" #include "CodeConverter.h" #include <windows.h> #include <vector> using std::vector; //ACSII转Unicode wstring CodeConverter::AcsiiToUnicode(const string& acsii_string) { wstring unicode_string; //CP_ACP - default to ANSI code page int len = MultiByteToWideChar(CP_ACP,acsii_string.c_str(),-1,0); if (ERROR_NO_UNICODE_TRANSLATION == len || 0 == len) { //return empty wstring return unicode_string; } vector<wchar_t> vec_result(len); int result_len = MultiByteToWideChar(CP_ACP,&vec_result[0],len); if (result_len != len) { //return empty wstring return unicode_string; } unicode_string = wstring(&vec_result[0]); return unicode_string; } //ACSII转UTF8 string CodeConverter::AcsiiToUtf8(const string& acsii_string) { wstring unicode_string = AcsiiToUnicode(acsii_string); //将ACSII转换为Unicode string utf8_string = UnicodeToUtf8(unicode_string); //将Unicode转换为UTF8 return utf8_string; } //Unicode转ACSII string CodeConverter::UnicodeToAcsii(const wstring& unicode_string) { string acsii_string; //CP_OEMCP - default to OEM code page int len = WideCharToMultiByte(CP_OEMCP,unicode_string.c_str(),NULL); if (ERROR_NO_UNICODE_TRANSLATION == len || 0 == len) { //return empty wstring return acsii_string; } vector<char> vec_result(len); int result_len = WideCharToMultiByte(CP_OEMCP,len,NULL);; if (result_len != len) { //return empty wstring return acsii_string; } acsii_string = string(&vec_result[0]); return acsii_string; } //Unicode转UTF8 string CodeConverter::UnicodeToUtf8(const wstring& unicode_string) { string utf8_string; //CP_UTF8 - UTF-8 translation int len = WideCharToMultiByte(CP_UTF8,NULL); if (0 == len) { //return empty wstring return utf8_string; } vector<char> vec_result(len); int result_len = WideCharToMultiByte(CP_UTF8,NULL);; if (result_len != len) { //return empty wstring return utf8_string; } utf8_string = string(&vec_result[0]); return utf8_string; } //UTF8转ACSII string CodeConverter::Utf8ToAcsii(const string& utf8_string) { wstring unicode_string = Utf8ToUnicode(utf8_string); //将UTF8转换为Unicode string acsii_string = UnicodeToAcsii(unicode_string); //将Unicode转换为ACSII return acsii_string; } //UTF8转Unicode wstring CodeConverter::Utf8ToUnicode(const string& utf8_string) { wstring unicode_string; //CP_UTF8 - UTF-8 translation int len = MultiByteToWideChar(CP_UTF8,utf8_string.c_str(),0); if (ERROR_NO_UNICODE_TRANSLATION == len || 0 == len) { //return empty wstring return unicode_string; } vector<wchar_t> vec_result(len); int result_len = MultiByteToWideChar(CP_UTF8,len); if (result_len != len) { //return empty wstring return unicode_string; } unicode_string = wstring(&vec_result[0]); return unicode_string; }
3、测试代码(部分测试)
// sqliteTest.cpp : 定义控制台应用程序的入口点。 // #include "stdafx.h" #include "sqliteHelper.h" #include <iostream> using namespace std; int _tmain(int argc,_TCHAR* argv[]) { //测试数据库 const string TestDB = "test.db"; //sql语句 string table_student = "Student"; string create_table_student = "CREATE TABLE " + table_student + "(" + "RID INTEGER PRIMARY KEY AUTOINCREMENT," + //ID "Name TEXT," + //姓名 "Age INTEGER" //年龄 ");"; //设置数据库名称 sqliteHelper::GetInstance().SetDBFileName(TestDB); //1. 创建学生表 cout << "1. 创建学生表..." << endl; bool ret = sqliteHelper::GetInstance().CreateTable(create_table_student,table_student); if (!ret) { cout << "创建学生表失败(" << sqliteHelper::GetInstance().GetLastError() << ")" << endl; getchar(); return -1; } cout << "创建学生表成功" << endl; //2. 添加学生 cout << "2. 添加学生..." << endl; string sql = "select count(*) from " + table_student + ";"; int num = 0; ret = sqliteHelper::GetInstance().GetDataCount(sql,num); if (!ret) { cout << "查询数据库失败(" << sqliteHelper::GetInstance().GetLastError() << ")" << endl; getchar(); return -1; } if (num <= 0) { string sql1 = "insert into " + table_student + "(Name,Age) Values('小红',12)"; string sql2 = "insert into " + table_student + "(Name,Age) Values('小明',12)"; string sql3 = "insert into " + table_student + "(Name,Age) Values('小张',13)"; string sqls[] = { sql1,sql2,sql3 }; for (int i=0; i<3; i++) { ret = sqliteHelper::GetInstance().InsertData(sqls[i]); if (!ret) { cout << "添加学生失败(" << sqliteHelper::GetInstance().GetLastError() << ")" << endl; getchar(); return -1; } } } cout << "添加学生成功" << endl; //Get data cout << "3. 查询学生..." << endl; vector<vector<string>> vecData; vector<vector<string>>::const_iterator row; vector<string>::const_iterator col; sql = "select * from " + table_student; sqliteHelper::GetInstance().GetDataInfo(sql,vecData); for (row=vecData.begin(); row!=vecData.end(); ++row) { for (col=(*row).begin(); col!=(*row).end(); ++col) { cout << *col << " "; } cout <<endl; } cout << "查询学生结束" << endl; getchar(); return 0; }