Revise : Linux sqlite3 轻量级数据库简单接口编程(c++)

最新推荐文章于 2024-04-21 22:42:18 发布

-浓烟下的诗歌电台

最新推荐文章于 2024-04-21 22:42:18 发布

阅读量304

点赞数

分类专栏： Unix/Linux系统编程文章标签： Linux sqlite3

本文链接：https://blog.csdn.net/weixin_43142013/article/details/98100189

版权

Unix/Linux系统编程专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、相关API

//数据库基本操作之：打开关闭与错误处理

//数据库基本操作之：打开关闭与错误处理
int sqlite3_open(const char*, sqlite3**);

int sqlite3_open16(const void*, sqlite3**);

int sqlite3_close(sqlite3*);

const char *sqlite3_errmsg(sqlite3*);

const void *sqlite3_errmsg16(sqlite3*);

int sqlite3_errcode(sqlite3*);

sqlite3_open() 函数返回一个整数错误代码,而不是像第二版中一样返回一个指向sqlite3结构体的指针.

sqlite3_open() 和 sqlite3_open16() 的不同之处在于：sqlite3_open16() 使用UTF-16编码(使用本地主机字节顺序)传递数据库文件名. 如果要创建新数据库, sqlite3_open16() 将内部文本转换为UTF-16编码, 反之sqlite3_open() 将文本转换为UTF-8编码.

打开或者创建数据库的命令会被缓存,直到这个数据库真正被调用的时候才会被执行. 而且允许使用PRAGMA声明来设置如本地文本编码或默认内存页面大小等选项和参数.

sqlite3_errcode() 通常用来获取最近调用的API接口返回的错误代码. sqlite3_errmsg() 则用来得到这些错误代码所对应的文字说明. 这些错误信息将以 UTF-8 的编码返回,并且在下一次调用任何SQLite API函数的时候被清除. sqlite3_errmsg16() 和 sqlite3_errmsg() 大体上相同,除了返回的错误信息将以 UTF-16 本机字节顺序编码.

需要注意的是不管sqlite3_open()执行成功与否都要执行sqlite3_close().

sqlite3_高级API文档，点此下载链接，提取码见文末~

//数据库核心操作所有SQL语句的执行接口

//数据库核心操作：sqlite3_exec();
int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);

sqlite3_exec 函数依然像它在SQLite2中一样承担着很多的工作. 该函数的第二个参数中可以编译和执行零个或多个SQL语句. 查询的结果返回给回调函数.

//另外一种查询方式：

sqlite3_get_table()

//函数原型

int sqlite3_get_table(sqlite3* pDB, const char *sql,char ***pResult, int * rowCount,int *columnCount, char** errMsg);

//函数功能：执行SQL 语句，通过一维数组返回结果；一般用于数据记录查询

//与sqlite_free_table(sqlite3 *pDB);联合使用

void sqlite3_free_table(char **result);

//函数功能：释放查询结果占用的内存；
//输入参数：result,通过函数sqlite3_get_table（）查询到的记录结果
//输出参数：无
//返回值：无

· 注意点 1：

输入参数：pDB,打开的数据库句柄；sql，待执行的SQL 字符串，以’\0’结尾；

输出参数：rowCount，查询出多少条记录（即查出多少行）；columnCount，查询出来的记录有多少个字段（多少列）； errMsg，返回错误信息；pResult，查询结果，是由字符串组成的一维数组（不要以为是二维数组，更不要以为是三维数组）。它的内存布局是：第一行是字段名称，后面紧接着每个字段的值；

返回值：执行成功返回SQLITE_OK，否则返回其他值。

· 注意点 2：

无论使用哪种查询方式，查询一定是最数据库最常用的接口。以及分清主调方为次级指针，当作为传入参数时，主调方可以不加修改地将变量传入被调方。；而当变量(为普通类型/聚合类型)属于作为传出参数时，我们需要用比他高一级的指针，传入被调方--->被调方成功处理完毕，将原内存区数据修改并返回。

//实验代码 1：---使用回调查询

/*****************************************************
copyright (C), 2019-2020, Lighting Studio. Co.,     Ltd. 
File name：
Author：xozofunny    Version:0.1    Date: 
Description：
Funcion List: 
*****************************************************/

#include <iostream>
#include <string>
#include <sqlite3.h>
using namespace std;

#define Err_exit(m)\
    {\
        perror(m);\
        exit(-1);\
    }while(0)

#define SQL 1024

void create_table(sqlite3 *pdb);
void seek_table(sqlite3 *pdb);
static int display_call_back(void *para, int column_count, char **col_val, char **col_name);
void insert_value(sqlite3 *pdb);

int main(void)
{
    sqlite3 *pdb = NULL;
    char *errmsg;
    char *filename = "/mnt/hgfs/Linux_share/JSETC/Sqilte/test.db";
    
    int ret = sqlite3_open(filename, &pdb); 
    if(ret != SQLITE_OK)
    {
        fprintf(stderr, "Error Reason:%s\n", sqlite3_errmsg(pdb));
        Err_exit("sqlite3_open");
    }
    else cout<<"database open success!"<<endl;

    //创建表
    create_table(pdb);
    //插值
    insert_value(pdb);
    //回调查询
    seek_table(pdb);

    ret = sqlite3_close(pdb);
    if(ret != SQLITE_OK)
    {
        fprintf(stderr, "Error Reason:%s\n", sqlite3_errmsg(pdb));
        Err_exit("sqlite3_close");
    }
    else cout<<"database was closed!"<<endl;

    return 0;
}

void create_table(sqlite3 *pdb)
{
    char *errmsg;
    //string sql;
    char *sql = new char[SQL];
    
    strcpy(sql, "create table if not exists student(id int primary key not null,\
           name varchar(8), age int, sex varchar(8), score real, resume text);");
    
    int ret = sqlite3_exec(pdb, sql, NULL, NULL, &errmsg);
    if(ret != SQLITE_OK)
    {
        cerr<<"create table fail:"<<errmsg<<endl;
        Err_exit("sqlite3_exec");
    }
    else cout<<"create table success"<<endl;

    delete sql;
}

void insert_value(sqlite3 *pdb)
{
   char *errmsg;
   cout<<"please input correct column>>>:"<<endl;
   int count;cin>>count;
   int *id = new int[count];
   memset(id, 0, sizeof(*id));

   for(int i = 0; i < count; i++)
   {
       char *sql = new char[SQL];
       cout<<"please input id>>>:"<<endl;
       //int id = 0;
       cin>>id[i];
       for(int j = 0; j < count; j++)
       {
           if(id[i] == id[j] && (i != j))
           {
               cout<<"id repeat!,please input again"<<endl;
               cin>>id[i];
           }
       }
       cout<<"please input name>>>:"<<endl;
       char *name = new char[20];
       cin >> name;
       cout<<"please input age>>>:"<<endl;
       int age; cin >> age;
       cout<<"please input sex>>>:"<<endl;
       char *sex = new char[4];
       cin >> sex;
       cout<<"please input score>>>:"<<endl;
       float score; cin >> score;
       cout<<"please input resume>>>:"<<endl;
       char *resume = new char[512];
       cin >> resume;
       
       sprintf(sql, "insert into student values(%d,'%s',%d,'%s',%f,'%s');", id[i], name, age, sex, score, resume);
       
       int ret = sqlite3_exec(pdb, sql, NULL, NULL, &errmsg);
       if(ret != SQLITE_OK)
       {
           cerr<<"insert value fail:"<<errmsg<<endl;
           memset(sql, 0, sizeof(*sql));
           Err_exit("sqlite3_exec");
       }
       else
       {
            memset(sql, 0, sizeof(*sql));
            cout<<"insert success"<<endl;
       }
       delete[] sql; delete[] name;
       delete[] sex; delete[] resume;
   }

    delete[] id;

}

void seek_table(sqlite3 *pdb)
{
    char *errmsg = NULL;
    bool tmp = 1;

    //while(1)
    //{
        char *sql = new char[SQL];
        //cout<<"please input correct sql search language>>>:";
        strcpy(sql, "select * from student;");
        int ret = sqlite3_exec(pdb, sql, display_call_back, (void *)&tmp, &errmsg);
        if(ret != SQLITE_OK)
        {
            cout<<"search error:" << errmsg << endl;
            delete[] sql;
        }
        else delete[] sql;
}

static int display_call_back(void *para, int col_count, char **col_val, char **col_name)
{
    bool *tmp = (bool *)para;

    if(*tmp == true)
    {
        for(int i = 0; i < col_count; i++)
        {
            cout<<col_name[i]<<"\t\t";
        }
        cout<<endl;
        *tmp = false;
    }
    for(int i = 0; i < col_count; i++)
    {
        cout<<col_val[i]<<"\t\t";
    }
    cout<<endl;


    return SQLITE_OK;
}

//编译运行 1：

当用户的输入为0时，且数据库中原本就存在数据，则：

//实验代码 2 ：使用非回调查询

/*****************************************************
copyright (C), 2019-2020, Lighting Studio. Co.,     Ltd. 
File name：
Author：xozofunny    Version:0.1    Date: 
Description：
Funcion List: 
*****************************************************/

#include <iostream>
#include <string>
#include <sqlite3.h>
using namespace std;

#define Err_exit(m)\
    {\
        perror(m);\
        exit(-1);\
    }while(0)

#define SQL 1024

void create_table(sqlite3 *pdb);
void seek_table(sqlite3 *pdb);
static int display_call_back(void *para, int column_count, char **col_val, char **col_name);
void insert_value(sqlite3 *pdb);
void display(sqlite3 *pdb);

int main(void)
{
    sqlite3 *pdb = NULL;
    char *errmsg;
    char *filename = "/mnt/hgfs/Linux_share/JSETC/Sqilte/test.db";
    
    int ret = sqlite3_open(filename, &pdb); 
    if(ret != SQLITE_OK)
    {
        fprintf(stderr, "Error Reason:%s\n", sqlite3_errmsg(pdb));
        sqlite3_close(pdb);
        Err_exit("sqlite3_open");
    }
    else cout<<"database open success!"<<endl;

    //创建表
    create_table(pdb);
    //插值
    insert_value(pdb);
    //回调查询
    cout<<"==================================call_back_search=================================="<<endl;
    seek_table(pdb);
   //非回调查询 
    cout<<"==================================Not_call_back_search=============================="<<endl;
    display(pdb);

    ret = sqlite3_close(pdb);
    if(ret != SQLITE_OK)
    {
        fprintf(stderr, "Error Reason:%s\n", sqlite3_errmsg(pdb));
        sqlite3_close(pdb);
        Err_exit("sqlite3_close");
    }
    else cout<<"database was closed!"<<endl;

    return 0;
}

void create_table(sqlite3 *pdb)
{
    char *errmsg;
    //string sql;
    char *sql = new char[SQL];
    
    strcpy(sql, "create table if not exists student(id int primary key not null,\
           name varchar(8), age int, sex varchar(8), score real, resume text);");
    
    int ret = sqlite3_exec(pdb, sql, NULL, NULL, &errmsg);
    if(ret != SQLITE_OK)
    {
        cerr<<"create table fail:"<<errmsg<<endl;
        sqlite3_close(pdb);
        Err_exit("sqlite3_exec");
    }
    else cout<<"create table success"<<endl;

    delete sql;
}

void insert_value(sqlite3 *pdb)
{
   char *errmsg;
   cout<<"please input correct column>>>:";
   int count;cin>>count;
   int *id = new int[count];
   memset(id, 0, sizeof(*id));

   for(int i = 0; i < count; i++)
   {
       char *sql = new char[SQL];
       cout<<"please input id>>>:";
       //int id = 0;
       cin>>id[i];
       for(int j = 0; j < count; j++)
       {
           if(id[i] == id[j] && (i != j))
           {
               cout<<"id repeat!,please input again"<<endl;
               cin>>id[i];
           }
       }
       cout<<"please input name>>>:";
       char *name = new char[20];
       cin >> name;
       cout<<"please input age>>>:";
       int age; cin >> age;
       cout<<"please input sex>>>:";
       char *sex = new char[4];
       cin >> sex;
       cout<<"please input score>>>:";
       float score; cin >> score;
       cout<<"please input resume>>>:";
       char *resume = new char[512];
       cin >> resume;
       
       sprintf(sql, "insert into student values(%d,'%s',%d,'%s',%f,'%s');", id[i], name, age, sex, score, resume);
       
       int ret = sqlite3_exec(pdb, sql, NULL, NULL, &errmsg);
       if(ret != SQLITE_OK)
       {
           cerr<<"insert value fail:"<<errmsg<<endl;
           memset(sql, 0, sizeof(*sql));
            sqlite3_close(pdb);
           Err_exit("sqlite3_exec");
       }
       else
       {
            memset(sql, 0, sizeof(*sql));
            cout<<"insert success"<<endl;
       }
       delete[] sql; delete[] name;
       delete[] sex; delete[] resume;
   }

    delete[] id;

}

void seek_table(sqlite3 *pdb)
{
    char *errmsg = NULL;
    bool tmp = 1;

    //while(1)
    //{
        char *sql = new char[SQL];
        //cout<<"please input correct sql search language>>>:";
        strcpy(sql, "select * from student;");
        int ret = sqlite3_exec(pdb, sql, display_call_back, (void *)&tmp, &errmsg);
        if(ret != SQLITE_OK)
        {
            cout<<"search error:" << errmsg << endl;
            sqlite3_close(pdb);
            delete[] sql;
        }
        else delete[] sql;
}

static int display_call_back(void *para, int col_count, char **col_val, char **col_name)
{
    bool *tmp = (bool *)para;

    if(*tmp == true)
    {
        for(int i = 0; i < col_count; i++)
        {
            cout<<col_name[i]<<"\t\t";
        }
        cout<<endl;
        *tmp = false;
    }
    for(int i = 0; i < col_count; i++)
    {
        cout<<col_val[i]<<"\t\t";
    }
    cout<<endl;


    return SQLITE_OK;
}

void display(sqlite3 *pdb)
{
    char *errmsg;
    char *sql = new char[SQL];
    char **result;
    int rowc, colc;

    strcpy(sql, "select * from student;");
    
    int ret = sqlite3_get_table(pdb, sql, &result, &rowc, &colc, &errmsg);
    if(ret != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_get_table error:%s\n", errmsg);
        sqlite3_close(pdb);
        Err_exit("get_table");
    }

    for(int i = 0; i < colc; i++)
        cout<<result[i]<<"\t\t";
    cout<<endl;

    for(int i = colc; i < (colc * (rowc + 1)); i++)
    {
        cout<<result[i]<<"\t\t";
        if((i+1)%colc == 0)
            cout<<endl;
    }
    //cout<<endl;

    sqlite3_free_table(result);
    
    delete[] sql;
}

//编译运行 2：