使用C++ 11 lambda表达式对于SQLite数据库的简单

SQLite数据库 提供了基于纯C的API，使用起来比较难受，具体可以参考菜鸟教程。
本文使用C++11对SQLite数据库进行了简单封装，其中sqlite3.h和sqlite3.c两个文件是SQLite官方的，这里不再列出。
因为要在嵌入式设备上增量记录状态，所以需要引入数据库，最简单的数据库就是SQLite了。
代码如下
conanfile.txt

[requires]
 opencv/4.5.5
 boost/1.72.0

[generators]
cmake

CMakeLists.txt

cmake_minimum_required(VERSION 3.3)


project(23_sqlite)

set(ENV{PKG_CONFIG_PATH} "$ENV{PKG_CONFIG_PATH}:/usr/local/lib/pkgconfig/")

set ( CMAKE_CXX_FLAGS "-pthread")
set(CMAKE_CXX_STANDARD 17)
add_definitions(-g)

include(${CMAKE_BINARY_DIR}/conanbuildinfo.cmake)
conan_basic_setup()

include_directories(${INCLUDE_DIRS})
LINK_DIRECTORIES(${LINK_DIRS})

file( GLOB main_file_list ${CMAKE_CURRENT_SOURCE_DIR}/*.cpp) 

foreach( main_file ${main_file_list} )
    file(RELATIVE_PATH filename ${CMAKE_CURRENT_SOURCE_DIR} ${main_file})
    string(REPLACE ".cpp" "" file ${filename})
    add_executable(${file}  ${main_file} sqlite3.c sqllite_op.cc)
    target_link_libraries(${file} ${CONAN_LIBS} pthread)
endforeach( main_file ${main_file_list})

sqlite_op.h

#ifndef _FREDRIC_SQLITE_OP_H_
#define _FREDRIC_SQLITE_OP_H_

#include <string>
#include <map>
#include <vector>
#include "sqlite3.h"

using ResultMap = std::vector<std::map<std::string, std::string>>;

struct SQLiteOp {
public:
    SQLiteOp(std::string const& db_name_);
    ~SQLiteOp();
    void Open();
    void Close();
    bool ExecUpdate(std::string const& updateSql);
    ResultMap Query(std::string const& querySql);
private:
    std::string db_name;
    sqlite3 *db;
    bool is_closed {true};
};

#endif

sqlite_op.cc

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

SQLiteOp::SQLiteOp(std::string const& db_name_): db_name(db_name_) {}

void SQLiteOp::Open() {
    char *zErrMsg = 0;
    int rc;
    char *sql;

    /* Open database */
    rc = sqlite3_open(db_name.data(), &db);
    if( rc ){
        std::cerr << "Can't open database: " << sqlite3_errmsg(db) << "\n"; 
        exit(0);
    }else{
        is_closed = false;
        std::cout << "Opened database successfully\n";
    }
}

void SQLiteOp::Close() {
    std::cout << "Close DB\n";
    sqlite3_close(db);
    is_closed = true;
}

bool SQLiteOp::ExecUpdate(std::string const& updateSql) {
    char *zErrMsg = 0;
    int rc = sqlite3_exec(db, updateSql.data(), nullptr, 0, &zErrMsg);
    if( rc != SQLITE_OK ){
        std::cerr << "SQL error: " << zErrMsg << "\n";
        sqlite3_free(zErrMsg);
        return false;
    }else{
        std::cout << "Records created successfully\n";
        return true;
    }
}

ResultMap SQLiteOp::Query(std::string const& querySql) {
    ResultMap results;
    char *zErrMsg = 0;
    /* Execute SQL statement */
    int rc = sqlite3_exec(db, querySql.data(), [](void *data, int argc, char **argv, char **azColName) {
            int i;
            auto& results_ = *((ResultMap*)(data));
            std::map<std::string, std::string> result;
            for(i=0; i<argc; i++){
                if(argv[i]) {
                    result[azColName[i]] =  argv[i];
                } else {
                    result[azColName[i]] = "NULL";
                }
            }
            results_.emplace_back(std::move(result));
            return 0;
    }, (void*)&results, &zErrMsg);

    if( rc != SQLITE_OK ){
        std::cerr << "SQL error: " << zErrMsg << "\n";
        sqlite3_free(zErrMsg);
    }else{
        std::cout << "Operation done successfully\n";
    }
    return results;
}

SQLiteOp::~SQLiteOp() {
    if(!is_closed) {
        std::cout << "Close DB\n";
        sqlite3_close(db);
    }
}

sqlite_demo.cpp

#include <stdio.h>
#include <stdlib.h>
#include <map>
#include <string>
#include <iostream>
#include <boost/filesystem.hpp>
#include "sqllite_op.h"


void createDBStructure(SQLiteOp& sqlite_op) {
   /* Create SQL statement */
    std::string createTabelSql = "CREATE TABLE COMPANY("  \
         "ID INT PRIMARY KEY     NOT NULL," \
         "NAME           TEXT    NOT NULL," \
         "AGE            INT     NOT NULL," \
         "ADDRESS        CHAR(50)," \
         "SALARY         REAL );";
    sqlite_op.ExecUpdate(createTabelSql);
    
    /* Create SQL statement */
    std::string insertDataSql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
    sqlite_op.ExecUpdate(insertDataSql);
}

int main(int argc, char* argv[]) {
    std::string db_name {"./test.db"};
    bool shouldCreateDB {false};
    if(!boost::filesystem::exists(db_name)) {
        shouldCreateDB = true;
    }

    SQLiteOp sqlite_op {db_name};
    sqlite_op.Open();
    std::string querySql = "SELECT * from COMPANY";
    std::cout << "DB Name: " << db_name << std::endl;

    if(shouldCreateDB) {
        std::cout << "db not exists\n";
        createDBStructure(sqlite_op);
    }

    auto results = sqlite_op.Query(querySql);
    for(auto const& result: results) {
        for(auto const& entry: result) {
            std::cout << entry.first << ": " << entry.second << "\t";
        }
        std::cout << "\n";
    }

    sqlite_op.Close();
    sqlite_op.Open();

    results = sqlite_op.Query(querySql);
    for(auto const& result: results) {
        for(auto const& entry: result) {
             std::cout << entry.first << ": " << entry.second << "\t";
        }
        std::cout << "\n";
    }
    return 0;
}

程序输出如下，