sqlite

c++客户端项目中需要使用db来保存本地的一些用户信息，sqlite是个轻量级的db，速度也ok，于是选择sqlite来存储本地信息
首先在官网http://www.sqlite.org/download.html下载我们需要的文件
sqlite-amalgamation-3350500：包含sqlite3.h等头文件
sqlite-dll-win32-x86-3350500：包含sqlite3.dll等文件
sqlite-tools-win32-x86-3350500：包含sqlite3.exe等文件
使用vs自带的工具来生成sqlite3.lib
工具名称：Developer Command Prompt for VS 2019

E:\sqlite\sqlite-tools-win32-x86-3350500>cd E:\sqlite\sqlite-dll-win32-x86-3350500

E:\sqlite\sqlite-dll-win32-x86-3350500>
E:\sqlite\sqlite-dll-win32-x86-3350500>
E:\sqlite\sqlite-dll-win32-x86-3350500>lib /def:sqlite3.def /machine:ix86
Microsoft (R) Library Manager Version 14.27.29112.0
Copyright (C) Microsoft Corporation.  All rights reserved.

  正在创建库 sqlite3.lib 和对象 sqlite3.exp

E:\sqlite\sqlite-dll-win32-x86-3350500>

创建一个控制台程序，导入头文件和库，简单的测试下sqlite

#pragma once
#include <iostream>
#include "sqlite3.h"

class sqlite_helper {
public:
    static sqlite_helper& get_instance();

    bool opendb(const char* db_path);
    bool closedb();
    bool exec_sql(const char* psql);

private:
    sqlite_helper() {}
    ~sqlite_helper() {}

    sqlite_helper(const sqlite_helper& other) {}
    sqlite_helper& operator=(const sqlite_helper& other) {}

private:
    sqlite3* pDB = nullptr;
};

#include "sqlitehelper.h"


sqlite_helper& sqlite_helper::get_instance() {
    static sqlite_helper instance_;
    return instance_;
}

bool sqlite_helper::opendb(const char* db_path) {
    int nret = sqlite3_open(db_path, &pDB);
    if (SQLITE_OK != nret) {
        std::cout << "open db : " << db_path << " failed!" << std::endl;
        return false;
    }
    return true;
}

bool sqlite_helper::closedb() {
    sqlite3_close(pDB);

    return true;
}

bool sqlite_helper::exec_sql(const char* psql) {
    int nret =  sqlite3_exec(pDB, psql, 0, 0, 0);
    if (SQLITE_OK != nret) {
        std::cout << "exec_sql : " << psql << " failed!" << std::endl;
        return false;
    }
    return true;
}

#include <iostream>
#include "sqlitehelper.h"

bool adduser(const std::string& name, const std::string& age) {
    std::string strsql;
    strsql += "insert into user(name,age)";
    strsql += "values('";
    strsql += name;
    strsql += "',";
    strsql += age;
    strsql += ");";

    return sqlite_helper::get_instance().exec_sql(strsql.c_str());
}

int main()
{
    sqlite_helper::get_instance().opendb("E:\\sqlite\\test.db");
    adduser("zhangsan", "20");
    std::cout << "Hello World!\n";
}

用sqlite expert 简单的看下db里面的数据，发现已经写入成功了

2021519-161234.png

如何使用sqlite查看sql的执行情况

PS D:\sqlite> .\sqlite3.exe .\test.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .tables
message_table  user
sqlite> explain query plan select * from message_table where sid='1560935030001' ;
QUERY PLAN
`--SEARCH message_table USING INDEX index_test (sid=?)