FMDB第三方的使用笔记

随即：在项目开发的过程中，需要使用数据库对数据进行存储。例如对app推送消息的历史消息的存储。

FMDatabase *_infoDb;

一、创建数据库

#pragma mark - 创建数据库
-(void)createSqlite{
//获取沙盒目录
NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString*documents =[paths objectAtIndex:0];
NSString *dataBase_path=[documents stringByAppendingPathComponent:@"INFO.sqlite"];

//在指定目录下创建FMDB
_infoDb =[[FMDatabase alloc]initWithPath:dataBase_path];
}

二、创建表格

#pragma mark 创建表格
-(void)openSqlite{

//判断是否存在数据库，不存在就创建
if (!_infoDb) {
    [self createSqlite];
}

//获取用户的唯一标识，作为表名
NSUserDefaults*user=[NSUserDefaults standardUserDefaults];
NSString*cardID=[user objectForKey:@"cardID"];

NSString*infoName;
if (cardID) {
    infoName=[NSString stringWithFormat:@"%@info",[cardID MD5Digest]];

}else{
    infoName=@"info";   
}

//创建表格
NSString*sql=[NSString stringWithFormat:@"CREATE TABLE '%@' (dateline,id,link,title,type,readOrNot)",infoName];

//打开数据库
if ([_infoDb open]) {
    //执行sql语句创建表格
    BOOL res =[_infoDb executeUpdate:sql];

    if (!res){
        NSLog(@"创建表格失败");
    }else{
        NSLog(@"创建表格成功");
    }
    [_infoDb close];
}else{
    NSLog(@"打开数据库失败");
}
}

三、添加数据

-(void)addDataArray:(NSArray*)dataArray{

//判断数据库是否存在
if (!_infoDb) {
    [self createSqlite];
}
//判断数据库是否打开
if (![_infoDb open]) {
    NSLog(@"数据库打开失败");
    //[self openSqlite];
}
//开启缓存
[_infoDb setShouldCacheStatements:YES];

//判断表格是否创建
NSUserDefaults*user=[NSUserDefaults standardUserDefaults];

NSString*cardID=[user objectForKey:@"cardID"];

NSString*infoName;
if (cardID) {
    infoName=[NSString stringWithFormat:@"%@info",[cardID MD5Digest]];
}else{
    infoName=@"info";

}
if (![_infoDb tableExists:infoName]) {

    [self openSqlite];
}

//开启队列和线程安全
NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString*documents =[paths objectAtIndex:0];
NSString *dataBase_path=[documents stringByAppendingPathComponent:@"INFO.sqlite"];
//创建队列
FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath:dataBase_path];

[queue inDatabase:^(FMDatabase *db) {

    for (int i=0; i<dataArray.count; i++) {

        NSDictionary*dataDic=dataArray[i];

        NSString *dateLine=[dataDic objectForKey:DADELINE];
        NSString *infoId=[dataDic objectForKey:ID];
        NSString *link=[dataDic objectForKey:LINK];
        NSString *title=[dataDic objectForKey:TITLE];
        NSString *type=[dataDic objectForKey:TYPE];

        NSString*sql=[NSString stringWithFormat:@"select * from '%@' where id = ?",infoName];

        //@"select * from '%@' where id =?",infoName,infoId
        FMResultSet*resultSet=[_infoDb executeQuery:sql,infoId];

        if ([resultSet next]) {
            //包含

        }else{
            //不包含，添加数据

            NSString*sqlAdd=[NSString stringWithFormat:@"insert into '%@' (dateline,id,link,title,type,readOrNot) values (?,?,?,?,?,?)",infoName];

            //[_infoDb executeUpdate:@"insert into info values(?,?,?,?,?,?)",dateLine,infoId,link,title,type,@"0"];

            BOOL isFinishAdd=[_infoDb executeUpdate:sqlAdd,dateLine,infoId,link,title,type,@"0"];
            if (isFinishAdd) {

                NSLog(@"add is finish ");
            }else{   
                NSLog(@"add is errno");
            }   
        }   
    } 
}];
}

四、修改数据

#pragma mark 修改数据
-(void)upDateDic:(NSDictionary*)dataDic{

//判断数据库是否存在
if (!_infoDb) {
    [self createSqlite];
}
//判断数据库是否打开
if (![_infoDb open]) {
    NSLog(@"数据库打开失败");
}
//开启缓存
[_infoDb setShouldCacheStatements:YES];

//判断表格是否创建
NSUserDefaults*user=[NSUserDefaults standardUserDefaults];
NSString*cardID=[user objectForKey:@"cardID"];
NSString*infoName;
if (cardID) {
    infoName=[NSString stringWithFormat:@"%@info",[cardID MD5Digest]];
}else{
    infoName=@"info";
}

if (![_infoDb tableExists:infoName]) {

    [self openSqlite];
}
//查询表中有没有相同元素，
NSString *infoId=[dataDic objectForKey:ID];
NSString *sql=[NSString stringWithFormat:@"select * from '%@' where id = ?",infoName];

FMResultSet*resultSet=[_infoDb executeQuery:sql,infoId];

if ([resultSet next]) {
    //包含

    NSString*upDateSql=[NSString stringWithFormat:@"update '%@' set readOrNot =1 where id =?",infoName];

    //@"update '%@' set readOrNot =? where id =?",infoName,@"1",infoId

  BOOL re= [_infoDb executeUpdate:upDateSql,infoId];

    if (re) {
        NSLog(@"修改成功，已读");
    }else{
        NSLog(@"修改失败");
    }
}else{

}
    [_infoDb close];
}

五、删除数据

#pragma mark 删除数据

-(void)deleteInfo:(NSString*)infoId{
//
if (!_infoDb) {

    [self createSqlite];
}
if (![_infoDb open]) {

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

    return;
}
[_infoDb setShouldCacheStatements:YES];
NSUserDefaults*user=[NSUserDefaults standardUserDefaults]; 
NSString*cardID=[user objectForKey:@"cardID"];

NSString*infoName;
if (cardID) {
    infoName=[NSString stringWithFormat:@"%@info",[cardID MD5Digest]];
}else{
    infoName=@"info";
}

if (![_infoDb tableExists:infoName]) {
    return;
}
NSString *sql=[NSString stringWithFormat:@"delete from '%@' where id=?",infoName];

BOOL isFinishDelete =[_infoDb executeUpdate:sql,infoId];

if (isFinishDelete) {
    NSLog(@"delete is finish");
}else{   
    NSLog(@"delete is errno");
}
[_infoDb close];
}

六、查询数据

#pragma mark 查询数据
-(void)fetch{

if (!_infoDb) { 
    [self createSqlite];
}
if (![_infoDb open]) {
    NSLog(@"打开数据库失败");
    return;
}
[_infoDb setShouldCacheStatements:YES];


NSUserDefaults*user=[NSUserDefaults standardUserDefaults];
NSString*cardID=[user objectForKey:@"cardID"];
NSString*infoName;
if (cardID) {
    infoName=[NSString stringWithFormat:@"%@info",[cardID MD5Digest]];
}else{
    infoName=@"info";   
}
if (![_infoDb tableExists:infoName]) {
    return;
}
//按照 时间降序查询数据，ASC是数据升序
NSString *fetchPatch=[NSString stringWithFormat:@"SELECT * FROM '%@' order by dateline DESC ",infoName];// ORDER BY id ASC，升序查询

FMResultSet *resultSet=[_infoDb executeQuery:fetchPatch];

NSMutableArray*mutableArray=[NSMutableArray arrayWithCapacity:0];

while ([resultSet next]) {

    NSMutableDictionary *dic=[NSMutableDictionary dictionaryWithCapacity:0];
    NSString*dateline=[resultSet stringForColumn:DADELINE];
    NSString*infoid=[resultSet stringForColumn:ID];
    NSString*link=[resultSet stringForColumn:LINK];
    NSString*title=[resultSet stringForColumn:TITLE];
    NSString*type=[resultSet stringForColumn:TYPE];
    NSString*readOrNot=[resultSet stringForColumn:@"readOrNot"];

    [dic setValue:dateline forKey:DADELINE];
    [dic setValue:infoid forKey:ID];
    [dic setValue:link forKey:LINK];
    [dic setValue:title forKey:TITLE];
    [dic setValue:type forKey:TYPE];
    [dic setValue:readOrNot forKey:@"readOrNot"];
    [mutableArray addObject:dic];
}
self.dataArray =[NSMutableArray arrayWithArray:mutableArray];

[_infoDb close];
}