iOS开发~FMDB的使用

//获取沙盒路径
NSArray *filePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentPath = [filePath objectAtIndex:0];
NSLog(@"filePath:%@",filePath);
NSString *dbFilePath = [documentPath stringByAppendingPathComponent:@"db.sqlite"];

//建立数据库
FMDatabase *database = [FMDatabase databaseWithPath:dbFilePath];

//建表
//判断数据库是否已经打开，如果没有打开，提示失败
if (![self.database open]) {
    NSLog(@"数据库打开失败");
    return;
}
//为数据库设置缓存，提高查询效率
[self.database setShouldCacheStatements:YES];
//判断数据库中是否已经存在这个表，如果不存在则创建该表
BOOL result=[self.database executeUpdate:@"CREATE TABLE IF NOT EXISTS condition (condition_id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL);"];
if (result) {
    NSLog(@"创表成功");
} else {
    NSLog(@"创表失败");
}
[self getDataBaseArray];

[self.database close];


//添加
if ([self.database open]) {
    NSString *insertSql1= [NSString stringWithFormat:@"INSERT INTO 'condition' ('condition_id','name') VALUES ('1','%@')", @"交通"];
    BOOL res = [self.database executeUpdate:insertSql1];
    
    NSString *insertSql2= [NSString stringWithFormat:@"INSERT INTO 'condition' ('condition_id','name') VALUES ('2','%@')", @"教育"];
    [self.database executeUpdate:insertSql2];
    
    if (!res) {
        NSLog(@"error when insert db table");
    } else {
        NSLog(@"success to insert db table");
    }
    [self.dataBaseArray addObject:@"交通"];
    [self.dataBaseArray addObject:@"教育"];
    [self.database close];
    
}


//查询
if ([self.database open]) {
    NSString * sql = [NSString stringWithFormat:
                      @"SELECT * FROM condition"];
    FMResultSet * rs = [self.database executeQuery:sql];
    NSLog(@"rs:%@",rs);
    while ([rs next]) {
        int Id = [rs intForColumn:@"condition_id"];
        NSString * name = [rs stringForColumn:@"name"];
        NSLog(@"name = %@, id = %d", name, Id);
        [self.dataBaseArray addObject:name];
    }
    [self.database close];
}

//删除
if ([self.database open]) {
    
    NSString *deleteSql = [NSString stringWithFormat:@"delete from condition where condition_id = '%d'", (i + 1)];
    BOOL res = [self.database executeUpdate:deleteSql];
    
    if (!res) {
        NSLog(@"error when delete db table");
    } else {
        NSLog(@"success to delete db table");
    }
    [self.database close];
    
}


//修改
if ([self.database open]) {
    NSString *updateSql = [NSString stringWithFormat:@"UPDATE 'condition' SET 'name' = 'lin' WHERE 'name' = '张三'"];
    BOOL res = [self.database executeUpdate:updateSql];
    if (!res) {
        NSLog(@"error when update db table");
    } else {
        NSLog(@"success to update db table");
    }
    [self.database close];
    
}