sqlite3的使用代码
以创建MyObject类对应的myobject表为例
#import <Foundation/Foundation.h> #import <sqlite3.h> #import "MyObject.h" @interface DBCommon : NSObject{ id theDelegate; //声明数据库 sqlite3 *database; //是否第一次创建该数据库 BOOL firstCreate; } //打开数据库 -(BOOL)open; //创建数据表MyObject -(BOOL)createTableMyObject:(sqlite3 *)db; //删除数据表MyObject -(BOOL)dropTableMyObject:(sqlite3*)db; //插入数据到MyObject -(BOOL)insertMyObject:(MyObject *)myObject; //更新数据到MyObject -(BOOL)updateMyObject:(MyObject *)myObject; //删除MyObject数据 -(BOOL)deleteMyObject:(NSInteger)ID; //删除所有MyObject数据 -(BOOL)deleteAllMyObjects; //关闭数据库 -(BOOL)close; //查询所有MyObject数据 -(NSArray*)queryAllMyObjects; //根据ID查询MyObject -(MyObject *)queryMyObjectById:(NSInteger)ID; @end
#import "DBCommon.h" @implementation DBCommon -(BOOL)open{ //获取sqlite3数据库文件路径 NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentDirectory = [paths objectAtIndex:0]; NSString *path = [documentDirectory stringByAppendingPathComponent:@"database.sqlite"]; NSFileManager *fileManager = [NSFileManager defaultManager]; BOOL find = [fileManager fileExistsAtPath:path]; //判断文件是否存在 if(find){ //打开数据库、返回操作是否正确 if(sqlite3_open([path UTF8String], &database) != SQLITE_OK){ sqlite3_close(database); NSLog(@"SQLCommon:打开数据库错误"); return NO; } return YES; } if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) { firstCreate = YES; //调用创建数据库方法 [self createTableMyObject:database]; return YES; }else{ sqlite3_close(database); NSLog(@"SQLCommon:打开数据库错误"); return NO; } } -(BOOL)createTableMyObject:(sqlite3 *)db{ //声明SQL语句 char *sql = "create table myobject(id integer primary key autoincrement,\ name text)"; sqlite3_stmt *statement; //sqlite3_prepare_v2参数：数据库、SQL语句、长度小于0时自动计算长度、解析后存放的数据内容 if(sqlite3_prepare_v2(database, sql, -1, &statement, nil) != SQLITE_OK){ NSLog(@"SQLCommon:准备创建数据表myobject错误"); return NO; } //把SQL语句写入解析的结构体中 int success = sqlite3_step(statement); //析构结构体 sqlite3_finalize(statement); if(success != SQLITE_DONE){ NSLog(@"SQLCommon:创建数据表myobject错误"); return NO; } NSLog(@"SQLCommon:创建数据表myobject成功"); return YES; } -(BOOL)dropTableMyObject:(sqlite3*)db{ char *sql = "drop table myobject"; sqlite3_stmt *statement; if(sqlite3_prepare_v2(database, sql, -1, &statement, nil) != SQLITE_OK){ NSLog(@"SQLCommon:准备删除数据表myobject错误"); return NO; } int success = sqlite3_step(statement); sqlite3_finalize(statement); if(success != SQLITE_DONE){ NSLog(@"SQLCommon:删除数据表myobject错误"); return NO; }else{ NSLog(@"SQLCommon:删除数据表myobject成功"); return YES; } } -(BOOL)insertMyObject:(MyObject *)myobject{ sqlite3_stmt *statement; static char *sql = "insert or replace into myobject(name) values(?)"; int success = sqlite3_prepare_v2(database, sql, -1, &statement, nil); if(success != SQLITE_OK){ NSLog(@"SQLCommon:插入myobject数据失败"); return NO; } sqlite3_bind_text(statement, 1, [myobject.name UTF8String],-1,SQLITE_TRANSIENT); success = sqlite3_step(statement); sqlite3_finalize(statement); if(success == SQLITE_ERROR){ NSLog(@"SQLCommon:插入myobject数据失败"); return NO; } NSLog(@"插入myobject数据成功"); return YES; } -(BOOL)updateMyObject:(MyObject *)myobject{ sqlite3_stmt *statement; static char *sql = "update myobject set name=? where id=?"; int success = sqlite3_prepare_v2(database, sql, -1, &statement, nil); if(success != SQLITE_OK){ NSLog(@"SQLCommon:修改myobject数据失败"); return NO; } sqlite3_bind_text(statement, 1, [myobject.name UTF8String],-1,SQLITE_TRANSIENT); sqlite3_bind_int(statement, 2, myobject.id); success = sqlite3_step(statement); sqlite3_finalize(statement); if(success == SQLITE_ERROR){ NSLog(@"SQLCommon:修改myobject数据失败"); return NO; } NSLog(@"修改myobject数据成功"); return YES; } -(BOOL)deleteMyObject:(NSInteger)ID{ sqlite3_stmt *statement; static char *sql = "delete from myobject where id=?"; int success = sqlite3_prepare_v2(database, sql, -1, &statement, nil); if(success != SQLITE_OK){ NSLog(@"SQLCommon:准备删除myobject数据失败"); return NO; } sqlite3_bind_int(statement, 1, ID); success = sqlite3_step(statement); sqlite3_finalize(statement); if(success == SQLITE_ERROR){ NSLog(@"SQLCommon:删除myobject数据失败"); return NO; } NSLog(@"删除myobject数据成功"); return YES; } -(BOOL)deleteAllMyObjects{ sqlite3_stmt *statement; static char *sql = "delete from myobject"; int success = sqlite3_prepare_v2(database, sql, -1, &statement, nil); if(success != SQLITE_OK){ NSLog(@"SQLCommon:准备删除myobject所有数据失败"); return NO; } success = sqlite3_step(statement); sqlite3_finalize(statement); if(success == SQLITE_ERROR){ NSLog(@"SQLCommon:删除myobject所有数据失败"); return NO; } NSLog(@"删除myobject所有数据成功"); return YES; } -(BOOL)close{ sqlite3_close(database); return YES; } -(NSArray*)queryAllMyObjects{ NSMutableArray *array = [[NSMutableArray alloc] init]; sqlite3_stmt *statement = nil; char *sql = "select id,name from myobject"; if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK){ NSLog(@"SQLCommon:准备查询myobject错误"); }while (sqlite3_step(statement) == SQLITE_ROW) { int Id = sqlite3_column_int(statement, 0); char *name = (char*)sqlite3_column_text(statement, 1); MyObject *myobject = [[MyObject alloc] init]; myobject.cid = Id; myobject.name = name!=NULL?[NSString stringWithUTF8String:name]:@""; [array addObject:myobject]; } sqlite3_finalize(statement); return array; } -(MyObject *)queryMyObjectById:(NSInteger)ID{ sqlite3_stmt *statement = nil; char *sql = "select id,name from myobject where id=?"; if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK){ NSLog(@"SQLCommon:准备查询myobject错误"); } sqlite3_bind_int(statement, 1, ID); MyObject *myobject; if(sqlite3_step(statement) == SQLITE_ROW){ //int Id = sqlite3_column_int(statement, 0); char *name = (char*)sqlite3_column_text(statement, 1); myobject = [[MyObject alloc] init]; myobject.id = ID; myobject.name = name!=NULL?[NSString stringWithUTF8String:name]:@""; } sqlite3_finalize(statement); return myobject; }
MyObject.h @interface MyObject : NSObject{ NSInteger id; NSString *name;//名称 } @property(nonatomic) NSInteger id; @property(nonatomic,strong) NSString *name; @end
