数据库使用(FMDB)

在xcode中新建一个空项目，新建一个类，命名为DataBaseHelper, .h文件申明一个单例和几个口函数，代码如下：

#import <Foundation/Foundation.h>
#import "FMDatabase.h"

@interface DataBaseHelper : NSObject

@property (nonatomic, strong) FMDatabase *db;

//单例
+ (DataBaseHelper *)sharedDataBaseHelper;
//插入数据
- (void)insertNoticeDictionary:(NSDictionary *)dict;
//查询数据
- (NSArray *)queryNoticeData;
//修改数据
- (void)updateNoticeDataWithNewDictionary:(NSDictionary *)newDictionary messageId:(NSString *)messageId;

@end

.m文件代码如下：

#import "DataBaseHelper.h"

static DataBaseHelper *helper = nil;

@implementation DataBaseHelper

#pragma mark - 创建单例
+ (DataBaseHelper *)sharedDataBaseHelper{
   static dispatch_once_t onceToken;
   dispatch_once(&onceToken, ^{
       helper = [[DataBaseHelper alloc] init];
       [helper createDataBase];
       [helper createTable];
   });
   return helper;
}


#pragma mark - 创建数据库
- (void)createDataBase{
   NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES) firstObject];
   NSString *filePath = [document stringByAppendingPathComponent:@"notices.sqlite"];
   self.db = [FMDatabase databaseWithPath:filePath];
}


#pragma mark - 创建表
- (void)createTable{
   if ([self.db open]) {
       BOOL result = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_notice (id integer PRIMARY KEY AUTOINCREMENT, dict BLOB NOT NULL, timeStamp datetime NOT NULL, messageId text NOT NULL);"];
       if (result) {
           NSLog(@"创建表成功");
       }else{
           NSLog(@"创建表失败");
       }
       [self.db close];
   }else{
       NSLog(@"数据库打开失败");
   }
}


#pragma mark - 插入数据
- (void)insertNoticeDictionary:(NSDictionary *)dict {
   if ([self.db open]) {
       NSNumber *startFromDate = [self getCurrentTimestamp];
       NSDictionary *noticeDict = @{@"messageId":dict[@"messageId"],
                                    @"fromId":dict[@"from"][@"fromId"],
                                    @"fromName":dict[@"from"][@"name"],
                                    @"fromAvatar":dict[@"from"][@"avatar"],
                                    @"toId":dict[@"to"][@"toId"],
                                    @"toName":dict[@"to"][@"name"],
                                    @"toAvatar":dict[@"to"][@"avatar"],
                                    @"title":dict[@"content"][@"title"],
                                    @"payload":dict[@"payload"],
                                    @"startFromDate":startFromDate
                                    };
       NSData *jsonData = [NSJSONSerialization dataWithJSONObject:noticeDict options:NSJSONWritingPrettyPrinted error:nil];
       NSString *messageId = dict[@"messageId"];
       NSString *dateStr = [self getCurrentTimeStr];
       BOOL result = [self.db executeUpdate:@"INSERT INTO t_notice (dict, timeStamp, messageId) VALUES (?,?,?);", jsonData, dateStr, messageId];
       if (result) {
           NSLog(@"插入成功");
       }else{
           NSLog(@"插入失败");
       }
       [self.db close];
   }else{
       NSLog(@"数据库打开失败");
   }
}

#pragma mark - 根据时间先后查询数据
- (NSArray *)queryNoticeData {
   NSError *error;
   NSMutableArray *arr = [NSMutableArray array];
   if ([self.db open]) {
       FMResultSet *resultSet = [self.db executeQuery:@"SELECT * FROM t_notice order by timeStamp desc"];
       while ([resultSet next]) {
           NSData *resultData = [resultSet objectForColumnName:@"dict"];
           NSDictionary *dict = [NSJSONSerialization JSONObjectWithData:resultData options:kNilOptions error:&error];
           [arr addObject:dict];
       }
   }
   return arr;
}

#pragma mark - 根据messageId修改数据
- (void)updateNoticeDataWithNewDictionary:(NSDictionary *)newDictionary messageId:(NSString *)messageId {
   NSData *jsonData = [NSJSONSerialization dataWithJSONObject:newDictionary options:NSJSONWritingPrettyPrinted error:nil];
   if ([self.db open]) {
       BOOL result = [self.db executeUpdate:@"UPDATE t_notice SET dict = ? WHERE messageId = ?", jsonData, messageId];
       if (result) {
           NSLog(@"修改数据成功");
       } else {
           NSLog(@"修改数据失败");
       } [self.db close];
   } else {
       NSLog(@"数据库打开失败");
   }
}


#pragma mark - 获取当前时间
- (NSString *)getCurrentTimeStr {
   NSDate *currentDate = [NSDate date];
   NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
   [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
   NSString *dateString = [dateFormatter stringFromDate:currentDate];
   return dateString;
}


#pragma mark - 获取当前时间戳
- (NSNumber *)getCurrentTimestamp {
   NSTimeInterval interval = [[NSDate date] timeIntervalSince1970];
   NSNumber *startFromDate = [NSNumber numberWithDouble:interval];
   return startFromDate;
}