使用Sqlite3数据库
准备依赖
下载依赖
- sqlite3官网
访问官网下载
把下载的依赖sqlite3.c复制到源文件目录下
NUGET安装插件
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="sqlite3_c_plus_plus" version="1.0.3" targetFramework="native" />
</packages>
创建数据库
头部定义
#include<iostream> //标准输入输出流
#include<sqlite3.h> //使用sqlite3的头文件
#include <vector> //因为c++编译的时候编码为ASCII sqlite数据库编码为UTF-8,需要转换编码
#include<Windows.h>//转换编码需要用到这个函数库
using namespace std; //使用标准的std函数
//数据库函数定义
static int createDB(const char* s);
main函数代码
int main(int argc, char** argv, char** env)
{
//数据库操作
//const char* dir = "C:\project\data.db";
//同级目录创建data.db
const char* dir = "data.db";
createDB(dir);
return 0;
}
创建数据库函数
//创建数据库
static int createDB(const char* s) {
sqlite3* DB;
int exit = 0;
exit = sqlite3_open(s, &DB);
sqlite3_close(DB);
return 0;
}
创建数据表
头部定义
static int createTable(const char* s);
main函数代码
int main(int argc, char** argv, char** env)
{
//数据库操作
//const char* dir = "C:\project\data.db";
//同级目录创建data.db
const char* dir = "data.db";
createDB(dir);
createTable(dir);
return 0;
}
创建数据表函数
//插入数据表
static int createTable(const char* s) {
sqlite3* DB;
string sql = "CREATE TABLE IF NOT EXISTS USER ("
"ID INTEGER PRIMARY KEY AUTOINCREMENT,"
"NAME VARCHAR NOT NULL,"
"PHONE VARCHAR NOT NULL,"
"AGE INT NOT NULL,"
"ADDRESS CHAR(50) NOT NULL,"
"NOTE CHAR(20) NOT NULL); ";
try
{
int exit = 0;
exit = sqlite3_open(s, &DB);
char* messageError;
exit = sqlite3_exec(DB, sql.c_str(), NULL, 0, &messageError);
if (exit != SQLITE_OK) {
cerr << "Error Create DB" << endl;
sqlite3_free(messageError);
}
else {
cout << "Table Create Success!" << endl;
}
sqlite3_close(DB);
}
catch (const exception& e)
{
cerr << e.what();
}
return 0;
}
插入数据
头部定义
static int insertData(const char* s);
main函数代码
int main(int argc, char** argv, char** env)
{
//数据库操作
//const char* dir = "C:\project\data.db";
//同级目录创建data.db
const char* dir = "data.db";
createDB(dir);
createTable(dir);
insertData(dir);
return 0;
}
插入数据函数
//插入数据
static int insertData(const char* s) {
sqlite3* DB;
char* messageError;
int exit = sqlite3_open(s, &DB);
string sql = "INSERT INTO USER(NAME,PHONE,AGE,ADDRESS,NOTE) VALUES('张三','18953856789',23,'北京市海淀区','同学');"
"INSERT INTO USER(NAME,PHONE,AGE,ADDRESS,NOTE) VALUES('李四','18953234789',13,'北京市大兴区','同事');"
"INSERT INTO USER(NAME,PHONE,AGE,ADDRESS,NOTE) VALUES('小花','18953856888',53,'北京市朝阳区','舍友');"
"INSERT INTO USER(NAME,PHONE,AGE,ADDRESS,NOTE) VALUES('白马王子','13153856789',20,'北京市海淀区','同学');";
sql = ASCII2UTF8(sql);
exit = sqlite3_exec(DB, sql.c_str(), NULL, 0, &messageError);
if (exit != SQLITE_OK) {
cerr << "插入数据错误!" << endl;
sqlite3_free(messageError);
}
else {
cout << "数据插入成功!" << endl;
}
return 0;
}
查询数据
头部定义
static int selectData(const char* s);
static int callback(void* NotUsed, int argc, char** argv, char** azColName);
main函数代码
int main(int argc, char** argv, char** env)
{
//数据库操作
//const char* dir = "C:\project\data.db";
//同级目录创建data.db
const char* dir = "data.db";
createDB(dir);
createTable(dir);
insertData(dir);
selectData(dir);
return 0;
}
查询数据函数
//查询数据
static int selectData(const char* s) {
sqlite3* DB;
int exit = sqlite3_open(s, &DB);
string sql = "SELECT * FROM USER";
sqlite3_exec(DB, sql.c_str(), callback, NULL, NULL);
return 0;
}
//查询回调函数
static int callback(void* NotUsed, int argc, char** argv, char** azColName) {
//因为sqlite返回的数据是UTF-8格式的,而控制台的打印UTF-8数据会乱码,所以使用这个函数设置控制它的编码格式
//两种方式都可以实现
//system("chcp 65001");
SetConsoleOutputCP(65001);
for (int i = 0; i < argc; i++) {
cout << azColName[i] << ": " << argv[i] << endl;
}
cout << endl;
return 0;
}
更新数据
头部定义
static int updateData(const char* s);
main函数代码
int main(int argc, char** argv, char** env)
{
//数据库操作
//const char* dir = "C:\project\data.db";
//同级目录创建data.db
const char* dir = "data.db";
createDB(dir);
createTable(dir);
insertData(dir);
selectData(dir);
updateData(dir);
return 0;
}
更新数据函数
//更新数据
static int updateData(const char* s) {
sqlite3* DB;
char* messageError;
int exit = sqlite3_open(s, &DB);
string sql = "UPDATE USER SET PHONE = '18956668888' WHERE NAME = '张三';";
sql = ASCII2UTF8(sql);
exit = sqlite3_exec(DB, sql.c_str(), NULL, 0, &messageError);
if (exit != SQLITE_OK) {
cerr << "更新数据错误!" << endl;
sqlite3_free(messageError);
}
else {
cout << "数据更新成功!" << endl;
}
return 0;
}
删除数据
头部定义
static int deleteData(const char* s);
main函数代码
int main(int argc, char** argv, char** env)
{
//数据库操作
//const char* dir = "C:\project\data.db";
//同级目录创建data.db
const char* dir = "data.db";
createDB(dir);
createTable(dir);
deleteData(dir);
insertData(dir);
selectData(dir);
updateData(dir);
return 0;
}
删除数据函数
//删除数据
static int deleteData(const char* s) {
sqlite3* DB;
int exit = sqlite3_open(s, &DB);
string sql = "DELETE FROM USER;";
exit = sqlite3_exec(DB, sql.c_str(), callback, NULL, NULL);
return 0;
}
编码转换函数
编码转换函数代码
//UTF-8转Unicode
std::wstring Utf82Unicode(const std::string& utf8string) {
int widesize = ::MultiByteToWideChar(CP_UTF8, 0, utf8string.c_str(), -1, NULL, 0);
if (widesize == ERROR_NO_UNICODE_TRANSLATION) {
throw std::exception("Invalid UTF-8 sequence.");
}
if (widesize == 0) {
throw std::exception("Error in conversion.");
}
std::vector<wchar_t> resultstring(widesize);
int convresult = ::MultiByteToWideChar(CP_UTF8, 0, utf8string.c_str(), -1, &resultstring[0], widesize);
if (convresult != widesize) {
throw std::exception("La falla!");
}
return std::wstring(&resultstring[0]);
}
//unicode 转为 ascii
string WideByte2Acsi(wstring& wstrcode) {
int asciisize = ::WideCharToMultiByte(CP_OEMCP, 0, wstrcode.c_str(), -1, NULL, 0, NULL, NULL);
if (asciisize == ERROR_NO_UNICODE_TRANSLATION) {
throw std::exception("Invalid UTF-8 sequence.");
}
if (asciisize == 0) {
throw std::exception("Error in conversion.");
}
std::vector<char> resultstring(asciisize);
int convresult = ::WideCharToMultiByte(CP_OEMCP, 0, wstrcode.c_str(), -1, &resultstring[0], asciisize, NULL, NULL);
if (convresult != asciisize) {
throw std::exception("La falla!");
}
return std::string(&resultstring[0]);
}
//utf-8 转 ascii
string UTF_82ASCII(string& strUtf8Code) {
string strRet("");
//先把 utf8 转为 unicode
wstring wstr = Utf82Unicode(strUtf8Code);
//最后把 unicode 转为 ascii
strRet = WideByte2Acsi(wstr);
return strRet;
}
//ascii 转 Unicode
wstring Acsi2WideByte(string& strascii) {
int widesize = MultiByteToWideChar(CP_ACP, 0, (char*)strascii.c_str(), -1, NULL, 0);
if (widesize == ERROR_NO_UNICODE_TRANSLATION) {
throw std::exception("Invalid UTF-8 sequence.");
}
if (widesize == 0) {
throw std::exception("Error in conversion.");
}
std::vector<wchar_t> resultstring(widesize);
int convresult = MultiByteToWideChar(CP_ACP, 0, (char*)strascii.c_str(), -1, &resultstring[0], widesize);
if (convresult != widesize) {
throw std::exception("La falla!");
}
return std::wstring(&resultstring[0]);
}
//Unicode 转 Utf8
std::string Unicode2Utf8(const std::wstring& widestring) {
int utf8size = ::WideCharToMultiByte(CP_UTF8, 0, widestring.c_str(), -1, NULL, 0, NULL, NULL);
if (utf8size == 0) {
throw std::exception("Error in conversion.");
}
std::vector<char> resultstring(utf8size);
int convresult = ::WideCharToMultiByte(CP_UTF8, 0, widestring.c_str(), -1, &resultstring[0], utf8size, NULL, NULL);
if (convresult != utf8size) {
throw std::exception("La falla!");
}
return std::string(&resultstring[0]);
}
//ascii 转 Utf8
string ASCII2UTF8(string& strAsciiCode) {
string strRet("");
//先把 ascii 转为 unicode
wstring wstr = Acsi2WideByte(strAsciiCode);
//最后把 unicode 转为 utf8
strRet = Unicode2Utf8(wstr);
return strRet;
}
代码区域
原文链接: https://www.cnblogs.com/bartonboy/p/12848832.html
欢迎关注
微信关注下方公众号,第一时间获取干货硬货;公众号内回复【pdf】免费获取数百本计算机经典书籍;
也有高质量的技术群,里面有嵌入式、搜广推等BAT大佬
原创文章受到原创版权保护。转载请注明出处:https://www.ccppcoding.com/archives/347082
非原创文章文中已经注明原地址,如有侵权,联系删除
关注公众号【高性能架构探索】,第一时间获取最新文章
转载文章受原作者版权保护。转载请注明原作者出处!