持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第1天，点击查看活动详情
需求
最近音频类项目，有一个模块，收听过的内容，需要存在本地，需要记录收听的时长，下次接着播放。上限50条，按收听时间倒序展示
方案
设计本地数据库，用FMDB 执行sql语句。考虑到数据库不止这个地方用到。后续的收藏，订阅也有可能需要存到本地。所以数据库需要复用
需要解决的问题
SQL的增删改查：
- 建表：create table if not exists 'tablename' (column1 类型, column2 类型...)
- 增 insert into 'tablename' (key1,key2...) values (v1,v2...)
- 删 delete from 'tablename' where key = value
- 改 update 'tablename' set key1 = v1, key2 = v2 where key = v
- 查 select * from 'tablename' where key = v
问题一： 需要获取model的属性，和类型，建表 插入 查询都需要 解决
**unsigned** **int** count = 0;
objc_property_t *properties = class_copyPropertyList(class, &count);
**for** (**int** i = 0; i<count; i++) {
objc_property_t propertyx = properties[i];
NSString *propertyName = [NSString stringWithUTF8String:property_getName(propertyx)];
**const** **char** *propertyChar = property_getAttributes(propertyx);
NSString *propertyNamex = [NSString stringWithUTF8String:propertyChar];
NSString *typeKey = [propertyNamex componentsSeparatedByString:@","][0];
NSString *typeStr = [typeDict objectForKey:typeKey];
**if** (!typeStr || typeStr.length<1) {
typeStr = @"text";
}
**if** ([propertyName isEqualToString:primaryKey]) {
**continue**;
}
[sql appendFormat:@",'%@' %@", propertyName,typeStr];
}
[sql appendString:@")"];
问题二：数据存储上限50条，这里面会有更新，所以不能按照插入的顺序删除，考虑到取数据的时候也是按照更新的事件排序，所以加个时间的字段，每次插入成功的时候判断当前count 大于50进行删除
//查询数量
-(**int**)searchCountPrimaryKey:(NSString *)primaryKey
{
NSString *sql = [NSString stringWithFormat:@"SELECT COUNT(%@) FROM %@",primaryKey,**self**.tableName];
FMResultSet *s = [**self**.db executeQuery:sql];
**if** ([s next]) {
**int** totalCount = [s intForColumnIndex:0];
**return** totalCount;
}
**return** 0;
}
//超过50条 删除
-(**void**)deleteMoreThan:(**int**)value
{
NSString *sql =[NSString stringWithFormat:@"DELETE FROM %@ AS t WHERE t.timestr <= (SELECT t1.timestr FROM %@ AS t1 ORDER BY t1.timestr DESC LIMIT %d,1)", **self**.tableName,**self**.tableName,value];
**BOOL** result = [**self**.db executeUpdate:sql];
**if** (result) {
NSLog(@"删除成功");
}**else**{
NSLog(@"删除失败");
}
}
最终成果:
- 建表，这里根据实际情况设置了主键，数据来自服务端，所以主键不自增， 和服务端数据保持一致。考虑到其他地方可能需要自增主键，就都加上
-(**BOOL**)creatData:(Class)class primary:(NSString *)primaryKey isAutoincrement:(**BOOL**)isIncrement
{
NSDictionary *typeDict = @{@"Ti":@"integer",@"TB":@"integer",@"T@\"NSString\"":@"text"};
NSMutableString * sql = [NSMutableString stringWithFormat:@"create table if not exists '%@' (", **self**.tableName];
// [sql appendString:@"'%@' test primary key ",uniqueKey];
**if** (isIncrement) {
[sql appendFormat:@"'%@' integer primary key autoincrement",primaryKey];
}**else**{
[sql appendFormat:@"'%@' test primary key ",primaryKey];
}
**unsigned** **int** count = 0;
objc_property_t *properties = class_copyPropertyList(class, &count);
**for** (**int** i = 0; i<count; i++) {
objc_property_t propertyx = properties[i];
NSString *propertyName = [NSString stringWithUTF8String:property_getName(propertyx)];
**const** **char** *propertyChar = property_getAttributes(propertyx);
NSString *propertyNamex = [NSString stringWithUTF8String:propertyChar];
NSString *typeKey = [propertyNamex componentsSeparatedByString:@","][0];
NSString *typeStr = [typeDict objectForKey:typeKey];
**if** (!typeStr || typeStr.length<1) {
typeStr = @"text";
}
**if** ([propertyName isEqualToString:primaryKey]) {
**continue**;
}
[sql appendFormat:@",'%@' %@", propertyName,typeStr];
}
[sql appendString:@")"];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, **YES**);
NSString *documentDirectory = [paths objectAtIndex:0];
\
NSString *infraredDataBasePath = [documentDirectory stringByAppendingPathComponent:@"gjwavee.db"];
NSLog(@"path==%@",infraredDataBasePath);
FMDatabase *db = [FMDatabase databaseWithPath:infraredDataBasePath];
**self**.db = db;
**if** ([db open]) {
NSLog(@"打开成功");
**if** ([db executeUpdate:sql]) {
NSLog(@"创建表成功！");
**return** **YES**;
}**else** {
**return** **NO**;
}
}**else**{
NSLog(@"open error");
**return** **NO**;
}
}
- 查询所有 这里不想耦合字典转模型的内容，所以就返回字典数组
- (NSMutableArray *)queryAllData:(Class)class {
NSString * sql = [NSString stringWithFormat:@"select * from '%@'", **self**.tableName];
FMResultSet * resultSet = [**self**.db executeQuery:sql];
**id** model = **nil**;
NSMutableArray * mArr = [NSMutableArray arrayWithCapacity:0];
**while** ([resultSet next]) {
model = [class new];
NSMutableDictionary *mutabDict = [NSMutableDictionary dictionary];
**unsigned** **int** count = 0;
objc_property_t *properties = class_copyPropertyList(class, &count);
**for** (**int** i = 0; i<count; i++) {
objc_property_t propertyx = properties[i];
NSString *key = [NSString stringWithUTF8String:property_getName(propertyx)];
[mutabDict setValue:[resultSet stringForColumn:key] forKey:key];
}
[mArr addObject:mutabDict];
}
**return** mArr;
}
剩下的增删改查就不一一列出来，稍后放到github上去
