sqlite数据库简单操作

代码如下：

#include <iostream>
#include <sstream>
#include "sqlite3.h"
using namespace std;
sqlite3 *db;
sqlite3_stmt *stmt;//记录了一个SQL语句
const char* zTail;
char* errMsg;
int createTable()
{
	int res;
  	std::string dropTab = "drop table test;";
	string str = "create table test(name text,num int);";
	res = sqlite3_exec(db,dropTab.c_str(),&errMsg);
	if(res != SQLITE_OK)
	{
		cout<<"执行SQL语句出错."<<errMsg<<endl;
		return -1;	
	}
	res = sqlite3_exec(db,str.c_str(),&errMsg);
	if(res != SQLITE_OK)
	{
		cout<<"执行创建SQL语句出错."<<errMsg<<endl;
		return -1;
	}
	else
	{
		cout<<"创建table语句成功执行."<<endl;
	}
	return 0;
}

int insert1()
{
	int res;
	//开始事务
	res = sqlite3_exec(db,"begin transaction;",&errMsg);
	if (res != SQLITE_OK)
	{
		cout<<"开启事务失败."<<endl;
		return -1;
	}
	//sqlite3_transaction trans(db);
	//该函数就是把一个sql语句解析到stmt结构体里
	//第三个参数小雨0表示sqlite会自动计算前面sql语句的长度
	//解析后的语句就存放在stmt中
	res = sqlite3_prepare(db,"insert into test(name,num)values(?,?)",-1,&stmt,&zTail);
	if(res != SQLITE_OK)
	{
		cout<<"解析失败."<<endl;
		return -1;
	}
	else
	{
		cout<<"解析成功."<<endl;
		sqlite3_bind_text(stmt,1,"zhai",sizeof("zhai"),NULL);
		sqlite3_bind_int(stmt,2,23);
		sqlite3_step(stmt);

		sqlite3_reset(stmt);
		//重置后可继续插入第二行记录
		sqlite3_bind_text(stmt,"zhang",sizeof("zhang"),224);
		sqlite3_step(stmt);

		sqlite3_reset(stmt);
		sqlite3_bind_text(stmt,"zhou",sizeof("zhou"),11);
		sqlite3_step(stmt);
	}
	res = sqlite3_exec(db,"commit transaction;",&errMsg);
	if(res != SQLITE_OK)
	{
		cout<<"提交事务失败."<<endl;
		return -1;
	}
	//trans.commit();
	return 0;
}
//static int callback(void *NotUsed,int argc,char**argv,char**azColName)
//{
//	//argc表示列数，argv表示每列的值，azColName表示列名称
//	for(int i = 0;i < argc;i++)
//	{
//		cout<<azColName[i]<<"="<<(argv[i]?argv[i]:"NULL")<<",";
//	}
//	cout<<"n";
//	return 0;
//}
//int select1()
//{
//	char *errMsg;
//	string strSQL = "select * from test_tab;";
//	int res =  sqlite3_exec(pDB,strSQL.c_str(),callback,&errMsg);
//	if(res != SQLITE_OK)
//	{
//		cout<<"执行SQL出错."<<errMsg<<endl;
//		return -1;
//	}
//	else
//	{
//		cout<<"SQL成功执行."<<endl;
//	}
//	return 0;
//}
int select1()
{
	int res;
	res = sqlite3_prepare(db,"select * from test;",&zTail);
	if(res != SQLITE_OK)
	{
		cout<<"查询失败."<<endl;
		return -1;
	}
	int result = sqlite3_step(stmt);
	while( result == SQLITE_ROW ) 
	{
		const unsigned char* name = sqlite3_column_text( stmt,1 );
		int num = sqlite3_column_int( stmt,2 );
		result = sqlite3_step(stmt);
	}
}
int main()
{
	int res  = sqlite3_open("D:sql.db",&db);
	if(res)
	{
		cout<<"can't open database!"<<sqlite3_errmsg(db);
		sqlite3_close(db);
		return -1;
	}
	res = createTable();
	if(res != 0)
	{
		return 0;
	}
	res = insert1();
	if(res != 0)
	{
		return 0;
	}
	res = select1();
	if(res != 0)
	{
		return 0;
	}
	sqlite3_finalize(stmt);
	system("pause");
	return 0;
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!