在《VS2019下Sqlite3数据库的环境搭建及简单应用》，这篇中描述了第三方sqlite3库的环境配置和简单使用，重新整理下操作类的逻辑；
其实就是增删改查。
一、代码实现
基本功能实现如下：
// WSqlite.h#ifndef WSQLITE_H_#define WSQLITE_H_#include #include #include #include "sqlite3.h"#pragma comment(lib, "sqlite3.lib")using namespace std;class WSqlite {public:WSqlite();~WSqlite();int CreateDbFile(const string &path);// 创建数据库文件int CreateTable(const string& sqlCreatetable);// 创建数据库表int Opendb(const string& path);// 连接数据库int Insert(const string& sqlInsert);// 增int Delete(const string& sqlDelete);// 删int Update(const string& sqlUpdate);// 改int QueryData(const string& sqlQuery, vector &arrKey, vector<vector> &arrValue);// 查private:sqlite3* pDb = NULL;private://sqlie对象的销毁放在析构里，不需要用户关心void Destory();};#endif// WSqlite.cpp#include #include #include #include #include "sqlite3.h"#include "WSqlite.h"using namespace std;#pragma comment(lib, "sqlite3.lib")WSqlite::WSqlite(){pDb = NULL;}WSqlite::~WSqlite(){Destory();}void WSqlite::Destory(){if (pDb){sqlite3_close(pDb);pDb = NULL;}}int WSqlite::CreateDbFile(const string& path){return sqlite3_open(path.c_str(), &pDb);}int WSqlite::CreateTable(const string& sqlCreatetable) {char* szMsg = NULL;return sqlite3_exec(pDb, sqlCreatetable.c_str(), NULL, NULL, &szMsg);}int WSqlite::Opendb(const string& path){return sqlite3_open(path.c_str(), &pDb);}int WSqlite::Insert(const string& sqlInsert){if (sqlInsert.empty()) {return -1;}char* zErrMsg = NULL;int ret = sqlite3_exec(pDb, sqlInsert.c_str(), NULL, NULL, &zErrMsg);if (zErrMsg) {sqlite3_free(zErrMsg);}return ret;}int WSqlite::Delete(const string& sqlDelete){int nCols = 0;int nRows = 0;char** azResult = NULL;char* errMsg = NULL;int res = sqlite3_get_table(pDb, sqlDelete.c_str(), &azResult, &nRows, &nCols, &errMsg);if (res != SQLITE_OK) {return false;} if (azResult) {sqlite3_free_table(azResult);}if (errMsg) {sqlite3_free(errMsg);}return true;}int WSqlite::Update(const string& sqlUpdate){char* zErrMsg = NULL;int ret = sqlite3_exec(pDb, sqlUpdate.c_str(), NULL, NULL, &zErrMsg);if (zErrMsg) {sqlite3_free(zErrMsg);}return ret;}int WSqlite::QueryData(const string& sqlQuery, vector& arrKey, vector<vector>& arrValue){if (sqlQuery.empty()) {return -1;}int nCols = -1;int nRows = -1;char** azResult = NULL;char* errMsg = NULL;int index = 0;const int ret = sqlite3_get_table(pDb, sqlQuery.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 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 ret;}二、测试验证
2.1 数据格式
dbName: run.db;
sheetName: myfriends;
| ID | Name | Age | Major |
|---|---|---|---|
| 1 | xiaohuoche | 9 | sing |
| 2 | xiaoshuai | 8 | dance |
| 3 | xiaomei | 7 | rap |
2.2 测试函数
2.2.1 创建数据库文件和表格
void crteateTable() {// 获取pathchar buffer[MAX_PATH];_getcwd(buffer, MAX_PATH);string path = buffer;path += "\\run.db";cout << path << endl;// 创建db数据库文件WSqlite sqlOperate;int result = sqlOperate.CreateDbFile(path);if (result != SQLITE_OK){cout << "文件创建失败！" << endl;}else{// 创建数据库表const char* sql = "create table myfriends(ID integer primary key autoincrement,Name string,Age integer,Major string)";result = sqlOperate.CreateTable(sql);if (result != SQLITE_OK){cout << "表创建失败！" <<endl;}else{cout << "表创建成功！" << endl;}}}运行结果如下：
2.2.2 增
void test_addData() {// 获取pathchar buffer[MAX_PATH];_getcwd(buffer, MAX_PATH);string path = buffer;path += "\\run.db";// 连接数据库WSqlite sqlOperate;int result = sqlOperate.Opendb(path);if (result != SQLITE_OK){cout<<"文件打开失败"<<endl;return;}// 插入一行信息string strSQL = "insert into myfriends(ID, Name, Age, Major)";strSQL += "values('1', 'xiaohuoche', '9', 'sing');";result = sqlOperate.Insert(strSQL);if (result != SQLITE_OK){cout<<"插入失败"<<endl;return;}// 再插入两行strSQL = "insert into myfriends(ID, Name, Age, Major)";strSQL += "values('2', 'xiaoshuai', '8', 'dance');";result = sqlOperate.Insert(strSQL);if (result != SQLITE_OK){cout<<"插入失败"<<endl;return;}strSQL = "insert into myfriends(ID, Name, Age, Major)";strSQL += "values('3', 'xiaomei', '7', 'rap');";result = sqlOperate.Insert(strSQL);if (result != SQLITE_OK){cout<<"插入失败"<<endl;return;}}运行结果如下：
2.2.3 删
void test_deleteData() {// 获取pathchar buffer[MAX_PATH];_getcwd(buffer, MAX_PATH);string path = buffer;path += "\\run.db";// 连接数据库WSqlite sqlOperate;int result = sqlOperate.Opendb(path);if (result != SQLITE_OK){cout<<"文件打开失败"<<endl;return;}// 删除第二行信息string tableName = "myfriends";string strKey = "ID";string strSQL = "delete from " + tableName + " where ";strSQL.append(strKey + " = 2");result = sqlOperate.Delete(strSQL);if (result != SQLITE_OK){cout << "删除时文件打开失败" << endl;return;}cout<<"删除成功"<<endl;}运行结果如下：
2.2.4 改
void test_updateData() {// 获取pathchar buffer[MAX_PATH];_getcwd(buffer, MAX_PATH);string path = buffer;path += "\\run.db";// 连接数据库WSqlite sqlOperate;int result = sqlOperate.Opendb(path);if (result != SQLITE_OK){cout<<"文件打开失败"<<endl;return;}// 修改第二行信息string strSQL = "update myfriends set Major =";strSQL = strSQL + "'math'" + "where ID =" + "2";result = sqlOperate.Update(strSQL);if (result != SQLITE_OK){cout << "修改时文件打开失败" << endl;return;}cout<<"修改成功"<<endl;}回撤删除动作之后，运行结果如下，小帅开始major in math了：
2.2.5 查
void test_queryData() {// 获取pathchar buffer[MAX_PATH];_getcwd(buffer, MAX_PATH);string path = buffer;path += "\\run.db";// 连接数据库WSqlite sqlOperate;int result = sqlOperate.Opendb(path);if (result != SQLITE_OK){cout<<"文件打开失败"<<endl;return;}// 查询第二行信息string strSQL = "select * from myfriends where ID = 2";vector arrKey;vector<vector> arrValue;result = sqlOperate.QueryData(strSQL, arrKey, arrValue);if (result == SQLITE_OK && !arrKey.empty() && !arrValue.empty()){cout << "单行查询结果：\n";for(int i1=0;i1运行结果如下：
三、其他
3.1 关于查询sql语句Select带参数的用法
当我们执行形如"select * from sheetname "时的sql查询语句时，默认是从第一行开始检索整个数据库信息，有时为了特定需求，可以指定限制检索起始位置及检索数量；
select * from sheetname limit parameter1, parameter2上述sql语句的含义是，从第param1行开始，读取接下来的param2行，parameter2可以是负数，为负时检索所有行；如上param1和param2是用","分割的，若是用OFFSET分割，则限制值是第一个数字，而偏移量(offset)是第二个数字 ，两种情况是反着的！
3.2 数据库路径包含中文
需要注意，SqLite只支持UTF-8编码格式，所以无法识别包含汉字的多字符集。
//多字符集转换为UnicodeWCHAR *CDB::mbcsToUnicode(const char *zFilename) { int nByte; WCHAR *zMbcsFilename; int codepage = AreFileApisANSI() " />C++使用Sqlitevector的reserve的使用（避免内存重新分配以及内存分配的方式）
Sqlite3查询指定行数数据
C++ 获取当前路径
MFC操作SQlite，打开数据库路径存在中文，解决方案