[sqlite3]_[初级]_[常用操作]

场景

1.sqlite3在很多小型项目都会用到, 比如用来存储Android的联系人，短信等. 这个小巧的sqlite3是文件型的数据库, 也方便做配置文件和程序打包. 使用sql查询也方面. 所以sqlite3在非结构化数据时比xml来的方便, 高效,省空间.

2.在移动开发盛行的年代, sqlite3是必备的技术了.

说明

1.以下是sqlite3常见的增删改查操作和一些注意事项.

2.比json,xml文件更小更容易查询, 可以使用软件SqliteBrowser来管理DB. 麻烦就是不能使用文本编辑器直接查看. 建议用于比较多的设置选项保存.

3.sqlite3是开源的数据库, 代码质量很高, 阅读源码也可以从中学到很多东西. 用C写代码的话有个缺点就是在增量打开资源时, 每次如果if条件不成立也需要按顺序释放. 可以使用C++的特性 std::shared_ptr 解决.

例子

#include "gtest/gtest.h"#include <Windows.h>#include <memory>#include "sqlite3.h"using namespace std;static int PCallback(void* data,int argc,char** argv,char** err){    auto count = (int*)data;    *count = atoi(argv[0]);    return 0;}bool TestSqlite3Api(){    sqlite3* db = NULL;    sqlite3_stmt* query = NULL;    int ret = 0;    // 创建DB文件    // 如果不需要创建db文件,使用sqlite3_open即可    ret = sqlite3_open_v2("test.db",&db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);    if (SQLITE_OK != ret)        return false;    std::shared_ptr<sqlite3> sp_db(db,[](sqlite3* db)    {         std::cout << "sqlite3_close" << std::endl;        sqlite3_close(db);    });    // 创建表    std::string create = "CREATE TABLE IF NOT EXISTS items (userid INTEGER PRIMARY KEY, ipaddr"    " TEXT,username TEXT,useradd TEXT,userphone INTEGER,age INTEGER, "                                                    "time TEXT NOT NULL DEFAULT"                                                                              " (NOW()));";    // 注意,如果使用了sqlite3_stmt不关闭的话调用sqlite3_close是返回 SQLITE_BUSY 失败的.    sqlite3_stmt *create_stmt = NULL;    ret = sqlite3_prepare(db, create.c_str(), create.size(), &create_stmt, NULL);    if(SQLITE_OK != ret)        return false;    std::shared_ptr<sqlite3_stmt> create_sp(create_stmt,[](sqlite3_stmt* stmt)    {        std::cout << "sqlite3_finalize create_stmt" << std::endl;        sqlite3_finalize(stmt);     });    if (sqlite3_step(create_stmt) != SQLITE_DONE)         return false;    // 插入表数据    string insert = "INSERT INTO items (time, ipaddr,username,useradd,userphone,age) "        "VALUES ('7:30', '192.187.27.55','vivekanand','kolkatta','04456823948',74);"; // WORKS!    sqlite3_stmt *insert_stmt = NULL;    ret = sqlite3_prepare(db, insert.c_str(), insert.size(), &insert_stmt, NULL);    if(SQLITE_OK != ret)        return false;    std::shared_ptr<sqlite3_stmt> insert_sp(insert_stmt,[](sqlite3_stmt* stmt)    {        std::cout << "sqlite3_finalize insert_stmt" << std::endl;        sqlite3_finalize(stmt);     });    ret = sqlite3_step(insert_stmt);    if (ret != SQLITE_DONE)         return false;    // 查询表数据    string select = "select count(0) from items;";    sqlite3_stmt *select_stmt = NULL;    ret = sqlite3_prepare(db, select.c_str(), select.size(), &select_stmt, NULL);    if(SQLITE_OK != ret)        return false;    std::shared_ptr<sqlite3_stmt> select_sp(select_stmt,[](sqlite3_stmt* stmt)    {        std::cout << "sqlite3_finalize selectStmt" << std::endl;        sqlite3_finalize(stmt);     });    //1. 查询方式1    ret = sqlite3_step(select_stmt);    int count = 0;    while(ret == SQLITE_ROW )     {         count+= sqlite3_column_int(select_stmt,0);        break;    }    std::cout << "frist select count: " << count << std::endl;    //2. 查询方式2    count = 0;    ret = sqlite3_exec(db,"select count(0) from items;",PCallback,&count,NULL);    if(ret != SQLITE_OK)        return false;    std::cout << "second select count: " << count << std::endl;    return true;}TEST(test_sqlite3,TestSqlite3){    ASSERT_TRUE(TestSqlite3Api(),SQLITE_OK);}

输出

Note: Google Test filter = test_sqlite3.TestSqlite3[==========] Running 1 test from 1 test case.[----------] Global test environment set-up.[----------] 1 test from test_sqlite3[ RUN      ] test_sqlite3.TestSqlite3frist select count: 1second select count: 1sqlite3_finalize selectStmtsqlite3_finalize insert_stmtsqlite3_finalize create_stmtsqlite3_close[       OK ] test_sqlite3.TestSqlite3 (296 ms)[----------] 1 test from test_sqlite3 (312 ms total)[----------] Global test environment tear-down[==========] 1 test from 1 test case ran. (328 ms total)

参考

SQLite C/C++ Tutorial

C-language Interface Specification for SQLite

An Introduction To The SQLite C/C++ Interface

quickstart