iOSsqlite增删改查简单封装(基于FMDB)

  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

  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