一个简单sqlite3的封装类

一个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 的使用有个入门级了解了.