C++操作SQLite示例笔记

SQLLite是一个轻量级关系型数据库，设计的目标是嵌入式的，很多桌面应用程序（如火狐浏览器，QQ，skype）以及手机应用经常用它来作为数据存储，毕竟纯文本或者xml格式的读取不方便。C语言操作SQLite3的方式很简单，下面的程序大致演示了数据表创建，插入数据，查询数据几个常用的数据操作。

#include<iostream>extern "C"{#include"sqlite3.h"}using namespace std;//callback functionint select_cb_function(void *data,int n_cols,char **colvals,char **colnames){int i;static bool title_shown = false;if(title_shown == false){//print col namefor(i = 0;i < n_cols;i ++){cout << colnames[i] << "\t";}cout << endl;title_shown = true;}//print select resultsfor(i = 0;i < n_cols;i ++){cout << colvals[i] << "\t";}cout << endl;return 0;}int main(){sqlite3 *sdb = NULL;char *err = NULL;//open a databaseint rc = sqlite3_open("temp.db",&sdb);if(!rc){//create a tablerc = sqlite3_exec(sdb,"create table user(name varchar(20),age int)",0,0,&err);if(rc != SQLITE_OK){cout << "error occured " << err << endl;sqlite3_close(sdb);exit(-1);}//insert a recordfor(int i = 0; i < 2;i ++){rc = sqlite3_exec(sdb,"insert into user values(\"ciaos\",25)",0,0,&err);if(rc != SQLITE_OK){cout << "error occured " << err << endl;sqlite3_close(sdb);exit(-1);}}//select recordsrc = sqlite3_exec(sdb,"select * from user",select_cb_function,0,&err);if(rc != SQLITE_OK){cout << "error occured " << err << endl;sqlite3_close(sdb);exit(-1);}sqlite3_close(sdb);}return 0;}

当然需要引用sqlite3.dll，sqlite3.lib可以自行下载源码编译或者使用sqlite3.def文件编译时指定生成lib文件。下面是我封装的一个操作类

sqliteproxy.h头文件内容

#ifndef __SQLITEPROXY_H#define __SQLITEPROXY_H#include<map>extern "C"{#include"sqlite3.h"}using std::map;typedef struct _DBConn{sqlite3 *dbc;}DBConn;typedef struct _DBResult{bool status;char **result;int nRow;int nColumn;}DBResult;class SQLiteProxy{private:std::map<char *,DBConn>pxs;DBConn OpenSQLite(char *dbname){map<char *,DBConn>::iterator it = pxs.find(dbname);if(it == pxs.end()){DBConn dbp;dbp.dbc = NULL;int res = sqlite3_open(dbname,&dbp.dbc);if(res != SQLITE_OK){exit(-1);}pxs.insert(map<char *,DBConn>::value_type(dbname,dbp));return dbp;}else{return it->second;}}public:SQLiteProxy(){}~SQLiteProxy(){int status;for(map<char *,DBConn>::iterator it = pxs.begin(), ite = pxs.end();it != ite;it ++){status = sqlite3_close(it->second.dbc);if(status != SQLITE_OK){exit(-1);}}pxs.clear();}//create|insert|update|delete ...bool Exec(char *dbname,char *sql){int res;char *err = NULL;DBConn db = OpenSQLite(dbname);res = sqlite3_exec(db.dbc,sql,0,0,&err);if(res != SQLITE_OK){return false;}return true;}//selectDBResult Query(char *dbname,char *sql){int res;char *err = NULL;DBConn db = OpenSQLite(dbname);DBResult dbr;res = sqlite3_get_table(db.dbc,sql,&dbr.result,&dbr.nRow,&dbr.nColumn,&err);if(res == SQLITE_OK){dbr.status = true;return dbr;}dbr.status = false;return dbr;}};#endif

select查询操作调用Query函数，其他操作调用Exec函数，使用示例如下：

#include<iostream>#include"sqliteproxy.h"using namespace std;int main(){//// new//SQLiteProxy *sp = new SQLiteProxy();bool res;res = sp->Exec("1.db","create table user(name varchar(20),age int)");cout << res << endl;res = sp->Exec("1.db","insert into user values('ciaos',25)");cout << res << endl;res = sp->Exec("1.db","delete from user");cout << res << endl;res = sp->Exec("1.db","insert into user values('ciaos',25)");cout << res << endl;DBResult dbr = sp->Query("1.db","select * from user");if(dbr.status){int index = dbr.nColumn;for(int i =1;i<=dbr.nRow;i++){for(int j =0;j<dbr.nColumn;j++){cout << dbr.result[j] << " " << dbr.result[index] << "  ";index ++;}cout << endl;}}//// delete//delete sp;//return 0;}