SQLite C++操作类
来源:互联网 发布:淘宝买家退款率8% 编辑:程序博客网 时间:2024/06/14 14:27
为了方便SQLite的使用,封装了一个SQLite的C++类,同时支持ANSI 和UNICODE编码。代码如下:
头文件(SQLite.h)
- /********************************************************************
- filename: SQLite.h
- created: 2012-11-05
- author: firehood
- purpose: SQLite数据库操作类
- *********************************************************************/
- #pragma once
- #include <windows.h>
- #include "..\SQLite\sqlite3.h"
- #pragma comment(lib,"SQLite.lib")
- typedef BOOL (WINAPI *QueryCallback) (void *para, int n_column, char **column_value, char **column_name);
- typedef enum _SQLITE_DATATYPE
- {
- SQLITE_DATATYPE_INTEGER = SQLITE_INTEGER,
- SQLITE_DATATYPE_FLOAT = SQLITE_FLOAT,
- SQLITE_DATATYPE_TEXT = SQLITE_TEXT,
- SQLITE_DATATYPE_BLOB = SQLITE_BLOB,
- SQLITE_DATATYPE_NULL= SQLITE_NULL,
- }SQLITE_DATATYPE;
- class SQLite;
- class SQLiteDataReader
- {
- public:
- SQLiteDataReader(sqlite3_stmt *pStmt);
- ~SQLiteDataReader();
- public:
- // 读取一行数据
- BOOL Read();
- // 关闭Reader,读取结束后调用
- void Close();
- // 总的列数
- int ColumnCount(void);
- // 获取某列的名称
- LPCTSTR GetName(int nCol);
- // 获取某列的数据类型
- SQLITE_DATATYPE GetDataType(int nCol);
- // 获取某列的值(字符串)
- LPCTSTR GetStringValue(int nCol);
- // 获取某列的值(整形)
- int GetIntValue(int nCol);
- // 获取某列的值(长整形)
- long GetInt64Value(int nCol);
- // 获取某列的值(浮点形)
- double GetFloatValue(int nCol);
- // 获取某列的值(二进制数据)
- const BYTE* GetBlobValue(int nCol, int &nLen);
- private:
- sqlite3_stmt *m_pStmt;
- };
- class SQLiteCommand
- {
- public:
- SQLiteCommand(SQLite* pSqlite);
- SQLiteCommand(SQLite* pSqlite,LPCTSTR lpSql);
- ~SQLiteCommand();
- public:
- // 设置命令
- BOOL SetCommandText(LPCTSTR lpSql);
- // 绑定参数(index为要绑定参数的序号,从1开始)
- BOOL BindParam(int index, LPCTSTR szValue);
- BOOL BindParam(int index, const int nValue);
- BOOL BindParam(int index, const double dValue);
- BOOL BindParam(int index, const unsigned char* blobValue, int nLen);
- // 执行命令
- BOOL Excute();
- // 清除命令(命令不再使用时需调用该接口清除)
- void Clear();
- private:
- SQLite *m_pSqlite;
- sqlite3_stmt *m_pStmt;
- };
- class SQLite
- {
- public:
- SQLite(void);
- ~SQLite(void);
- public:
- // 打开数据库
- BOOL Open(LPCTSTR lpDbFlie);
- // 关闭数据库
- void Close();
- // 执行非查询操作(更新或删除)
- BOOL ExcuteNonQuery(LPCTSTR lpSql);
- BOOL ExcuteNonQuery(SQLiteCommand* pCmd);
- // 查询
- SQLiteDataReader ExcuteQuery(LPCTSTR lpSql);
- // 查询(回调方式)
- BOOL ExcuteQuery(LPCTSTR lpSql,QueryCallback pCallBack);
- // 开始事务
- BOOL BeginTransaction();
- // 提交事务
- BOOL CommitTransaction();
- // 回滚事务
- BOOL RollbackTransaction();
- // 获取上一条错误信息
- LPCTSTR GetLastErrorMsg();
- public:
- friend class SQLiteCommand;
- private:
- sqlite3 *m_db;
- };
/********************************************************************filename: SQLite.hcreated:2012-11-05author:firehoodpurpose:SQLite数据库操作类*********************************************************************/#pragma once#include <windows.h>#include "..\SQLite\sqlite3.h" #pragma comment(lib,"SQLite.lib") typedef BOOL (WINAPI *QueryCallback) (void *para, int n_column, char **column_value, char **column_name);typedef enum _SQLITE_DATATYPE{SQLITE_DATATYPE_INTEGER = SQLITE_INTEGER,SQLITE_DATATYPE_FLOAT = SQLITE_FLOAT,SQLITE_DATATYPE_TEXT = SQLITE_TEXT,SQLITE_DATATYPE_BLOB = SQLITE_BLOB,SQLITE_DATATYPE_NULL= SQLITE_NULL,}SQLITE_DATATYPE;class SQLite;class SQLiteDataReader{public:SQLiteDataReader(sqlite3_stmt *pStmt);~SQLiteDataReader();public:// 读取一行数据BOOL Read();// 关闭Reader,读取结束后调用void Close();// 总的列数 int ColumnCount(void);// 获取某列的名称 LPCTSTR GetName(int nCol);// 获取某列的数据类型SQLITE_DATATYPE GetDataType(int nCol);// 获取某列的值(字符串)LPCTSTR GetStringValue(int nCol);// 获取某列的值(整形)int GetIntValue(int nCol);// 获取某列的值(长整形)long GetInt64Value(int nCol);// 获取某列的值(浮点形)double GetFloatValue(int nCol);// 获取某列的值(二进制数据)const BYTE* GetBlobValue(int nCol, int &nLen);private:sqlite3_stmt *m_pStmt;};class SQLiteCommand{public:SQLiteCommand(SQLite* pSqlite);SQLiteCommand(SQLite* pSqlite,LPCTSTR lpSql);~SQLiteCommand();public:// 设置命令BOOL SetCommandText(LPCTSTR lpSql);// 绑定参数(index为要绑定参数的序号,从1开始)BOOL BindParam(int index, LPCTSTR szValue);BOOL BindParam(int index, const int nValue);BOOL BindParam(int index, const double dValue);BOOL BindParam(int index, const unsigned char* blobValue, int nLen);// 执行命令BOOL Excute();// 清除命令(命令不再使用时需调用该接口清除)void Clear();private:SQLite *m_pSqlite;sqlite3_stmt *m_pStmt;};class SQLite{public:SQLite(void);~SQLite(void);public:// 打开数据库 BOOL Open(LPCTSTR lpDbFlie);// 关闭数据库void Close(); // 执行非查询操作(更新或删除)BOOL ExcuteNonQuery(LPCTSTR lpSql);BOOL ExcuteNonQuery(SQLiteCommand* pCmd); // 查询SQLiteDataReader ExcuteQuery(LPCTSTR lpSql);// 查询(回调方式)BOOL ExcuteQuery(LPCTSTR lpSql,QueryCallback pCallBack);// 开始事务BOOL BeginTransaction();// 提交事务BOOL CommitTransaction();// 回滚事务BOOL RollbackTransaction();// 获取上一条错误信息LPCTSTR GetLastErrorMsg();public:friend class SQLiteCommand;private:sqlite3 *m_db;};
源文件(SQLite.cpp)
- /********************************************************************
- filename: SQLite.cpp
- created: 2012-11-05
- author: firehood
- purpose: SQLite数据库操作类
- *********************************************************************/
- #include "SQLite.h"
- const char* WcharToUtf8(const wchar_t *pwStr)
- {
- if (pwStr == NULL)
- {
- return NULL;
- }
- int len = WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, NULL, 0, NULL, NULL);
- if (len <= 0)
- {
- return NULL;
- }
- char *pStr = new char[len];
- WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, pStr, len, NULL, NULL);
- return pStr;
- }
- const wchar_t* Utf8ToWchar(const char *pStr)
- {
- if (pStr == NULL)
- {
- return NULL;
- }
- int len = MultiByteToWideChar(CP_UTF8, 0, pStr, -1, NULL, 0);
- if (len <= 0)
- {
- return NULL;
- }
- wchar_t *pwStr = new wchar_t[len];
- MultiByteToWideChar(CP_UTF8, 0, pStr, -1, pwStr, len);
- return pwStr;
- }
- SQLite::SQLite(void):
- m_db(NULL)
- {
- }
- SQLite::~SQLite(void)
- {
- Close();
- }
- BOOL SQLite::Open(LPCTSTR lpDbFlie)
- {
- if(lpDbFlie == NULL)
- {
- return FALSE;
- }
- #ifdef UNICODE
- if(sqlite3_open16(lpDbFlie,&m_db) != SQLITE_OK)
- #else
- if(sqlite3_open(lpDbFlie,&m_db) != SQLITE_OK)
- #endif
- {
- return FALSE;
- }
- return TRUE;
- }
- void SQLite::Close()
- {
- if(m_db)
- {
- sqlite3_close(m_db);
- m_db = NULL;
- }
- }
- BOOL SQLite::ExcuteNonQuery(LPCTSTR lpSql)
- {
- if(lpSql == NULL)
- {
- return FALSE;
- }
- sqlite3_stmt* stmt;
- #ifdef UNICODE
- if(sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
- #else
- if(sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
- #endif
- {
- return FALSE;
- }
- sqlite3_step(stmt);
- return (sqlite3_finalize(stmt) == SQLITE_OK) ? TRUE : FALSE ;
- }
- BOOL SQLite::ExcuteNonQuery(SQLiteCommand* pCmd)
- {
- if(pCmd == NULL)
- {
- return FALSE;
- }
- return pCmd->Excute();
- }
- // 查询(回调方式)
- BOOL SQLite::ExcuteQuery(LPCTSTR lpSql,QueryCallback pCallBack)
- {
- if(lpSql == NULL || pCallBack == NULL)
- {
- return FALSE;
- }
- char *errmsg = NULL;
- #ifdef UNICODE
- const char *szSql = WcharToUtf8(lpSql);
- if(sqlite3_exec(m_db, szSql, pCallBack, NULL, &errmsg) != SQLITE_OK)
- {
- delete[] szSql;
- return FALSE;
- }
- delete[] szSql;
- #else
- if(sqlite3_exec(m_db, lpSql, pCallBack, NULL, &errmsg) != SQLITE_OK)
- {
- return FALSE;
- }
- #endif
- return TRUE;
- }
- // 查询
- SQLiteDataReader SQLite::ExcuteQuery(LPCTSTR lpSql)
- {
- if(lpSql == NULL)
- {
- return FALSE;
- }
- sqlite3_stmt* stmt;
- #ifdef UNICODE
- if(sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
- #else
- if(sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)
- #endif
- {
- return FALSE;
- }
- return SQLiteDataReader(stmt);
- }
- // 开始事务
- BOOL SQLite::BeginTransaction()
- {
- char * errmsg = NULL;
- if(sqlite3_exec(m_db,"BEGIN TRANSACTION;",NULL,NULL,&errmsg) != SQLITE_OK)
- {
- return FALSE;
- }
- return TRUE;
- }
- // 提交事务
- BOOL SQLite::CommitTransaction()
- {
- char * errmsg = NULL;
- if(sqlite3_exec(m_db,"COMMIT TRANSACTION;;",NULL,NULL,&errmsg) != SQLITE_OK)
- {
- return FALSE;
- }
- return TRUE;
- }
- // 回滚事务
- BOOL SQLite::RollbackTransaction()
- {
- char * errmsg = NULL;
- if(sqlite3_exec(m_db,"ROLLBACK TRANSACTION;",NULL,NULL,&errmsg) != SQLITE_OK)
- {
- return FALSE;
- }
- return TRUE;
- }
- // 获取上一条错误信息
- LPCTSTR SQLite::GetLastErrorMsg()
- {
- #ifdef UNICODE
- return (LPCTSTR)sqlite3_errmsg16(m_db);
- #else
- return sqlite3_errmsg(m_db);
- #endif
- }
- SQLiteDataReader::SQLiteDataReader(sqlite3_stmt *pStmt):
- m_pStmt(pStmt)
- {
- }
- SQLiteDataReader::~SQLiteDataReader()
- {
- Close();
- }
- // 读取一行数据
- BOOL SQLiteDataReader::Read()
- {
- if(m_pStmt == NULL)
- {
- return FALSE;
- }
- if(sqlite3_step(m_pStmt) != SQLITE_ROW)
- {
- return FALSE;
- }
- return TRUE;
- }
- // 关闭Reader,读取结束后调用
- void SQLiteDataReader::Close()
- {
- if(m_pStmt)
- {
- sqlite3_finalize(m_pStmt);
- m_pStmt = NULL;
- }
- }
- // 总的列数
- int SQLiteDataReader::ColumnCount(void)
- {
- return sqlite3_column_count(m_pStmt);
- }
- // 获取某列的名称
- LPCTSTR SQLiteDataReader::GetName(int nCol)
- {
- #ifdef UNICODE
- return (LPCTSTR)sqlite3_column_name16(m_pStmt, nCol);
- #else
- return (LPCTSTR)sqlite3_column_name(m_pStmt, nCol);
- #endif
- }
- // 获取某列的数据类型
- SQLITE_DATATYPE SQLiteDataReader::GetDataType(int nCol)
- {
- return (SQLITE_DATATYPE)sqlite3_column_type(m_pStmt, nCol);
- }
- // 获取某列的值(字符串)
- LPCTSTR SQLiteDataReader::GetStringValue(int nCol)
- {
- #ifdef UNICODE
- return (LPCTSTR)sqlite3_column_text16(m_pStmt, nCol);
- #else
- return (LPCTSTR)sqlite3_column_text(m_pStmt, nCol);
- #endif
- }
- // 获取某列的值(整形)
- int SQLiteDataReader::GetIntValue(int nCol)
- {
- return sqlite3_column_int(m_pStmt, nCol);
- }
- // 获取某列的值(长整形)
- long SQLiteDataReader::GetInt64Value(int nCol)
- {
- return (long)sqlite3_column_int64(m_pStmt, nCol);
- }
- // 获取某列的值(浮点形)
- double SQLiteDataReader::GetFloatValue(int nCol)
- {
- return sqlite3_column_double(m_pStmt, nCol);
- }
- // 获取某列的值(二进制数据)
- const BYTE* SQLiteDataReader::GetBlobValue(int nCol, int &nLen)
- {
- nLen = sqlite3_column_bytes(m_pStmt, nCol);
- return (const BYTE*)sqlite3_column_blob(m_pStmt, nCol);
- }
- SQLiteCommand::SQLiteCommand(SQLite* pSqlite):
- m_pSqlite(pSqlite),
- m_pStmt(NULL)
- {
- }
- SQLiteCommand::SQLiteCommand(SQLite* pSqlite,LPCTSTR lpSql):
- m_pSqlite(pSqlite),
- m_pStmt(NULL)
- {
- SetCommandText(lpSql);
- }
- SQLiteCommand::~SQLiteCommand()
- {
- }
- BOOL SQLiteCommand::SetCommandText(LPCTSTR lpSql)
- {
- #ifdef UNICODE
- if(sqlite3_prepare16_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)
- #else
- if(sqlite3_prepare_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)
- #endif
- {
- return FALSE;
- }
- return TRUE;
- }
- BOOL SQLiteCommand::BindParam(int index, LPCTSTR szValue)
- {
- #ifdef UNICODE
- if(sqlite3_bind_text16(m_pStmt, index, szValue, -1, SQLITE_TRANSIENT) != SQLITE_OK)
- #else
- if(sqlite3_bind_text(m_pStmt, index, szValue,-1, SQLITE_TRANSIENT) != SQLITE_OK)
- #endif
- {
- return FALSE;
- }
- return TRUE;
- }
- BOOL SQLiteCommand::BindParam(int index, const int nValue)
- {
- if(sqlite3_bind_int(m_pStmt, index, nValue) != SQLITE_OK)
- {
- return FALSE;
- }
- return TRUE;
- }
- BOOL SQLiteCommand::BindParam(int index, const double dValue)
- {
- if(sqlite3_bind_double(m_pStmt, index, dValue) != SQLITE_OK)
- {
- return FALSE;
- }
- return TRUE;
- }
- BOOL SQLiteCommand::BindParam(int index, const unsigned char* blobBuf, int nLen)
- {
- if(sqlite3_bind_blob(m_pStmt, index, blobBuf,nLen,NULL) != SQLITE_OK)
- {
- return FALSE;
- }
- return TRUE;
- }
- BOOL SQLiteCommand::Excute()
- {
- sqlite3_step(m_pStmt);
- return (sqlite3_reset(m_pStmt) == SQLITE_OK) ? TRUE : FALSE ;
- }
- void SQLiteCommand::Clear()
- {
- if(m_pStmt)
- {
- sqlite3_finalize(m_pStmt);
- }
- }
/********************************************************************filename: SQLite.cppcreated:2012-11-05author:firehoodpurpose:SQLite数据库操作类*********************************************************************/#include "SQLite.h"const char* WcharToUtf8(const wchar_t *pwStr){if (pwStr == NULL){return NULL;}int len = WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, NULL, 0, NULL, NULL);if (len <= 0){return NULL;}char *pStr = new char[len];WideCharToMultiByte(CP_UTF8, 0, pwStr, -1, pStr, len, NULL, NULL);return pStr;}const wchar_t* Utf8ToWchar(const char *pStr){if (pStr == NULL){return NULL;}int len = MultiByteToWideChar(CP_UTF8, 0, pStr, -1, NULL, 0);if (len <= 0){return NULL;}wchar_t *pwStr = new wchar_t[len];MultiByteToWideChar(CP_UTF8, 0, pStr, -1, pwStr, len);return pwStr;}SQLite::SQLite(void):m_db(NULL){}SQLite::~SQLite(void){Close();}BOOL SQLite::Open(LPCTSTR lpDbFlie){if(lpDbFlie == NULL){return FALSE;}#ifdef UNICODE if(sqlite3_open16(lpDbFlie,&m_db) != SQLITE_OK)#else if(sqlite3_open(lpDbFlie,&m_db) != SQLITE_OK)#endif{return FALSE;}return TRUE;}void SQLite::Close(){if(m_db){sqlite3_close(m_db);m_db = NULL;}}BOOL SQLite::ExcuteNonQuery(LPCTSTR lpSql){if(lpSql == NULL){return FALSE;} sqlite3_stmt* stmt; #ifdef UNICODE if(sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#else if(sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#endif{ return FALSE; } sqlite3_step(stmt);return (sqlite3_finalize(stmt) == SQLITE_OK) ? TRUE : FALSE ;}BOOL SQLite::ExcuteNonQuery(SQLiteCommand* pCmd){if(pCmd == NULL){return FALSE;}return pCmd->Excute();}// 查询(回调方式)BOOL SQLite::ExcuteQuery(LPCTSTR lpSql,QueryCallback pCallBack){if(lpSql == NULL || pCallBack == NULL){return FALSE;}char *errmsg = NULL;#ifdef UNICODE const char *szSql = WcharToUtf8(lpSql);if(sqlite3_exec(m_db, szSql, pCallBack, NULL, &errmsg) != SQLITE_OK){delete[] szSql;return FALSE;} delete[] szSql;#elseif(sqlite3_exec(m_db, lpSql, pCallBack, NULL, &errmsg) != SQLITE_OK){return FALSE;} #endifreturn TRUE;}// 查询SQLiteDataReader SQLite::ExcuteQuery(LPCTSTR lpSql){if(lpSql == NULL){return FALSE;} sqlite3_stmt* stmt; #ifdef UNICODE if(sqlite3_prepare16_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#elseif(sqlite3_prepare_v2(m_db, lpSql, -1, &stmt, NULL) != SQLITE_OK)#endif{ return FALSE; } return SQLiteDataReader(stmt);}// 开始事务BOOL SQLite::BeginTransaction(){char * errmsg = NULL; if(sqlite3_exec(m_db,"BEGIN TRANSACTION;",NULL,NULL,&errmsg) != SQLITE_OK){return FALSE;} return TRUE;}// 提交事务BOOL SQLite::CommitTransaction(){char * errmsg = NULL;if(sqlite3_exec(m_db,"COMMIT TRANSACTION;;",NULL,NULL,&errmsg) != SQLITE_OK){return FALSE;} return TRUE;}// 回滚事务BOOL SQLite::RollbackTransaction(){char * errmsg = NULL;if(sqlite3_exec(m_db,"ROLLBACK TRANSACTION;",NULL,NULL,&errmsg) != SQLITE_OK){return FALSE;} return TRUE;}// 获取上一条错误信息LPCTSTR SQLite::GetLastErrorMsg(){#ifdef UNICODE return (LPCTSTR)sqlite3_errmsg16(m_db);#else return sqlite3_errmsg(m_db);#endif}SQLiteDataReader::SQLiteDataReader(sqlite3_stmt *pStmt):m_pStmt(pStmt){}SQLiteDataReader::~SQLiteDataReader(){Close();}// 读取一行数据BOOL SQLiteDataReader::Read(){if(m_pStmt == NULL){return FALSE;}if(sqlite3_step(m_pStmt) != SQLITE_ROW){return FALSE;}return TRUE;}// 关闭Reader,读取结束后调用void SQLiteDataReader::Close(){if(m_pStmt){sqlite3_finalize(m_pStmt);m_pStmt = NULL;}}// 总的列数int SQLiteDataReader::ColumnCount(void){return sqlite3_column_count(m_pStmt);}// 获取某列的名称 LPCTSTR SQLiteDataReader::GetName(int nCol){#ifdef UNICODE return (LPCTSTR)sqlite3_column_name16(m_pStmt, nCol);#elsereturn (LPCTSTR)sqlite3_column_name(m_pStmt, nCol);#endif}// 获取某列的数据类型SQLITE_DATATYPE SQLiteDataReader::GetDataType(int nCol){return (SQLITE_DATATYPE)sqlite3_column_type(m_pStmt, nCol);}// 获取某列的值(字符串)LPCTSTR SQLiteDataReader::GetStringValue(int nCol){#ifdef UNICODE return (LPCTSTR)sqlite3_column_text16(m_pStmt, nCol);#else return (LPCTSTR)sqlite3_column_text(m_pStmt, nCol);#endif}// 获取某列的值(整形)int SQLiteDataReader::GetIntValue(int nCol){return sqlite3_column_int(m_pStmt, nCol);}// 获取某列的值(长整形)long SQLiteDataReader::GetInt64Value(int nCol){return (long)sqlite3_column_int64(m_pStmt, nCol);}// 获取某列的值(浮点形)double SQLiteDataReader::GetFloatValue(int nCol){return sqlite3_column_double(m_pStmt, nCol);}// 获取某列的值(二进制数据)const BYTE* SQLiteDataReader::GetBlobValue(int nCol, int &nLen){nLen = sqlite3_column_bytes(m_pStmt, nCol);return (const BYTE*)sqlite3_column_blob(m_pStmt, nCol);}SQLiteCommand::SQLiteCommand(SQLite* pSqlite):m_pSqlite(pSqlite),m_pStmt(NULL){}SQLiteCommand::SQLiteCommand(SQLite* pSqlite,LPCTSTR lpSql):m_pSqlite(pSqlite),m_pStmt(NULL){SetCommandText(lpSql);}SQLiteCommand::~SQLiteCommand(){}BOOL SQLiteCommand::SetCommandText(LPCTSTR lpSql){#ifdef UNICODE if(sqlite3_prepare16_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)#elseif(sqlite3_prepare_v2(m_pSqlite->m_db, lpSql, -1, &m_pStmt, NULL) != SQLITE_OK)#endif{ return FALSE; } return TRUE;}BOOL SQLiteCommand::BindParam(int index, LPCTSTR szValue){#ifdef UNICODE if(sqlite3_bind_text16(m_pStmt, index, szValue, -1, SQLITE_TRANSIENT) != SQLITE_OK)#else if(sqlite3_bind_text(m_pStmt, index, szValue,-1, SQLITE_TRANSIENT) != SQLITE_OK)#endif{return FALSE;}return TRUE;}BOOL SQLiteCommand::BindParam(int index, const int nValue){if(sqlite3_bind_int(m_pStmt, index, nValue) != SQLITE_OK){return FALSE;}return TRUE;}BOOL SQLiteCommand::BindParam(int index, const double dValue){if(sqlite3_bind_double(m_pStmt, index, dValue) != SQLITE_OK){return FALSE;}return TRUE;}BOOL SQLiteCommand::BindParam(int index, const unsigned char* blobBuf, int nLen){if(sqlite3_bind_blob(m_pStmt, index, blobBuf,nLen,NULL) != SQLITE_OK){return FALSE;}return TRUE;}BOOL SQLiteCommand::Excute(){sqlite3_step(m_pStmt);return (sqlite3_reset(m_pStmt) == SQLITE_OK) ? TRUE : FALSE ;}void SQLiteCommand::Clear(){if(m_pStmt){sqlite3_finalize(m_pStmt);}}
使用方法
通过SQLite类操作数据库的基本代码如下:
- void SqliteOperate()
- {
- TCHAR *szDbPath = _T("Book.db");
- ::DeleteFile(szDbPath);
- SQLite sqlite;
- // 打开或创建数据库
- //******************************************************
- if(!sqlite.Open(szDbPath))
- {
- _tprintf(_T("%s\n"),sqlite.GetLastErrorMsg());
- return;
- }
- //******************************************************
- // 创建数据库表
- //******************************************************
- TCHAR sql[512] = {0};
- _stprintf(sql,_T("%s"),
- _T("CREATE TABLE [Book] (")
- _T("[id] INTEGER NOT NULL PRIMARY KEY, ")
- _T("[name] NVARCHAR(20), ")
- _T("[author] NVARCHAR(20), ")
- _T("[catagory_id] INTEGER REFERENCES [Category]([id]), ")
- _T("[abstruct] NVARCHAR(100) ,")
- _T("[path] NVARCHAR(50), ")
- _T("[image] BLOB);")
- _T("CREATE INDEX [Book_id] ON [Book] ([id]);")
- );
- if(!sqlite.ExcuteNonQuery(sql))
- {
- printf("Create database table failed...\n");
- }
- //******************************************************
- // 插入数据【普通方式】
- DWORD dwBeginTick = GetTickCount();
- //******************************************************
- // 当一次性插入多条记录时候,采用事务的方式,提高效率
- sqlite.BeginTransaction();
- // 批量插入数据
- for(int i=0;i<1000;i++)
- {
- memset(sql,0,sizeof(sql));
- _stprintf(sql,_T("insert into Book(name,author,catagory_id) values('红高粱%d','莫言',1)"),i);
- if(!sqlite.ExcuteNonQuery(sql))
- {
- _tprintf(_T("%s\n"),sqlite.GetLastErrorMsg());
- break;
- }
- }
- // 提交事务
- sqlite.CommitTransaction();
- printf("Insert Data Take %dMS...\n",GetTickCount()-dwBeginTick);
- //******************************************************
- // 插入数据【通过参数绑定的方式,提交批量数据时,比上面的普通模式效率更高(提高约45%),同时可支持插入二进制数据】
- dwBeginTick = GetTickCount();
- //******************************************************
- // 当一次性插入多条记录时候,采用事务的方式,提高效率
- sqlite.BeginTransaction();
- memset(sql,0,sizeof(sql));
- _stprintf(sql,_T("insert into Book(name,author,catagory_id,image) values(?,'韩寒',?,?)"));
- SQLiteCommand cmd(&sqlite,sql);
- // 批量插入数据
- for(int i=0;i<1000;i++)
- {
- TCHAR strValue[16] = {0};
- _stprintf(strValue,_T("他的国%d"),i);
- // 绑定第一个参数(name字段值)
- cmd.BindParam(1,strValue);
- // 绑定第二个参数(catagory_id字段值)
- cmd.BindParam(2,20);
- BYTE imageBuf[] = {0xff,0xff,0xff,0xff};
- // 绑定第三个参数(image字段值,二进制数据)
- cmd.BindParam(3,imageBuf,sizeof(imageBuf));
- if(!sqlite.ExcuteNonQuery(&cmd))
- {
- _tprintf(_T("%s\n"),sqlite.GetLastErrorMsg());
- break;
- }
- }
- // 清空cmd
- cmd.Clear();
- // 提交事务
- sqlite.CommitTransaction();
- printf("Insert Data Take %dMS...\n",GetTickCount()-dwBeginTick);
- //******************************************************
- // 查询
- dwBeginTick = GetTickCount();
- //******************************************************
- memset(sql,0,sizeof(sql));
- _stprintf(sql,_T("%s"),_T("select * from Book where name = '他的国345'"));
- SQLiteDataReader Reader = sqlite.ExcuteQuery(sql);
- int index = 0;
- int len = 0;
- while(Reader.Read())
- {
- _tprintf( _T("***************【第%d条记录】***************\n"),++index);
- _tprintf( _T("字段名:%s 字段值:%d\n"),Reader.GetName(0),Reader.GetIntValue(0));
- _tprintf( _T("字段名:%s 字段值:%s\n"),Reader.GetName(1),Reader.GetStringValue(1));
- _tprintf( _T("字段名:%s 字段值:%s\n"),Reader.GetName(2),Reader.GetStringValue(2));
- _tprintf( _T("字段名:%s 字段值:%d\n"),Reader.GetName(3),Reader.GetIntValue(3));
- _tprintf( _T("字段名:%s 字段值:%s\n"),Reader.GetName(4),Reader.GetStringValue(4));
- // 读取图片二进制文件
- const BYTE *ImageBuf = Reader.GetBlobValue(6,len);
- _tprintf( _T("*******************************************\n"));
- }
- Reader.Close();
- printf("Query Take %dMS...\n",GetTickCount()-dwBeginTick);
- //******************************************************
- // 关闭数据库
- sqlite.Close();
- }
void SqliteOperate(){TCHAR *szDbPath = _T("Book.db");::DeleteFile(szDbPath);SQLite sqlite;// 打开或创建数据库//******************************************************if(!sqlite.Open(szDbPath)){_tprintf(_T("%s\n"),sqlite.GetLastErrorMsg());return;} //******************************************************// 创建数据库表 //******************************************************TCHAR sql[512] = {0};_stprintf(sql,_T("%s"),_T("CREATE TABLE [Book] (")_T("[id] INTEGER NOT NULL PRIMARY KEY, ")_T("[name] NVARCHAR(20), ")_T("[author] NVARCHAR(20), ")_T("[catagory_id] INTEGER REFERENCES [Category]([id]), ")_T("[abstruct] NVARCHAR(100) ,")_T("[path] NVARCHAR(50), ")_T("[image] BLOB);")_T("CREATE INDEX [Book_id] ON [Book] ([id]);"));if(!sqlite.ExcuteNonQuery(sql)){printf("Create database table failed...\n");}//******************************************************// 插入数据【普通方式】DWORD dwBeginTick = GetTickCount();//******************************************************// 当一次性插入多条记录时候,采用事务的方式,提高效率sqlite.BeginTransaction();// 批量插入数据for(int i=0;i<1000;i++){memset(sql,0,sizeof(sql));_stprintf(sql,_T("insert into Book(name,author,catagory_id) values('红高粱%d','莫言',1)"),i);if(!sqlite.ExcuteNonQuery(sql)){_tprintf(_T("%s\n"),sqlite.GetLastErrorMsg());break;}}// 提交事务sqlite.CommitTransaction();printf("Insert Data Take %dMS...\n",GetTickCount()-dwBeginTick); //******************************************************// 插入数据【通过参数绑定的方式,提交批量数据时,比上面的普通模式效率更高(提高约45%),同时可支持插入二进制数据】dwBeginTick = GetTickCount(); //******************************************************// 当一次性插入多条记录时候,采用事务的方式,提高效率sqlite.BeginTransaction();memset(sql,0,sizeof(sql));_stprintf(sql,_T("insert into Book(name,author,catagory_id,image) values(?,'韩寒',?,?)"));SQLiteCommand cmd(&sqlite,sql);// 批量插入数据for(int i=0;i<1000;i++){TCHAR strValue[16] = {0};_stprintf(strValue,_T("他的国%d"),i);// 绑定第一个参数(name字段值)cmd.BindParam(1,strValue);// 绑定第二个参数(catagory_id字段值)cmd.BindParam(2,20);BYTE imageBuf[] = {0xff,0xff,0xff,0xff};// 绑定第三个参数(image字段值,二进制数据)cmd.BindParam(3,imageBuf,sizeof(imageBuf));if(!sqlite.ExcuteNonQuery(&cmd)){_tprintf(_T("%s\n"),sqlite.GetLastErrorMsg());break;}}// 清空cmdcmd.Clear();// 提交事务sqlite.CommitTransaction();printf("Insert Data Take %dMS...\n",GetTickCount()-dwBeginTick); //******************************************************// 查询dwBeginTick = GetTickCount();//******************************************************memset(sql,0,sizeof(sql));_stprintf(sql,_T("%s"),_T("select * from Book where name = '他的国345'"));SQLiteDataReader Reader = sqlite.ExcuteQuery(sql);int index = 0;int len = 0;while(Reader.Read()){_tprintf( _T("***************【第%d条记录】***************\n"),++index);_tprintf( _T("字段名:%s 字段值:%d\n"),Reader.GetName(0),Reader.GetIntValue(0));_tprintf( _T("字段名:%s 字段值:%s\n"),Reader.GetName(1),Reader.GetStringValue(1));_tprintf( _T("字段名:%s 字段值:%s\n"),Reader.GetName(2),Reader.GetStringValue(2));_tprintf( _T("字段名:%s 字段值:%d\n"),Reader.GetName(3),Reader.GetIntValue(3));_tprintf( _T("字段名:%s 字段值:%s\n"),Reader.GetName(4),Reader.GetStringValue(4));// 读取图片二进制文件const BYTE *ImageBuf = Reader.GetBlobValue(6,len);_tprintf( _T("*******************************************\n"));}Reader.Close();printf("Query Take %dMS...\n",GetTickCount()-dwBeginTick); //******************************************************// 关闭数据库sqlite.Close();}
运行结果
Insert Data Take 645MS...
Insert Data Take 229MS...
***************【第1条记录】***************
字段名:id 字段值:1346
字段名:name 字段值:他的国345
字段名:author 字段值:韩寒
字段名:catagory_id 字段值:20
字段名:abstruct 字段值:(null)
*******************************************
Query Take 63MS...
- C#SQLite操作类
- c/c++ 操作sqlite
- c/c++ 操作sqlite
- c/c++ 操作sqlite
- C操作SQLite数据库
- C操作SQLite数据库
- Objective-c简单操作SQLite
- iOS sqlite C语言操作
- Sqlite 操作类(转)
- sqlite操作类
- SQLite数据库操作类
- SQLite C++操作类
- SQLite数据库操作类
- SQLite C++操作类
- SQLite C++操作类
- SQLite C++操作类
- SQLite 数据库操作类
- Sqlite数据库操作类
- 我的亲身经历说说C++各种IDE的比较
- 基于SQLITE数据库的C语言编程
- 静态方法
- 比赛调试利器-freopen
- Aizu - 0015
- SQLite C++操作类
- weka链接SQLserver
- Matlab中cov函数的算法
- 代码块与静态代码块
- boost::asio 无法接受新连接的处理方法
- 一步一步实现C++操作SQLite数据库
- SQlite数据库的C编程接口(一) 简介 ——《Using SQlite》读书笔记
- SQlite数据库的C编程接口(二) 数据库连接 ——《Using SQlite》读书笔记
- linux 中echo命令的换行方法