C++使用Sqlite_简单下划线之父_c++ sqlite

网络 02-07 6398

最近项目中有使用到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); return ret; } int CSqliteOperator::Delete(const string& sql) { int nCols = 0; int nRows = 0; char **azResult = NULL; char *errMsg = NULL; return sqlite3_get_table(pDB, sql.c_str(), &azResult, &nRows, &nCols, &errMsg); } int CSqliteOperator::Update(const string& sql) { char* zErrMsg = NULL; int ret = sqlite3_exec(pDB, sql.c_str(), NULL, NULL, &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++; } } 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); } 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, "温馨提示"); } }

Demo链接