C++使用Sqlite

最近项目中有使用到sqlite处理数据，发现以前最基本的SQL有点把握不住了，就赶紧复习回顾一下。.1.先下载SQLlite官网下载包https://www.sqlite.org/download.htmlSQLite Download Page - Source Code - 我选择的版本号是：version 3.35.5下载解压完成后，拿到四个文件：2.添加进我们VS工程。开发环境：VS201

简单下划线之父

11568人浏览 · 2021-05-22 18:09:18

简单下划线之父 · 2021-05-22 18:09:18 发布

最近项目中有使用到sqlite处理数据，发现以前最基本的SQL有点把握不住了，就赶紧复习回顾一下。
1.先下载SQLlite官网下载包
https://www.sqlite.org/download.html
SQLite Download Page - Source Code - 我选择的版本号是：version 3.35.5
在这里插入图片描述
下载解压完成后，拿到四个文件：

2.添加进我们VS工程
开发环境：VS2013。
添加四个文件到我们的工程：

–编译错误;

错误原因：不该使用预编译头。
解决方法：选中两个源文件-属性-C/C+±预编译头-不使用预编译头
在这里插入图片描述
嘿，编译成功
3.demo工程中添加工具类【CSqliteOperator】
实现基本的功能：增删改查战士。

#pragma once
#include <iostream>
#include <vector>
#include <map>
#include <string>
#include "../sqlite/sqlite3.h"
using namespace std;


class CSqliteOperator
{
public:
	CSqliteOperator();
	~CSqliteOperator();
	
	int CreateSqlFile(const string& path);
	int CreateTable(const string& sql);
	int Open(const string& path);

	int Insert(const string& sql);
	int Delete(const string& sql);
	int Update(const string& sql);
	int FindCurrentTableMaxKey(const string& tableName, const string& strKey, int& nMaxKey);//查找当前表最大主键
	int FindAllData(const string& sql , vector<string>& arrKey, vector<vector<string>>& arrValue);

private:
	//sqlie对象的销毁放在析构里，不需要用户关心
	void Destory();
private:
	sqlite3 *pDB;
};

#include "stdafx.h"
#include "SqliteOperator.h"


CSqliteOperator::CSqliteOperator()
{
	pDB = NULL;
}


CSqliteOperator::~CSqliteOperator()
{
	Destory();
}


void CSqliteOperator::Destory()
{
	if (pDB)
	{
		sqlite3_close(pDB);
		pDB = NULL;
	}
}

int CSqliteOperator::CreateSqlFile(const string& path)
{
	return sqlite3_open(path.c_str(), &pDB);
}

int CSqliteOperator::CreateTable(const string& sql)
{
	char *szMsg = NULL;
	return sqlite3_exec(pDB, sql.c_str(), NULL, NULL, &szMsg);
}

int CSqliteOperator::Open(const string& path)
{
	return sqlite3_open(path.c_str(), &pDB);
}

int CSqliteOperator::Insert(const string& sql)
{
	if (sql.empty()) return -1;
	
	char* zErrMsg = NULL;
	int ret = sqlite3_exec(pDB, sql.c_str(), NULL, NULL, &zErrMsg);
	if (zErrMsg)
	{
		sqlite3_free(zErrMsg);
	}
	return ret;
}

int CSqliteOperator::Delete(const string& sql)
{
	int nCols = 0;
	int nRows = 0;
	char **azResult = NULL;
	char *errMsg = NULL;
	int result = sqlite3_get_table(pDB, sql.c_str(), &azResult, &nRows, &nCols, &errMsg);
	if (result != SQLITE_OK)
	{
		return false;
	}
	if (azResult)
	{
		sqlite3_free_table(azResult);
	}
	if (errMsg)
	{
		sqlite3_free(errMsg);
	}
	return true;
}

int CSqliteOperator::Update(const string& sql)
{
	char* zErrMsg = NULL;

	int ret = sqlite3_exec(pDB, sql.c_str(), NULL, NULL, &zErrMsg);
	if (zErrMsg)
	{
		sqlite3_free(zErrMsg);
	}
	return ret;
}

int CSqliteOperator::FindCurrentTableMaxKey(const string& tableName, const string& strKey, int& nMaxKey)
{
	nMaxKey = -1;
	if (tableName.empty() || strKey.empty()) return -1;
	string sql = "select * from " + tableName;

	int nCol = -1;
	int nRow = -1;
	int index = -1;
	char **azResult = NULL;
	char *errMsg = NULL;

	int result = sqlite3_get_table(pDB, sql.c_str(), &azResult, &nRow, &nCol, &errMsg);

	index = nCol;

	//取出最新的，对比穿进去的主键串，就是主键Max值
	for (int i = 0; i < nRow; i++)
	{
		for (int j = 0; j < nCol; j++)
		{
			string s1 = azResult[j];
			string s2 = azResult[index];
			if (s1 == strKey)
			{
				nMaxKey = atoi(azResult[index]);
			}
			index++;
		}
	}

	if (azResult)
	{
		sqlite3_free_table(azResult);
	}
	if (errMsg)
	{
		sqlite3_free(errMsg);
	}
	return result;
}

int CSqliteOperator::FindAllData(const string& sql, vector<string>& arrKey, vector<vector<string>>& arrValue)
{
	if (sql.empty()) return -1;

	int nCols = 0;
	int nRows = 0;
	char **azResult = NULL;
	char *errMsg = NULL;
	int index = 0;
	const int result = sqlite3_get_table(pDB, sql.c_str(), &azResult, &nRows, &nCols, &errMsg);

	index = nCols;
	arrKey.clear();
	arrKey.reserve(nCols);
	arrValue.clear();
	arrValue.reserve(nRows);

	bool bKeyCaptured = false;
	for (int i = 0; i < nRows; i++)
	{
		vector<string> temp;
		for (int j = 0; j < nCols; j++)
		{
			if (!bKeyCaptured)
			{
				arrKey.push_back(azResult[j]);
			}
			temp.push_back(azResult[index]);
			index++;
		}
		bKeyCaptured = true;
		arrValue.push_back(temp);
	}

	if (azResult)
	{
		sqlite3_free_table(azResult);
	}
	if (errMsg)
	{
		sqlite3_free(errMsg);
	}
	return result;
}

4.测试用例
4.1列出主要测试函数：标的创建、数据的、增、删、改、查：

/************************************************************************/
/*主键nID，自增属性*/
/************************************************************************/
void CDemoDlg::OnBnClickedButtonCreateTable()
{
	const CString path = GetAppPath() + "\\hunter.db";
	const CString name = "hunter";

	CSqliteOperator operatorr;

	if (!PathFileExists(path))
	{
		int result = operatorr.CreateSqlFile(path.GetString());

		if (result != SQLITE_OK)
		{
			MessageBox("文件创建失败", "温馨提示");
		}
		else
		{
			const char *sql = "create table hunter(nID integer primary key autoincrement,name string,age integer,sex integer)";
			result = operatorr.CreateTable(sql);
			if (result != SQLITE_OK)
			{
				MessageBox("表创建失败", "温馨提示");
			}
			else
			{
				MessageBox("表创建成功", "温馨提示");
			}
		}
	}
	else
	{
		MessageBox("表已存在", "温馨提示");
	}
}


/************************************************************************/
/* 先添加DB对象，再添加内存对象，保持同步                                     */
/************************************************************************/
void CDemoDlg::OnBnClickedButtonAdd()
{
	const CString path = GetAppPath() + "\\hunter.db";
	const CString name = "hunter";

	if (!PathFileExists(path))
	{
		MessageBox("文件不存在", "温馨提示");
		return;
	}

	CString strName, strAge, strSex;
	CHunter hunter;
	GetDlgItemText(IDC_EDIT_NAME, strName);
	hunter.name = strName;
	GetDlgItemText(IDC_EDIT_AGE, strAge);
	hunter.age = atoi(strAge);
	GetDlgItemText(IDC_EDIT_SEX, strSex);
	hunter.sex = atoi(strSex);

	if (strName.IsEmpty() || strAge.IsEmpty() || strSex.IsEmpty())
	{
		MessageBox("数据不健全", "温馨提示");
		return;
	}

	bool bFind = false;
	for (auto it : m_arrData)
	{
		if (it.name == hunter.name &&
			it.age == hunter.age &&
			it.sex == hunter.sex)
		{
			bFind = true;
			break;
		}
	}
	if (bFind)
	{
		MessageBox("数据已存在", "温馨提示");
		return;
	}

	CSqliteOperator operatorr;
	int result = operatorr.Open(path.GetString());
	if (result != SQLITE_OK)
	{
		MessageBox("文件打开失败", "温馨提示");
		return;
	}

	string strSQL = "insert into hunter(name,age,sex)";
	strSQL += "values('";
	strSQL += hunter.name;
	strSQL += "',";

	strSQL += "'";
	strSQL += std::to_string(hunter.age);
	strSQL += "',";

	strSQL += "'";
	strSQL += std::to_string(hunter.sex);
	strSQL += "',";

	CString temp = strSQL.c_str();
	temp.TrimRight(",");
	temp.Append(")");

	strSQL = temp;
	result = operatorr.Insert(strSQL);
	if (result != SQLITE_OK)
	{
		MessageBox("插入失败", "温馨提示");
		return;
	}
	int nID = 0;
	result = operatorr.FindCurrentTableMaxKey("hunter", "nID", nID);
	if (result != SQLITE_OK)
	{
		MessageBox("查询失败", "温馨提示");
		return;
	}

	if (nID == 0)
	{
		nID = 1;
	}
	hunter.nID = nID;

	m_arrData.push_back(hunter);
	UpdateList();

	MessageBox("添加成功", "温馨提示");
}


/************************************************************************/
/* 先删除DB对象，再删除内存对象，保持同步                                     */
/************************************************************************/
void CDemoDlg::OnBnClickedButtonDelete()
{
	const CString path = GetAppPath() + "\\hunter.db";
	const CString name = "hunter";

	if (!PathFileExists(path))
	{
		MessageBox("文件不存在", "温馨提示");
		return;
	}

	const int nSel = m_listCtrl.GetSelectionMark();
	if (nSel < 0 || nSel >= m_arrData.size())
	{
		MessageBox("请选择条目", "温馨提示");
		return;
	}

	CSqliteOperator operatorr;
	int result = operatorr.Open(path.GetString());
	if (result != SQLITE_OK)
	{
		MessageBox("文件打开失败", "温馨提示");
		return;
	}

	CString tableName = "hunter";
	CString strKey = "nID";
	const int nID = m_arrData[nSel].nID;

	string temp = to_string(nID);
	string strSQL = "delete from " + tableName + " where ";
	strSQL.append(strKey + " =");
	strSQL.append(temp);

	result = operatorr.Delete(strSQL);
	if (result != SQLITE_OK)
	{
		MessageBox("文件打开失败", "温馨提示");
		return;
	}

	m_arrData.erase(m_arrData.begin() + nSel);
	UpdateList();
	MessageBox("删除成功", "温馨提示");
}

/************************************************************************/
/* 先修改DB对象，再修改内存对象，保持同步                                     */
/************************************************************************/
void CDemoDlg::OnBnClickedButtonModify()
{
	const int nSel = m_listCtrl.GetSelectionMark();
	if (nSel < 0 || nSel >= m_arrData.size()) return;

	const CString path = GetAppPath() + "\\hunter.db";
	const CString name = "hunter";

	if (!PathFileExists(path))
	{
		MessageBox("文件不存在", "温馨提示");
		return;
	}

	CString str;
	CSqliteOperator operatorr;
	int result = operatorr.Open(path.GetString());
	if (result != SQLITE_OK)
	{
		MessageBox("文件打开失败", "温馨提示");
		return;
	}

	const int nID = m_arrData[nSel].nID;
	CString newName;
	GetDlgItemText(IDC_EDIT_NAME2, newName);
	CString newAge;
	GetDlgItemText(IDC_EDIT_AGE2, newAge);
	CString newSex;
	GetDlgItemText(IDC_EDIT_SEX2, newSex);
	if (newName.IsEmpty() || newAge.IsEmpty() || newSex.IsEmpty())
	{
		MessageBox("未设置修改数据", "温馨提示");
		return;
	}

	string strSQL = "update hunter set name =";
	strSQL += "'";
	strSQL += newName;
	strSQL += "',";

	strSQL += "age ='";
	strSQL += newAge;
	strSQL += "',";

	strSQL += "sex ='";
	strSQL += newSex;
	strSQL += "',";

	CString ss = strSQL.c_str();
	ss.TrimRight(',');

	ss.Append(" where nID =");
	CString temp;
	temp.Format("%d", nID);
	ss.Append(temp);
	strSQL = ss.GetString();

	result = operatorr.Update(strSQL.c_str());
	if (result != SQLITE_OK)
	{
		MessageBox("数据库更新失败", "温馨提示");
		return;
	}

	auto& item = m_arrData[nSel];
	item.name = newName;
	item.age = atoi(newAge);
	item.sex = atoi(newSex);

	UpdateList();
	MessageBox("修改成功", "温馨提示");
}

void CDemoDlg::OnBnClickedButtonFind()
{
	const CString path = GetAppPath() + "\\hunter.db";
	const CString name = "hunter";

	if (!PathFileExists(path))
	{
		MessageBox("文件不存在", "温馨提示");
		return;
	}

	CString str;

	CSqliteOperator operatorr;
	int result = operatorr.Open(path.GetString());
	if (result != SQLITE_OK)
	{
		MessageBox("文件打开失败", "温馨提示");
		return;
	}

	GetDlgItemText(IDC_EDIT_ID, str);
	if (str.IsEmpty()) return;

	string strSQL = "select * from " + name + " where nID = ";
	strSQL += str;
	vector<string> arrKey;
	vector<vector<string>> arrValue;
	result = operatorr.FindAllData(strSQL, arrKey, arrValue);

	if (result == SQLITE_OK && !arrKey.empty() && !arrValue.empty())
	{
		CString temp = arrKey[0].c_str();
		temp.Append(": ");
		temp.Append(arrValue[0][0].c_str());
		temp.Append(",");

		CString text = "查询成功: ";
		text.Append(temp);

		temp = arrKey[1].c_str();
		temp.Append(": ");
		temp.Append(arrValue[0][1].c_str());
		temp.Append(",");
		text.Append(temp);

		temp = arrKey[2].c_str();
		temp.Append(": ");
		temp.Append(arrValue[0][2].c_str());
		text.Append(temp);

		MessageBox(text, "温馨提示");
	}
}