IOS开发～FMDB备忘

增：四种方式向数据表中插入数据

    1）[[DBHelper sharedHelper].db executeUpdate:@"insert into tb_test (username,sex) values (?,?)" , @"lizhongfu", @"male"];    2）[[DBHelper sharedHelper].db executeUpdateWithFormat:@"insert into tb_test values (%@, %@)", @"lzf", @"nan"];    3）[[DBHelper sharedHelper].db executeUpdate:@"insert into tb_test (username,sex) values (?, ?)" withArgumentsInArray:[NSArray arrayWithObjects:@"zfl", @"nanren", nil]];        4）NSMutableDictionary *dic = [NSMutableDictionary dictionaryWithCapacity:0];    [dic setObject:@"lll" forKey:@"username"];    [dic setObject:@"zzz" forKey:@"sex"];    [[DBHelper sharedHelper].db executeUpdate:@"insert into tb_test values (:username, :sex)" withParameterDictionary:dic];

删：

[[DBHelper sharedHelper].db executeUpdate:@"DELETE FROM tb_test WHERE username = ?", @"lll"];

改： 两种方式更新数据表中数据

    1)[[DBHelper sharedHelper].db executeUpdate:@"update tb_test set username = ? where sex = ?" , @"lizhongfu", @"nanren"];        2)NSMutableDictionary *dic = [NSMutableDictionary dictionaryWithCapacity:0];    [dic setObject:@"update_name" forKey:@"username"];    [dic setObject:@"update_sex" forKey:@"sex"];    [[DBHelper sharedHelper].db executeUpdate:@"update tb_test set username = :username, sex = :sex where sex = 'male'" withParameterDictionary:dic];

查：

    FMResultSet *rs = [[DBHelper sharedHelper].db executeQuery:@"SELECT * FROM tb_test"];    while ([rs next])    {        NSLog(@"username:%@ sex:%@", [rs stringForColumn:@"username"], [rs stringForColumn:@"sex"]);    }

例1：

FMDatabase* db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];// open 和 close 是成对的 methodif (![db open]) { //判断数据库是否已经打开，如果没有打开，提示失败   //Error handling -- can't open database}// 为数据库设置缓存，提高查询效率 [db setShouldCacheStatements:YES];// executeUpdate 执行更新 insert updata delete[db executeUpdate:@"create table test (a text, b text, c integer, d double, e double)"];// 會執行 BEGIN EXCLUSIVE TRANSACTION 命令鎖住資料庫。[db beginTransaction]; //开始一个事务int i = 0;while (i++ < 20) {  // 類似 stringWithFormat: 的語法！  [db executeUpdate:@"insert into test (a, b, c, d, e) values (?, ?, ?, ?, ?)" ,         @"hi'", // 不需要把'寫成\'或是''         [NSString stringWithFormat:@"number %d", i],         [NSNumber numberWithInt:i],         [NSDate date],         [NSNumber numberWithFloat:2.2f]]; } [db commit];//提交这个事务,期间可以RollbackTrans回滚 [db close];

例2:

首先要先导入第三方类库FMdatabase    获得存放数据库文件的沙盒地址    +(NSString *)databaseFilePath    {                NSArray *filePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);        NSString *documentPath = [filePath objectAtIndex:0];        NSLog(@"%@",filePath);        NSString *dbFilePath = [documentPath stringByAppendingPathComponent:@"db.sqlite"];        return dbFilePath;            }    创建数据库的操作    +(void)creatDatabase    {        db = [[FMDatabasedatabaseWithPath:[selfdatabaseFilePath]] retain];    }    创建表    +(void)creatTable    {        //先判断数据库是否存在，如果不存在，创建数据库        if (!db) {            [selfcreatDatabase];        }        //判断数据库是否已经打开，如果没有打开，提示失败        if (![db open]) {            NSLog(@"数据库打开失败");            return;        }                //为数据库设置缓存，提高查询效率        [dbsetShouldCacheStatements:YES];                //判断数据库中是否已经存在这个表，如果不存在则创建该表        if(![dbtableExists:@"people"])        {            [db executeUpdate:@"CREATE TABLES people(people_id INTEGER PRIMARY KEY AUTOINCREAMENT, name TEXT, age INTEGER) "];                                    NSLog(@"创建完成");        }            }    增加表数据    +(void)insertPeople:(People *)aPeople    {        if (!db) {            [selfcreatDatabase];        }                if (![db open]) {            NSLog(@"数据库打开失败");            return;        }                [dbsetShouldCacheStatements:YES];                if(![dbtableExists:@"people"])        {            [selfcreatTable];        }        //以上操作与创建表是做的判断逻辑相同        //现在表中查询有没有相同的元素，如果有，做修改操作        FMResultSet *rs = [dbexecuteQuery:@"select * from people where people_id = ?",[NSStringstringWithFormat:@"%d",aPeople.peopleID]];        if([rs next])        {            NSLog(@"dddddslsdkien");            [dbexecuteUpdate:@"update people set name = ?, age = ? where people_id = 1",aPeople.name,[NSStringstringWithFormat:@"%d",aPeople.age]];        }        //向数据库中插入一条数据        else{            [dbexecuteUpdate:@"INSERT INTO people (name, age) VALUES (?,?)",aPeople.name,[NSStringstringWithFormat:@"%d",aPeople.age]];        }                    }    删除数据    +(void)deletePeopleByID:(int)ID    {        if (!db) {            [selfcreatDatabase];        }                if (![db open]) {            NSLog(@"数据库打开失败");            return;        }                [dbsetShouldCacheStatements:YES];                //判断表中是否有指定的数据， 如果没有则无删除的必要，直接return        if(![dbtableExists:@"people"])        {            return;        }        //删除操作        [db executeUpdate:@"delete from people where people_id = ?", [NSStringstringWithFormat:@"%d",ID]];                [db close];    }    修改操作与增加操作的步骤一致    查询    +(NSArray *)getAllPeople    {                if (!db) {            [selfcreatDatabase];        }                if (![db open]) {            NSLog(@"数据库打开失败");            return nil;        }                [dbsetShouldCacheStatements:YES];                if(![dbtableExists:@"people"])        {            return nil;        }                //定义一个可变数组，用来存放查询的结果，返回给调用者         NSMutableArray *peopleArray = [[NSMutableArrayalloc] initWithArray:0];        //定义一个结果集，存放查询的数据        FMResultSet *rs = [dbexecuteQuery:@"select * from people"];        //判断结果集中是否有数据，如果有则取出数据        while ([rs next]) {            People *aPeople = [[People alloc] init];                        aPeople.peopleID = [rs intForColumn:@"people_id"];            aPeople.name = [rs stringForColumn:@"name"];            aPeople.age = [rs intForColumn:@"age"];            //将查询到的数据放入数组中。             [peopleArray addObject:aPeople];        }        return [peopleArray autorelease];    }

江湖救急，先整理这么多～