【已解决】vs2015下c++对sqlite的操作

本博文源于笔者操作sqlite3，借鉴了很多文章的思路，这里并整理了c++常用的对数据库的操作供大家点赞收藏以后备用。包含了：c++对sqlite3的创建数据库、创建数据表、写入数据表、读取数据表、删除数据表。也包括了最基础的让c++运行sqlite3.内容供读者参考，希望对大家有所帮助。

1、如何让c++运行sqlite3

虽然让c++运行sqlite3不是重点，但这里放个连接供大家参考。
跳转链接

2、创建数据库

void CreateDataBase(const char* dataBaseName) {int result = sqlite3_open_v2(dataBaseName, &sql, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE, NULL);if (result == SQLITE_OK) {std::cout << "打开数据库连接成功" << std::endl;;}else {std::cout << "打开数据库连接失败" << std::endl;}
}

3、创建数据表

void CreateTable(const char* tableContent) {sqlite3_stmt* stmt = NULL;        //stmt语句句柄int result = sqlite3_prepare_v2(sql, tableContent, -1, &stmt, NULL);if (result == SQLITE_OK) {std::clog << "创建数据表成功" << std::endl;sqlite3_step(stmt);}else {std::clog << "创建数据表失败" << std::endl;}sqlite3_finalize(stmt);
}

写入语句

void InsertValue(const char* sqlContent) {// 表单中插入信息//进行插入前的准备工作——检查语句合法性//-1代表系统会自动计算SQL语句的长度sqlite3_stmt* stmt = NULL;int result = sqlite3_prepare_v2(sql, sqlContent, -1, &stmt, NULL);if (result == SQLITE_OK) {std::clog << "添加数据语句OK" << std::endl;//执行该语句sqlite3_step(stmt);}else {std::clog << "添加数据语句有问题" << std::endl;}//清理语句句柄，准备执行下一个语句sqlite3_finalize(stmt);
}

读取信息


void ReadTable() {// 查询char* errmsg;char** mResult;//结果集int mRow;//行数int mCol;//列数int nResult = sqlite3_get_table(sql, "select * from devices;", &mResult, &mRow, &mCol, &errmsg);int nIndex = mCol;cout << "mRow: " << mRow << endl;cout << "mCol: " << mCol << endl;if (mRow > 0 && mCol > 0){for (int i = 0; i < mRow; i++){for (int j = 0; j < mCol; j++){cout << mResult[j] << ": " << mResult[nIndex] << endl;++nIndex;}cout << endl;}sqlite3_free_table(mResult);}
}

删除数据表

void DelTable(const char* sqlContent) {sqlite3_stmt* stmt = NULL;int result = sqlite3_prepare_v2(sql, sqlContent, -1, &stmt, NULL);if (result == SQLITE_OK) {std::clog << "删除数据完毕OK" << std::endl;sqlite3_step(stmt);}else {std::clog << "删除数据有问题" << std::endl;}
}

完整测试样例

int main() {//创建数据库CreateDataBase();//创建数据表const char *sqlCreateTable = "CREATE TABLE IF NOT EXISTS devices (""deviceid INTEGER PRIMARY KEY,""devMac TEXT,""devSn TEXT,""keySn TEXT,""matNum TEXT,""openId TEXT,""workNum TEXT);";CreateTable(sqlCreateTable);//插入数据const char *sqlInsert = "INSERT INTO devices  VALUES (1, '00:12:31:8f:a7:52','812aab3b5b57cce4','0012110909801968','','64e0886f60b2be8101031868','lc123');";InsertValue(sqlInsert);//读取数据ReadTable();//删除数据//const char* sqlDel = "delete from  devices where deviceid=1";//DelTable(sqlDel);//ReadTable();system("pause");return 0;
}