fmdb使用小结

1.fmdb 设置primary key后，如果主键相同的话，insert into只能插入一条，后面插入的无效。 

建表时设置主键：

[db executeUpdate:@"create table FriendFullList (Jid text primary key,Name text,Use_name text,Email text,Subscription text, Phone text,nickName text)"];

2.修改操作示例

- (void)insertDataToFriendFullList:(FMDatabase *)db UserModel:(UserModel *)model

{

    //现在表中查询有没有相同的元素，如果有且有变化，做修改操作，如果无变化则不做任何操作

    FMResultSet *rs = [dbexecuteQuery:@"select * from FriendFullList where Jid = ?", model.jid];

   if([rs next])

    {

       if (![[rs stringForColumn:@"nickName"]isEqualToString:model.nickName] || [[rsstringForColumn:@"Phone"]isEqualToString:model.phone])

        {

            [db executeUpdate:@"update FriendFullList set nickName = ?, Phone = ? where Jid = ?",model.nickName, model.phone, model.jid];

        }

    }

    //向数据库中插入一条数据

   else

    {

        [db executeUpdate:@"insert into FriendFullList (Jid,Name,Use_name,Email,Subscription,Phone,nickName) values (?,?,?,?,?,?,?)" ,model.jid,model.name,model.userName,model.Email,model.subscription,model.phone,model.nickName];

    }

}

3.如果要插入整数字段，sql语句应这么写

[defaultDBManager.databaseQueueinDatabase:^(FMDatabase *db) {

           NSString *sql=[NSStringstringWithFormat:@"insert into conversationRecordList (Current_User_Id, UserName, nickName, phoneNumber, voipTime, timeStamp, callDuration, conversationStatus) values ('%@','%@','%@','%@','%@','%ld','%d','%d')" ,[[NSUserDefaultsstandardUserDefaults]objectForKey:@"userName"],conversationRecordModel.userName,conversationRecordModel.nickName,conversationRecordModel.phoneNumber,conversationRecordModel.voipTime,conversationRecordModel.timeStamp, conversationRecordModel.callDuration,conversationRecordModel.conversationStatus];

            [dbexecuteUpdate:sql];

//            NSLog(@"********)))%@",sql);

        }];

3.用这种方式时，传值要用对象 比如BOOL型@YES

//群被解散后，更新ChatGroupFullTable群名全表bDestroy字段

-(void)updateGroupTabelForDestroyField:(NSString *)roomJid

{

    [defaultDBManager.databaseQueue inDatabase:^(FMDatabase *db) {

        //更新群名称全表

        [db executeUpdate:@"update  ChatGroupFullTable set bDestroy = ? where roomJid = ? and Current_User_Id = ? ",@YES,roomJid,[[NSUserDefaults standardUserDefaults]objectForKey:@"userName"]];

        

    }];

}

这种方式时，可以直接传bool

 NSString *insertChatListSql = [NSString stringWithFormat:@"insert into ChatList (Current_User_Id,lasttimeStamp,toUserId,lastMessage,lastMessageType,bIsVideoCall) values('%@','%ld','%@','%@','%d','%d')",[[NSUserDefaults standardUserDefaults]objectForKey:@"userName"],conversationRecordModel.timeStamp,conversationRecordModel.userName,conversationRecordModel.msg,MSG_FILETYPE_CONVERSATIONRECORD,conversationRecordModel.bIsVideoCall];

                    [db executeUpdate:insertChatListSql];