关于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 = [resultSet intForColumnIndex:0];

    }

    return count>0;

}

//获取聊天列表

-(NSMutableArray *)getChatList

{

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

    FMResultSet *resultSet = [_database executeQuery:sql];

    NSMutableArray *array = [[NSMutableArray alloc] init];

    while ([resultSet next])

    {

        NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:[resultSet stringForColumn:@"fromUser"],@"fromUser",[resultSet stringForColumn:@"content"],@"content",[resultSet stringForColumn:@"toUser"],@"toUser",[resultSet stringForColumn:@"fromUserHead"],@"fromUserHead",[resultSet stringForColumn:@"toUserHead"],@"toUserHead", nil];

        [array addObject:dict];

    }

    return array;

}

//插入聊天列表

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

{

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

    BOOL b = [_database executeUpdate:sql];

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

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

    b = [_database executeUpdate: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 = [_database executeQuery:sql,fromUser];

    int count = 0;

    if ([resultSet next])

    {

        count = [resultSet intForColumnIndex:0];

    }

    return count>0;

}

//修改信息已读

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

{

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

    BOOL b = [_database executeUpdate:sql];

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

}

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

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

{

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

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

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

    FMResultSet *resultSet = [_database executeQuery:sql,str];

    //返回多条信息

    NSMutableArray *array = [[NSMutableArray alloc] init];

    while ([resultSet next])

    {

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

        [array addObject:dict];

    }

    return array;

}

//获取我的聊天记录

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

{

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

    FMResultSet *resultSet = [_database executeQuery:sql];

    NSMutableArray *array = [[NSMutableArray alloc] init];

    while ([resultSet next])

    {

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

        [array addObject:dict];

    }

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

    return array;

}

//查看未读消息数

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

{

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

    NSString *count = [[NSString alloc] init];

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

    FMResultSet *resultSet = [_database executeQuery:sql];

    while ([resultSet next])

    {

        count = [resultSet stringForColumn:@"count"];

    }

    return count;

}