天天省钱快报

YDFMDB.m 13KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341
  1. //
  2. // YDFMDB.m
  3. // YUEDU
  4. //
  5. // Created by jcymac on 2017/12/6.
  6. // Copyright © 2017年 kuxuan. All rights reserved.
  7. //
  8. #import "YDFMDB.h"
  9. #import <FMDB.h>
  10. @interface YDFMDB()
  11. @property(nonatomic,strong) FMDatabase *jcyDB;
  12. @end
  13. @implementation YDFMDB
  14. +(instancetype)sharedInstance{
  15. static YDFMDB *YDDB = nil;
  16. static dispatch_once_t onceToken;
  17. dispatch_once(&onceToken, ^{
  18. YDDB = [[YDFMDB alloc]init];
  19. });
  20. return YDDB;
  21. }
  22. -(FMDatabase *)jcyDB{
  23. if (!_jcyDB) {
  24. NSString *filePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"jcydb.sqlite"];
  25. NSLog(@"数据库路径:\n %@",filePath);
  26. _jcyDB= [FMDatabase databaseWithPath:filePath];
  27. }
  28. return _jcyDB;
  29. }
  30. -(BOOL)executeUpdateByString:(NSString *)sql{
  31. BOOL isSucc=NO;
  32. [self.jcyDB open];
  33. isSucc=[self.jcyDB executeUpdate:sql];
  34. [self.jcyDB close];
  35. return isSucc;
  36. }
  37. - (BOOL)createTableName:(NSString *)tableName fieldNamesDictionaryArray:(NSArray*)fieldNamesDictionaryArray{
  38. if ( nil==tableName|| [@"" isEqualToString:tableName]) {
  39. NSLog(@"表名错误");
  40. return NO;
  41. }
  42. //创建表 添加id 创建时间 token字段
  43. [self.jcyDB open];
  44. BOOL isSucc = [self.jcyDB executeUpdate:[NSString stringWithFormat:@"create table if not exists %@(myId integer primary key autoincrement,createdTime TEXT NOT NULL);",tableName]];
  45. [self.jcyDB close];
  46. if (isSucc) {
  47. if (!(nil==fieldNamesDictionaryArray||0==fieldNamesDictionaryArray.count)) {
  48. isSucc=[self addFieldNamesDictionaryArray:fieldNamesDictionaryArray withTableName:tableName];
  49. }
  50. }else{
  51. NSLog(@"创建表失败");
  52. }
  53. return isSucc;
  54. }
  55. -(BOOL)dropTableByTableName:(NSString *)tableName{
  56. BOOL isSucc=NO;
  57. if ( nil==tableName|| [@"" isEqualToString:tableName]) {
  58. NSLog(@"表名错误");
  59. return isSucc;
  60. }
  61. [self.jcyDB open];
  62. isSucc=[self.jcyDB executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@",tableName]];
  63. if (!isSucc) {
  64. NSLog(@"删除表失败!请检查:\n(1)表名是否错误(已经被删除的表,或者拼写错误);\n(2)该表被其他表关联(外键);\n(3)其余错误");
  65. }
  66. [self.jcyDB close];
  67. return isSucc;
  68. }
  69. //添加字段
  70. - (BOOL)addFieldNamesDictionaryArray:(NSArray*)fieldNamesDictionaryArray withTableName:(NSString *)tableName{
  71. BOOL isSucc=NO;
  72. if (nil==fieldNamesDictionaryArray||fieldNamesDictionaryArray.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
  73. NSLog(@"缺失字段或者表名");
  74. return isSucc;
  75. }
  76. [self.jcyDB open];
  77. for (NSDictionary *fieldName in fieldNamesDictionaryArray) {
  78. NSString *name=fieldName[@"name"];
  79. NSString *type=fieldName[@"type"];
  80. if (nil==name||nil==type) {
  81. NSLog(@"传入字典数组错误,两个key分别为:name与type");
  82. return isSucc;
  83. }
  84. isSucc= [self.jcyDB columnExists:name inTableWithName:tableName];
  85. if (!isSucc) {
  86. NSString *str = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@;",tableName,name,type];
  87. NSLog(@"添加字段 sql:%@",str);
  88. isSucc= [self.jcyDB executeUpdate:str];
  89. }else{
  90. NSLog(@"有重复字段");
  91. break;
  92. }
  93. if (!isSucc) {
  94. break;
  95. }
  96. }
  97. [self.jcyDB close];
  98. return isSucc;
  99. }
  100. //删除字段
  101. - (BOOL)deleteFieldNamesStringArray:(NSArray*)fieldNamesStringArray withTableName:(NSString *)tableName;{
  102. BOOL isSucc=NO;
  103. if (nil==fieldNamesStringArray||fieldNamesStringArray.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
  104. NSLog(@"缺失字段或者表名");
  105. return isSucc;
  106. }
  107. [self.jcyDB open];
  108. for (NSString *name in fieldNamesStringArray) {
  109. NSString *str = [NSString stringWithFormat:@"ALTER TABLE %@ DROP COLUMN %@;",tableName,name];
  110. isSucc= [self.jcyDB executeUpdate:str];
  111. if (!isSucc) {
  112. NSLog(@"添加字段失败");
  113. break;
  114. }
  115. }
  116. [self.jcyDB close];
  117. return isSucc;
  118. }
  119. //增加数据 传递NSArray(key value)
  120. - (BOOL)addSingleDataStringDictionary:(NSDictionary *)dataStringDictionary withTableName:(NSString *)tableName{
  121. BOOL isSucc=NO;
  122. if (nil==dataStringDictionary||dataStringDictionary.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
  123. NSLog(@"缺失字段或者表名");
  124. return isSucc;
  125. }
  126. NSString *keyStr=[self linkStringBySymbol:@"," withNSArray:[dataStringDictionary allKeys] onBothSides:@""];
  127. NSString *valueStr=[self linkStringBySymbol:@"," withNSArray:[dataStringDictionary allValues] onBothSides:@"'"];
  128. /*************/
  129. if (keyStr && valueStr) {
  130. [self.jcyDB open];
  131. NSString *str=[NSString stringWithFormat:@"insert into %@(%@,%@) values(%@,'%@');",tableName,keyStr,@"createdTime",valueStr,[NSString stringWithFormat:@"%ld", (long)[[NSDate date] timeIntervalSince1970]]];
  132. isSucc=[self.jcyDB executeUpdate:str];
  133. NSLog(@"添加sql:%@ 状态:%@",str,@(isSucc));
  134. [self.jcyDB close];
  135. }else{
  136. NSLog(@"添加失败");
  137. }
  138. return isSucc;
  139. }
  140. //删除数据 key value
  141. - (BOOL)deleteDataByEqualConditionStringDictionary:(NSDictionary *)conditionStringDictionary withTableName:(NSString *)tableName{
  142. BOOL isSucc=NO;
  143. if (nil==tableName || [tableName isEqualToString:@""]) {
  144. NSLog(@"缺失表名");
  145. return isSucc;
  146. }
  147. //连接条件
  148. NSString *conditionString;
  149. NSMutableArray *conditionArray=[[NSMutableArray alloc]init];
  150. for (NSString *key in conditionStringDictionary) {
  151. [conditionArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:conditionStringDictionary[key]]];
  152. }
  153. conditionString=[self linkStringBySymbol:@" and " withNSArray:conditionArray onBothSides:@""];//获取条件
  154. //删除
  155. isSucc=[self deleteDataByConditionString:conditionString withTableName:tableName];
  156. return isSucc;
  157. }
  158. - (BOOL)deleteDataByConditionString:(NSString *)conditionString withTableName:(NSString *)tableName{
  159. BOOL isSucc=NO;
  160. if (nil==tableName || [tableName isEqualToString:@""]) {
  161. NSLog(@"缺失表名");
  162. return isSucc;
  163. }
  164. [self.jcyDB open];
  165. if (conditionString&&![@"" isEqualToString:conditionString]) {
  166. NSString *str=[NSString stringWithFormat:@"delete from %@ where %@;",tableName,conditionString];
  167. isSucc=[self.jcyDB executeUpdate:str];
  168. NSLog(@"有条件删除sql:%@ 状态:%@",str,@(isSucc));
  169. }else{
  170. NSLog(@"无条件删除");
  171. NSString *str=[NSString stringWithFormat:@"delete from %@;",tableName];
  172. NSLog(@"无条件删除sql:%@ 状态:%@",str,@(isSucc));
  173. isSucc=[self.jcyDB executeUpdate:str];
  174. }
  175. [self.jcyDB close];
  176. return isSucc;
  177. }
  178. //更改数据
  179. - (BOOL)changeDataByEqualConditionStringDictionary:(NSDictionary *)conditionStringDictionary withNewStringDictionary:(NSDictionary *)newStringDictionary withTableName:(NSString *)tableName{
  180. BOOL isSucc=NO;
  181. if (nil==newStringDictionary||newStringDictionary.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
  182. NSLog(@"缺失字段或者表名");
  183. return isSucc;
  184. }
  185. //连接新数据
  186. NSString *newDataString;
  187. NSMutableArray *newDataArray=[[NSMutableArray alloc]init];
  188. for (NSString *key in newStringDictionary) {
  189. [newDataArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:newStringDictionary[key]]];
  190. }
  191. newDataString=[self linkStringBySymbol:@"," withNSArray:newDataArray onBothSides:@""];//获取新数据字符串
  192. //连接条件
  193. NSString *conditionString;
  194. NSMutableArray *conditionArray=[[NSMutableArray alloc]init];
  195. for (NSString *key in conditionStringDictionary) {
  196. [conditionArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:conditionStringDictionary[key]]];
  197. }
  198. conditionString=[self linkStringBySymbol:@" and " withNSArray:conditionArray onBothSides:@""];//获取条件
  199. isSucc=[self changeDataByConditionString:conditionString withNewString:newDataString withTableName:tableName];
  200. return isSucc;
  201. }
  202. - (BOOL)changeDataByConditionString:(NSString *)conditionString withNewString:(NSString *)newString withTableName:(NSString *)tableName{
  203. BOOL isSucc=NO;
  204. if (nil==newString||[@"" isEqualToString:newString] || nil==tableName || [tableName isEqualToString:@""]) {
  205. NSLog(@"缺失字段或者表名");
  206. return isSucc;
  207. }
  208. [self.jcyDB open];
  209. if (conditionString&&![@"" isEqualToString:conditionString]) {
  210. NSString *str=[NSString stringWithFormat:@"UPDATE %@ SET %@ where %@;",tableName,newString,conditionString];
  211. isSucc=[self.jcyDB executeUpdate:str];
  212. NSLog(@"有条件修改SQL:%@ 状态:%@",str,@(isSucc));
  213. }else{
  214. NSString *str=[NSString stringWithFormat:@"UPDATE %@ SET %@;",tableName,newString];
  215. isSucc=[self.jcyDB executeUpdate:str];
  216. NSLog(@"无条件修改SQL:%@ 状态:%@",str,@(isSucc));
  217. }
  218. [self.jcyDB close];
  219. return isSucc;
  220. }
  221. //查询数据
  222. - (NSArray *)selectDataByEqualConditionStringDictionary:(NSDictionary *)conditionStringDictionary withSelectStringArray:(NSArray *)selectStringArray withTableName:(NSString *)tableName limit:(NSInteger)limit descID:(BOOL)descID{
  223. NSArray *arr=[[NSArray alloc]init];
  224. if (nil==tableName || [tableName isEqualToString:@""]) {
  225. NSLog(@"缺失表名");
  226. return nil;
  227. }
  228. NSString *conditionString;
  229. NSMutableArray *conditionArray=[[NSMutableArray alloc]init];
  230. for (NSString *key in conditionStringDictionary) {
  231. [conditionArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:conditionStringDictionary[key]]];
  232. }
  233. conditionString=[self linkStringBySymbol:@" and " withNSArray:conditionArray onBothSides:@""];//获取条件
  234. arr=[self selectDataByConditionString:conditionString withSelectStringArray:selectStringArray withTableName:tableName limit:limit descID:descID];
  235. return [arr copy];
  236. }
  237. - (NSArray *)selectDataByConditionString:(NSString *)conditionString withSelectStringArray:(NSArray *)selectStringArray withTableName:(NSString *)tableName limit:(NSInteger)limit descID:(BOOL)descID{
  238. NSMutableArray *arr=[[NSMutableArray alloc]init];
  239. FMResultSet *set;
  240. [self.jcyDB open];
  241. NSString *strDest=@"";
  242. NSString *strLimit=@"";
  243. if (descID) {
  244. strDest=@"order by myId desc";
  245. }
  246. if (limit>0) {
  247. strLimit=[NSString stringWithFormat:@" limit %@",[@(limit) stringValue]];
  248. }
  249. if (conditionString&&![@"" isEqualToString:conditionString]) {
  250. NSString *str=[NSString stringWithFormat:@"select * from %@ where %@ %@ %@;",tableName,conditionString,strDest,strLimit];
  251. set=[self.jcyDB executeQuery:str];
  252. NSLog(@"有条件查询SQL%@ ",str);
  253. }else{
  254. NSString *str=[NSString stringWithFormat:@"select * from %@ %@ %@;",tableName,strDest,strLimit];
  255. set=[self.jcyDB executeQuery:str];
  256. NSLog(@"无条件查询SQL%@ ",str);
  257. }
  258. if (set) {//查询到数据
  259. while ([set next]) {
  260. NSMutableDictionary *dic=[[NSMutableDictionary alloc]init];
  261. for (NSString *key in selectStringArray) {
  262. [dic setValue:[set stringForColumn:key] forKey:key];
  263. }
  264. [arr addObject:dic];
  265. }
  266. }else{
  267. NSLog(@"没有查询到数据");
  268. }
  269. [self.jcyDB close];
  270. return [arr copy];
  271. }
  272. #pragma mark -私有方法
  273. -(NSArray *)addSingleQuotationMarksWithArray:(NSArray *)array{
  274. NSMutableArray *newArray=[[NSMutableArray alloc]init];
  275. for (NSString *str in array) {
  276. NSString *newStr=[NSString stringWithFormat:@"'%@'",str];
  277. [newArray addObject:newStr];
  278. }
  279. return newArray;
  280. }
  281. //利用逗号连接
  282. -(NSString *)linkStringBySymbol:(NSString *)symbol withNSArray:(NSArray *)array onBothSides:(NSString *)bothSymbol{//这里可以默认写入token time等基本信息
  283. NSMutableString *str=[[NSMutableString alloc]initWithString:@""];
  284. for (NSUInteger i=0; i<array.count; i++) {
  285. if (i==0) {
  286. [str appendFormat:@"%@%@%@",bothSymbol,array[i],bothSymbol];
  287. }else{
  288. [str appendFormat:@" %@ %@%@%@",symbol,bothSymbol,array[i],bothSymbol];
  289. }
  290. }
  291. return [str copy];
  292. }
  293. //利用等号连接
  294. -(NSString *)linkStringByEqualSymbolWithKey:(NSString *)key withValue:(NSString *)value{
  295. NSMutableString *str=[[NSMutableString alloc]initWithString:@""];
  296. [str appendFormat:@" %@",key];
  297. [str appendFormat:@"='%@'",value];
  298. return [str copy];
  299. }
  300. @end