FMDB使用

#import <Foundation/Foundation.h>

#import "MovieModal.h"

#import "ActivityModal.h"

#import "FMDatabase.h"

#import "MovieCollection.h"

#import "ActivityCollection.h"

@interface DataBaseTool : NSObject

{

   FMDatabase *db;

   NSMutableArray *arrayMovCollection;

   NSMutableArray *arrayActCollection;

}

//创建movie表格的方法

- (void)createMovieTable;

//创建activity表格的方法

- (void)createActivityTable;

//收藏电影的方法

- (BOOL)insertMovieToTable:(NSString *)name picUrl:(NSString *)url movieId:(NSInteger)movieId;

//收藏活动的方法

- (BOOL)insertActivityToTable:(ActivityModal *)active;

//根据ID判断是否存在

- (BOOL)isExistsWithID:(NSInteger)num;

//根据活动的title判断是否存在

- (BOOL)isExistsWithTitle:(NSString *)title;

//查询电影收藏夹的电影信息

- (NSMutableArray *)selectWithMovieTable;

//查询活动收藏夹的活动信息

- (NSMutableArray *)selectWithActivityTable;

//清空电影收藏夹

- (BOOL)clearMovieTable;

//清空活动收藏夹

- (BOOL)clearActivityTable;

@end

／／－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

#import "DataBaseTool.h"

@implementation DataBaseTool

- (instancetype)init

{

   self = [super init];

   if (self) {

      //创建数据库路径并且跟db进行关联

      NSString *douPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];

      NSString *dbPath = [douPath stringByAppendingPathComponent: @"movies.sqlite"];

      db = [FMDatabase databaseWithPath: dbPath];

      arrayActCollection = [NSMutableArray array];

      arrayMovCollection = [NSMutableArray array];      

      //NSLog(@"%@",NSHomeDirectory());

   }

   return self;

}

//创建电影表

- (void)createMovieTable

{

   if ([db open]) {

      NSString *createTableSql = [NSString stringWithFormat:@"create table if not exists movie (id integer primary key autoincrement,movieName text,picUrl text,movieId integer)"];

      BOOL result = [db executeUpdate: createTableSql];

      if (result) {

         //NSLog(@"建表成功！");

      } else {

         NSLog(@"建表失败！");

      }

   }

   [db close];

}

//创建活动表

- (void)createActivityTable

{

   //打开数据库

   if ([db open]) {

      NSString *createTableSql = [NSString stringWithFormat:@"create table if not exists activity(id integer primary key autoincrement,title text,picurl text)"];

      //执行语句

      BOOL result = [db executeUpdate: createTableSql];

      if (result) {

         //NSLog(@"建表成功！");

      } else {

         NSLog(@"建表失败！");

      }

   }

   [db close];

}

//电影收藏插入数据

- (BOOL)insertMovieToTable:(NSString *)name picUrl:(NSString *)url movieId:(NSInteger)movieId

{

   if ([db open]) {

      NSString *insertSql = [NSString stringWithFormat:@"insert into movie (movieName,picUrl,movieId) values ('%@','%@',%ld)",name,url,movieId];

      BOOL result = [db executeUpdate: insertSql];

      if (result) {

         [db close];

         return YES;

      }

   }

   [db close];

   return NO;

}

//活动收藏插入数据

- (BOOL)insertActivityToTable:(ActivityModal *)active

{

   if ([db open]) {

      //创建插入语句

      NSString *insertSql = [NSString stringWithFormat:@"insert into activity(title,picurl) values ('%@','%@')",active.title,active.image_hlarge];

      //执行语句

      BOOL result = [db executeUpdate: insertSql];

      if (result) {

         [db close];

         return YES;

      }

   }

   [db close];

   return NO;

}

//根据电影ID判断数据是否存在

- (BOOL)isExistsWithID:(NSInteger)num

{

   if ([db open]) {

      NSString *selectSql = [NSString stringWithFormat: @"select * from movie where movieId = '%ld'",num];

      FMResultSet *set = [db executeQuery: selectSql];

      while ([set next]) {

         NSInteger numResult = [set intForColumn:@"movieId"];

         if (numResult == num) {

            //存在

            [db close];

            return YES;

         }

      }

   }

   [db close];

   return NO;

}

//根据活动title判断数据是否存在

- (BOOL)isExistsWithTitle:(NSString *)title

{

   if ([db open]) {

      //创建查询语句

      NSString *selectSql = [NSString stringWithFormat:@"select * from activity where title = '%@'",title];

      //执行语句

      FMResultSet *set = [db executeQuery: selectSql];

      while ([set next]) {

         NSString *titleResult = [set stringForColumn:@"title"];

         if ([titleResult isEqualToString: title]) {

            //存在

            [db close];

            return YES;

         }

      }

   }

   [db close];

   return NO;

}

//查询电影收藏夹的电影信息

- (NSMutableArray *)selectWithMovieTable

{

   if ([db open]) {

      //创建查询语句

      NSString *selectSql = [NSString stringWithFormat:@"select * from movie"];

      //执行语句

      FMResultSet *set = [db executeQuery: selectSql];

      while ([set next]) {

         MovieCollection *mov = [[MovieCollection alloc] init];

         mov.movieTitle = [set stringForColumn:@"movieName"];

         mov.moviePicUrl = [set stringForColumn:@"picUrl"];

         mov.movieId = [set intForColumn:@"movieId"];

         [arrayMovCollection addObject: mov];

      }

   }

   [db close];

   return arrayMovCollection;

}

//查询活动收藏夹的活动信息

- (NSMutableArray *)selectWithActivityTable

{

   if ([db open]) {

      //创建查询语句

      NSString *selectSql = [NSString stringWithFormat:@"select * from activity"];

      //执行语句

      FMResultSet *set = [db executeQuery: selectSql];

      while ([set next]) {

         ActivityCollection *act = [[ActivityCollection alloc] init];

         act.actTitle = [set stringForColumn:@"title"];

         act.actPicUrl = [set stringForColumn:@"picurl"];

         [arrayActCollection addObject: act];

      }

   }

   [db close];

   return arrayActCollection;

}

//清空

- (BOOL)clearMovieTable

{

   if ([db open]) {

      NSString *clearSql = [NSString stringWithFormat:@"delete from movie"];

      BOOL result = [db executeUpdate: clearSql];

      if (result) {

         [db close];

         return  YES;

      }

   }

   [db close];

   return NO;

}

- (BOOL)clearActivityTable

{

   return NO;

}

@end