C++实现Sqlite3增删改查

#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);}