sqlite3-入门日记4-实现C++类封装

一、前言：

今天试了下如何用C++类实现接口封装，感觉蛮好 。用于封装的类主要有两个，SQLiteStatement类和SQLiteWrapper类，是一个老外写的。我看了下源码，主要是对C接口进行了封装，好处自然不用说，可以重用。很佩服老外的技巧，在这里就引用下他们的代码供大家分享下他们的思想。

源代码链接： http://www.adp-gmbh.ch/sqlite/wrapper.html

二、类源码：

1.头文件：SQLiteWrapper.h
SQLiteWrapper.h

1/*2 SQLiteWrapper.h34 Copyright (C) 2004 René Nyffenegger56 This source code is provided 'as-is', without any express or implied7 warranty. In no event will the author be held liable for any damages8 arising from the use of this software.910 Permission is granted to anyone to use this software for any purpose,11 including commercial applications, and to alter it and redistribute it12 freely, subject to the following restrictions:1314 1. The origin of this source code must not be misrepresented; you must not15 claim that you wrote the original source code. If you use this source code16 in a product, an acknowledgment in the product documentation would be17 appreciated but is not required.1819 2. Altered source versions must be plainly marked as such, and must not be20 misrepresented as being the original source code.2122 3. This notice may not be removed or altered from any source distribution.2324 René Nyffenegger rene.nyffenegger@adp-gmbh.ch25  */2627 #ifndef SQLITE_WRAPPER_H__28#define SQLITE_WRAPPER_H__2930 #include <string>31 #include <vector>3233 #include "sqlite3.h"3435class SQLiteStatement{36private:37// SQLiteStatement's ctor only to be called by SQLiteWrapper38   friend class SQLiteWrapper;39 SQLiteStatement(std::stringconst& statement, sqlite3* db);4041public:42 SQLiteStatement();4344enum dataType{45 INT=SQLITE_INTEGER,46 FLT=SQLITE_FLOAT,47 TXT=SQLITE_TEXT,48 BLB=SQLITE_BLOB,49 NUL=SQLITE_NULL,50 };5152 dataType DataType(int pos_zero_indexed);5354int ValueInt(int pos_zero_indexed);55 std::string ValueString(int pos_zero_indexed);5657// SQLiteStatement(const SQLiteStatement&);58~SQLiteStatement();5960//SQLiteStatement& operator=(SQLiteStatement const&);61  62bool Bind(int pos_zero_indexed, std::stringconst& value);63bool Bind(int pos_zero_indexed, double value);64bool Bind(int pos_zero_indexed, int value);65bool BindNull(int pos_zero_indexed);6667bool Execute();6869bool NextRow();7071/* Call Reset if not depending on the NextRow cleaning up.72 For example for select count(*) statements*/73bool Reset();7475bool RestartSelect();7677private:78//void DecreaseRefCounter();7980//int* ref_counter_; // not yet used...81   sqlite3_stmt* stmt_;82 };8384class SQLiteWrapper {85public:86 SQLiteWrapper();8788bool Open(std::stringconst& db_file);8990class ResultRecord {91public:92 std::vector<std::string> fields_;93 };9495class ResultTable {96 friend class SQLiteWrapper;97public:98 ResultTable():ptr_cur_record_(0){}99100 std::vector<ResultRecord> records_;101102 ResultRecord* next(){103if (ptr_cur_record_<records_.size()){104return&(records_[ptr_cur_record_++]);105 }106return0;107 }108109private:110void reset(){111 records_.clear();112 ptr_cur_record_=0;113 }114115private:116 unsigned int ptr_cur_record_;117 };118119bool SelectStmt(std::stringconst& stmt,ResultTable& res);120bool DirectStatement(std::stringconst& stmt);121 SQLiteStatement* Statement(std::stringconst& stmt);122123 std::string LastError();124125// Transaction related126bool Begin();127bool Commit();128bool Rollback();129130private:131132staticint SelectCallback(void*p_data,int num_fields,char**p_fields,char**p_col_names);133134 sqlite3* db_;135 };136137#endif

2.类实现文件：SQLiteWrapper.cpp
SQLiteWrapper.cpp

1/*2 SQLiteWrapper.cpp34 Copyright (C) 2004 René Nyffenegger56 This source code is provided 'as-is', without any express or implied7 warranty. In no event will the author be held liable for any damages8 arising from the use of this software.910 Permission is granted to anyone to use this software for any purpose,11 including commercial applications, and to alter it and redistribute it12 freely, subject to the following restrictions:1314 1. The origin of this source code must not be misrepresented; you must not15 claim that you wrote the original source code. If you use this source code16 in a product, an acknowledgment in the product documentation would be17 appreciated but is not required.1819 2. Altered source versions must be plainly marked as such, and must not be20 misrepresented as being the original source code.2122 3. This notice may not be removed or altered from any source distribution.2324 René Nyffenegger rene.nyffenegger@adp-gmbh.ch2526 */2728 #include "SQLiteWrapper.h"29// TODO: raus30 #include <windows.h>3132 SQLiteWrapper::SQLiteWrapper():db_(0){33 }3435bool SQLiteWrapper::Open(std::stringconst& db_file){36if(sqlite3_open(db_file.c_str(),&db_)!=SQLITE_OK){37returnfalse;38 }39returntrue;40 }4142bool SQLiteWrapper::SelectStmt(std::stringconst& stmt,ResultTable& res){43char*errmsg;44int ret;4546 res.reset();4748 ret=sqlite3_exec(db_,stmt.c_str(),SelectCallback,static_cast<void*>(&res),&errmsg);4950if (ret!=SQLITE_OK){51returnfalse;52 }53returntrue;54// if (ret != SQLITE_OK) {55// std::cout << stmt << " [" << errmsg << "]" << std::endl;56// }57 }5859// TODO parameter p_col_names60int SQLiteWrapper::SelectCallback(void*p_data,int num_fields,char**p_fields,char** p_col_names) {61 ResultTable* res=reinterpret_cast<ResultTable*>(p_data);6263 ResultRecord record;6465 #ifdef SQLITE_WRAPPER_REPORT_COLUMN_NAMES66// Hubert Castelain: column names in the first row of res if res is empty6768if(res->records_.size()==0) {69 ResultRecord col_names;7071for(int i=0;i<num_fields;i++){72if(p_fields[i])73 col_names.fields_.push_back(p_col_names[i]);74else75 col_names.fields_.push_back("(null)"); // or what else ?76 }77 res->records_.push_back(col_names);78 }79#endif8081for(int i=0;i<num_fields;i++) {82// Hubert Castelain: special treatment if null83if(p_fields[i])84 record.fields_.push_back(p_fields[i]);85else86 record.fields_.push_back("<null>");87 }8889 res->records_.push_back(record);9091return0;92 }9394 SQLiteStatement* SQLiteWrapper::Statement(std::stringconst& statement){95 SQLiteStatement* stmt;96try{97 stmt=new SQLiteStatement(statement,db_);98return stmt;99 }100catch(constchar* e){101return0;102 }103 }104105 SQLiteStatement::SQLiteStatement(std::stringconst& statement,sqlite3* db) {106if(sqlite3_prepare(107 db,108 statement.c_str(), // stmt109-1, /*读取的字节长度，若小于0，则终止；若大于0，则为能读取的最大字节数*/110&stmt_, /*用来指向输入参数中下一个需要编译的SQL语句存放的SQLite statement对象的指针*/1110/*指针：指向stmt未编译的部分*/112 )113!=SQLITE_OK){114throw sqlite3_errmsg(db);115 }116117if(!stmt_){118throw"stmt_ is 0";119 }120 }121122 SQLiteStatement::~SQLiteStatement(){123125// 语法: int sqlite3_finalize(sqlite3_stmt *pStmt);126if(stmt_) sqlite3_finalize(stmt_);127 }128129 SQLiteStatement::SQLiteStatement():130 stmt_(0)131 {132 }133134bool SQLiteStatement::Bind(int pos_zero_indexed,std::stringconst& value){135if (sqlite3_bind_text(136 stmt_,137 pos_zero_indexed+1, // 通配符索引138 value.c_str(),139 value.length(), // 文本长度140 SQLITE_TRANSIENT // SQLITE_TRANSIENT: SQLite 自我复制141 )142!=SQLITE_OK) {143returnfalse;144 }145returntrue;146 }147148bool SQLiteStatement::Bind(int pos_zero_indexed, double value) {149if(sqlite3_bind_double(150 stmt_,151 pos_zero_indexed+1, // 通配符索引152 value153 )154!=SQLITE_OK) {155returnfalse;156 }157returntrue;158 }159160bool SQLiteStatement::Bind(int pos_zero_indexed,int value) {161if (sqlite3_bind_int(162 stmt_,163 pos_zero_indexed+1, // 通配符索引164 value165 )166!=SQLITE_OK){167returnfalse;168 }169returntrue;170 }171172bool SQLiteStatement::BindNull(int pos_zero_indexed){173if (sqlite3_bind_null(174 stmt_,175 pos_zero_indexed+1// 通配符索引176 )177!=SQLITE_OK) {178returnfalse;179 }180returntrue;181 }182183bool SQLiteStatement::Execute() {184int rc=sqlite3_step(stmt_);185if (rc==SQLITE_BUSY){186 ::MessageBox(0,"SQLITE_BUSY",0,0);187returnfalse;188 }189if(rc==SQLITE_ERROR){190 ::MessageBox(0,"SQLITE_ERROR",0,0);191returnfalse;192 }193if(rc==SQLITE_MISUSE){194 ::MessageBox(0,"SQLITE_ERROR",0,0);195returnfalse;196 }197if(rc!=SQLITE_DONE){198//sqlite3_reset(stmt_);199returnfalse;200 }201 sqlite3_reset(stmt_);202returntrue;203 }204205 SQLiteStatement::dataType SQLiteStatement::DataType(int pos_zero_indexed){206return dataType(sqlite3_column_type(stmt_, pos_zero_indexed));207 }208209int SQLiteStatement::ValueInt(int pos_zero_indexed){210return sqlite3_column_int(stmt_,pos_zero_indexed);211 }212213 std::string SQLiteStatement::ValueString(int pos_zero_indexed){214return std::string(reinterpret_cast<constchar*>(sqlite3_column_text(stmt_,pos_zero_indexed)));215 }216217bool SQLiteStatement::RestartSelect(){218 sqlite3_reset(stmt_);219returntrue;220 }221222bool SQLiteStatement::Reset(){223int rc=sqlite3_step(stmt_);224225 sqlite3_reset(stmt_);226227if (rc==SQLITE_ROW) returntrue;228returnfalse;229 }230231bool SQLiteStatement::NextRow() {232int rc=sqlite3_step(stmt_);233234if (rc==SQLITE_ROW){235returntrue;236 }237if(rc==SQLITE_DONE){238 sqlite3_reset(stmt_);239returnfalse;240 }241elseif(rc==SQLITE_MISUSE){242 ::MessageBox(0,"SQLiteStatement::NextRow SQLITE_MISUSE",0,0);243 }244elseif(rc==SQLITE_BUSY){245 ::MessageBox(0,"SQLiteStatement::NextRow SQLITE_BUSY",0,0);246 }247elseif(rc==SQLITE_ERROR){248 ::MessageBox(0,"SQLiteStatement::NextRow SQLITE_ERROR",0,0);249 }250returnfalse;251 }252253bool SQLiteWrapper::DirectStatement(std::stringconst& stmt){254char*errmsg;255int ret;256257 ret=sqlite3_exec(db_,stmt.c_str(),0,0,&errmsg);258259if(ret!=SQLITE_OK) {260returnfalse;261 }262returntrue;263264//if(ret != SQLITE_OK) {265// std::cout << stmt << " [" << errmsg << "]" << std::endl;266//}267 }268269 std::string SQLiteWrapper::LastError(){270return sqlite3_errmsg(db_);271 }272273bool SQLiteWrapper::Begin(){274return DirectStatement("begin");275 }276277bool SQLiteWrapper::Commit(){278return DirectStatement("commit");279 }280281bool SQLiteWrapper::Rollback(){282return DirectStatement("rollback");283 }

3.实例文件：

3.1 创建数据库和数据库表：Create_DB_Table.cpp
Create_DB_Table.cpp

1/*2 * 功能：创建数据库和数据库表。3 * open()：若指定数据库不存在，则创建；否则打开4 * DirectStatement():可以用于执行大部分SQL语句，但对SELECT语句有些例外。5 *6 */7 #include <iostream>8 #include "SQLiteWrapper.h"910int main() {11 SQLiteWrapper sqlite;12if (sqlite.Open("SQLiteWrapper.db")) {13 std::cout<<"SQLiteWrapper.db created or opened"<<std::endl;14 }15else {16 std::cout<<"couldn't open SQLiteWrapper.db"<<std::endl;17 }1819if(sqlite.DirectStatement("Create table foo(bar,baz)")){20 std::cout<<"table foo created"<<std::endl;21 }22else23 std::cout<<"couldn't insert into foo"<<std::endl;2425return0;26 }

3.2 插入数据：Insert_DB_Data.cpp
Insert_DB_Data.cpp

1/*2 * 功能：向数据库表插入记录3 *4 */5 #include <iostream>67 #include "SQLiteWrapper.h"89int main() {10 SQLiteWrapper sqlite;11if (sqlite.Open("SQLiteWrapper.db")) {12 std::cout<<"SQLiteWrapper.db created or opened"<<std::endl;13 }14else {15 std::cout<<"couldn't open SQLiteWrapper.db"<<std::endl;16 }1718if(sqlite.DirectStatement("insert into foo values(1,2)")){19 std::cout<<"values(1,2) into foo inserted"<<std::endl;2021 }22else23 std::cout<<"couldn't insert into foo"<<std::endl;2425return0;26 }

3.3 绑定参数执行：Bind_Param_Execute.cpp
Bind_Param_Execute.cpp

1/*2 * 功能：Bind()封装sqlite3_bind_*系列函数，类中表现为重载函数，给SQL声明中的通配符赋值，若未绑定，则为空3 * Execute():实现Sqlite3_step(s) 和Sqlite3_reset(s)机制。4 */5 #include <iostream>67 #include "SQLiteWrapper.h"89int main(){10 SQLiteWrapper sqlite;11if(sqlite.Open("SQLiteWrapper.db")){12 std::cout<<"SQLiteWrapper.db created or opened"<<std::endl;13 }14else{15 std::cout<<"couldn't open SQLiteWrapper.db"<<std::endl;16 }1718 SQLiteStatement* stmt=sqlite.Statement("insert into foo values(?,?)");1920if(stmt->Bind(0,3)){21 std::cout<<"value 3 successfully bound at pos 0"<<std::endl;22 }23else{24 std::cout<<"value 3 NOT successfully bound at pos 0: "<<sqlite.LastError()<<std::endl;25 }26if(stmt->Bind(1, 4)){27 std::cout<<"value 4 successfully bound at pos 1"<<std::endl;28 }29else{30 std::cout<<"value 4 NOT successfully bound at pos 1:"<<sqlite.LastError()<<std::endl;31 }3233// 第一次执行Execute34if(stmt->Execute()){35 std::cout<<"statement executed"<<std::endl;36 }37else{38 std::cout<<"error executing statement: "<<sqlite.LastError()<<std::endl;39 }4041if(stmt->Bind(0, 5)){42 std::cout<<"value 5 successfully bound at pos 0"<<std::endl;43 }44else{45 std::cout<<"value 5 NOT successfully bound at pos 0"<<std::endl;46 }4748if(stmt->Bind(1, 6)){49 std::cout<<"value 6 successfully bound at pos 1"<<std::endl;50 }51else{52 std::cout<<"value 6 NOT successfully bound at pos 1"<<std::endl;53 }5455// 第二次执行Execute56if(stmt->Execute()){57 std::cout<<"statement executed"<<std::endl;58 }59else {60 std::cout<<"error executing statement: "<<sqlite.LastError()<<std::endl;61 }6263return0;64 }

3.4 输出数据库数据：Print_DB_Data.cpp
Print_DB_Data.cpp

1/*2 * 功能：演示了使用类成员函数获取数据库表的数据,并将数据输出到屏幕3 * Statement():返回一个指向SQLiteStatement类的指针4 * NextRow（）：只要数据未取完，就返回True5 * DataType()：返回访问列的数据类型6 * ValueString()：返回std::string.7 */8 #include <iostream>9 #include "SQLiteWrapper.h"1011int main(){12 SQLiteWrapper sqlite;13if (sqlite.Open("SQLiteWrapper.db")){14 std::cout<<"SQLiteWrapper.db created or opened"<<std::endl;15 }16else{17 std::cout<<"couldn't open SQLiteWrapper.db"<<std::endl;18 }1920 SQLiteStatement* stmt=sqlite.Statement("select * from foo");2122while(stmt->NextRow()){23 std::cout<<stmt->DataType (0)<<" - "<<stmt->DataType (1) <<" | "<<24 stmt->ValueString(0)<<" - "<<stmt->ValueString(1)<<std::endl;25 }2627return0;28 }

三、后记：

感叹SQLite的博大精深。

～～～路漫漫其修远兮，吾将上下而求索。
原文链接: https://www.cnblogs.com/ballwql/archive/2011/03/28/1997494.html

欢迎关注

微信关注下方公众号，第一时间获取干货硬货；公众号内回复【pdf】免费获取数百本计算机经典书籍