【已解决】vs2015下c++对sqlite的操作

本博文源于笔者操作sqlite3，借鉴了很多文章的思路，这里并整理了c++常用的对数据库的操作供大家点赞收藏以后备用。包含了：c++对sqlite3的创建数据库、创建数据表、写入数据表、读取数据表、删除数据表。也包括了最基础的让c++运行sqlite3.内容供读者参考，希望对大家有所帮助。

1、如何让c++运行sqlite3

虽然让c++运行sqlite3不是重点，但这里放个连接供大家参考。
跳转链接

2、创建数据库

void CreateDataBase(const char* dataBaseName) {
	int result = sqlite3_open_v2(dataBaseName, &sql, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE, NULL);
	if (result == SQLITE_OK) {
		std::cout << "打开数据库连接成功" << std::endl;;
	}
	else {
		std::cout << "打开数据库连接失败" << std::endl;
	}
}

3、创建数据表

void CreateTable(const char* tableContent) {
	sqlite3_stmt* stmt = NULL;        //stmt语句句柄
	int result = sqlite3_prepare_v2(sql, tableContent, -1, &stmt, NULL);

	if (result == SQLITE_OK) {
		std::clog << "创建数据表成功" << std::endl;
		sqlite3_step(stmt);
	}
	else {
		std::clog << "创建数据表失败" << std::endl;
	}
	sqlite3_finalize(stmt);
}

写入语句

void InsertValue(const char* sqlContent) {
	// 表单中插入信息
	//进行插入前的准备工作——检查语句合法性
	//-1代表系统会自动计算SQL语句的长度
	sqlite3_stmt* stmt = NULL;
	int result = sqlite3_prepare_v2(sql, sqlContent, -1, &stmt, NULL);

	if (result == SQLITE_OK) {
		std::clog << "添加数据语句OK" << std::endl;
		//执行该语句
		sqlite3_step(stmt);
	}
	else {
		std::clog << "添加数据语句有问题" << std::endl;
	}
	//清理语句句柄，准备执行下一个语句
	sqlite3_finalize(stmt);
}

读取信息

void ReadTable() {
	// 查询
	char* errmsg;
	char** mResult;//结果集
	int mRow;//行数
	int mCol;//列数
	int nResult = sqlite3_get_table(sql, "select * from devices;", &mResult, &mRow, &mCol, &errmsg);
	int nIndex = mCol;
	cout << "mRow: " << mRow << endl;
	cout << "mCol: " << mCol << endl;
	if (mRow > 0 && mCol > 0)
	{
		
		for (int i = 0; i < mRow; i++)
		{
			for (int j = 0; j < mCol; j++)
			{
				cout << mResult[j] << ": " << mResult[nIndex] << endl;
				++nIndex;
			}
			cout << endl;
		}
		sqlite3_free_table(mResult);
	}
}

删除数据表

void DelTable(const char* sqlContent) {
	sqlite3_stmt* stmt = NULL;
	int result = sqlite3_prepare_v2(sql, sqlContent, -1, &stmt, NULL);

	if (result == SQLITE_OK) {
		std::clog << "删除数据完毕OK" << std::endl;
		sqlite3_step(stmt);
	}
	else {
		std::clog << "删除数据有问题" << std::endl;
	}
}

完整测试样例

int main() {
	//创建数据库
	CreateDataBase();
	//创建数据表


	const char *sqlCreateTable = "CREATE TABLE IF NOT EXISTS devices ("
		"deviceid INTEGER PRIMARY KEY,"
		"devMac TEXT,"
		"devSn TEXT,"
		"keySn TEXT,"
		"matNum TEXT,"
		"openId TEXT,"
		"workNum TEXT);";
	CreateTable(sqlCreateTable);
	//插入数据
	const char *sqlInsert = "INSERT INTO devices  VALUES (1, '00:12:31:8f:a7:52','812aab3b5b57cce4','0012110909801968','','64e0886f60b2be8101031868','lc123');";
	InsertValue(sqlInsert);
	//读取数据
	ReadTable();
	//删除数据
	//const char* sqlDel = "delete from  devices where deviceid=1";
	//DelTable(sqlDel);
	//ReadTable();

	system("pause");
	return 0;
}

测试效果