C++使用sqlite3操作数据库

        在《VS2019下Sqlite3数据库的环境搭建及简单应用》，这篇中描述了第三方sqlite3库的环境配置和简单使用，重新整理下操作类的逻辑；

​        其实就是增删改查。

一、代码实现

         基本功能实现如下：

// WSqlite.h
#ifndef WSQLITE_H_
#define WSQLITE_H_

#include <string>
#include <vector>
#include <iostream>
#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<string> &arrKey, vector<vector<string>> &arrValue);	// 查

private:
	sqlite3* pDb = NULL;

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

#endif

// WSqlite.cpp
#include <iostream>
#include <string>
#include <vector>
#include <fstream>

#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<string>& arrKey, vector<vector<string>>& 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<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 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() {
    // 获取path
    char 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() {
	// 获取path
    char 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() {
	// 获取path
    char 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() {
	// 获取path
    char 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() {
	// 获取path
    char 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<string> arrKey;
	vector<vector<string>> arrValue;
	result = sqlOperate.QueryData(strSQL, arrKey, arrValue);

	if (result == SQLITE_OK && !arrKey.empty() && !arrValue.empty())
	{
		cout << "单行查询结果：\n";
		for(int i1=0;i1<arrValue[0].size();i1++ ){
			cout << arrKey[i1] <<":"<< "\t";
            cout << arrValue[0][i1] << "\t";
		}
		cout << "\n";
	}
	else{
        cout << "查询时文件打开失败" << endl;
	}
	// 查询整个表的信息
	strSQL = "select * from myfriends";
	arrKey.clear();
	arrValue.clear();
	result = sqlOperate.QueryData(strSQL, arrKey, arrValue);

	if (result == SQLITE_OK && !arrKey.empty() && !arrValue.empty())
	{
		cout << "整个数据库查询结果：\n";
		for(int i1=0;i1<arrValue.size();i1++ ){
			for(int i2=0;i2<arrValue[0].size();i2++ ){
                cout << arrKey[i2] <<":"<< "\t";
                cout << arrValue[i1][i2] << "\t";
			}
            cout << "\n";
		}
	}
	else{
        cout << "查询时文件打开失败" << endl;
	}
	return;
}

运行结果如下：

三、其他

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编码格式，所以无法识别包含汉字的多字符集。

//多字符集转换为Unicode
WCHAR *CDB::mbcsToUnicode(const char *zFilename) 
{ 
    int nByte; 
    WCHAR *zMbcsFilename; 
    int codepage = AreFileApisANSI() ? CP_ACP : CP_OEMCP; 
    nByte = MultiByteToWideChar(codepage, 0, zFilename, -1, NULL,0)*sizeof(WCHAR); 
    zMbcsFilename = (WCHAR *)malloc(nByte*sizeof(zMbcsFilename[0])); 
    if( zMbcsFilename==0 ) 
    { 
    	return 0; 
    } 
    nByte = MultiByteToWideChar(codepage, 0, zFilename, -1, zMbcsFilename, nByte); 
    if(nByte == 0) 
    { 
        free(zMbcsFilename); 
        zMbcsFilename = 0; 
    }
    return zMbcsFilename; 
}
//Unicode转为UTF-8
char *CDB::unicodeToUtf8(const WCHAR *zWideFilename) 
{ 
    int nByte; char *zFilename; 
    nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, 0, 0, 0, 0); 
    zFilename = (char *)malloc(nByte); 
    if(zFilename == 0) 
    {  
    	return 0;
    } 
    nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, zFilename, nByte, 0, 0); 
    if( nByte == 0 ) 
    { 
        free(zFilename); 
        zFilename = 0; 
    } 
    return zFilename; 
} 
// 调用
WCHAR* wcPath;
wcPath = mbcsToUnicode(pathStr.c_str());//paths即为带有中文的全路径字符串
char* path = unicodeToUtf8(wcPath);

参考链接：

C++使用Sqlite

vector的reserve的使用（避免内存重新分配以及内存分配的方式）

Sqlite3查询指定行数数据

C++ 获取当前路径

MFC操作SQlite，打开数据库路径存在中文，解决方案