Sqlite的操作(增加,删除,查询,修改)
int OpenDB(sqlite3** db) |
{ |
int result = sqlite3_open(DBNAME,db); |
#ifdef DEBUG |
if(result!=0) |
{ |
printf("Open DataBase Fail ,Result Code:%d \n",result); |
} |
else |
{ |
printf("Open DataBase Sucess\n"); |
} |
#endif |
return result; |
} |
int CloseDB(sqlite3* db) |
{ |
int result = sqlite3_close(db); |
#ifdef DEBUG |
if(result!=0) |
{ |
printf("Close DataBase fail,ResultCode:%d \n",result); |
} |
else |
{ |
printf("Close DataBase Success\n"); |
} |
#endif |
return result; |
} |
创建表:
//创建表操作 |
void CreateTable() |
{ |
sqlite3 *db; |
sqlite3_stmt *stmt = NULL; |
char * ErrMessage = NULL; |
const char * Error = NULL; |
char * str_CreateTable="Create Table Student ( studentID INTEGER,studentName Text,HeadImage Blob)"; //创建表的SQL语句 |
int rc =0; |
if(OpenDB(&db)!=SQLITE_OK) |
{ |
return; |
} |
rc = sqlite3_exec(db,str_CreateTable,0,0,&ErrMessage); //执行 |
if(rc!=SQLITE_OK) |
{ |
printf("操作数据库失败!ErrorMessge:%s\n",ErrMessage); |
return; |
} |
sqlite3_finalize(stmt); |
CloseDB(db); |
} |
Insert操作 |
//Insert操作 |
void InsertOperation(void) |
{ |
sqlite3 *db; |
sqlite3_stmt *stmt = NULL; |
char * ErrMessage = NULL; |
const char * Error = NULL; |
char * str_Insert="Insert Into Student ( [studentID] ,[studentName],[HeadImage]) values(1,'Alex',?)"; //SQL语句 |
int rc =0; |
if(OpenDB(&db)!=SQLITE_OK) |
{ |
return; |
} |
|
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"); |
CloseDB(db); |
} |
Update操作:
//Insert操作 |
void InsertOperation(void) |
{ |
sqlite3 *db; |
sqlite3_stmt *stmt = NULL; |
char * ErrMessage = NULL; |
const char * Error = NULL; |
char * str_Insert="Insert Into Student ( [studentID] ,[studentName],[HeadImage]) values(1,'Alex',?)"; //SQL语句 |
int rc =0; |
if(OpenDB(&db)!=SQLITE_OK) |
{ |
return; |
} |
|
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"); |
CloseDB(db); |
} |
Delete操作:
void DeleteOperation(void) |
{ |
sqlite3 *db; |
sqlite3_stmt *stmt = NULL; |
char * ErrMessage = NULL; |
const char * Error = NULL; |
char * str_Update="Delete From Student where [studentID] =1"; //SQL语句 |
int rc =0; |
if(OpenDB(&db)!=SQLITE_OK) |
{ |
return; |
} |
|
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"); |
CloseDB(db); |
} |
Select操作: |
void SelectOperation(void) |
{ |
sqlite3 *db; |
sqlite3_stmt *stmt = NULL; |
char * ErrMessage = NULL; |
const char * Error = NULL; |
char * str_Update="select * From Student "; //SQL语句 |
int rc =0; |
if(OpenDB(&db)!=SQLITE_OK) |
{ |
return; |
} |
|
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 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,image); |
} |
CloseDB(db); |
} |
