OC-数据库存储，使用FMDB进行数据库操作

63 阅读 0 评论 42 点赞

我是靠谱客的博主贪玩电话，最近开发中收集的这篇文章主要介绍OC-数据库存储，使用FMDB进行数据库操作，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

第一种：一个数据库中，同时存储多张数据表(此处举例为两张表)

.h文件

#import <Foundation/Foundation.h>

@class GoodsDetailsVO;

extern NSString * DataStoreType(int level);

extern NSString * tableName;

@interface DatabaseCenter : NSObject

/**

* 获取单例对象

+ (DatabaseCenter *)shareInstance;

/**

* 添加一条数据

- (BOOL)addRecordWithGoodsDetailsVO:(GoodsDetailsVO *)model;

/**

* 删除一条数据

- (BOOL)removeRecordWithGoodsDetailsVO:(GoodsDetailsVO *)model;

/**

* 检查某件商品是否被记录过

- (BOOL)isExistRecordWithGoodsDetailsVO:(GoodsDetailsVO *)model;

/**

* 获取记录的列表

- (NSArray *)recordList;

/**

* 删除所有数据

- (void)removeAllRecord;

@end

.m文件

#import "DatabaseCenter.h"

#import "GoodsDetailsVO.h"

#import "FMDatabase.h"

@interface DatabaseCenter ()

{

FMDatabase *_database;

}

@end

@implementation DatabaseCenter

NSString* DataStoreType (int type)

{

NSString *str;

switch (type)

{

case 0:

str = @"GoodsRecord";

break;

case 1:

str = @"GoodsCollect";

break;

default:

break;

}

return str;

}

/**

* 获取单例对象

+ (DatabaseCenter *)shareInstance

{

staticDatabaseCenter *dc =nil;

static dispatch_once_t onceToken;

dispatch_once(&onceToken, ^{

dc = [[[self class] alloc]init];

});

[dc createDatabaseTable];

return dc;

}

- (id)init

{

if(self = [superinit])

{

[selfinitDatabaseTwoDataStore];

}

return self;

}

/**

* 初始化数据库

- (void)initDatabaseTwoDataStore

{

NSString *path = [NSStringstringWithFormat:@"%@/Documents/data.sqlite",NSHomeDirectory()];

_database = [[FMDatabasealloc]initWithPath:path];

if(!_database.open)

{

return;

}

/**

* 创建数据表

- (void)createDatabaseTable

{

FMResultSet * set = [_databaseexecuteQuery:[NSStringstringWithFormat:@"select count(*) from sqlite_master where type ='table' and name = '%@'",tableName]];

[set next];

NSInteger count = [set intForColumnIndex:0];

BOOL existTable = !!count;

if (existTable)

{

// TODO:是否更新数据库

NSLog(@"数据表已存在");

}

else

{

// TODO: 插入新的数据库 (此处字段约束说明，可以不写)

//create table if not exists 这样写的话，即使已经存在这个表也能创建成功

//primary key 主键

//autoincrement not null 自增并且不能为空

//主键参考网址：http://baike.baidu.com/link?url=O-lrCgPGivbMN1avhL1GyQ6U5VOzJw_7wwK65zlbnuL_eKB20Jtt6DTTfzptr_UdWlX1jLTGdGcLKfVgdGe55mH00pqVWRYcOAYPHBP14Fto1DWFogWdNKqFGvPJAjTG5fi5E_AOmYD0pqqoa9Ggia

NSString *sql = [NSStringstringWithFormat:@"create table if not exists %@ ("

" id integer primary key autoincrement not null, "

" mpId integer not null, "

" name varchar(128), "

" urlImage varchar(1024), "

" price integer "

");",tableName];

BOOL res = [_databaseexecuteUpdate:sql];

if (!res)

{

NSLog(@"数据表创建失败");

}

else

{

NSLog(@"数据表创建成功");

}

/**

* 添加一条记录

- (BOOL)addRecordWithGoodsDetailsVO:(GoodsDetailsVO *)model

{

BOOL ret = NO;

NSString *sql = [NSStringstringWithFormat:@"insert into %@ (mpId,name,urlImage,price) values(?,?,?,?)",tableName];

//executeUpdate其他参数一律时字符串格式

BOOL b = [_databaseexecuteUpdate:sql,

model.mpId,

model.name,

model.urlImage,

model.price];

if(!b)

{

NSLog(@"插入失败!");

ret = NO;

}

else

{

NSLog(@"插入成功");

ret = YES;

}

return ret;

}

/**

* 删除一条数据

- (BOOL)removeRecordWithGoodsDetailsVO:(GoodsDetailsVO *)model

{

BOOL ret = NO;

NSString *sql = [NSStringstringWithFormat:@"delete from %@ where mpId=?",tableName];

BOOL b = [_databaseexecuteUpdate:sql,

model.mpId];

if(!b)

{

NSLog(@"删除失败!");

ret = NO;

}

else

{

NSLog(@"删除成功!");

ret = YES;

}

return ret;

}

/**

* 检查某件商品是否被浏览过

- (BOOL)isExistRecordWithGoodsDetailsVO:(GoodsDetailsVO *)model

{

NSString *sql = [NSStringstringWithFormat:@"select * from %@ where mpId=?",tableName];

FMResultSet *resultSet = [_databaseexecuteQuery:sql,

model.mpId];

int count=0;

while ([resultSet next])

{

count++;

}

return count>0;

}

/**

* 获取记录的列表

- (NSArray *)recordList

{

NSString *sql = [NSStringstringWithFormat:@"select * from %@",tableName];

FMResultSet *resultSet = [_databaseexecuteQuery:sql];

NSMutableArray *marr = [[NSMutableArrayalloc]init];

while([resultSet next])

{

GoodsDetailsVO *model = [[GoodsDetailsVOalloc]init];

model.mpId = [resultSet stringForColumn:@"mpId"];

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

model.urlImage = [resultSet stringForColumn:@"urlImage"];

model.price = [resultSet stringForColumn:@"price"];

[marr addObject:model];

}

return marr;

}

/**

* 删除所有数据

- (void)removeAllRecord

{

NSArray *arr = [selfrecordList];

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

{

DatabaseCenter *dc = [DatabaseCentershareInstance];

[dc removeRecordWithGoodsDetailsVO:arr[i]];

}

@end

使用

NSString *tableName;

//标注使用哪个表

tableName = DataStoreType(0);

//初始化对象

DatabaseCenter *dc = [DatabaseCentershareInstance];

//判断是否记录过此条数据

if (![dcisExistRecordWithGoodsDetailsVO:self.detailModel])

{

//将添加过的数据，保存到数据库

[dc addRecordWithGoodsDetailsVO:self.detailModel];

}

//获取记录列表

self.dataList = [DatabaseCentershareInstance].recordList;

//删除指定的一条数据

GoodsRecordCell *cell = (GoodsRecordCell*)[tableViewcellForRowAtIndexPath:indexPath];

[dc removeRecordWithGoodsDetailsVO:cell.model];

//删除所有数据

[dc removeAllRecord];

第二种：一个数据库中，只存在一张数据表(此处以存储高德地图位置信息为例，基本上都是大同小异的)

.h文件

#import <Foundation/Foundation.h>

@class AMapPOI;

@interface DataBaseCenter : NSObject

/**

* 获取单例对象

+ (DataBaseCenter *)shareInstance;

/**

* 添加一条数据

- (void)addHistoryrRecordWithAMapPOI:(AMapPOI *)amapPoi;

/**

* 删除一条数据

- (void)removeHistoryrRecordWithAMapPOI:(AMapPOI *)amapPoi;

/**

* 检查此地址是否已经存入历史记录

- (BOOL)isExistHistoryrRecordWithAMapPOI:(AMapPOI *)amapPoi;

/**

* 获取记录的列表

- (NSArray *)recordList;

/**

* 删除所有数据

- (void)removeAllRecord;

@end

.m文件

#import "DataBaseCenter.h"

@interface DataBaseCenter ()

{

FMDatabase *_database;

}

@end

@implementation DataBaseCenter

/**

* 获取单例对象

+ (DataBaseCenter *)shareInstance

{

staticDataBaseCenter *dc =nil;

if(dc == nil)

{

dc = [[[self class] alloc]init];

}

return dc;

}

- (id)init

{

if(self = [superinit])

{

[selfinitDataBaseDataStore];

}

return self;

}

/**

* 初始化数据库

- (void)initDataBaseDataStore

{

NSString *path = [NSStringstringWithFormat:@"%@/Documents/data.sqlite",NSHomeDirectory()];

_database = [[FMDatabasealloc]initWithPath:path];

if(!_database.open)

{

return;

}

//创建历史记录数据表

NSString *sql =@"create table if not exists historyrRecordList(uid,name,type,address,province,pcode,city,citycode,district,adcode,gridcode,latitude,longitude)";

BOOL b = [_databaseexecuteUpdate:sql];

if(!b)

{

NSLog(@"数据表创建失败!");

}

else

{

NSLog(@"数据表创建成功!");

}

/**

* 添加一条记录

- (void)addHistoryrRecordWithAMapPOI:(AMapPOI *)amapPoi;

{

NSString *sql =@"insert into historyrRecordList (uid,name,type,address,province,pcode,city,citycode,district,adcode,gridcode,latitude,longitude) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";

BOOL b = [_databaseexecuteUpdate:sql,

amapPoi.uid,

amapPoi.name,

amapPoi.type,

amapPoi.address,

amapPoi.province,

amapPoi.pcode,

amapPoi.city,

amapPoi.citycode,

amapPoi.district,

amapPoi.adcode,

amapPoi.gridcode,

[NSString stringWithFormat:@"%f",amapPoi.location.latitude],

[NSString stringWithFormat:@"%f",amapPoi.location.longitude]];

if(!b)

{

NSLog(@"插入失败!");

}

else

{

NSLog(@"插入成功");

}

/**

* 删除一条数据

- (void)removeHistoryrRecordWithAMapPOI:(AMapPOI *)amapPoi;

{

NSString *sql =@"delete from historyrRecordList where uid=?";

BOOL b = [_databaseexecuteUpdate:sql,

amapPoi.uid];

if(!b)

{

NSLog(@"删除失败!");

}

else

{

NSLog(@"删除成功!");

}

/**

* 检查此地址是否已经存入历史记录

- (BOOL)isExistHistoryrRecordWithAMapPOI:(AMapPOI *)amapPoi;

{

NSString *sql =@"select * from historyrRecordList where uid=?";

FMResultSet *resultSet = [_databaseexecuteQuery:sql,

amapPoi.uid];

int count=0;

while ([resultSet next])

{

count++;

}

return count>0;

}

/**

* 获取记录的列表

- (NSArray *)recordList;

{

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

FMResultSet *resultSet = [_databaseexecuteQuery:sql];

NSMutableArray *marr = [[NSMutableArrayalloc]init];

while([resultSet next])

{

AMapPOI *amapPoi = [[AMapPOIalloc]init];

amapPoi.uid = [resultSet stringForColumn:@"uid"];

amapPoi.name = [resultSet stringForColumn:@"name"];

amapPoi.type = [resultSet stringForColumn:@"type"];

amapPoi.address = [resultSet stringForColumn:@"address"];

amapPoi.province = [resultSet stringForColumn:@"province"];

amapPoi.pcode = [resultSet stringForColumn:@"pcode"];

amapPoi.city = [resultSet stringForColumn:@"city"];

amapPoi.citycode = [resultSet stringForColumn:@"citycode"];

amapPoi.district = [resultSet stringForColumn:@"district"];

amapPoi.adcode = [resultSet stringForColumn:@"adcode"];

amapPoi.gridcode = [resultSet stringForColumn:@"gridcode"];

AMapGeoPoint *locationT = [[AMapGeoPointalloc]init];

locationT.latitude = [[resultSet stringForColumn:@"latitude"] floatValue];

locationT.longitude = [[resultSet stringForColumn:@"longitude"] floatValue];

amapPoi.location = locationT;

[marr addObject:amapPoi];

}

return marr;

}

/**

* 删除所有数据

- (void)removeAllRecord;

{

NSArray *arr = [selfrecordList];

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

{

DataBaseCenter *dc = [DataBaseCentershareInstance];

[dc removeHistoryrRecordWithAMapPOI:arr[i]];

}

@end

使用：除了不需要标注表名，其他的与第一种方式一样，请查看第一种方式

注意：此处以高德地图为例，api中的经纬度的模型对象是location，存储的时候，要存入的是location的latitude与longitud的数值，而不能存入location这个对象。取值的时候，要先初始化AMapGeoPoint对象，用AMapGeoPoint的对象来接收数据库中的存储的值。不初始化AMapGeoPoint对象的话，取出来的经纬度的值是空的。原因是因为AMapGeoPoint的指针对象为空，所以赋值不上去。