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

落日映苍穹つ 2024-04-20 03:47 69阅读 0赞

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

## 1、如何让c++运行sqlite3 ##

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

## 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;
    }

### 测试效果 ###

![在这里插入图片描述][89d2f633753248d19cfac23f4aef7baa.png]

[Link 1]: https://blog.csdn.net/weixin_44122235/article/details/121610956
[89d2f633753248d19cfac23f4aef7baa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/1e56c41c57c245faaf53ae659bdbc08a.png