你的位置:首页 > 操作系统

[操作系统]iOS sqlite 增删改查 简单封装(基于 FMDB)


/**
 *  对 sqlite 的使用进行简单封装,仅涉及简单的单表 增删改查
 *
 *  基于 FMDB
 *
 *  操作基于 model ,数据库表字段与 model 属性一一对应,对 model 整体进行操作
 *
 *  根据 model 对象自动建表,字段类型只支持 NSString , NSIteger , float
 *
 *  用到 runtime 运行时获取 model 属性
 *
 */

 1 // 2 // AGDatabaseManager.h 3 // 4 // Created by Ager on 15/11/10. 5 // Copyright © 2015年 Ager. All rights reserved. 6 // 7  8  9 /** 10  * 对 sqlite 的使用进行简单封装,仅涉及简单的单表 增删改查 11  * 12  * 基于 FMDB 13  * 14  * 操作基于 model ,数据库表字段与 model 属性一一对应,对 model 整体进行操作 15  * 16  * 根据 model 对象自动建表,字段类型只支持 NSString , NSIteger , float 17  * 18  * 用到 runtime 运行时获取 model 属性 19  * 20 */ 21  22 #import <Foundation/Foundation.h> 23  24 @interface AGDatabaseManager : NSObject 25  26 + (AGDatabaseManager*)shareAGDatabaseManager; 27  28 /** 29  * 创建表格 30  * 31  * @param cls  model 类 32  * @param tbName 表名 33  * @param keyName 主键字段 34  * @param key  主键的属性设置 35  * 36  * @return 创建表格是否成功 37 */ 38 - (BOOL)creatTable:(Class)cls tableName:(NSString*)tbName keyName:(NSString*)keyName primaryKey:(NSString*) key; 39  40  41 /** 42  * 向表格插入数据 43  * 44  * @param model 数据模型与数据库表格对应 45  * @param tbName 要操作的表名 46  * 47  * @return 添加是否成功 48 */ 49 - (BOOL)insert:(id)model tableName:(NSString*)tbName; 50  51  52 /** 53  * 更新数据 54  * 55  * @param tbName 要操作的表名 56  * @param model 数据模型与数据库表格对应 57  * @param str  更新操作查要更新的数据的条件 58  * 59  * @return 更新是否成功 60 */ 61 - (BOOL)update:(id)model tableName:(NSString*)tbName where:(NSString*)str; 62  63  64 /** 65  * 删除数据 66  * 67  * @param tbName 要删除数据的表名 68  * @param str  要删除的数据的查找条件 69  * 70  * @return 删除是否成功 71 */ 72 - (BOOL)deleteTableName:(NSString*)tbName where:(NSString*)str; 73  74  75 /** 76  * 查询数据 77  * 78  * @param model 数据模型与数据库表格对应 79  * @param tbName 要操作的表名 80  * @param str  删除操作查要删除的数据的条件 81  * 82  * @return 查询结果 (数组每一项为字典) 83 */ 84 - (NSArray*)select:(Class)model tableName:(NSString*)tbName where:(NSString*)str; 85  86  87 /** 88  * 查询全部数据 89  * 90  * @param model 数据模型与数据库表格对应 91  * @param tbName 要操作的表名 92  * 93  * @return 查询结果 (数组每一项为字典) 94 */ 95 - (NSArray*)selectALL:(Class)model tableName:(NSString*)tbName; 96  97  98  99 100 @end

AGDatabaseManager.h
 1 // 2 // AGDatabaseManager.m 3 // 4 // Created by Ager on 15/11/10. 5 // Copyright © 2015年 Ager. All rights reserved. 6 // 7  8 #import "AGDatabaseManager.h" 9 #import "FMDatabase.h" 10 #import <objc/runtime.h> 11  12 static FMDatabase *fmdb = nil; 13  14 @implementation AGDatabaseManager 15  16  17 - (instancetype)init{ 18   if (self = [super init]) { 19      20     static dispatch_once_t oneToken; 21     dispatch_once(&oneToken, ^{ 22       NSString *document = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)[0]; 23       NSString *filePath = [document stringByAppendingPathComponent:@"database.sqlite"]; 24       NSLog(@"%@",document); 25       fmdb = [FMDatabase databaseWithPath:filePath]; 26  27     }); 28   } 29   return self; 30 } 31  32  33 + (AGDatabaseManager*)shareAGDatabaseManager{ 34   return [[AGDatabaseManager alloc]init]; 35 } 36  37  38 - (BOOL)creatTable:(Class)cls tableName:(NSString*)tbName keyName:(NSString*)keyName primaryKey:(NSString*) key{ 39    40   NSArray *array = [self getModelAllProperty:cls]; 41   NSMutableString *sql = [NSMutableString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (",tbName]; 42    43   for (int i = 0; i < array.count; i++) { 44     NSDictionary *dic = array[i]; 45     [sql appendFormat:@"%@ %@ ",[dic objectForKey:@"name"],[dic objectForKey:@"type"]]; 46     if(keyName != nil && [keyName isEqualToString:[dic objectForKey:@"name"]]){ 47       [sql appendString:key]; 48     } 49     if (i < array.count - 1){ 50       [sql appendString:@","]; 51     } 52   } 53    54   [sql appendString:@")"]; 55    56   NSLog(@"创建表格: %@",sql); 57    58   [fmdb open]; 59   BOOL result = [fmdb executeUpdate:[sql copy]]; 60   NSLog(@"创建表格:%@",result ? @"成功":@"失败"); 61   [fmdb close]; 62   return result; 63 } 64  65  66 - (BOOL)insert:(id)model tableName:(NSString*)tbName{ 67    68   NSArray *array = [self getModelAllProperty:[model class]]; 69    70   NSMutableString *propertyStr = [[NSMutableString alloc]init]; 71   NSMutableString *valuesStr = [[NSMutableString alloc]init]; 72    73   for (int i = 0; i < array.count; i++) { 74     NSDictionary *dic = array[i]; 75     [propertyStr appendString:[dic objectForKey:@"name"]]; 76     [valuesStr appendFormat:@"'%@'",[model valueForKey:[dic objectForKey:@"name"]]]; 77      78     if (i < array.count - 1){ 79       [propertyStr appendString:@","]; 80       [valuesStr appendString:@","]; 81     } 82   } 83   NSMutableString *sql = [NSMutableString stringWithFormat:@"INSERT INTO %@ (%@) values (%@)",tbName,propertyStr ,valuesStr]; 84   NSLog(@"添加数据 : %@",sql); 85   [fmdb open]; 86   BOOL result = [fmdb executeUpdate:[sql copy]]; 87   [fmdb close]; 88   NSLog(@"添加数据:%@",result ? @"成功":@"失败"); 89    90   return result; 91 } 92  93  94 - (BOOL)update:(id)model tableName:(NSString*)tbName where:(NSString*)str{ 95   NSArray *array = [self getModelAllProperty:[model class]]; 96   NSMutableString *sql = [NSMutableString stringWithFormat:@"UPDATE %@ SET ",tbName]; 97    98   for (int i = 0; i < array.count; i++) { 99     NSDictionary *dic = array[i];100     NSString *pro = [dic objectForKey:@"name"];101     [sql appendFormat:@"%@ = '%@'",pro,[model valueForKey:pro]];102     if (i < array.count - 1){103       [sql appendString:@","];104     }105   }106   107   [sql appendFormat:@" where %@",str];108   109   NSLog(@"修改数据 : %@",sql);110   [fmdb open];111   BOOL result = [fmdb executeUpdate:[sql copy]];112   [fmdb close];113   NSLog(@"更新数据:%@",result ? @"成功":@"失败");114   return result;115 }116 117 118 - (BOOL)deleteTableName:(NSString*)tbName where:(NSString*)str{119   NSString *sql = [NSString stringWithFormat:@"delete from %@ where %@",tbName,str];120   NSLog(@"删除数据 : %@",sql);121   [fmdb open];122   BOOL result = [fmdb executeUpdate:sql];123   [fmdb close];124   NSLog(@"更新数据:%@",result ? @"成功":@"失败");125   return result;126 }127 128 129 - (NSArray*)select:(Class)model tableName:(NSString*)tbName where:(NSString*)str{130   NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@",tbName,str];131   NSArray *array = [self getModelAllProperty:[model class]];132   [fmdb open];133   NSLog(@"查询数据 : %@",sql);134   FMResultSet *set = [fmdb executeQuery:sql];135   NSMutableArray *allArray = [[NSMutableArray alloc]init];136   while ([set next]) {137     NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];138     for (int i = 0; i < array.count; i++) {139       NSDictionary *dic1 = array[i];140       NSString *pro = [dic1 objectForKey:@"name"];141       [dic setValue:[set stringForColumn:pro] forKey:pro];142     }143     [allArray addObject:dic];144   }145   146   [set close];147   [fmdb close];148   return [allArray copy];149 }150 151 - (NSArray*)selectALL:(Class)model tableName:(NSString*)tbName {152   NSString *sql = [NSString stringWithFormat:@"select * from %@ ",tbName];153   NSArray *array = [self getModelAllProperty:[model class]];154   [fmdb open];155   NSLog(@"查询数据 : %@",sql);156   FMResultSet *set = [fmdb executeQuery:sql];157   NSMutableArray *allArray = [[NSMutableArray alloc]init];158   while ([set next]) {159     NSMutableDictionary *dic = [[NSMutableDictionary alloc]init];160     for (int i = 0; i < array.count; i++) {161       NSDictionary *dic1 = array[i];162       NSString *pro = [dic1 objectForKey:@"name"];163       [dic setValue:[set stringForColumn:pro] forKey:pro];164     }165     [allArray addObject:dic];166   }167   168   [set close];169   [fmdb close];170   return [allArray copy];171 }172 173 174 175 176 #pragma mark --- 辅助方法 ---177 178 /**179  * 获取 model 类全部的属性和属性类型180  *181  * @param cls model 类 class182  *183  * @return 返回 model 的属性和属性类型184 */185 - (NSArray *)getModelAllProperty:(Class)cls{186   187   unsigned int count = 0;188   objc_property_t *propertys = class_copyPropertyList(cls, &count);189   NSMutableArray *array = [NSMutableArray array];190   for (int i = 0; i < count; i++) {191     192     objc_property_t property = propertys[i];193     NSString *propertyName = [NSString stringWithCString:property_getName(property) encoding:NSUTF8StringEncoding];194     195     NSString *type = [self getPropertyAttributeValue:property name:@"T"];196     197     if ([type isEqualToString:@"q"]||[type isEqualToString:@"i"]) {198       type = @"INTEGER";199     }else if([type isEqualToString:@"f"] || [type isEqualToString:@"d"]){200       type = @"FLOAT";201     }else{202       type = @"TEXT";203     }204        205     NSDictionary *dic = [NSDictionary dictionaryWithObjectsAndKeys:propertyName , @"name",type , @"type", nil];206     207     [array addObject:dic];208     209   }210   free(propertys);211   212   return array.copy;213 }214 215 /**216  * 获取属性的特征值217 */218 219 - (NSString*)getPropertyAttributeValue:(objc_property_t) pro name:(NSString*)name{220   221   unsigned int count = 0;222   objc_property_attribute_t *attributes = property_copyAttributeList(pro, &count);223   224   for (int i = 0 ; i < count; i++) {225     objc_property_attribute_t attribute = attributes[i];226     if (strcmp(attribute.name, name.UTF8String) == 0) {227       return [NSString stringWithCString:attribute.value encoding:NSUTF8StringEncoding];228     }229   }230   free(attributes);231   return nil;232 }233 234 @end

AGDatabaseManager.m

FMDB + AGDatabaseManager 文件