C++实现Sqlite3增删改查

快来打我* 2022-06-10 03:06 408阅读 0赞

#include <iostream>
    #include <string>
    #include <vector>
    #include <list>
    
    #include "sqlite3.h"
    
    static const char* kDatabaseName = "test.db";
    
    static void OpenDB();
    static void CreateTable();
    static void InsertData();
    static void SelectData();
    static void UpdateData();
    static void DeleteData();
    static void StepExecute();
    
    int main1() {
      OpenDB();
      CreateTable();
      InsertData();
      SelectData();
      UpdateData();
      DeleteData();
      StepExecute();
      return 0;
    }
    
    static void OpenDB() {
      sqlite3* db = NULL;
    
      // The database is opened for reading and writing, and is created if it does not already exist. 
      // 如果数据库存在直接打开，如果不存在则创建之后再打开。打开方式为可读可写。
      int rc = sqlite3_open(kDatabaseName, &db);
      if (rc != SQLITE_OK) {
        int error_code = sqlite3_errcode(db);
        const char* error_msg = sqlite3_errmsg(db);
    
        std::cout << "Open failed!" << std::endl;
        std::cout << "Error code: " << error_code << std::endl;
        std::cout << "Error message: " << error_msg << std::endl;
        return;
      }
    
      std::cout << "Open successfully!" << std::endl;
    
      sqlite3_close(db);
    }
    
    static void CreateTable() {
      sqlite3* db = NULL;
    
      int rc = sqlite3_open(kDatabaseName, &db);
      if (rc != SQLITE_OK) {
        std::cout << sqlite3_errmsg(db) << std::endl;
      }
    
      const char* kCreateTableSql = "CREATE TABLE COMPANY( \
                                     ID INT PRIMARY KEY NOT NULL, \
                                     NAME TEXT NOT NULL, \
                                     AGE INT NOT NULL, \
                                     ADDRESS CHAR(50), \
                                     SALARY REAL);";
    
      char* error_msg = NULL;
      // 如果失败会动态分配一个error_msg,注意要手动free。
      rc = sqlite3_exec(db, kCreateTableSql, NULL, NULL, &error_msg);
      if (rc == SQLITE_OK) {
        std::cout << "Create Table COMPANY successfully!" << std::endl;
      } else {
        std::cout << "Create Table COMPANY failed!" << std::endl;
        std::cout << "Error msg: " << error_msg << std::endl;
        sqlite3_free(error_msg);
      }
    
    
      sqlite3_close(db);
    }
    
    static std::string InitInsertSql(const std::string& id,
                                     const std::string& name,
                                     const std::string& age,
                                     const std::string& address,
                                     const std::string& salary) {
      std::string sql = "INSERT INTO COMPANY(ID, NAME, AGE, ADDRESS, SALARY) VALUES(";
      sql += id + ", '" + name + "', " + age + ", '" + address + "', " + salary + ");";
      return sql.c_str();
    }
    
    static void InsertData() {
      sqlite3* db = NULL;
    
      int rc = sqlite3_open(kDatabaseName, &db);
      if (rc != SQLITE_OK) {
        std::cout << "Open " << kDatabaseName << " failed! Error msg: " << sqlite3_errmsg(db) << std::endl;
      }
    
      std::string insert_sql = InitInsertSql("1", "Zhe", "18", "BeiJing", "1000");
      insert_sql += InitInsertSql("2", "Ma", "19", "ShangHai", "2000");
      insert_sql += InitInsertSql("3", "Miki", "20", "GuangZhou", "3000");
      insert_sql += InitInsertSql("4", "Li", "21", "ShenZhen", "4000");
      insert_sql += InitInsertSql("5", "Zhang", "22", "Wuhan", "5000");
    
      char* error_msg = NULL;
      rc = sqlite3_exec(db, insert_sql.c_str(), NULL, NULL, &error_msg);
      if (rc == SQLITE_OK) {
        std::cout << "Insert successfully!" << std::endl;
      } else {
        std::cout << "Insert failed! Error msg: " << error_msg << std::endl;
        sqlite3_free(error_msg);
      }
    
      sqlite3_close(db);
    }
    
    //typedef int(*sqlite3_callback)(
    //  void*,    /* Data provided in the 4th argument of sqlite3_exec() */
    //  int,      /* The number of columns in row */
    //  char**,   /* An array of strings representing fields in the row */
    //  char**    /* An array of strings representing column names */
    //  );
    //  SQLite 将为 SQL 参数内执行的每个 SELECT 语句中处理的每个记录调用这个回调函数。
    static int SelectCallback(void* data, int col_count, char** col_values, char** col_names) {
      for (int i = 0; i < col_count; ++i) {
        if (col_values[i] == NULL) {
          continue;
        }
    
        std::cout << col_names[i] << " = " << col_values[i] << std::endl;
      }
    
      const char* seperator = reinterpret_cast<const char*>(data);
      std::cout << seperator << std::endl;
    
      return 0;
    }
    
    static void SelectData() {
      sqlite3* db = NULL;
    
      int rc = sqlite3_open(kDatabaseName, &db);
      if (rc != SQLITE_OK) {
        std::cout << "Open " << kDatabaseName << " failed! Error msg: " << sqlite3_errmsg(db) << std::endl;
      }
    
      const char* kSelectSql = "SELECT * FROM COMPANY;";
      const char* kSeperator = "================================";
      char* error_msg = NULL;
    
      rc = sqlite3_exec(db, kSelectSql, SelectCallback, (void*)kSeperator, &error_msg);
      if (rc == SQLITE_OK) {
        std::cout << "Select successfully!" << std::endl;
      } else {
        std::cout << "Select failed! Error msg: " << error_msg << std::endl;
        sqlite3_free(error_msg);
      }
    
      sqlite3_close(db);
    }
    
    // sqlite3_exec可以执行多条语句，回调函数用于其中的select语句。
    static void UpdateData() {
      sqlite3* db = NULL;
    
      int rc = sqlite3_open(kDatabaseName, &db);
      if (rc != SQLITE_OK) {
        std::cout << "Open " << kDatabaseName << " failed! Error msg: " << sqlite3_errmsg(db) << std::endl;
      }
    
      const char* kSelectSql = "UPDATE COMPANY SET SALARY = 9999 WHERE ID = 1; SELECT * FROM COMPANY;";
      const char* kSeperator = "================================";
      char* error_msg = NULL;
    
      rc = sqlite3_exec(db, kSelectSql, SelectCallback, (void*)kSeperator, &error_msg);
      if (rc == SQLITE_OK) {
        std::cout << "Update successfully!" << std::endl;
      } else {
        std::cout << "Update failed! Error msg: " << error_msg << std::endl;
        sqlite3_free(error_msg);
      }
    
      sqlite3_close(db);
    }
    
    static void DeleteData() {
      sqlite3* db = NULL;
    
      int rc = sqlite3_open(kDatabaseName, &db);
      if (rc != SQLITE_OK) {
        std::cout << "Open " << kDatabaseName << " failed! Error msg: " << sqlite3_errmsg(db) << std::endl;
      }
    
      const char* kSelectSql = "DELETE FROM COMPANY WHERE ID = 4; SELECT * FROM COMPANY;";
      const char* kSeperator = "================================";
      char* error_msg = NULL;
    
      rc = sqlite3_exec(db, kSelectSql, SelectCallback, (void*)kSeperator, &error_msg);
      if (rc == SQLITE_OK) {
        std::cout << "Delete successfully!" << std::endl;
      } else {
        std::cout << "Delete failed! Error msg: " << error_msg << std::endl;
        sqlite3_free(error_msg);
      }
    
      sqlite3_close(db);
    }
    
    // The statement sqlite3_step is evaluated up to the point where the first row of results are available.
    // To advance to the second row of results, invoke sqlite3_step() again.
    // 1.如果SQL语句执行成功或者正常将返回SQLITE_DONE, 否则将返回错误代码。
    // 2.如果SQL返回了一个单行结果集，sqlite3_step() 函数将返回 SQLITE_ROW,然后继续调用sqlite3_step来得到下一个结果。
    // 3.如果不能打开数据库文件则会返回 SQLITE_BUSY。
    static void StepExecute() {
      sqlite3* db = NULL;
    
      int rc = sqlite3_open(kDatabaseName, &db);
      if (rc != SQLITE_OK) {
        std::cout << "Open " << kDatabaseName << " failed! Error msg: " << sqlite3_errmsg(db) << std::endl;
      }
    
      const char* kSelectSql = "SELECT * FROM COMPANY WHERE ID = ? OR ID = ?;";
    
      sqlite3_stmt* stmt = NULL;
      // sqlite3_prepare2 接口把一条SQL语句编译成字节码留给后面的执行函数。
      rc = sqlite3_prepare_v2(db, kSelectSql, strlen(kSelectSql), &stmt, NULL);
      if (rc != SQLITE_OK) {
        std::cout << "Failed to prepare sql!" << std::endl;
        sqlite3_finalize(stmt);
        return;
      }
    
      // sqlite3_bind 所包含的全部接口，它们是用来给SQL声明中的通配符赋值的。
      sqlite3_bind_int(stmt, 1, 2);
      sqlite3_bind_int(stmt, 2, 3);
    
      do {
        rc = sqlite3_step(stmt);
        if (rc == SQLITE_ERROR) {
          std::cout << "Failed to step execute!" << std::endl;
          break;
        }
    
        if (rc == SQLITE_DONE) {
          std::cout << "Finished to step execute!" << std::endl;
          break;
        }
    
        int column_count = sqlite3_column_count(stmt);
        for (int i = 0; i < column_count; ++i) {
          const unsigned char* column_value = sqlite3_column_text(stmt, i);
          if (column_value == NULL) {
            continue;
          }
    
          std::cout << sqlite3_column_name(stmt, i) << " = " << column_value << std::endl;
        }
    
        std::cout << "=====================================" << std::endl;
      } while (rc == SQLITE_ROW);
    
      sqlite3_finalize(stmt);
      sqlite3_close(db);
    }