C++之Sqlite3小白入门

发布于 2019-11-28  约 21 分钟

一.sqlite3简介

1.个人浅见

sqlite3(官方文档)是一种轻量级且应用广泛的关系型数据库的一种。sqlite3数据库可运行在类Unix和window系统之上,其中,大部分的类Unix系统(如Linux)都默认安装了sqlite3数据库,个人使用的系统为Ubuntu 16.04,默认情况下系统已经安装了sqlite3数据库,检测方法为:在Terminal中执行sqlite3,即可进入sqlite3的命令编辑模式,若失败请手动编译安装sqlite3数据库。

首先明白几个概念:

1.DML(Data Manipulattion Language),顾名思义--数据操作语言。所谓数据操作,是指对数据的插入(insert),删除(delete),更新(update)等。
2.DQL(Data Query Language)--数据查询语言。DQL的基本结构是由SELECT等查询语句及其子语句组成。
3.DDL(Data Definition Language)--数据定义语言。DDL是对数据库进行创建的语言。比如创建数据库(create database),创建数据表(create table),删除数据表(drop table)等。

2.sqlite3语法

sqlite有其独特的语法规则。

1.大小写的敏感性.在sqlite中,是不区分大小写的,例如创建表语句`CREATE TABLE tablename;`;和`create table tablename;`效果是完全一样的,再比如查询语句'select * from tablename'和'SELECT * FROM tablename'也是完全一样的。
2.所有的sqlite语句可以以任何关键字开始,`select`,`insert`,`create`等,以距离最近的 **“;”** 作为本条语句的结束标志。

3.sqlite的命令

sqlite数据库中有一些特殊的命令,这些特殊的命令都以 .(点)开头,命令结尾不需要“;” 结尾,这些特殊的命令被称为sqlite的点命令。有一个特殊的点命令.help,它可以列出所有sqlite的点命令及其使用说明,如图所示:sqlietehelp.png

sqlite点命令的详细介绍

二.sqlite3之C++接口

sqlite提供原生态的C/C++接口,从其C/C++接口文档可以查看所有的接口及使用方法。简单介绍几个常用的对象和接口:

1.`sqliet3`.数据库对象,类似于数据库的入口。
2.`sqlite3_stmt`.状态存储对象,按照官网解释 “ An instance of this object represents a single SQL statement that has been compiled into binary form and is ready to be evaluated.” 通俗来说,就是它是一个对象实体,该实体所存储的东西是已经**预编译**好的单条SQL语句,这个实体对象可以被后续的sqlite3语句执行。`sqlite3_stmt`对象类似于C/C++语言中**预编译**的结果(在C++编程中,预编译的结果存储在二进制文件中,我们一般看不到)。
3.`sqlite3_open(const char *filename,sqlite3 **ppDb)`接口。该函数用于打开或创建(若`filename`不存在)一个数据库对象,创建的结果保存在`ppDd`所指定的`sqlite3`对象中.
4.`sqlite3_prepare_v2(sqlite3 *db,const char *zSql,int nByte,sqlite3_stmt **ppStmt,const char **pzTail);`接口,该函数主要是对SQL语句进行预编译,并将结果存在参数4指定的对象中。其中参数2`zSal`是代指一条SQL语句的字符串,参数3代指该`zSql`字符串的最大占用字节长度,参数4 `双重指针参数ppStmt`即存储经过sqlite3编译后的SQL语句的结果,最后一个参数指代参数`zSql`字符串的没有使用的地址偏移量,一般置`nullptr`即可。该函数执行成功,返回`int`类型的`SQLITE_OK`,失败返回错误代码。
5.`sqlite3_step(sqlite3_stmt *);`接口,该函数主要是用于执行`sqlite3_prepare()`函数所编译好的`sqlite3_stmt`对象。对与DDL和DML而言,该函数执行成功返回`SQLITE_DONE`,对于DQL而言,若执行成功,则返回`SQLITE_ROW`。
6.`sqlite3_finalize(sqlite3_stmt *pStmt);`接口,主要用于释放`sqlite3_stmt`对象所占用的资源,执行成功过返回`SQLITE_OK;`。
7.`sqlite3_close(sqlite3 *);`接口,该函数主要用于关闭`sqlite3`数据库对象,成功返回`SQLITE_OK`。

以上两个对象(1 和 2)以及5个接口函数(3-7)应该算是SQLite c/c++编程中最重要也是最基础的,几乎每个SQLite C编程都离不开。当然还有一些其他重要的函数,比如sqlite3_bind()族类,sqlite3_column()族类,以及sqlite3_exec()等,这些函数的存在极大方便了sqlite3编程。

三.sqlite3编程示例

在这里创建了一个sqlite操作的类Data,所有对sqlite数据库的操作都作为该类的方法,该程序文件如下:

#include <sqlite3.h>
#include <string.h>
#include <iostream>
#include <string>
using namespace std;
//创建操作数据库的类

class Data
{
public:
    Data(){
        cout<<"执行构造函数"<<endl;    
    };
  
    ~Data(){
        cout<<"执行析构函数"<<endl;
     }
    //1.创建数据库和数据表
    void createData() ;
    //2.插入数据
    void insertData();
    //3.更新数据(更新第id条记录的第n列字段的值)
    void updateData(const int id,const int n );
    //4.显示开启事物,批量插入数据
    void insertBatch() ;
    //5.选择数据(选择特定行数的数据并打印出来)
    void selectData(const int row,const int offset)  ;
    //6.关闭数据库
    void close(){
        sqlite3_close(conn);    
        return ;
    }
    //7.清空数据库表,以便于下次操作测试
    void drop();
private:
    sqlite3 *conn = nullptr;
    int rc;
    int count = 10;
};

void Data::updateData(const int id,const int n){
    const char *field = nullptr;
    const char *update ; 
    char str[64] = {0};
    if(1 == n){
        cout<<"约束主键不能修改"<<endl;
       // field = "ID";   
      //  update = "update test set ID = %d where ID = %d";
      //  sprintf(str,update,n,id);
     }else if(2 == n){
        field = "name";    
        update = "update test set name = '%s' where ID = %d";
        sprintf(str,update,"yxg",id);
     }else{
        field = "width";    
        update = "update test set width = %f where ID = %d";
        sprintf(str,update,99.8,id);
     };
    
    sqlite3_stmt *stmt = nullptr;
    if(sqlite3_prepare_v2(conn,str,strlen(str),&stmt,nullptr) != SQLITE_OK){
        sqlite3_finalize(stmt);
        close();
        return ;
    }
    if(sqlite3_step(stmt)!=SQLITE_DONE){
        sqlite3_finalize(stmt);
        close();
        return;
    }

    sqlite3_finalize(stmt); 
}

void Data::selectData(const int row,const int offset) {
    int r = row,o = offset;
    const char * select1 = "select * from test limit %d,%d ";
    char select[64] = {0};
    sprintf(select,select1,row,offset);
    sqlite3_stmt *stmt = nullptr;
    // const char * select = "select * from test limit 1,1";
    if(sqlite3_prepare_v2(conn,select,strlen(select),&stmt,nullptr) != SQLITE_OK){
        cout<<"编译select语句失败"<<endl;
        close();
        sqlite3_finalize(stmt);
        return ;
    }
    if(sqlite3_step(stmt) == SQLITE_ROW){
        // cout<<"查询好了:"<<endl;
        int fieldcount = sqlite3_column_count(stmt);
        cout<<"该表所含字段数量是:"<<fieldcount<<endl;
    
        for(int i = 0;i < fieldcount; ++i){
            int type = sqlite3_column_type(stmt,i);
            if(type == SQLITE_INTEGER){
                int v = sqlite3_column_int(stmt,i);
                cout<<"ID   is:  "<<v<<endl;    
            }else if(type == SQLITE_TEXT){
                const char *v=(const char *)sqlite3_column_text(stmt,i);
                string s =  v;
                cout<<"Name is:  "<<s<<endl;
            }else if(type == SQLITE_FLOAT){
                int v = sqlite3_column_int(stmt,i);
                cout<<"Age  is:  "<<v<<endl;
            }else{
                cout<<"The result is nullptr!!"<<endl;    
            }   
            
        }        
    }
    sqlite3_finalize(stmt);
            
}

void Data::drop(){
    
    const char *drop = "drop table test";
    sqlite3_stmt *stmt  = nullptr;
    if(sqlite3_prepare_v2(conn,drop,strlen(drop),&stmt,nullptr ) != SQLITE_OK){
        close();
        sqlite3_finalize(stmt);
        return ;
    }
    if(sqlite3_step(stmt) == SQLITE_DONE){
        cout<<"成功销毁数据表"<<endl;
    }
            
    sqlite3_finalize(stmt);
    close();    
        
}

void Data::insertBatch(){
    //开启一个事物
    const char *begin = "begin transaction";
    sqlite3_stmt *stmt = nullptr;
    if(sqlite3_prepare_v2(conn,begin,strlen(begin),&stmt,nullptr)!=SQLITE_OK){
        close();
        cout<<"预编译事物失败!!"<<endl;
        return ;    
    }
    if(sqlite3_step(stmt) != SQLITE_DONE){
        close();
        cout<<"执行事物失败"<<endl;
        return;
    }
    sqlite3_finalize(stmt);

    //基于绑定变量插入数据
    const char *insert = "insert into test values(?,?,?)";
    sqlite3_stmt *stmt2 = nullptr;
    if(sqlite3_prepare_v2(conn,insert,strlen(insert),&stmt2,nullptr) != SQLITE_OK){
        close();
        return ;
    }
   // char *name = "this is name";
    for(int i = 0; i < count; ++i){
        //数据表最左边的索引为1
        sqlite3_bind_int(stmt2,1,i);
        string name = "this is name ";
        name += to_string(i);
        sqlite3_bind_text(stmt2,2,name.c_str(),sizeof(name),SQLITE_TRANSIENT);
        sqlite3_bind_double(stmt2,3,19.1*i);
        if(sqlite3_step(stmt2)!= SQLITE_DONE){
            close();
            sqlite3_finalize(stmt2);
            return ;    
        }
        sqlite3_reset(stmt2);
        cout<<"Insert succeed!"<<endl;
    }
    sqlite3_finalize(stmt2);

    //提交事务
    const char * commit= "commit";
    sqlite3_stmt *stmt3 = nullptr;
    if(sqlite3_prepare_v2(conn,commit,strlen(commit),&stmt3,nullptr)!= SQLITE_OK){
        close();
        sqlite3_finalize(stmt3);
        return;    
    }
    if(sqlite3_step(stmt3)!=SQLITE_DONE){
           
        close();
        sqlite3_finalize(stmt3);
        return;    
     }
     sqlite3_finalize(stmt3);
       
        
}

void Data::createData()  {
    //在当前目录下打开(或创建)test.db数据库。
    rc = sqlite3_open("test.db",&conn);    
    if(rc != SQLITE_OK){
        close();
        cout<<"创建数据库失败!!"<<endl;
        return ;
    }
    //SQL语句
    /*
    const char *createTable = "create table test(" \
        "ID INT PRIMARY KEY NOT NULL,"\
        "name TEXT NOT NULL," \
        "age INT NOT NULL," \
        " );";*/
    const char * createTable = "create table test(ID INT PRIMARY KEY NOT NULL,name TEXT,width REAL)";
    sqlite3_stmt *stmt = nullptr;
    //预编译SQL语句
    if(sqlite3_prepare_v2(conn,createTable,strlen(createTable),&stmt,nullptr) != SQLITE_OK){
        cout<<"预编译失败"<<endl;
        sqlite3_finalize(stmt);
        close();
        return;
    }
    //执行SQL语句
    if(sqlite3_step(stmt) != SQLITE_DONE){
        sqlite3_finalize(stmt);
        cout<<"执行失败"<<endl;
        close();
        return ;
    }
    sqlite3_finalize(stmt);
    cout<<"创建数据库和数据表成功!!"<<endl;
         
}

int main(int argc, char **argv){

    Data *base = new Data;
    base->createData();
    base->insertBatch();
    //打印从第一条记录开始的一条记录(记录索引从0开始)
    base->selectData(1,1);
    base->updateData(1,2);

    base->drop();
    delete base;
    base = nullptr;
    return 0;    
}

从以上简单示例可以看出,sqlite3c++编程基本都要经过三个步骤(在不使用sqlite3_exec()):

1.声明`SQL`语句和`sqlite3_stmt` 对象;
2.调用`sqlite3_prepare()`函数 “预编译”;
3.调用`sqlite3_step()`函数执行`SQL`操作.

在上面几个类方法中,唯一有点特别的是void insertBatch()函数,与其他功能函数的主要区别就是:在该函数中,显示的开启了一个事务,所有的插入操作均在该事务内完成,直到该事务被显示的提交
所谓事务,其实就是一块执行单元,一块代码段,在该执行单元内的sqlite操作,都不会自动的提交到数据库中,直到显示的执行 SQL commit。在sqlite中,所有的操作本质都是“事务性”的,只不过被sqlite隐式的创建并提交,默认情况下,每一个sqlite3函数的调用都伴随着事务的发生。
一般情况下,数据插入类似下面的代码:

void insert(){
    const char *insertSQL= " ..... ";
    sqlite3_stmt 对象;
    for(int i=0;i < count;++i){
        执行预编译 sqlite3_prepare();
        执行插入 sqlite3_step();
        ....
        ....
    }
}

在方法void insertBatch()中,如要插入大批量数据,只需要执行一次 “预编译” 即可,然后基于数据绑定的方式在事务内部执行SQL插入语句,极大节省了sqlite3_prepare_v2()函数的调用次数(通常 函数sqlite3_prepare_v2()比函数sqlite3_step()执行时占用时间更多)。

可以看出,在执行大批量操作时,显示的创建并提交事务,能够优化编码,并提高代码执行效率,并且各个事务之间是隔离状态--不同事务之间相互独立和透明的。

注意:在结束数据库操作的时候,一定记得执行sqlite3_close()函数关闭该库,在执行关闭操作之前,也要确保每一个sqlite3_stmt对象的资源被释放(调用sqlite3_finalize()),否则容易造成内存泄露。

阅读 931发布于 2019-11-28

推荐阅读

专注知识整理,总结和分享

25 人关注
21 篇文章
专栏主页
目录