数据库含外键关联表（重要！！！）

1.创建NSObject

2.用单例模式创建

+(id)shareDataCenter

{//创建单例

DataCenter*dataCeter;

if(dataCeter==nil) {

dataCeter=[[DataCenteralloc]init];

}

returndataCeter;

}

3.重写父类init方法；

-(instancetype)init

{

self=[superinit];

if(self) {

[selfinitDataBase];

}

returnself;

}

4.在initDataBase方法中创建数据库

5.其它页面进行使用时：

1.加入数据库头文件；#import"DataCenter.h"

2.实例化单例对象；_center=[DataCentershareDataCenter];

3.用_center进行数据库操作；

//

//  DataCenter.h

//  aixianmian爱限免

//

//  Copyright (c) 2015年Mickey. All rights reserved.

//

#import

#import"FMDatabase.h"//数据库第三方

#import"DetailModel.h"//详细信息模型

@interfaceDataCenter :NSObject

+(id)shareDataCenter;//单例模式

@property(nonatomic,strong)FMDatabase*dataBase;

#pragma mark -主页数据

//插入主页的数据

-(void)insertUserFaceDataWith:(NSArray*)arry;

//查询所有数据

-(NSArray*)selectRecordOfData;

//移除数据库数据

-(BOOL)deleteRecordFromOfData;

#pragma mark -收藏

//添加收藏

-(BOOL)addRecordWithAppModel:(DetailModel*)model;

//删除收藏通过ID

-(BOOL)deleteRecordWithAppId:(NSString*)appId;

//删除收藏通过字段

-(BOOL)deleteRecordWithByName:(NSString*)name;

//查询收藏

-(BOOL)selectRecordWith:(NSString*)appId;

//查询收藏的图片链接

-(NSArray*)selectImgUrlRecord;

#pragma mark -专题数据库

//创建专题的表

-(BOOL)createTopicTable;

//创建推荐应用的表

-(BOOL)createSuggestTable;

//插入专题的数据

-(void)insertTopicDataWith:(NSArray*)array;

//插入推荐应用的数据

-(void)insertSuggestDataWith:(NSArray*)array andTitle:(NSString*)title;

//查询专题和推荐应用的数据

-(NSArray*)selectTopicdata;

//删除表

-(void)deleteDataFromTable;

@end

//

//  DataCenter.m

//  aixianmian爱限免

//  Copyright (c) 2015年Mickey. All rights reserved.

//

#import"DataCenter.h"

#import"DataModelOfTopic.h"

#import"applicationsModel.h"

#import"DataModel.h"

@implementationDataCenter

+(id)shareDataCenter

{//创建单例

DataCenter *dataCeter;

if(dataCeter==nil) {

dataCeter=[[DataCenter alloc]init];

}

returndataCeter;

}

-(instancetype)init

{

self=[superinit];

if(self) {

[selfinitDataBase];

}

returnself;

}

-(void)initDataBase

{

//创建数据库

NSString *strPath=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES)[0];

NSString *dbPath=[strPath stringByAppendingPathComponent:@"MM.db"];//拼接数据库名

//  NSLog(@"%@",dbPath);//获取沙盒路径

_dataBase=[FMDatabase databaseWithPath:dbPath];

if([_dataBaseopen]) {//判断数据库是否打开

//    NSLog(@"数据库成功打开");

}else

{

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

}

//创建表(收藏)

NSString*sqlCreateTable=@"create table if not exists ybh(Id integer primary key autoincrement,name text,applicationId text)";

BOOLb=[_dataBaseexecuteUpdate:sqlCreateTable];

if(!b) {

NSLog(@"创建失败");

}

//添加收藏图片路径字段

NSString*sqlAdd=@"alter table ybh add column ulrImg text";

[_dataBaseexecuteUpdate:sqlAdd];

//添加收藏类型字段

NSString*sqlAddcate=@"alter table ybh add column categoryName text";

[_dataBaseexecuteUpdate:sqlAddcate];

//创建表2(APP)

NSString*appSql=@"create table if not exists appTabl (id integer primary key autoincrement not null, applicationId text,name text,iconUrl text,type text,lastPrice text,currentPrice text,startCount text,shareTime text,favorites text,downloads text)";

BOOLb1=[_dataBaseexecuteUpdate:appSql];

if(!b1) {

NSLog(@"创建App表格失败");

}

[selfcreateTopicTable];

[selfcreateSuggestTable];

}

#pragma mark -主页数据

//插入主页的数据

-(void)insertUserFaceDataWith:(NSArray *)arry

{

NSString * sql =@"insert into appTabl (applicationId,name,iconUrl,type,lastPrice,currentPrice,startCount,shareTime,favorites,downloads) values (?,?,?,?,?,?,?,?,?,?)";

for(inti=0; i

DataModel *model=arry[i];

[_dataBase executeUpdate:sql,model.applicationId,model.name,model.iconUrl,model.categoryName,model.lastPrice,model.currentPrice,model.starCurrent,model.shares,model.favorites,model.downloads];

}

}

//查询所有数据

-(NSArray *)selectRecordOfData

{

NSString *sql=@"select * from appTabl";

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

FMResultSet *set=[_dataBase executeQuery:sql];

while(set.next) {

DataModel *model=[[DataModel alloc]init];

model.applicationId = [set stringForColumn:@"applicationId"];

model.name = [set stringForColumn:@"name"];

model.iconUrl = [set stringForColumn:@"iconUrl"];

model.categoryName = [set stringForColumn:@"type"];

model.lastPrice = [set stringForColumn:@"lastPrice"];

model.starCurrent = [set stringForColumn:@"startCount"];

model.shares = [set stringForColumn:@"shareTime"];

model.favorites = [set stringForColumn:@"favorites"];

model.downloads = [set stringForColumn:@"downloads"];

model.currentPrice = [set stringForColumn:@"currentPrice"];

[arr addObject:model];

}

returnarr;

}

//移除数据

-(BOOL)deleteRecordFromOfData

{

NSString *sqlDele=@"delete from appTabl";

BOOLb=[_dataBase executeUpdate:sqlDele];

returnb;

}

#pragma mark -详情页收藏(按钮)

//添加收藏

-(BOOL)addRecordWithAppModel:(DetailModel *)model

{

NSString *sql=@"insert into ybh(name,applicationId,ulrImg,categoryName)values(?,?,?,?)";

BOOLb=[_dataBase executeUpdate:sql,model.name,model.applicationId,model.iconUrl,model.categoryName];

returnb;

}

//删除收藏

-(BOOL)deleteRecordWithAppId:(NSString *)appId

{

NSString *deleteSql=@"delete from ybh where applicationId=?";

BOOLb=[_dataBase executeUpdate:deleteSql,appId];

returnb;

}

//删除收藏通过name字段

-(BOOL)deleteRecordWithByName:(NSString *)name

{

NSString *deleteSql=@"delete from ybh where name=?";

BOOLb=[_dataBase executeUpdate:deleteSql,name];

returnb;

}

//查询收藏的图片链接

-(NSArray *)selectImgUrlRecord

{

NSString *selectSql=@"select * from ybh";

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

FMResultSet *set=[_dataBase executeQuery:selectSql];

while(set.next) {

DetailModel *detaiModel=[[DetailModel alloc]init];

detaiModel.iconUrl=[set stringForColumn:@"ulrImg"];

detaiModel.name=[set stringForColumn:@"name"];

detaiModel.applicationId=[set stringForColumn:@"applicationId"];

detaiModel.categoryName=[set stringForColumn:@"categoryName"];

[arr  addObject:detaiModel];

}

returnarr;

}

//查询收藏

-(BOOL)selectRecordWith:(NSString *)appId

{

NSString *selectSql=@"select count(*)from ybh where applicationId=?";

FMResultSet *set=[_dataBase executeQuery:selectSql,appId];

intcount=0;

while(set.next) {

count=[set intForColumnIndex:0];

}

returncount>0;

}

//创建专题的表

-(BOOL)createTopicTable

{

//创建表

NSString *sqlCreateTable=@"create table if not exists TopicTable(id integer primary key autoincrement not null,title text,desc text,desc_img text,img text)";

BOOLb=[_dataBase executeUpdate:sqlCreateTable];

if(!b) {

NSLog(@"创建失败");

}

returnb;

}

//创建推荐应用的表

-(BOOL)createSuggestTable

{

//创建表

NSString *sqlCreateTable=@"create table if not exists SuggestTable(id integer primary key autoincrement not null,applicationId text,downloads text,iconUrl text,name text,ratingOverall text,starOverall text)";

BOOLb=[_dataBase executeUpdate:sqlCreateTable];

if(!b) {

NSLog(@"创建失败");

}

//添加字段

NSString *addSql=@"alter table SuggestTable add column title text";

[_dataBase executeUpdate:addSql];

returnb;

}

//插入专题的数据

-(void)insertTopicDataWith:(NSArray *)array

{

//由专题界面调用该方法将arry数据传输过来

NSString *sql=@"insert into TopicTable(title,desc,desc_img,img) values (?,?,?,?)";

for(inti=0; i

DataModelOfTopic *topic=array[i];

BOOLb=[_dataBase executeUpdate:sql,topic.title,topic.desc,topic.desc_img,topic.img];

if(b) {

//   NSLog(@"专题插入成功");

[selfinsertSuggestDataWith:topic.arrApplications andTitle:topic.title];//调用插入推荐应用数据方法

}else

{

NSLog(@"专题插入失败");

}

}

}

//插入推荐应用的数据(由插入专题单独调用),在插入专题时插入推荐应用

-(void)insertSuggestDataWith:(NSArray *)array andTitle:(NSString *)title

{

//array由专题模型的数组属性传递,title为两个数据表的外键;

NSString *sql=@"insert into SuggestTable(title,applicationId,downloads,iconUrl,name,ratingOverall,starOverall)values(?,?,?,?,?,?,?)";

for(inti=0; i

applicationsModel *application=array[i];

BOOLb=[_dataBase executeUpdate:sql,title,application.applicationId,application.downloads,application.iconUrl,application.name,application.ratingOverall,application.starOverall];

if(b) {

// NSLog(@"推荐应用插入成功");

}else

{

NSLog(@"推荐应用插入失败");

}

}

}

//查询专题和推荐应用的数据

-(NSArray *)selectTopicdata

{

NSString *sql=@"select * from TopicTable";

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

FMResultSet *set=[_dataBase executeQuery:sql];

while(set.next) {

DataModelOfTopic *topic=[[DataModelOfTopic alloc]init];

topic.title=[set stringForColumn:@"title"];

topic.desc=[set stringForColumn:@"desc"];

topic.desc_img=[set stringForColumn:@"desc_img"];

topic.img=[set stringForColumn:@"img"];

topic.arrApplications=[selfselectSuggesTableArrayTitle:topic.title];//调用推荐应用的查询方法,用外键title传入

[arrDB addObject:topic];

}

returnarrDB;

}

-(NSArray *)selectSuggesTableArrayTitle:(NSString*)title

{

NSString *sql=@"select * from SuggestTable where title=?";

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

FMResultSet *set=[_dataBase executeQuery:sql,title];

while(set.next) {

applicationsModel *application=[[applicationsModel alloc]init];

application.applicationId=[set stringForColumn:@"applicationId"];

application.downloads=[set stringForColumn:@"downloads"];

application.iconUrl=[set stringForColumn:@"iconUrl"];

application.name=[set stringForColumn:@"name"];

application.ratingOverall=[set stringForColumn:@"ratingOverall"];

application.starOverall=[set stringForColumn:@"starOverall"];

[arry addObject:application];

}

returnarry;

}

#pragma mark -删除数据

-(void)deleteDataFromTable

{

NSString *sql=@"delete from TopicTable";

NSString *sqlSuggest=@"delete from SuggestTable";

[_dataBase executeUpdate:sql];

[_dataBase executeUpdate:sqlSuggest];

}

@end