linux中对SQLite3函数的封装

个人对SQLite3数据库函数增删改查的封装

/*version 1.0*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <stdbool.h>
#define PATH  "water.db" //可以根据具体情况更改数据库地址


char *zErrMsg = NULL;
const char *Error = NULL;
sqlite3_stmt *stmt = NULL;


//打开数据库
sqlite3* OpenDB()
{
    sqlite3* db;
    int result = sqlite3_open(PATH,&db);
    if(result!=0)
    {  
        printf("Open DataBase Fail ,Result Code:%d \n",result);
        return 0;
    } 
    else
      printf("Open DataBase Sucess\n");
    
   return db;


}




//关闭数据库
int CloseDB(sqlite3* db)
{
  
   int result =sqlite3_close(db);


   if(result!=0)
       printf("Close DataBase fail,ResultCode:%d \n",result);
    else
     printf("Close DataBase Success\n");
   return result;
}






//Insert操作二进制数据
void InsertOperation(sqlite3 *db,int id,char *name)
{
   
   char str_Insert[4096];
   sprintf(str_Insert,"Insert Into Student ( [studentID] ,[studentName],[HeadImage]) values(%d,'%s',?)",id,name);//SQL语句
   int rc =0;
   
    rc =sqlite3_prepare(db,str_Insert,-1,&stmt,&Error);
    if(rc!=SQLITE_OK)
        {
          printf("准备执行插入语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
          return;
        }
    char _headImage[3] = {0x41,0x42,0x43};
    
    sqlite3_bind_blob(stmt,1,&_headImage,3,NULL);//二进制数据 //进行数据绑定
    rc =sqlite3_step(stmt);
    if(rc!=SQLITE_DONE)
      {
      printf("插入失败！Result Code:%d  ErrorMessage:%s",rc,Error);
      return;
      }
     printf("插入成功!\n");
   
     sqlite3_finalize(stmt);
}


//update操作
void UpdateOperation(sqlite3 *db)
{
   
    char *str_Update="update Student set [HeadImage] = ? where [studentID] = 1";//SQL语句
    int rc =0;
   
        
    rc =sqlite3_prepare(db,str_Update,-1,&stmt,&Error);
    if(rc!=SQLITE_OK)
        {
            printf("准备执行更新语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
           return;
        }
   char _headImage[3] = {0x41,0x42,0x43};
    
   sqlite3_bind_blob(stmt,1,&_headImage,3,NULL);//二进制数据
    rc =sqlite3_step(stmt);
    if(rc!=SQLITE_DONE)
    {
      printf("Update失败！Result Code:%d  ErrorMessage:%s",rc,Error);
      return;
        }
    printf("Update成功!\n");
    sqlite3_finalize(stmt);
   
}




//删除操作
void DeleteOperation(sqlite3 *db,int id)
{
  
    char  str_Update[1024];
    sprintf(str_Update,"Delete From Student where [studentID] =%d",id);//SQL语句
    int rc =0;
   
    rc =sqlite3_prepare(db,str_Update,-1,&stmt,&Error);
    if(rc!=SQLITE_OK)
      {
        printf("准备执行删除语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
       return;
       }
    rc =sqlite3_step(stmt);
    if(rc!=SQLITE_DONE)
    {
      printf("删除失败！Result Code:%d  ErrorMessage:%s",rc,Error);
      return;
       }
    printf("删除成功!\n");
     sqlite3_finalize(stmt);
 
}




void SelectOperation(sqlite3 *db)
{
  
    char * str_Update="select * From Student ";//SQL语句
    int rc =0;
 
    rc =sqlite3_prepare(db,str_Update,-1,&stmt,&Error);
    if(rc!=SQLITE_OK)
    {
        printf("准备执行查询语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
        return;
    }
     while(1)
     {
           if(sqlite3_step(stmt)!=SQLITE_ROW)
           {
               sqlite3_finalize(stmt);
               break;
           }
           int studentid =sqlite3_column_int(stmt,0);
           const unsigned char * studentName = sqlite3_column_text(stmt,1);
           const void * image =sqlite3_column_blob(stmt,2);
           printf("studentID is %d ,Name is %s,HeadImage is %s \n",studentid,studentName,(char *)image);
      }
}




int main()
{
    sqlite3* db = NULL;
    if( db = OpenDB(db))
    {
    
    InsertOperation(db,12,"xiaowan");
    UpdateOperation(db);
    DeleteOperation(db,1);
    SelectOperation(db);
    CloseDB(db);
   
    }
    return 0;
}