[iOS开发]FMDB的简单使用

Demo地址：http://download.csdn.net/detail/u012881779/8442261

//
//  DMDBManage.m
//  DMFMDBDemo

#import "DMDBManage.h"
static FMDatabase *db;
@implementation DMDBManage

/*
 *实例化FMDatabase
 */
+(void)instanceDatabase{
    //Document为ios中可读写的文件夹
    NSArray *paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
    NSString *documentDirectory = [paths objectAtIndex:0];
    
    //dbPath:数据库路径
    NSString *dbPath = [documentDirectory stringByAppendingPathComponent:@"DBManage.db"];
    
    //创建数据库实例,如果路径中不存在"DBManage.db"的文件,sqlite会自动创建"DBManage.db"
    db = [FMDatabase databaseWithPath:dbPath] ;
    if (![db open]) {
        
        NSLog(@"Could not open db.");
        return ;
    }else{
    
    }
    //创建表
    [self createTableAction];
}

/*
 *创建表
 *创建一个名为User的表，有两个字段分别为string类型的Name，integer类型的Age
 *[db executeUpdate:@"CREATE TABLE User (Name text,Sex text,Age integer,Native text,Phone integer,Address text)"];
 */
+(void)createTableAction{
    [db executeUpdate:@"CREATE TABLE User (Name text PRIMARY KEY,Sex text,Age integer,Native text,Phone integer,Address text)"];
}

/*
 *删除表和表中信息
 *将表连同表中信息一起删除但是日志文件中会有记录
 *drop table tabname
 */
+(void)dorpTableAction{
    [db executeUpdate:@"drop table User"];
}

/*
 *删除表中信息
 *将表中信息删除但是会保留这个表
 *delete from tabname
 */
+(void)deleteTableAction{
    [db executeUpdate:@"delete from User"];
}

/*
 *增加一个列
 *注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
 *Alter table tabname add colname coltype
 */
+(void)addColnameAction{
    [db executeUpdate:@"Alter table User add ID coltype"];
}

/*
 *插入数据
 *text对应为NSString integer对应为NSNumber的整形
 *[db executeUpdate:@"INSERT INTO User (Name,Sex,Age,Native,Phone,Address) VALUES(?,?,?,?,?,?)",@"姓名",@"男",[NSNumber numberWithInt:20],@"籍贯",@"电话号码",@"地址"];
 */
+(void)insertAction:(NSDictionary *)sqlCode{
    [self instanceDatabase];
    [db executeUpdate:@"INSERT INTO User (Name,Sex,Age,Native,Phone,Address) VALUES(?,?,?,?,?,?)",
     [sqlCode objectForKey:@"name"],
     [sqlCode objectForKey:@"sex"],
     [NSNumber numberWithInt:[[sqlCode objectForKey:@"age"] integerValue]],
     [sqlCode objectForKey:@"native"],
     [sqlCode objectForKey:@"phone"],
     [sqlCode objectForKey:@"address"]
     ];
}

/*
 *删除数据
 *[db executeUpdate:@"DELETE FROM User WHERE Name = ?",@"张三"];
 */
+(void)removeAction:(NSDictionary *)sqlCode{
    [self instanceDatabase];
    NSString *nameStr = [sqlCode objectForKey:@"name"];
    if([self judgeStringIsNull:nameStr]){
        [db executeUpdate:@"DELETE FROM User WHERE Name = ?",nameStr];
    }
}

/*
 *更新数据
 *[db executeUpdate:@"UPDATE User SET Name = ? WHERE Name = ?",@"李四",@"张三"];
 */
+(void)updateAction:(NSDictionary *)sqlCode{
    [self instanceDatabase];
    NSString *ageStr = [sqlCode objectForKey:@"age"];
    NSString *nameStr = [sqlCode objectForKey:@"name"];
    if([self judgeStringIsNull:ageStr] && [self judgeStringIsNull:nameStr]){
        NSString *sqlStr = [NSString stringWithFormat:@"UPDATE User SET Name = '%@',Sex = '%@',Age = %d,Native = '%@',Phone = '%@',Address = '%@' WHERE Name = '%@'",
                            [sqlCode objectForKey:@"name"],
                            [sqlCode objectForKey:@"sex"],
                            [ageStr  integerValue],
                            [sqlCode objectForKey:@"native"],
                            [sqlCode objectForKey:@"phone"],
                            [sqlCode objectForKey:@"address"],
                            nameStr];
      
        [db executeUpdate:sqlStr];
    }
}

/*
 *查找数据
 *
 */
+(NSDictionary *)findAction:(NSDictionary *)sqlCode{
    [self instanceDatabase];
    NSString *nameStr = [sqlCode objectForKey:@"name"];
    NSMutableDictionary *sqlDict = [[NSMutableDictionary alloc] init];
    if([self judgeStringIsNull:nameStr]){
        //返回数据库中第一条满足条件的结果
        FMResultSet *rs=[db executeQuery:@"SELECT * FROM User WHERE Name =?",nameStr];
        while ([rs next]){
            [sqlDict setObject:[rs stringForColumn:@"Name"] forKey:@"name"];
            [sqlDict setObject:[rs stringForColumn:@"Sex"]  forKey:@"sex"];
            [sqlDict setObject:[rs stringForColumn:@"Age"]  forKey:@"age"];
            [sqlDict setObject:[rs stringForColumn:@"Native"]  forKey:@"native"];
            [sqlDict setObject:[rs stringForColumn:@"Phone"]   forKey:@"phone"];
            [sqlDict setObject:[rs stringForColumn:@"Address"] forKey:@"address"];
        }
        [rs close];
    }
    return sqlDict;
}

//判断字符串不全为空
+(BOOL)judgeStringIsNull:(NSString *)string{
    BOOL result = NO;
    if(string != nil && string.length > 0){
        for (int i = 0; i < string.length; i ++) {
            NSString *subStr = [string substringWithRange:NSMakeRange(i, 1)];
            if(![subStr isEqualToString:@" "] && ![subStr isEqualToString:@""]){
                result = YES;
            }
        }
    }
    return result;
}

@end
 

Demo视图：