C++：Sqlite调用之增、读、改

背景

通常而言，我都会简单的使用文本文件进行程序数据的保存；但是在一些特殊情况下，例如并行读写，只有数据库才能满足需求。
Sqlite是一个非常简单的数据库，提供了可爱可亲的调用接口，以下给出了一些简单的demo，示例如何使用c++进行添加数据、读取数据和更新数据。

---

基础

假设要保存的数据结构如下：

struct DATA
{
	int id_;
	double weight_;

	DATA(int id, double weight) :id_(id), weight_(weight) {}
};

我们使用std::vector<DATA> dataVec存储部分数据在其中，如下：

id	weight
1	0.50
2	0.65
3	0.43

	std::vector<DATA> dataVec;
	dataVec.push_back(DATA(1, 0.50));
	dataVec.push_back(DATA(2, 0.65));
	dataVec.push_back(DATA(3, 0.43));

---

添加数据

添加数据库的第一步是创建数据库和表，代码如下：

	int  dataBaseStatus;
	sqlite3 *dataDB;
	std::string dataBaseFilePath = "data.db";

	// Open
	// Check File Exist
	// If not Exist it will creat new database
	int openDBStatus = sqlite3_open(dataBaseFilePath.c_str(), &dataDB);
	if (openDBStatus)
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(dataDB));
		return false;
	}
	else
	{
		fprintf(stdout, "Opened database successfully\n");
	}


	// Create Table
	std::string dataTable = "CREATE TABLE data("  \
		"id INTEGER NOT NULL," \
		"weight REAL    NOT NULL );";

	char *createDataTableErrMsg = nullptr;
	int createDataTable = sqlite3_exec(dataDB, dataTable.c_str(), NULL, NULL, &createDataTableErrMsg);
	if (createDataTable != SQLITE_OK)
	{
		fprintf(stderr, "SQL error: %s\n", createDataTableErrMsg);
		sqlite3_free(createDataTableErrMsg);
	}
	else
	{
		fprintf(stdout, "Table created successfully\n");
	}

此时用SqliteStudio打开数据库，即可看到：

完成表创建后，即可添加数据

	for (size_t i = 0; i<dataVec.size(); i++)
	{
		DATA &oneData = dataVec[i];

		string dataTableValue = "INSERT INTO data( \
				id,\
				weight)VALUES( \
				'"+std::to_string(oneData.id_)+"', '"+ std::to_string(oneData.weight_) +"');";

		char *insertIntoDataTableErrMsg = nullptr;
		int insertIntoDataTableStatus = sqlite3_exec(dataDB, dataTableValue.c_str(), NULL, NULL, &insertIntoDataTableErrMsg);
		if (insertIntoDataTableStatus != SQLITE_OK)
		{
			fprintf(stderr, "SQL error: %s\n", insertIntoDataTableErrMsg);
			sqlite3_free(insertIntoDataTableErrMsg);
		}
		else
		{
			fprintf(stdout, "Records created successfully\n");
		}
	}

此时再看数据库中已经填充了dataVec的数据

---

读取数据

假如现在有std::vector<DATA> dataVecNew,如何从数据库中读取对应的数据呢？代码如下：

	// Get Data
	char** pResult;
	int nRow;
	int nCol;
	string dataSelectFromDataTable = "SELECT * from data";
	char *dataSelectFromDataTableStatusErrMsg;
	int dataSelectFromDataTableStatus = sqlite3_get_table(dataDB, dataSelectFromDataTable.c_str(), &pResult, &nRow, &nCol, &dataSelectFromDataTableStatusErrMsg);
	if (dataSelectFromDataTableStatus != SQLITE_OK)
	{
		fprintf(stderr, "SQL error: %s\n", dataSelectFromDataTableStatusErrMsg);
		sqlite3_free(dataSelectFromDataTableStatusErrMsg);
	}
	else
	{
		fprintf(stdout, "Select successfully\n");
	}
	
	std::cout << nRow << std::endl;
	std::vector<DATA> dataVecNew;
	for (int i = 1; i<=nRow; i++)
	{
		printf("%s,%s",pResult[i*nCol + 0], pResult[i*nCol + 1]);
		dataVecNew.push_back(DATA(atoi(pResult[i*nCol + 0]), atof(pResult[i*nCol + 1])));
		printf("\n");
	}
	sqlite3_free_table(pResult);

输出如下：

---

更新数据

仅仅有数据库而不更新是完全没有意义的。假如修改

dataVec[0].weight = 22.55;

则对应数据库的更新方法如下：

	// Set Data
	dataVec[1].weight_ = 22.55;
	string dataSetFromDataTable = "UPDATE data SET weight = '"+std::to_string(dataVec[1].weight_)+"' WHERE id == '"+std::to_string(dataVec[1].id_)+"' ";
	char *dataSetFromDataTableStatusErrMsg = nullptr;
	int dataSetFromDataTableStatus = sqlite3_exec(dataDB, dataSetFromDataTable.c_str(), NULL, NULL, &dataSetFromDataTableStatusErrMsg);
	if (dataSetFromDataTableStatus != SQLITE_OK)
	{
		fprintf(stderr, "SQL error: %s\n", dataSetFromDataTableStatusErrMsg);
		sqlite3_free(dataSetFromDataTableStatusErrMsg);
	}
	else
	{
		fprintf(stdout, "Set successfully\n");
	}

数据库中内容如下：

---

完整代码：

#include <iostream>
#include <string>
#include <vector>
#include "sqlite3.h"
using namespace std;

struct DATA
{
	int id_;
	double weight_;

	DATA(int id, double weight) :id_(id), weight_(weight) {}
};

int main()
{
	std::vector<DATA> dataVec;
	dataVec.push_back(DATA(1, 0.50));
	dataVec.push_back(DATA(2, 0.65));
	dataVec.push_back(DATA(3, 0.43));


	int  dataBaseStatus;
	sqlite3 *dataDB;
	std::string dataBaseFilePath = "data.db";

	// Open
	// Check File Exist
	// If not Exist it will creat new database
	int openDBStatus = sqlite3_open(dataBaseFilePath.c_str(), &dataDB);
	if (openDBStatus)
	{
		fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(dataDB));
		return false;
	}
	else
	{
		fprintf(stdout, "Opened database successfully\n");
	}


	// Create Table
	std::string dataTable = "CREATE TABLE data("  \
		"id INTEGER NOT NULL," \
		"weight REAL    NOT NULL );";

	char *createDataTableErrMsg = nullptr;
	int createDataTable = sqlite3_exec(dataDB, dataTable.c_str(), NULL, NULL, &createDataTableErrMsg);
	if (createDataTable != SQLITE_OK)
	{
		fprintf(stderr, "SQL error: %s\n", createDataTableErrMsg);
		sqlite3_free(createDataTableErrMsg);
	}
	else
	{
		fprintf(stdout, "Table created successfully\n");
	}

	for (size_t i = 0; i<dataVec.size(); i++)
	{
		DATA &oneData = dataVec[i];

		string dataTableValue = "INSERT INTO data( \
				id,\
				weight)VALUES( \
				'"+std::to_string(oneData.id_)+"', '"+ std::to_string(oneData.weight_) +"');";

		char *insertIntoDataTableErrMsg = nullptr;
		int insertIntoDataTableStatus = sqlite3_exec(dataDB, dataTableValue.c_str(), NULL, NULL, &insertIntoDataTableErrMsg);
		if (insertIntoDataTableStatus != SQLITE_OK)
		{
			fprintf(stderr, "SQL error: %s\n", insertIntoDataTableErrMsg);
			sqlite3_free(insertIntoDataTableErrMsg);
		}
		else
		{
			fprintf(stdout, "Records created successfully\n");
		}
	}


	// Get Data
	char** pResult;
	int nRow;
	int nCol;
	string dataSelectFromDataTable = "SELECT * from data";
	char *dataSelectFromDataTableStatusErrMsg;
	int dataSelectFromDataTableStatus = sqlite3_get_table(dataDB, dataSelectFromDataTable.c_str(), &pResult, &nRow, &nCol, &dataSelectFromDataTableStatusErrMsg);
	if (dataSelectFromDataTableStatus != SQLITE_OK)
	{
		fprintf(stderr, "SQL error: %s\n", dataSelectFromDataTableStatusErrMsg);
		sqlite3_free(dataSelectFromDataTableStatusErrMsg);
	}
	else
	{
		fprintf(stdout, "Select successfully\n");
	}
	
	std::cout << nRow << std::endl;
	std::vector<DATA> dataVecNew;
	for (int i = 1; i<=nRow; i++)
	{
		printf("%s,%s",pResult[i*nCol + 0], pResult[i*nCol + 1]);
		dataVecNew.push_back(DATA(atoi(pResult[i*nCol + 0]), atof(pResult[i*nCol + 1])));
		printf("\n");
	}
	sqlite3_free_table(pResult);

	// Set Data
	dataVec[1].weight_ = 22.55;
	string dataSetFromDataTable = "UPDATE data SET weight = '"+std::to_string(dataVec[1].weight_)+"' WHERE id == '"+std::to_string(dataVec[1].id_)+"' ";
	char *dataSetFromDataTableStatusErrMsg = nullptr;
	int dataSetFromDataTableStatus = sqlite3_exec(dataDB, dataSetFromDataTable.c_str(), NULL, NULL, &dataSetFromDataTableStatusErrMsg);
	if (dataSetFromDataTableStatus != SQLITE_OK)
	{
		fprintf(stderr, "SQL error: %s\n", dataSetFromDataTableStatusErrMsg);
		sqlite3_free(dataSetFromDataTableStatusErrMsg);
	}
	else
	{
		fprintf(stdout, "Set successfully\n");
	}


	sqlite3_close(dataDB);

	return 0;
}