SQlite3 C++ 基本应用（sqlite3_stmt搞定二进制）

       SQlite3轻巧，效率高，是小型软件数据库的首选。近来在网上参考了些资料，自己动手编程，有些收获，整理了下代码，记下来...

源码中包括数据库数据的插入，查询等基本操作。

       源码：

#include <stdio.h>
#include <windows.h>
#include <iostream>
using namespace std;

extern "C"
{
	#include "./sqlite3.h"
};

void sqlite3_exec_report(sqlite3* db, const char* sql, sqlite3_callback func_callback, void *para);
void select_v2(sqlite3 *db, const char*sql);
void outputItem(sqlite3_stmt* stat, int nColumn, char* out_pic_path);
string GetType(int t);
//回调函数
int select_items(void *para, int n_column, char **column_val, char **column_name);//查询

int main(int args, char*argv[])
{
	int result, ret;
	sqlite3* db = NULL;
	char* errmsg = NULL;
	
	char module_path[100];

	//获取当前exe路径
	DWORD dwPathLen = GetModuleFileNameA(NULL, module_path, 100);
	char *p = module_path + dwPathLen;
	while (1)
	{
		char c = *p;
		if (c == '\\')
		{
			*(p+1) = '\0';
			break;
		}
		p--;
	}
	//合成数据库文件路径
	strcat_s(module_path, 100, "db\\mydb.db");
	string db_path(module_path);
	//打开(创建)数据库
	result = sqlite3_open(db_path.c_str(), &db);

	//----------------------------第一张表------------------------------
	//--不包含blob数据，通过普通的方法插入
	//-- --------------------------------------------------------------
	if (result == SQLITE_OK)
	{
		//创建第一张表
		sqlite3_exec_report(db, "create table MyTable_1(ID integer autoincrement primary key, name nvarchar(30)）", NULL, NULL);
		//先清除数据
		sqlite3_exec_report(db, "delete from MyTable_1", NULL, NULL);
		//插入数据	
		const char * sqls[] = {
			"insert into MyTable_1(ID,name) values(0, '钱学森')",
			"insert into MyTable_1(ID,name) values(1, '邓稼先')",
			"insert into MyTable_1(ID,name) values(2, '钱三强')",
			"insert into MyTable_1(ID,name) values(3, '李四光')",
			"insert into MyTable_1(ID,name) values(4, '贺才良')"
		};
		for (int i = 0; i < sizeof(sqls)/sizeof(char*); i++)
		{
			sqlite3_exec_report(db, sqls[i], NULL, NULL);
		}

		//查询插入的数据  用的是回调的方法
		sqlite3_exec_report(db, "select * from MyTable_1", select_items, NULL);
		sqlite3_close(db);
	}
	else
	{
		//打开数据库失败
		cout << sqlite3_errmsg(db) << endl;
		ret = -1;
	}

	//-----------------------------第二张表------------------------------
	//--包含blob数据，通过sqlite3_prepare，sqlite3_bind_blob，sqlite3_step
	//-- 的方法实现数据的插入
	//-- --------------------------------------------------------------
	result = sqlite3_open(module_path, &db);
	if (result==SQLITE_OK)
	{
		sqlite3_exec_report(db, "create table stu_msg(ID integer primary key autoincrement, name nvarchar(32), picture blob)", NULL, NULL);
		sqlite3_exec_report(db, "delete from stu_msg", NULL, NULL);

		sqlite3_stmt *stat;
		const char *pzTail = NULL;

		const char *sqls[] = {
			"insert into stu_msg(ID, name, picture) values(0, '华罗庚', ?);",
			"insert into stu_msg(ID, name, picture) values(1, '钱学森', ?);"
		};
		const char *names[] = {
			"pic\\hualuogen.jpg",
			"pic\\qianxuesen.jpg"
		};

		for (int j=0; j<sizeof(sqls)/sizeof(char*); j++)
		{
			//准备
			result = sqlite3_prepare(db, sqls[j], -1, &stat, &pzTail);

			if (!result && stat)
			{
				// 读取头像数据
				*(p+1) = '\0';//重复利用ModuleFileName
				strcat_s(module_path, 100, names[j]);
				FILE *file = NULL;
				fopen_s(&file, module_path, "rb+");
				char* data = NULL;
				long l_file_size = 0;
				if (file)
				{
					fseek(file, 0, SEEK_END);
					l_file_size = ftell(file);
					fseek(file, 0, SEEK_SET);
					data = new char[l_file_size];
					fread(data, 1, l_file_size, file);

					//和sql的第一个？绑定（如果有多个问号，那就要分开绑定）
					result = sqlite3_bind_blob(stat, 1, data, l_file_size, NULL);

					//将数据输入数据库
					result = sqlite3_step(stat);
					cout << "Insert result:" << sqlite3_errmsg(db) << endl;
					fclose(file);
					delete [] data;
				}			
				ret = 0;
			}
			else
			{
				//准备失败
				cout << "sqlite3_prepare failed!  " << sqlite3_errmsg(db) << endl; 
				ret = -1;
			}
			//释放stat
			sqlite3_finalize(stat);
		}
		
		//查看插入数据
		select_v2(db, "select ID, name, picture from stu_msg");

		//关闭数据库
		sqlite3_close(db);
	} 
	else
	{
		cout << "打开数据库" << module_path << sqlite3_errmsg(db);
		ret = -1;
	}

	//---------------------读出各记录的头像-----------------
	result = sqlite3_open(db_path.c_str(), &db);
	if (!result)
	{
		sqlite3_stmt *stat;
		const char *pzTail = NULL;
		sqlite3_prepare(db, "select * from stu_msg;", -1, &stat, &pzTail);
		int nColumn = sqlite3_column_count(stat);
		cout << "总的列数:" << nColumn << endl;

		result = sqlite3_step(stat);//读取某一条记录
		while (result == SQLITE_ROW) /* sqlite3_step() has another row ready */
		{
			*(p+1) = '\0';
			outputItem(stat, nColumn, module_path);
			cout << endl;
			result = sqlite3_step(stat);
		}
		cout << "end..." << endl;
	}

	return ret;
}

//输出一条记录
void outputItem(sqlite3_stmt* stat, int nColumn, char* out_pic_path)
{
	int m = -1;
	for (int k=0; k< nColumn; k++)
	{
		string s;
		int colType = sqlite3_column_type(stat, k);
		if (colType == 3)//text
		{
			m = k; //得到名字
		}
		s = GetType(colType);
		cout << "column " << k << ":" << s.c_str() << endl;

		switch(colType){
		case 1: cout << "Value: " << sqlite3_column_int(stat, k) << endl;break;
		case 2: cout << "Value: " << sqlite3_column_int(stat, k) << endl; break;
		case 3: cout << "Value: " << sqlite3_column_text(stat, k) << endl; break;		
		case 5: cout << "Value: " << "内容为空" << endl; break;
		case 4: cout << "Value: " << "二进制数据" << endl; 		
			const void* bi = sqlite3_column_blob(stat, k);
			int size = sqlite3_column_bytes(stat,k);
			char *name = NULL;
			if (m == -1)
			{
				name = "xxx";
			} 
			else
			{
				name = (char*)sqlite3_column_text(stat, m);
			}
			strcat_s(out_pic_path, 100, "out_pic\\");
			strcat_s(out_pic_path, 100, (char*)name);
			strcat_s(out_pic_path, 100, ".jpg");
			FILE *f = NULL;

			fopen_s(&f, out_pic_path, "wb+");
			if (f)
			{
				fwrite(bi, 1, size, f);
				fclose(f);
			}
			break;
		}
	}
	
}

//获取类型
string GetType(int t)
{
	string s;
	switch (t)
	{
	case 1: s = "SQLITE_INTEGER";break;
	case 2: s = "SQLITE_FLOATE"; break;
	case 3: s = "SQLITE_TEXT"; break;
	case 4: s = "SQLITE_BLOB"; break;
	case 5: s = "SQLITE_NULL"; break;
	}
	return s;
}

//执行sql语句并报告执行结果
void sqlite3_exec_report(sqlite3* db, const char* sql, sqlite3_callback func_callback, void *para)
{
	char* errmsg;
	int result = sqlite3_exec(db, sql, func_callback, para, &errmsg);
	cout << endl << endl;
	if(result != SQLITE_OK)
	{
		cout << "error code： " << result << endl
			<< "error： " << errmsg << endl;
	}
	else 
		cout << "SQL execute succeed: " << sql << endl;
}

//一条记录调用一次回调函数
int select_items(void *para, int n_column, char **column_val, char **column_name)
{
	int i;
	cout << "contain " << n_column << " columns" << endl;
	for (i = 0; i<n_column; i++)
	{
		cout << "column" << i << ": " << column_name[i] << endl
		     << "values: " << column_val[i] << endl;
	}
	return 0;
}

//另一种查询方式
void select_v2(sqlite3 *db, const char*sql)
{
#if 1
	
	char *errmsg = NULL;
	char** dbResult = NULL;
	int nRow, nColumn;
	int result = sqlite3_get_table( db, sql, &dbResult, &nRow, &nColumn, &errmsg );

	//查询成功
	int index = nColumn; //dbResult 前面第一行数据是字段名称，从 nColumn 索引开始才是真正的数据

	for( int i = 0; i < nRow ; i++ )
	{
		cout << "第" << i+1 << "条记录" << endl; 
		for(int j = 0 ; j < nColumn; j++ )
		{
			cout << "字段名：" << dbResult[j] << "  "
				<< "字段值：" << dbResult[index] << endl;
			++index; // dbResult 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示
		}
		cout << "------------" << endl;
	}

	sqlite3_free_table(dbResult);//释放查询空间
#endif
}

 

 另外推荐一款查看sqlites数据库的软件：sqlite expert 私人版，功能很强大伊~

>_<