关于FMDB 数据库自己看着学的，希望可以互相学习

//获取单例对象

+(id)sharedInstance

{

   static SchoolData *cr = nil;

   if (cr == nil)

    {

        cr = [[[super class] alloc] init];

    }

   return cr;

}

-(id)init

{

   self = [super init];

   if (self)

    {

        [self initDataBase];

    }

    return self;

}

-(void)initDataBase

{

    NSString *path = [NSString stringWithFormat:@"%@/Documents/chatRecord.sqlite",NSHomeDirectory()];

    _database = [[FMDatabase alloc] initWithPath:path];

   if (_database.open == NO)

    {

        NSLog(@"数据库没有打开");

       return;

    }

    NSString *sql = [NSString stringWithFormat:@"create table if not exists chatList ("

                     " id integer primary key autoincrement not null, "

                    " fromUser text, "

                    " content text, "

                    " toUser text,"

                    " fromUserHead text,"

                    " toUserHead text"

                    ");"

                     ];

   BOOL b = [_database executeUpdate:sql];

    NSLog(@"create b = %d",b);

}

-(void)initDatabaseWithFromUser:(NSString *)fromUser WithToUser:(NSString *)toUser

{

    //创建数据库

    NSString *path = [NSString stringWithFormat:@"%@/Documents/chatRecord.sqlite",NSHomeDirectory()];

    _database = [[FMDatabase alloc] initWithPath:path];

   if (_database.open == NO)

    {

        NSLog(@"数据库打开失败");

       return;

    }

    NSString *sql = [NSString stringWithFormat:@"create table if not exists _%@and%@ ("

                     " id integer primary key autoincrement not null, "

                    " fromUser text, "

                    " content text, "

                    " toUser text,"

                    " isRead text"

                    ");",fromUser,toUser];

   BOOL b =  [_database executeUpdate:sql];

    

//    NSString *sql1 = @"create table if not exists receive ("

//    " id integer primary key autoincrement not null, "

//    " uphone varchar(128), "

//    " content varchar(2048), "

//    " tphone varchar(32),"

//    " ptime varchar(1024),"

//    " from_user_name varchar(1024),"

//    " from_user_picurl varchar(1024)"

//    ");";

    NSLog(@"create table = %d",b);

}

//添加记录

-(BOOL)addRecordWithContent:(NSDictionary *)dict WithFromUser:(NSString *)fromUser WithToUser:(NSString *)toUser

{

    //NSString *sql = @"insert into applist(uphone,content,password,tphone) values(?,?,?,?)";

    NSString *sql = [NSString stringWithFormat:@"insert into _%@and%@(fromUser,content,toUser,isRead) values(?,?,?,?)",fromUser,toUser];

//    BOOL b = [_database executeUpdate:sql,[NSString stringWithFormat:@"%d",recordType],model.applicationId,model.name,model.iconUrl,@"limit",model.lastPrice,model.currentPrice];

   BOOL b = [_database executeUpdate:sql,[NSString stringWithFormat:@"%@",dict[@"fromUser"]],[NSString stringWithFormat:@"%@",dict[@"content"]],[NSString stringWithFormat:@"%@",dict[@"toUser"]],[NSString stringWithFormat:@"%@",dict[@"isRead"]]];

    NSLog(@"inster b = %d",b);

   return b;

}

//查看某个记录是否存在

-(BOOL)isExistsRecordWithUphone:(NSString *)uphone andTphone:(NSString *)tphone

{

    //NSString *sql = @"select count(*) from applist where applicationId = ? and recordType = ?";

    NSString *sql = @"select * from school where name like '%?%'";

    FMResultSet *resultSet = [_database executeQuery:sql,[NSString stringWithFormat:@"%@",uphone],[NSString stringWithFormat:@"%@",tphone]];

   int count = 0;

   if ([resultSet next])

    {

        count = [resultSetintForColumnIndex:0];

    }

   return count>0;

}

//获取聊天列表

-(NSMutableArray *)getChatList

{

    NSString *sql = [NSStringstringWithFormat:@"select * from chatList order by id desc"];

   FMResultSet *resultSet = [_databaseexecuteQuery:sql];

    NSMutableArray *array = [[NSMutableArrayalloc] init];

   while ([resultSet next])

    {

        NSDictionary *dict = [NSDictionarydictionaryWithObjectsAndKeys:[resultSetstringForColumn:@"fromUser"],@"fromUser",[resultSetstringForColumn:@"content"],@"content",[resultSetstringForColumn:@"toUser"],@"toUser",[resultSetstringForColumn:@"fromUserHead"],@"fromUserHead",[resultSetstringForColumn:@"toUserHead"],@"toUserHead",nil];

        [arrayaddObject:dict];

    }

   return array;

}

//插入聊天列表

-(void)addChatList:(NSDictionary *)dict WithFromUser:(NSString *)fromUser

{

    NSString *sql = [NSStringstringWithFormat:@"delete from chatList where fromUser = '%@' and id > 0;",fromUser];

   BOOL b = [_databaseexecuteUpdate:sql];

    NSLog(@"delete = %d",b);

    NSString *sql1 = [NSStringstringWithFormat:@"insert into%@(fromUser,content,toUser,fromUserHead,toUserHead) values(?,?,?,?,?,?)",fromUser];

    b = [_databaseexecuteUpdate:sql1,dict[@"fromUser"],dict[@"content"],dict[@"toUser"],dict[@"fromUserHead"],dict[@"toUserHead"]];

    NSLog(@"insert b = %d",b);

}

//查询是否有该聊天记录

-(BOOL)IsChatFromUser:(NSString *)fromUser WithToUser:(NSString *)toUser

{

    NSString *sql =@"select * from chatList where fromUser = ?";

   FMResultSet *resultSet = [_databaseexecuteQuery:sql,fromUser];

   int count = 0;

   if ([resultSet next])

    {

        count = [resultSetintForColumnIndex:0];

    }

   return count>0;

}

//修改信息已读

-(void)setChatIsReadFromUser:(NSString *)fromUser WithToUser:(NSString *)toUser

{

    NSString *sql = [NSStringstringWithFormat:@"update _%@and%@ set isRead=1 where id > 0 and isRead=0;",fromUser,toUser];

   BOOL b = [_databaseexecuteUpdate:sql];

    NSLog(@"set = %d",b);

}

//获取某个好友聊天的记录

-(NSMutableArray *)getRecordWithName:(NSString *)name

{

    //NSString *sql = @"select * from applist where recordType = ?";

   NSString *str = [NSStringstringWithFormat:@"%%%@%%",name];

    NSString *sql =@"select * from school where name like ?";

   FMResultSet *resultSet = [_databaseexecuteQuery:sql,str];

    //返回多条信息

    NSMutableArray *array = [[NSMutableArrayalloc] init];

   while ([resultSet next])

    {

       NSDictionary *dict = [NSDictionarydictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"name"],@"name",[resultSetstringForColumn:@"id"],@"id",nil];

        [arrayaddObject:dict];

    }

   return array;

}

//获取我的聊天记录

-(NSMutableArray *)getRecordFromUser:(NSString *)fromUser WithToUser:(NSString *)toUser AndMaxID:(NSString *)maxID

{

    NSString *sql = [NSStringstringWithFormat:@"select * from _%@and%@  where id < ? order by id desc limit 20",fromUser,toUser];

   FMResultSet *resultSet = [_databaseexecuteQuery:sql];

    NSMutableArray *array = [[NSMutableArrayalloc] init];

   while ([resultSet next])

    {

       NSDictionary *dict = [NSDictionarydictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"fromUser"],@"fromUser",[resultSetstringForColumn:@"content"],@"content",[resultSetstringForColumn:@"toUser"],@"toUser",[resultSetstringForColumn:@"isRead"],@"isRead",[resultSetstringForColumn:@"id"],@"id",nil];

        [arrayaddObject:dict];

    }

   NSLog(@"array = %@",array);

   return array;

}

//查看未读消息数

-(NSString *)getNoReadCount:(NSString *)fromUser WithToUser:(NSString *)toUser

{

    NSString *sql = [NSStringstringWithFormat:@"select count(*) as count from _%@and%@ where isRead = 0",fromUser,toUser];

   NSString *count = [[NSStringalloc] init];

    //#update message set isRead=1 where id > 0 and isRead=0;

   FMResultSet *resultSet = [_databaseexecuteQuery:sql];

   while ([resultSet next])

    {

        count = [resultSetstringForColumn:@"count"];

    }

   return count;

}