一个sqlite3封装类,后缀叫.h 还是叫.cpp 呢, 一般来说.h 和 .cpp 是分开写的, .h 只声明头文件,.cpp 来实现
但这里为了简单,把声明和实现放到一起了, 所以叫.h或.cpp 都可以, 这里就叫db.cpp 吧,代码如下:
实现了创建表,使用表的演示:
/* * author: hjjdebug * date: 2011 * sqlite 数据库，包含4个文件sqlite3.lib sqlite3.dll sqlite3.h sqlite3.def * 用navicat for sqlite 工具观察数据库文件.sqlite 文件 * 我很佩服开源精神。 * 一个简单sqlite的封装类，留个纪念吧 */ #include "sqlite3.h" #include "stdio.h" #include "stdlib.h" #include <vector> #include <string.h> #define DEBUGLEVEL 5 using namespace std; #ifndef WIN32 #define _atoi64(val) strtoll(val,NULL,10) #endif class ClipNode { public: int beginVideoPts; int endVideoPts; }; class DbClip { sqlite3 *db; public: int open(const char *filePath) { int ret = sqlite3_open(filePath, &db); return ret; } int create() { char sql[1000] = "create table clips(id integer primary key, bpts integer, epts integer, create_time datetime);"; int ret=sqlite3_exec(db,sql,0,0,0); return ret; } int insert(int id, int start, int end) { char sql[1000]; sprintf(sql,"insert into clips values (%d, %d, %d, datetime('now','localtime'));", id, start,end); return sqlite3_exec(db,sql,0,0,0); } int queryMaxId() { sqlite3_stmt * stmt; //stmt->statement, 语句 char sql[1000] = "select max(id) from clips;"; sqlite3_prepare(db,sql,-1,&stmt,0); int rc = sqlite3_step(stmt); if(rc==SQLITE_ROW) { int n = sqlite3_column_int(stmt,0); return n; } return -1; } int query(vector<ClipNode> &allNodes, vector <int> &ids) { char sql[512*1024]; char buffer[256]; int n_row=0, n_column=0; char **azResult; //存放结果 sprintf(sql,"select bpts, epts from clips where id in (%d",ids[0]); // sprintf(sql,"select bpts from clips;"); for(unsigned i=1; i<ids.size(); i++) { // itoa(ids[i],buffer,10); sprintf(buffer,"%d",ids[i]); strcat(sql,","); strcat(sql,buffer); } strcat(sql,");"); int rt=sqlite3_get_table(db,sql,&azResult,&n_row,&n_column,NULL); #if (DEBUGLEVEL > 3) printf("getTable rt:%d\n",rt); printf("sentense:\n%s\n",sql); //其中nrow为行数，ncolum为列数 printf("\nThe result of querying is : \n"); for(int i=1;i<n_row+1;i++) { for(int j=0;j<n_column;j++) printf("%s ",azResult[i*n_column+j]); printf("\n"); } #endif int index=2; // 跳开开始的一个空元素 ClipNode node; for(int i=0; i<n_row; i++) { node.beginVideoPts =_atoi64(azResult[index]); node.endVideoPts = _atoi64(azResult[index+1]); index+=2; allNodes.push_back(node); } return true; } int close() { return sqlite3_close(db); } };
补充一个测试程序main.cpp, 包含上上面文件db.cpp
#include <iostream> #include <stdio.h> #include "db.cpp" const char *tdb = "testDb.db"; int main(void) { vector<ClipNode> allNodes; vector<int> ids; DbClip *db = new DbClip(); int rt=db->open(tdb); printf("open rt is %d\n",rt); //0 is succeed. SQLITE_OK rt =db->create(); printf("create rt is %d\n",rt); //0 is succeed. 1. SQLITE_ERROR rt=db->insert(1,1,2); printf("insert rt is %d\n",rt); //0 is succeed. 19. SQLITE_CONSTRAINT db->insert(2,2,5); db->insert(3,2,8); int id=db->queryMaxId(); printf("maxID is %d\n",id); ids.push_back(1); ids.push_back(2); ids.push_back(3); db->query(allNodes,ids); db->close(); return 0; }把玩一下,就能对sqlite3 的使用有个入门级了解了.
