c++操作sqllite

項目中需要使用的sqllite，有想過使用內存的結果，好像都不大使用，最接近的算是vector了，但是查詢方式不大好，而且數據有好幾個字段，所以考慮了數據庫

其實挺簡單的，主要也就是一個class

main函數裡面有對應的測試函數

暫時沒有發現bug，如果出現，請務必提醒本人！謝啦！

詳情請看main函數

sql_lite_helper.h：

#ifndef  __INCLUDE_SQL_LITE_HELPER_H__
#define  __INCLUDE_SQL_LITE_HELPER_H__


#include sqlite3.h

#pragma comment(lib, sqlite3.lib)


class SQLiteHelper
{
public:
	SQLiteHelper() { }
	virtual ~SQLiteHelper() { CloseDB(); }

	// 打開數據庫
	int OpenDB(const char *path);
	// 關閉數據庫
	int CloseDB();
	// 創建一張表
	int CreateTable(const char *create_table_state);
	// 刪除一張表
	int DropTable(const char *table_name);
	// 查詢操作
	int Select(const char *select_state);
	// 插入操作
	int Insert(const char *insert_state);
	// 刪除操作
	int Delete(const char *delete_state);
	// 更新操作
	int Update(const char *update_state);
	
private:
	sqlite3 *sqlite_db_;// 數據庫的指針
	char* err_msg_;		// 錯誤信息
	bool is_close_;		// 關閉數據的標識

	// 主要用在selece操作中的顯示數據元素
	static int CallBackFunc(void *NotUsed, int argc, char **argv, char **azColName);
	// 執行sql語句
	int SqlStateExec(const char *sql_state);
};


#endif

sql_lite_helper.cpp：

#include iostream
using namespace std;

#include sql_lite_helper.h


int SQLiteHelper::OpenDB(const char *path)
{
	int res = sqlite3_open(path, &sqlite_db_);
	if(res)
	{
		cout << can't open database:  << sqlite3_errmsg(sqlite_db_);
		sqlite3_close(sqlite_db_);
		return -1;
	}

	is_close_ = false;

	return 0;
}


int SQLiteHelper::CloseDB()
{
	if (!is_close_)
	{
		int res = sqlite3_close(sqlite_db_);
		if(res)
		{
			cout << can't close database:  << sqlite3_errmsg(sqlite_db_);
			return -1;
		}
	}

	return 0;
}


int SQLiteHelper::CreateTable(const char *table_name_and_field)
{
	string create_table_state = create table ;
	create_table_state += table_name_and_field;
	create_table_state += ;;
	int res = SqlStateExec(create_table_state.c_str());
	if (res != SQLITE_OK)
	{ 
		cout << create table failed. << err_msg_ << endl;
		return -1;
	}
	else
	{
		cout << create table successed. << endl;
	}

	return 0;
}


int SQLiteHelper::DropTable(const char *table_name)
{
	string sql_state = drop table ;
	sql_state += table_name;
	sql_state += ;;
	int res = SqlStateExec(sql_state.c_str());
	if (res != SQLITE_OK)
	{ 
		cout << drop table failed. << err_msg_ << endl;
		return -1;
	}
	else
	{
		cout << drop table successed. << endl;
	}

	return 0;
}


int SQLiteHelper::Select(const char *select_state)
{
	int res = SqlStateExec(select_state);
	if (res != SQLITE_OK)
	{
		cout << select operate failed. << err_msg_ << endl;
		return -1;
	}
	else
	{
		cout << select operate successed. << endl;
	}

	return 0;
}


int SQLiteHelper::Insert(const char *insert_state)
{
	int res = sqlite3_exec(sqlite_db_, begin transaction;, CallBackFunc, 0, &err_msg_);
	res = SqlStateExec(insert_state);
	if (res != SQLITE_OK) 
	{
		cout << insert operate failed. << err_msg_ << endl;
		return -1;
	} 

	res = sqlite3_exec(sqlite_db_, commit transaction;, 0, 0, &err_msg_);

	cout << insert operate successed. << endl;

	return 0;
}


int SQLiteHelper::Delete(const char *delete_state)
{
	int res = SqlStateExec(delete_state);
	if (res != SQLITE_OK)
	{
		cout << delete operate failed. << err_msg_ << endl;
		return -1;
	}
	else
	{
		cout << delete operate successed. << endl;
	}

	return 0;
}


int SQLiteHelper::Update(const char *update_state)
{
	int res = SqlStateExec(update_state);
	if (res != SQLITE_OK)
	{
		cout << update operate failed. << err_msg_ << endl;
		return -1;
	}
	else
	{
		cout << update operate successed. << endl;
	}

	return 0;
}


int SQLiteHelper::CallBackFunc(void *not_used, int element_count, char **element, char **col_name)
{
	for(int index = 0 ; index < element_count ; index++)
	{
		cout << col_name[index] <<  =  << (element[index] ? element[index] : NULL) << , ;
	}

	cout << 
;

	return 0; 
}


int SQLiteHelper::SqlStateExec(const char *sql_state)
{
	return sqlite3_exec(sqlite_db_, sql_state, CallBackFunc, 0, &err_msg_);
}

main：

// test_use_sqlite.cpp : 定義控制台應用程序的入口點。
//

#include stdafx.h
#include iostream
#include sstream
using namespace std; 

#include sql_lite_helper.h


SQLiteHelper sql_lite_helper;


// Test：創建一個數據庫表
int TestCreateTable()
{
	return sql_lite_helper.CreateTable(test_table (id int, name varchar, age int));
}


// Test：測試插入數據
int TestInsert()
{
	for (int i= 1; i < 10; ++i)
	{
		std::stringstream str_sql;
		str_sql << insert into test_table values(;
		str_sql << i << ,<< (i + 10) << , << 23 << );;
		std::string str = str_sql.str();
		sql_lite_helper.Insert(str.c_str());
	}

	return 0;
}


// Test：測試刪除某個元素
int TestDelete()
{
	string str_sql= delete from test_table where id=4;;

	return sql_lite_helper.Delete(str_sql.c_str());
}


// Test：測試更新某個元素
int TestUpdate()
{
	string str_sql= update test_table set name='SQLite3' where name='17';;

	return sql_lite_helper.Update(str_sql.c_str());
}


// Test：測試查詢
int TestSelect()
{
	string str_sql= select * from test_table;;

	return sql_lite_helper.Select(str_sql.c_str());
}


// Test：測試刪除表
int TestDropTable()
{
	return sql_lite_helper.DropTable(test_table);
}


int main()
{
	int res = sql_lite_helper.OpenDB(./Test.db3);

	res = TestCreateTable();
	if (res != 0)
	{
		return 0;
	}

	res = TestInsert();
	if (res != 0)
	{
		return 0;
	}

	TestSelect();

	res = TestDelete();
	if (res != 0)
	{
		return 0;
	}

	res = TestUpdate();
	if (res != 0)
	{
		return 0;
	}

	TestSelect();

	TestDropTable();

	TestSelect();


	return 0;
}

顯示結果：