SQLite在VC环境下的使用，SQLiteVC环境使用

和通数据库htsjk.Com2019-07-25 23:56 来源:未知阅读:13723 评论 394 热度2

标签：

SQLite在VC环境下的使用，SQLiteVC环境使用

SQLite在VC环境下的使用涉及到单例模式、SQLite的C++接口、编码转换等，这里提供一个自以为比较完善的解决方案以供参考，欢迎拍砖指导。

注：编程环境VS2012，以动态链接方式使用SQLite（参考20110512 SQLite下载与SQLite.lib生成），本文也没有考虑多线程的问题。

1、SQLite操作类

SQLiteHelper.h

/*
** FileName     : SQLiteHelper.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, int n_column, char** column_value, 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, sql_utf8.c_str(), NULL, 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, sql_utf8.c_str(), &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, sql_utf8.c_str(), &pDBResult, &nRow, &nColumn, &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, sql_utf8.c_str(), &pDBResult, &nRow, &nColumn, &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     : CodeConverter.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

	static string BSTRToString(const BSTR& str);					//BSTR转string
	static BSTR StringToBSTR(const string& str);					//string转BSTR
};

CodeConverter.cpp

#include "CodeConverter.h"
#include <windows.h>
#include <comdef.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, 0, acsii_string.c_str(), -1, NULL, 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, 0, acsii_string.c_str(), -1, &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, 0, unicode_string.c_str(), -1, NULL, 0, NULL, 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, 0, unicode_string.c_str(), -1, &vec_result[0], len, NULL, 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, 0, unicode_string.c_str(), -1, NULL, 0, NULL, NULL);
	if (0 == len)
	{
		//return empty wstring
		return utf8_string;
	}

	vector<char> vec_result(len);
	int result_len = WideCharToMultiByte(CP_UTF8, 0, unicode_string.c_str(), -1, &vec_result[0], len, NULL, 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, 0, utf8_string.c_str(), -1, NULL, 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, 0, utf8_string.c_str(), -1, &vec_result[0], len);
	if (result_len != len)
	{
		//return empty wstring
		return unicode_string;
	}

	unicode_string = wstring(&vec_result[0]);
	return unicode_string;
}

//BSTR转string
string CodeConverter::BSTRToString(const BSTR& str)
{
	string newStr = (_bstr_t)str;
	return newStr;
}

//string转BSTR
BSTR CodeConverter::StringToBSTR(const string& str)
{
	_variant_t strVar(str.c_str());
	return strVar.bstrVal;
}

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;
}// 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;
}// 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;
}