#获取数据库及插入数据库操作
/**
* 获得特约店首页广告图片列表数据
*/
- (void)getAdListData{
//先从数据库中获取数据
if (!self.adListModel) {
//查询数据库
NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM t_%@", kMerchantAdListSqliteName];
[self queryDataFromFMBDOfMerchantTableWithSqliteName:kMerchantAdListSqliteName andQueryTableSql:querySql];
CCLog(@"%@",self.adListModel);
}
//数据库中没有从网络获取数据
if (!self.adListModel) {
[self requestAdListData];
}
}
#pragma mark 网络请求成功后
}else if([reqType isEqualToString:kReqTypeMerchantGetAdList]){ //广告图片列表接口
self.adListModel = [[MerchantAdListModel alloc]initWithData:responeObject error:NULL];
//将数据保存到本地数据库
//创建表单MerchantRecommendationShopList
NSString *createTableSql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS t_%@ (id integer PRIMARY KEY AUTOINCREMENT, %@ integer, %@ text, %@ text);",kMerchantAdListSqliteName,kMerchantAdListSqliteAdId,kMerchantAdListSqlitePic,kMerchantAdListSqliteHref];
[self creatFMBDOfMerchantTableWithSqliteName :kMerchantAdListSqliteName withCreateTableSql:createTableSql];
//将数据插入到数据库
for (MerchantAdPicModel *adPicModel in self.adListModel.adList) {
NSString *insertTableSql = [NSString stringWithFormat:@"INSERT INTO t_%@ (%@,%@,%@) VALUES (%d,'%@','%@')",kMerchantAdListSqliteName,kMerchantAdListSqliteAdId,kMerchantAdListSqlitePic,kMerchantAdListSqliteHref,adPicModel.id,adPicModel.pic,adPicModel.href];
[self insertDataToFMBDOfMerchantTableWithSqliteName:kMerchantAdListSqliteName andInsertTableSql:insertTableSql];
}
}
#pragma FMBD数据库操作
//获取数据库
- (FMDatabase *)getFMBDOfMerchantWithSqliteName:(NSString *) sqliteName{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentDirectory stringByAppendingPathComponent:[NSString stringWithFormat:@"%@.db",sqliteName]];
FMDatabase *db = [FMDatabase databaseWithPath:dbPath] ;
return db;
}
//创建表
- (void)creatFMBDOfMerchantTableWithSqliteName:(NSString *)sqliteName withCreateTableSql:(NSString *)createTableSql{
//获得数据库
FMDatabase *db=[self getFMBDOfMerchantWithSqliteName:sqliteName];
//打开数据库
if (![db open]) {
CCLog(@"数据库%@打开失败",sqliteName);
return;
}else{
//为数据库设置缓存，提高查询效率
[db setShouldCacheStatements:YES];
//创表
BOOL result=[db executeUpdate:createTableSql];
if (result) {
CCLog(@"创表成功");
}else{
CCLog(@"%@创表失败Reason:%@",sqliteName,db.lastErrorMessage);
}
}
[db close];
}
//插入数据
- (void)insertDataToFMBDOfMerchantTableWithSqliteName:(NSString *)sqliteName andInsertTableSql:(NSString *)insertTableSql{
//获得数据库
FMDatabase *db=[self getFMBDOfMerchantWithSqliteName:sqliteName];
//打开数据库
if (![db open]) {
CCLog(@"数据库%@打开失败",sqliteName);
return;
}
[db executeUpdate:insertTableSql];
if ([db hadError]) {
CCLog(@"%@插入失败! Reason:%@",sqliteName,db.lastErrorMessage);
}
[db close];
}
//查询数据
- (void)queryDataFromFMBDOfMerchantTableWithSqliteName:(NSString *)sqliteName andQueryTableSql:(NSString *)queryTableSql{
//获得数据库
FMDatabase *db=[self getFMBDOfMerchantWithSqliteName:sqliteName];
//打开数据库
if (![db open]) {
CCLog(@"数据库%@打开失败",sqliteName);
return;
}
FMResultSet *resultSet = [db executeQuery:queryTableSql];
//遍历查询结果
while (resultSet) { //注意：这里不能写成 while([resultSet next]),否则多遍历了一行
[self HandleresultSet:resultSet withSqliteName:sqliteName];
}
if ([db hadError]) {
CCLog(@"%@查询失败! Reason:%@",sqliteName,db.lastErrorMessage);
}
[db close];
}
//遍历查询结果
- (void)HandleresultSet:(FMResultSet *)resultSet withSqliteName:(NSString *)sqliteName{
if ([sqliteName isEqualToString:kMerchantAdListSqliteName]) {
while ([resultSet next]) {
int adId = [resultSet intForColumn:kMerchantAdListSqliteAdId];
NSString *adPic = [resultSet stringForColumn:kMerchantAdListSqlitePic];
NSString *adHref = [resultSet stringForColumn:kMerchantAdListSqliteHref];
MerchantAdPicModel *picModel = [[MerchantAdPicModel alloc]init];
picModel.id = adId;
picModel.pic = adPic;
picModel.href = adHref;
}
}
}
