sqlite3基本操作的函数封装

本文的sqlite3基本操作（建数据库、建表、插入数据、查询数据、删除数据）的函数封装是基于C语言的。

database.c

#include "database.h"

char            sql[128];
char            *zErrMsg=NULL;
int             nrow=0;
int             ncolumn = 0;


int open_database(sqlite3 **db,char *database_name)
{
        int             len;

        len = sqlite3_open(database_name,db);
        if(len)
        {
                printf("Open database name %s failure.\n",database_name);
                sqlite3_close(*db);
                return -1;
        }
        printf("Open a sqlite3 database name %s successfully!\n",database_name);

        return 0;
}

int create_table(sqlite3 **db,char *table_name,char *table_attribute)
{


        snprintf(sql,sizeof(sql),"CREATE TABLE %s(%s);",table_name,table_attribute);
        //log_info("sql=%s\n",sql);

        //sql="CREATE TABLE test(TEST CHAR(100));";

        if(sqlite3_exec(*db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK)
        {
                printf("Table %s already exist\n",table_name);
        }
        else
        {
                printf("Create table %s successfully\n",table_name);
        }

}

int insert_data(sqlite3 **db,char *table_name,char *attr,char *msg)
{
        snprintf(sql,sizeof(sql),"INSERT INTO %s(%s) VALUES('%s');",table_name,attr,msg);   //插入数据

        if(sqlite3_exec(*db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK)
        {
                sqlite3_close(*db);
                printf("Insert %s to table %s failure:%s\n",msg,table_name,strerror(errno));
                return -1;
        }
        printf("Insert %s to table %s successfully\n",msg,table_name);

        return 0;
}


int query_data(sqlite3 **db,char ***azResult,char *table_name)
{
        snprintf(sql,sizeof(sql),"select *from %s;",table_name);
        //sql="select *from test";

        if(sqlite3_get_table(*db,sql,azResult,&nrow,&ncolumn,&zErrMsg)!=SQLITE_OK)
        {
                sqlite3_close(*db);
                printf("Select *from %s failure\n",table_name);
                return -1;
        }
        printf("There are %d pieces of data in table %s\n",nrow,table_name);

        return nrow;
}

int delete_data(sqlite3 **db,char *table_name)
{
        snprintf(sql,sizeof(sql),"delete from %s;",table_name);
        //sql="delete from test";

        if(sqlite3_exec(*db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK)
        {
                sqlite3_close(*db);
                printf("Delete from %s failure\n",table_name);
                return -1;
        }
        printf("Delete data from table %s successfully!\n",table_name);

        return 0;
}

database.h

#ifndef _DATABASE_H_
#define _DATABASE_H_

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <stdlib.h>
#include <errno.h>
#include <libgen.h>
#include <sqlite3.h>

extern int open_database(sqlite3 **db,char *database_name);
extern int create_table(sqlite3 **db,char *table_name,char *table_attribute);
extern int insert_data(sqlite3 **db,char *table_name,char *attr,char *msg);
extern int query_data(sqlite3 **db,char ***azResult,char *table_name);
extern int delete_data(sqlite3 **db,char *table_name);

#endif

test_database.c

#include "database.h"


int main(void)
{
        sqlite3         *db;
        char            **azResult=NULL;

        if( open_database(&db,"test.db")<0 )
                return -1;

        if( create_table(&db,"test","TEST CHAR(100)")<0 )
                return -2;

        if( insert_data(&db,"test","TEST","Test nihao")<0 )
                return -3;

        if( query_data(&db,&azResult,"test")<0 )
                return -4;

        if( delete_data(&db,"test")<0 )
                return -5;

        if( query_data(&db,&azResult,"test")<0 )
                return -6;

        return 0;
}

结果：