一般的app中,都会有本地数据库,来存储一些数据或者缓存。虽然我们项目中也用到了数据库,但是以前都没怎么研究,昨天专门研究了一下这一块儿。在iOS中,数据库的调用我用的是FMDB。FMDB可以在gitHub上下载:https://github.com/ccgus/fmdb。下面我们来看看怎么使用FMDB。
1. 添加必要的文件
将下载下来的项目解压,然后将src文件夹下的除了fmdb.m文件之外的所有文件都添加到自己的项目工程中。
然后再在项目中添加libsqlite3.dylib。如图
2. 编写必要的代码
在这里我新建了一个FMDBManager单例。用来操作FMDB。下面我们来看看怎么操作
①.初始化Database
#define CREATE_TABLE_Student @"CREATE TABLE IF NOT EXISTS student(id integer primary key autoincrement , name TEXT(100) , number INTEGER , sex INTEGER , score INTEGER)"
- (BOOL)createDB
{
// 根据path获取db。一定要retain。否则会自动释放
_db = [[FMDatabase databaseWithPath:[self dbPath]] retain];
// 打开db
if ([_db open])
{
// 新建Table
if ([_db executeUpdate:CREATE_TABLE_Student])
{
NSLog(@"create table successed");
return YES;
}
{
NSLog(@"create table faild");
}
}else
{
NSLog(@"db open faild");
}
return NO;
}
//获取db的路径
- (NSString*)dbPath
{
NSArray* ary = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString* documentDic = [ary objectAtIndex:0];
NSString* dbPath = [documentDic stringByAppendingString:@"/database.db"];
return dbPath;
}
②. 创建单例
+ (FMDBManager*)shareInstance
{
static FMDBManager* manager = nil;
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
manager = [[FMDBManager alloc] initWithDB];
});
return manager;
}
- (id)initWithDB
{
if (self = [super init])
{
[self createDB];
}
return self;
}
③. 使用DataBase
- (BOOL)saveStudentInDB:(Student*)stu
{
NSString* sql = @"INSERT INTO student(name, number, sex, score) VALUES(?,?,?,?)";
if ([_db executeUpdate:sql,
stu.name,
[NSNumber numberWithInteger:stu.number],
[NSNumber numberWithBool:stu.sex],
[NSNumber numberWithInteger:stu.score]])
{
NSLog(@"save stu successed");
return YES;
}else
{
NSLog(@"save stu faild");
return NO;
}
}
- (NSMutableArray*)getStudentFromDBByName:(NSString*)name
{
NSMutableArray* ary = [NSMutableArray array];
NSString* sql = @"SELECT * FROM student WHERE name = ?";
FMResultSet* result = [_db executeQuery:sql,name];
while ([result next])
{
Student* stu = [Student studentWithResult:result];
[ary addObject:stu];
}
return ary;
}
- (NSMutableArray*)getStudentFromDBByNumber:(NSInteger)number
{
NSMutableArray* ary = [NSMutableArray array];
NSString* sql = [NSString stringWithFormat:@"SELECT * FROM student WHERE number = %d",number];
FMResultSet* result = [_db executeQuery:sql];
while ([result next])
{
Student* stu = [Student studentWithResult:result];
[ary addObject:stu];
}
return ary;
}
- (NSMutableArray*)getStudentFromDBByScoreAboveNumber:(NSInteger)number
{
NSMutableArray* ary = [NSMutableArray array];
NSString* sql = [NSString stringWithFormat:@"SELECT * FROM student WHERE score > %d",number];
FMResultSet* result = [_db executeQuery:sql];
while ([result next])
{
Student* stu = [Student studentWithResult:result];
[ary addObject:stu];
}
return ary;
}
- (NSMutableArray*)getStudentFromDBByScoreUnderNumber:(NSInteger)number
{
NSMutableArray* ary = [NSMutableArray array];
NSString* sql = [NSString stringWithFormat:@"SELECT * FROM student WHERE score < ?"];
FMResultSet* result = [_db executeQuery:sql,[NSNumber numberWithInteger:number]];
while ([result next])
{
Student* stu = [Student studentWithResult:result];
[ary addObject:stu];
}
return ary;
}
- (NSMutableArray*)getAllStudentFromDB
{
NSMutableArray* ary = [NSMutableArray array];
NSString* sql = @"SELECT * FROM student";
FMResultSet* result = [_db executeQuery:sql];
while ([result next]) {
Student* stu = [Student studentWithResult:result];
[ary addObject:stu];
}
return ary;
}
- (BOOL)deleteStudentInDBByNumber:(NSInteger)number
{
NSString* sql = @"DELETE FROM student WHERE number = ?";
if ([_db executeUpdate:sql,[NSNumber numberWithInteger:number]])
{
NSLog(@"delete successed");
return YES;
}else
{
NSLog(@"delete faild");
return NO;
}
}
具体demo见:点击打开链接http://download.csdn.net/detail/u011394507/7009999