sqlit的C++类库

/*
** FileName     : CodeConverter.h
*/

#ifndef CODECONVERTER_H
#define CODECONVERTER_H

	wchar_t* AcsiiToUnicode(const char* szAcsii,int* wszUnicodeLen);
	char* UnicodeToAcsii(const wchar_t* wszUnicode,int* szAcsiiLen);

	char* UnicodeToUtf8(const wchar_t* wszUnicode,int* szUft8Len);
	wchar_t* Utf8ToUnicode(const char* szUft8,int* wszUnicodeLen);

	char* AcsiiToUtf8(const char* szAcsii,int* szUft8Len);
	char* Utf8ToAcsii(const char* szUft8,int* szAcsiiLen);

	void strcpy_Utf8ToAcsii(OUT char *buf,IN char* uft8);
#endif

 

 

/*
** FileName     : CodeConverter.cpp
*/
#include "stdafx.h"

wchar_t* AcsiiToUnicode(const char* szAcsii,int* wszUnicodeLen)
{
	*wszUnicodeLen = MultiByteToWideChar(CP_ACP, 0, szAcsii, -1, NULL, 0);	
	wchar_t* wszUnicode = (wchar_t*)malloc(sizeof(wchar_t)*(*wszUnicodeLen + 1));
	memset(wszUnicode,0x00,sizeof(wchar_t)*(*wszUnicodeLen + 1));
	MultiByteToWideChar(CP_ACP, 0, szAcsii, -1, wszUnicode, *wszUnicodeLen);
	return wszUnicode;
}

char* UnicodeToAcsii(const wchar_t* wszUnicode,int* szAcsiiLen)
{
	*szAcsiiLen = WideCharToMultiByte(CP_OEMCP, 0, wszUnicode, -1, NULL, 0, NULL, NULL);	
	char* szAcsii = (char*)malloc(sizeof(char)*(*szAcsiiLen + 1));
	memset(szAcsii,0x00,sizeof(char)*(*szAcsiiLen + 1));
	WideCharToMultiByte(CP_OEMCP, 0, wszUnicode, -1, szAcsii, *szAcsiiLen, NULL, NULL);		
	return szAcsii;
}

char* UnicodeToUtf8(const wchar_t* wszUnicode,int* szUft8Len)
{
	*szUft8Len = WideCharToMultiByte(CP_UTF8, 0, wszUnicode, -1, NULL, 0, NULL, NULL);	
	char* szUft8 = (char*)malloc(sizeof(char)*(*szUft8Len + 1));
	memset(szUft8,0x00,sizeof(char)*(*szUft8Len + 1));		
	WideCharToMultiByte(CP_UTF8, 0, wszUnicode, -1, szUft8, *szUft8Len, NULL, NULL);	
	return szUft8;
}

wchar_t* Utf8ToUnicode(const char* szUft8,int* wszUnicodeLen)
{
	*wszUnicodeLen = MultiByteToWideChar(CP_UTF8, 0, szUft8, -1, NULL, 0);	
	wchar_t* wszUnicode = (wchar_t*)malloc(sizeof(wchar_t)*(*wszUnicodeLen + 1));
	memset(wszUnicode,0x00,sizeof(wchar_t)*(*wszUnicodeLen + 1));
	MultiByteToWideChar(CP_UTF8, 0, szUft8, -1, wszUnicode, *wszUnicodeLen);
	return wszUnicode;
}

char* AcsiiToUtf8(const char* szAcsii,int* szUft8Len)
{
	wchar_t* wszUnicode = NULL;
	int wszUnicodeLen = 0;
	wszUnicode = AcsiiToUnicode(szAcsii,&wszUnicodeLen);
	
	char* szUft8 = NULL;
	szUft8 = UnicodeToUtf8(wszUnicode,szUft8Len);
	
	free(wszUnicode);
	wszUnicode = NULL;
	return szUft8;
}

char* Utf8ToAcsii(const char* szUft8,int* szAcsiiLen)
{
	wchar_t* wszUnicode = NULL;
	int wszUnicodeLen = 0;

	wszUnicode = Utf8ToUnicode(szUft8,&wszUnicodeLen);
	char* szAcsii = NULL;
	szAcsii = UnicodeToAcsii(wszUnicode,szAcsiiLen);
	
	free(wszUnicode);
	wszUnicode = NULL;
	return szAcsii;
}

void strcpy_Utf8ToAcsii(OUT char *buf,IN char* uft8)
{
	char* szAcsii = NULL;
	int szAcsiiLen = 0;

	szAcsii = Utf8ToAcsii(uft8,&szAcsiiLen);
	strcpy_s(buf,szAcsiiLen,szAcsii);

	free(szAcsii);
	szAcsii = NULL;
}

 

 

/*
** FileName     : dbClass.h
*/

#ifndef DBCLASS_H
#define DBCLASS_H

#pragma once

#include "../sqlite3/sqlite3.h"
//#pragma comment(lib,"../sqlite3/sqlite3.lib")
#pragma comment(lib,"../../lib/sqlite3/sqlite3.lib")//这里需要从stdafx.h的路径开始计算

class dbClass
{
	#define BUFF_LEN 20		//定义“字段名”长度
	#define MAX_LEN 1024	//定义CSV文件一行最大长度
	
	#define DB_DIR "E:/my_app/db/"
	#define DB_NAME "my.db"
	#define SQLFILE_NAME "create.sql"

	#define CSV_FILE1 "E:/my_app/db/单位表.csv"
	#define TABLE1 "单位表"

	typedef struct tag_TableInfo
	{		
		int col_id;
		char col_name[BUFF_LEN];//字段名
		char col_decltype[BUFF_LEN];//定义类型

		tag_TableInfo(){
			memset(this,0x00,sizeof(tag_TableInfo));
		}
	}TableInfo,*pTableInfo;

	public:
		dbClass();//构造函数

		BOOL db_CreateDB();		
		BOOL db_OpenDB();
		BOOL db_CloseDB();
		BOOL db_ExcuteSQL(IN const char *sql);
		BOOL db_SelectSQL(IN const char *sql,char ***pazResult,int *pnRow, int *pnColumn);
		void db_FreeTable(char ***pazResult);
		BOOL db_InsertSQL(IN const char *sql,OUT int *id);
		BOOL db_UpdateSQL(IN const char *sql);
		BOOL db_DeleteSQL(IN const char *sql);
		char* db_GetLastError(){return m_pErrMsg;}

		BOOL db_InputCSVFile(IN const char *csvFile,IN const char *tableName);//导入csv文件
		BOOL db_OutputCSVFile(IN const char *csvFile,IN const char *sql);//导出csv文件
	private:		
		BOOL m_CreateTable();
		BOOL m_ReadSQLFile(IN const char *sqlFile,OUT char **sql,OUT int *sqlLen);
		BOOL m_FormatSQL(OUT char *f_str,IN const int f_strLen,IN const char *str,IN vector<TableInfo> *pvec);
		BOOL m_GetTableInfo(IN const char *tableName,IN const char *colNameStr,OUT vector<TableInfo> *pvec);
		
		sqlite3* m_pDB;	//数据库指针
		char m_dbFilePathName[256];//数据库文件路径	
		char m_sqlFilePathName[256];//sql文件路径	
		char m_pErrMsg[512];//保存错误信息
		void m_GetErrMsg(BOOL isUTF8,char* fileName,int fileLine,char* pErrMsg);		
		void m_split(const char *src,char split,char ***dst,int *dst_len);
};

#endif

 

/*
** FileName     : dbClass.cpp
*/

#include "stdafx.h"
#include "dbClass.h"

dbClass::dbClass()
{
	int dbFilePathNameLen = 256;
	memset(m_dbFilePathName,0x00,dbFilePathNameLen);
	strcpy_s(m_dbFilePathName,dbFilePathNameLen,DB_DIR);
	strcat_s(m_dbFilePathName,dbFilePathNameLen,DB_NAME);

	int sqlFilePathNameLen = 256;
	memset(m_sqlFilePathName,0x00,sqlFilePathNameLen);	
	strcpy_s(m_sqlFilePathName,sqlFilePathNameLen,DB_DIR);
	strcat_s(m_sqlFilePathName,sqlFilePathNameLen,SQLFILE_NAME);

	memset(m_pErrMsg,0x00,256);
	return;
}

BOOL dbClass::db_CreateDB()
{	
	BOOL b_ret = TRUE;
	int rc = SQLITE_OK;	
	//判断文件夹是否存在
	if(PathFileExists(_T(DB_DIR)) == FALSE)		
		CreateDirectory(_T(DB_DIR),NULL);//不存在，创建该文件夹
	
	CString cs_dbFilePathName(m_dbFilePathName);

	//判断数据库文件是否存在？存在，返回flase
	if(PathFileExists(cs_dbFilePathName) == TRUE){	
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"数据库文件已经存在。");
		return FALSE;
	}
	//不存在，创建新数据库
	rc = sqlite3_open(m_dbFilePathName,&m_pDB);//打开数据库，不存在时会创建数据库
	if (rc != SQLITE_OK){
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"打开数据库失败。");
		return FALSE;
	}

	b_ret = m_CreateTable();//执行sql文件内容，创建表
	if (b_ret != TRUE)	goto end_free;		

	b_ret = db_InputCSVFile(CSV_FILE1,TABLE1);
	if (b_ret != TRUE)	goto end_free;

end_free:
	rc = sqlite3_close(m_pDB);//关闭数据库
	return b_ret;
}

BOOL dbClass::db_OpenDB()
{
	//判断文件夹是否存在
	if(PathFileExists(_T(DB_DIR)) == FALSE){
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"文件夹不存在。");		
		return FALSE;
	}
	CString cs_dbFilePathName(m_dbFilePathName);

	//判断数据库文件是否存在？不存在，返回false
	if(PathFileExists(cs_dbFilePathName) == FALSE){	
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"数据库文件不存在。");		
		return FALSE;
	}

	//存在，打开数据库
	int rc = sqlite3_open(m_dbFilePathName,&m_pDB);
	if (rc != SQLITE_OK) {
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"打开数据库失败。");
		return FALSE;
	}
	return TRUE;
}

BOOL dbClass::db_CloseDB()
{
	int rc = sqlite3_close(m_pDB);//关闭数据库
	return TRUE;
}

BOOL dbClass::db_ExcuteSQL(IN const char *sql)
{
	BOOL b_ret = TRUE;
	int rc = 0;	
	char *sql_utf8 = NULL;
	int sql_utf8Len = 0;

	sql_utf8 = AcsiiToUtf8(sql,&sql_utf8Len);	

	char* pErrMsg = NULL;	
	rc = sqlite3_exec(m_pDB, sql_utf8, NULL, NULL, &pErrMsg);
	if (rc != SQLITE_OK){
		b_ret = FALSE;
		m_GetErrMsg(TRUE,__FILE__,__LINE__,pErrMsg);
		goto end_free;	
	}

end_free:	
	//释放变量内存
	if(pErrMsg != NULL) {sqlite3_free(pErrMsg);pErrMsg = NULL;}
	if(sql_utf8 != NULL) {free(sql_utf8);sql_utf8 = NULL;}
	return b_ret;
}

BOOL dbClass::db_SelectSQL(IN const char *sql,char ***pazResult,int *pnRow, int *pnColumn)
{	
	BOOL b_ret = TRUE;
	int rc = 0;	
	char *sql_utf8 = NULL;
	int sql_utf8Len = 0;

	sql_utf8 = AcsiiToUtf8(sql,&sql_utf8Len);	

	//查询结果,前面是字段名称，后面才是字段的值
	char *pErrMsg = NULL;
	rc = sqlite3_get_table(m_pDB, sql_utf8, pazResult, pnRow, pnColumn, &pErrMsg);	
	if (rc != SQLITE_OK){
		b_ret = FALSE;
		m_GetErrMsg(TRUE,__FILE__,__LINE__,pErrMsg);
		goto end_free;	
	}

end_free:
	//释放变量内存
	if(pErrMsg != NULL) {sqlite3_free(pErrMsg);pErrMsg = NULL;}
	if(sql_utf8 != NULL) {free(sql_utf8);sql_utf8 = NULL;}
	//sqlite3_free_table(*pazResult);//函数外再释放该内容
	return b_ret;
}

void dbClass::db_FreeTable(char ***pazResult)
{
	sqlite3_free_table(*pazResult);
}

BOOL dbClass::db_InsertSQL(IN const char *sql,OUT int *id)
{	
	BOOL b_ret = TRUE;
	int rc = 0;	

	b_ret = db_ExcuteSQL(sql);
	if (b_ret != TRUE)	goto end_free;

	char *sql_getrowid = "select last_insert_rowid();";
	char **pazResult = NULL;
	int nRow = 0;
	int nColumn = 0;
	char *pErrMsg = NULL;
	rc = sqlite3_get_table(m_pDB, sql_getrowid, &pazResult, &nRow, &nColumn, &pErrMsg);
	if (rc != SQLITE_OK){
		b_ret = FALSE;		
		m_GetErrMsg(TRUE,__FILE__,__LINE__,pErrMsg);		
		goto end_free;
	}
	*id = atoi(pazResult[1]);

end_free:
	if(pErrMsg != NULL) {sqlite3_free(pErrMsg);pErrMsg = NULL;}
	if(pazResult != NULL) {sqlite3_free_table(pazResult);pazResult = NULL;}
	return b_ret;
}

BOOL dbClass::db_UpdateSQL(IN const char *sql)
{
	return db_ExcuteSQL(sql);
}

BOOL dbClass::db_DeleteSQL(IN const char *sql)
{
	return db_ExcuteSQL(sql);
}

BOOL dbClass::m_CreateTable()
{
	BOOL b_ret = TRUE;
	int rc = 0;	
	char* sql_utf8 = NULL;
	int sql_utf8Len = 0;

	char* pErrMsg = NULL;

	b_ret = m_ReadSQLFile(m_sqlFilePathName,&sql_utf8,&sql_utf8Len);//读出的文件是UTF-8编码
	if (b_ret != TRUE)	goto end_free;
			
	rc = sqlite3_exec(m_pDB, sql_utf8, NULL, NULL, &pErrMsg);	
	if(rc != SQLITE_OK){
		b_ret = FALSE;
		m_GetErrMsg(TRUE,__FILE__,__LINE__,pErrMsg);
		goto end_free;
	}

end_free:
	if(pErrMsg != NULL) {sqlite3_free(pErrMsg);pErrMsg = NULL;}
	if(sql_utf8 != NULL) {free(sql_utf8);sql_utf8 = NULL;}
	return b_ret;
}

BOOL dbClass::m_ReadSQLFile(IN const char *sqlFile,OUT char **sql,OUT int *sqlLen)
{
	errno_t err;
	FILE *fp = NULL;
	int iLength = 0;
	err = fopen_s(&fp,sqlFile,"r");
	if(err != 0){
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"sql文件不存在。");		
		return FALSE;
	}

	fseek(fp,0,SEEK_END);
	iLength = ftell(fp);
	*sql = (char*)malloc(sizeof(char)*iLength);
	memset(*sql,0x00,iLength);	

	fseek(fp,0,SEEK_SET);	
	fread(*sql,1,iLength,fp);
	fclose(fp);
	fp = NULL;

	*sqlLen = iLength;
	return TRUE;
}

void dbClass::m_GetErrMsg(BOOL isUTF8,char* fileName,int fileLine,char* pErrMsg)
{
	char *pErrMsg_Acsii = NULL;
	int pErrMsg_AcsiiLen = 0;

	if(isUTF8 == TRUE)
		pErrMsg_Acsii = Utf8ToAcsii(pErrMsg,&pErrMsg_AcsiiLen);
	else
		pErrMsg_Acsii = pErrMsg;//指向一个字符串常量，pErrMsg_Acsii不需要free
	sprintf_s(m_pErrMsg,512,"err: [File=%s],[Line=%d]\nmsg=%s\n",fileName,fileLine,pErrMsg_Acsii);

	if(isUTF8 == TRUE)
		if(pErrMsg_Acsii != NULL) {free(pErrMsg_Acsii);pErrMsg_Acsii = NULL;}
}

BOOL dbClass::db_InputCSVFile(IN const char *csvFile,IN const char *tableName)//导入csv文件
{
	BOOL b_ret = TRUE;
	int rc = 0;	
	int i = 0;
	vector<TableInfo> vecColumn;

	FILE *fp = NULL;	
	char colNameStr[MAX_LEN] = {0x00};	
	char strLine[MAX_LEN] = {0x00};
	char f_strLine[MAX_LEN] = {0x00};	

	char sql[MAX_LEN] = {0x00};	

	if(fopen_s(&fp,csvFile,"r") != 0){
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"csv文件不存在。");	
		return FALSE;
	}
	//返回NULL则读取文件结束

	db_ExcuteSQL("BEGIN");//因为要操作多个insert，所以使用事务BEGIN，可明显加快执行速度
	while(fgets(strLine,MAX_LEN,fp) != NULL){
		i++;
		if(strLine[strlen(strLine)-1] == 0x0A)
			strLine[strlen(strLine)-1] = 0x00;//替换0x0A（换行符）

		if(i == 1) {
			strcpy_s(colNameStr,MAX_LEN,strLine);
			b_ret = m_GetTableInfo(tableName,colNameStr,&vecColumn);
			if(b_ret == FALSE)	goto end_free;
			continue;//第一行是字段名，不导入数据库。
		}

		//获取字段类型，对于字符类型加两个单引号（'）		
		b_ret = m_FormatSQL(f_strLine,MAX_LEN,strLine,&vecColumn);
		if(b_ret == FALSE)	goto end_free;
		sprintf_s(sql,MAX_LEN,"INSERT INTO %s (%s) VALUES (%s)",tableName,colNameStr,f_strLine);
		//printf("%d: %s\n\n",i-1,sql);		
		b_ret = db_ExcuteSQL(sql);
		if(b_ret == FALSE)	goto end_free;
	}
end_free:
	db_ExcuteSQL("END");//事务结束

	vector<TableInfo> vecTemp;
	vecTemp.swap(vecColumn);//释放内存

	if(fp != NULL) {fclose(fp);fp = NULL;}
	return b_ret;
}

BOOL dbClass::db_OutputCSVFile(IN const char *csvFile,IN const char *sql)//导出csv文件
{
	return FALSE;
	//BOOL b_ret = TRUE;
	//return b_ret;
}

/*
	输入：1,1,单位1,111111,0
	输出：1,1,'单位1','111111',0
*/
BOOL dbClass::m_FormatSQL(OUT char *f_str,IN const int f_strLen,IN const char *str,IN vector<TableInfo> *pvec)
{
	BOOL b_ret = TRUE;
	int i=0;

	memset(f_str,0x00,MAX_LEN);	

	char **dst = NULL;//字符串数组
	int dst_len = 0;
	m_split(str,',',&dst,&dst_len);
	
	for(i=0;i<(int)pvec->size();i++){
		if(strcmp(pvec->at(i).col_decltype,"integer") != 0){
			int len = strlen(dst[i]);
			memcpy_s(dst[i]+1,strlen(str),dst[i],len);
			dst[i][0] = '\'';
			dst[i][len+1] = '\'';
			dst[i][len+2] = 0x00;
		}		
		strcat_s(f_str,f_strLen,dst[i]);
		strcat_s(f_str,f_strLen,",");
	}
	if(f_str[strlen(f_str)-1] == ',')//去除字符串最后一个逗号
		f_str[strlen(f_str)-1] = 0x00;

	//释放内存空间
	for(i=0;i<dst_len;i++)
		if(dst[i] != NULL) {free(dst[i]);dst[i] = NULL;}
	if(dst != NULL) {free(dst);dst = NULL;}

	if(strlen(f_str) == 0){
		m_GetErrMsg(FALSE,__FILE__,__LINE__,"格式化字符串失败。");	
		b_ret = FALSE;
	}
	return b_ret;
}

void dbClass::m_split(const char *src,char split,char ***dst,int *dst_len)
{
	int max_size = strlen(src);
	int i = 0;
	int size = 0;
	int splitIndex[100] = {0x00};
	
	for(i=0;i<max_size;i++){
		if(src[i] == split){
			splitIndex[size] = i;
			size++;
		}
	}
	splitIndex[size] = max_size + 1;

	*dst = (char**)malloc(sizeof(char**) * (size+1));	
	int start_index = 0;
	for(i = 0;i<size+1;i++)
	{
		(*dst)[i] = (char*)malloc(sizeof(char*) * max_size);
		memset((*dst)[i],0x00,max_size);
		memcpy_s((*dst)[i],max_size,src + start_index,splitIndex[i]-start_index);	
		start_index = splitIndex[i]+1;
	}	
	*dst_len = size + 1;
}

BOOL dbClass::m_GetTableInfo(IN const char *tableName,IN const char *colNameStr,OUT vector<TableInfo> *pvec)
{
	BOOL b_ret = TRUE;

	sqlite3 *db = NULL;
	sqlite3_stmt *stmt = NULL;
	int rc = 0,i = 0,ncols = 0;
	
	char sql[MAX_LEN] = {0x00};	
	char *sql_utf8 = NULL;
	int sql_utf8Len = 0;
	
	sprintf_s(sql,MAX_LEN,"select %s from %s where 1=0",colNameStr,tableName);
	//printf("%s\n\n",sql);

	rc = sqlite3_open(m_dbFilePathName,&db);
	if(rc != SQLITE_OK)return FALSE;

	sql_utf8 = AcsiiToUtf8(sql,&sql_utf8Len);
	rc = sqlite3_prepare_v2(db,sql_utf8,sql_utf8Len,&stmt,NULL);
	if(sql_utf8 != NULL) {free(sql_utf8);sql_utf8 = NULL;}
	if(rc != SQLITE_OK)return FALSE;

	ncols = sqlite3_column_count(stmt);
	rc = sqlite3_step(stmt);
	for(i=0;i<ncols;i++)
	{
		TableInfo node;	
		node.col_id = i;

		char* szAcsii = NULL;int szAcsiiLen = 0;
		szAcsii = Utf8ToAcsii(sqlite3_column_name(stmt,i),&szAcsiiLen);
		strcpy_s(node.col_name,BUFF_LEN,szAcsii);//字段名
		free(szAcsii);szAcsii = NULL;

		strcpy_s(node.col_decltype,BUFF_LEN,sqlite3_column_decltype(stmt,i));//定义类型
		pvec->push_back(node);
	}
	rc = sqlite3_finalize(stmt);
	rc = sqlite3_close(db);
	return b_ret;
}