1,为什么要使用Sqlite3?
SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。
2,为什么使用SQLite version 3.8.4.3 2014-04-03 16:53:12的版本
（1）dll体积小661KB，最新的都已经超过2M，这个只有几百k
（2）使用旧版本日常所需功能旧版本都已经很完备，无需升级最新版本
3，使用教程
3.1拷贝必备的sqlite.dll 和.h .lib文件到工程目录
3.2 代码中导入 sqlite3.lib文件，及导入sqlite3.h头文件
3.3 编写业务代码及sqlite3操作代码
sqliteDataCenter.h
#pragma once
#include <tchar.h>
#include "sqlite3.h"
#include <iostream>
#include <list>
#include <stdio.h>
using namespace std;
#pragma comment(lib,"sqlite3.lib")
#if !defined(CSQLITE3_CENTER_HEAD)
#define CSQLITE3_CENTER_HEAD
class CFileBean;
#define NULL_CALLBACK NULL
#define SQLSMALLINT (short)
#define SQLUSMALLINT (unsigned short)
#define SQLUINTEGER (unsigned long)
/*
struct tagTIMESTAMP_STRUCT {
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
SQLUINTEGER fraction;
} TIMESTAMP_STRUCT;
*/
/* 常量定义 */
class CFileBean
{
public:
int iId;
CString strFilePath;
CString strFileName;
long lSize;
BYTE byMd5[16];
BYTE byRepeat;
BYTE byMoved;
int iRepeatCount;
CTime ctCreate;
CTime ctModify;
int iRemark;
public:
CRITICAL_SECTION cs;//可以理解为锁定一个资源
CFileBean(int iId,CString strFilePath,CString strFileName,long lSize,unsigned char *pbyMd5,BYTE byRepeat,BYTE byMoved,int iRepeatCount,CTime ctCreate,CTime ctModify,int iRemark)
{
memset(byMd5,0,16);
this->iId = iId;
this->strFilePath = strFilePath;
this->strFileName = strFileName;
this->lSize = lSize;
if(pbyMd5 != NULL)
{
memcpy(this->byMd5,pbyMd5,16);
}
this->byRepeat = byRepeat;
this->byMoved = byMoved;
this->iRepeatCount = iRepeatCount;
this->iRemark = iRemark;
this->ctCreate = ctCreate;
this->ctModify = ctModify;
//TRACE("test %I64u time=%s\r\n",createTime,ct.Format("%Y-%m-%d %H:%M:%S"));
}
void InitializeCs()
{
InitializeCriticalSection(&cs);//初始化临界区
}
};
class CSqliteDataCenter
{
public:
CSqliteDataCenter();
~CSqliteDataCenter(void);
public:
bool m_bOpenFlag;
char *m_pErrMsg;
CString MbcsToUtf8(const char *file);
bool openDB(CString dbName);//打开数据库
bool createUserTable();//创建记录表
bool createBinTable();
bool createConfigTable();
bool creaetAllTable();
/* t_user Table */
bool insertUser(CFileBean *fb);
bool queryTableCount(char *sql,int &iCount);
int QueryTableExist(CString strTableName);
list<CFileBean*> g_listUser;
//list<CCofig*> g_listConfig;
private :
void clearAll();
};
#endifsqliteDataCenter.cpp
#include "StdAfx.h"
#include "sqliteDataCenter.h"
sqlite3 * pDbConnection = NULL;
char asSqlExistTable[]="SELECT COUNT(*) FROM sqlite_master where type='table' and name='%s';";
/* Create table */
char acSqlCreateTable_t_file_records[] = ("create table t_file_records (iid integer primary key autoincrement,strFilePath TEXT,strFileName TEXT, lSize integer,byMd5 TEXT,byRepeat integer,byMoved integer,iRepeatCount integer,ctCreate bigint,ctModify bigint,addtime bigint default (datetime('now', 'localtime')),remarks integer);");
//insert into t_user (userId,password, permissions,nickname,createTime,remarks) values(8,'pass',2,'nickname2',(datetime('now','localtime')),2);
char acSqlInsertTable_t_file_record[] = ("insert into t_file_records (strFilePath,strFileName,lSize,byMd5,byRepeat,byMoved,iRepeatCount,ctCreate,ctModify,remarks) values('%s','%s',%d,'%d',%d,%d,%d,%I64d,%I64d,%d);");
char acSqlQueryTable_t_file_record[] = ("select iid,strFilePath,strFileName,lSize,byMd5,byRepeat,byMoved,iRepeatCount,ctCreate,ctModify,remarks from t_file_records;");
char acSqlQueryCount_t_file_record[] = ("select count(id) from t_file_records where byRepeat=0;");
const int constCharMsgMaxLen = 256;
const int constCharQuerySqlLen256 = 256;
const int constCharQuerySqlLen512 = 512;
const int constCharQuerySqlLen1K = 1024;
const int constCharQuerySqlLen2K = 2048;
CSqliteDataCenter::CSqliteDataCenter()
{
}
CSqliteDataCenter::~CSqliteDataCenter(void)
{
sqlite3_close(pDbConnection);
}
CString CSqliteDataCenter::MbcsToUtf8(const char *file)
{
CString str;
WCHAR *pwchar=0;
CHAR *pchar=0;
int len=0;
int codepage = AreFileApisANSI() ? CP_ACP : CP_OEMCP;
len=MultiByteToWideChar(codepage, 0, file, -1, NULL,0);
pwchar=new WCHAR[len];
if(pwchar!=0)
{
len = MultiByteToWideChar(codepage, 0, file, -1, pwchar, len);
if( len!=0 )
{
len = WideCharToMultiByte(CP_UTF8, 0, pwchar, -1, 0, 0, 0, 0);
pchar=new CHAR[len];
if(pchar!=0)
{
len = WideCharToMultiByte(CP_UTF8, 0, pwchar, -1, pchar, len,0, 0);
if(len!=0)
{
str=pchar;
}
delete pchar;
}
delete pwchar;
}
}
return str;
}
//在调用 sqlite_open 前将文件路径，通过MbcsToUtf8函数将编码转换到utf8即可支持中文路径。
bool CSqliteDataCenter::openDB(CString dbName)
{
dbName = MbcsToUtf8(dbName.GetBuffer(dbName.GetLength()));
m_bOpenFlag = false;
m_pErrMsg = NULL;
if(SQLITE_OK == sqlite3_open(dbName.GetBuffer(dbName.GetLength()), &pDbConnection) )
{
m_bOpenFlag = true;
//creaetAllTable 来创建数据库表
if(QueryTableExist("t_file_records")<1)
{
creaetAllTable();
//插入通用模板用户
/* CUserBean cUser(0, time(NULL),"通用用户","password",5,"通用用户",0,0);
if(insertUser(&cUser))
{
TRACE("初始化通用模板用户\r\n");
}else
{
TRACE("初始化通用模板用户----[失败]\r\n");
}*/
}else
{
}
}
dbName.ReleaseBuffer();
return m_bOpenFlag;
}
int CSqliteDataCenter::QueryTableExist(CString strTableName)
{
char acSql[constCharQuerySqlLen256] = {0};
sprintf(acSql,asSqlExistTable,strTableName);
int iCountTemp = 0;
if(!queryTableCount(acSql,iCountTemp))
{
//查询失败了
iCountTemp = -1;
}
return iCountTemp;
}
/* create table */
bool CSqliteDataCenter::creaetAllTable()
{
bool bResult = true;;
char acMsg[constCharMsgMaxLen] = {0};
char *pc = NULL;//&acMsg[0];//(char*) malloc(256);
//memset(pc,0,256);
if(!(SQLITE_OK == sqlite3_exec(pDbConnection, acSqlCreateTable_t_file_records, NULL_CALLBACK, NULL, &m_pErrMsg)))
{
TRACE("CSqliteDataCenter()->%s createUserTable()\r\n",m_pErrMsg);
sqlite3_free(m_pErrMsg);
m_pErrMsg = NULL;
bResult = false;
}else
{
TRACE("创建表:t_user\r\n");
}
return bResult;
}
bool CSqliteDataCenter::insertUser(CFileBean *fb)
{
//TRACE("insertRecord()pad_dev_id=%d,dev_type=%d,result=%d,i64Time=%I64d,source=%d,voltage=%d,current=%d,temp=%d,remark=%d\r\n",rc->pad_dev_id,
char szSqlInsert[constCharQuerySqlLen256] = {0};
sprintf(szSqlInsert,acSqlInsertTable_t_file_record,
fb->strFilePath,
fb->strFileName,
fb->lSize,
fb->byMd5[0],
fb->byRepeat,
fb->byMoved,
fb->iRepeatCount,
fb->ctCreate,
fb->ctModify,
fb->iRemark);
TRACE("CSqliteDataCenter::insertUser()->%s %s\r\n",szSqlInsert,fb->strFileName);
if( SQLITE_OK != sqlite3_exec(pDbConnection , szSqlInsert, NULL_CALLBACK, NULL, &m_pErrMsg) )
{
TRACE("CSqliteDataCenter::insertUser()->%s %s\r\n",m_pErrMsg,fb->strFileName);
sqlite3_free(m_pErrMsg);
m_pErrMsg = NULL;
return false;
}
return true;
}
bool CSqliteDataCenter::queryTableCount(char *sql,int &iCount)
{
iCount = 0;
// 执行SELECT语句查询数据
sqlite3_stmt* stmt3 = NULL;
//acSqlQueryTable_t_Config
if (sqlite3_prepare_v2(pDbConnection,sql,strlen(sql),&stmt3,NULL) != SQLITE_OK)
{
if (stmt3)
{
sqlite3_finalize(stmt3);
}
return false;
}
do
{
int r = sqlite3_step(stmt3);
if (r == SQLITE_ROW)
{
iCount = sqlite3_column_int(stmt3,0);
}
else if (r == SQLITE_DONE)
{
TRACE("Select queryCount Finished\n");
break;
}
else
{
TRACE("Failed to queryCount SELECT.\n");
sqlite3_finalize(stmt3);
return false;
}
} while (true);
sqlite3_finalize(stmt3);
return true;
}
4 调用，#include "sqliteDataCenter.h" 后在::OnInitDialog()中调用
// TODO: Add extra initialization here
CSqliteDataCenter sqlitedc;
sqlitedc.openDB("C:\\Users\\stw\\sqlite3test\\Debug\\filetest.db");
BYTE byMd5[16]={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16};
// CFileBean(int iId,CString strFilePath,CString strFileName,long lSize,unsigned char *pbyMd5,BYTE byRepeat,BYTE byMoved,int iRepeatCount,CTime ctCreate,CTime ctModify,int iRemark)
CFileBean fb(0,"C:\\Users\\stw\\sqlite3test\\Debug\\","filetest.db",111,byMd5,0,1,2,3,4,5);
sqlitedc.insertUser(&fb);5，工具使用说明，下载的sqlite3.exe是可执行文件的工具，可以用它来操作db数据库或创建数据库
完整代码上传到资源。
