meimei的专栏

****ios中FMDB第三方数据库实现增删查改****

一、SQLite () 是一个轻量级的关系数据库。iOS SDK很早就支持了SQLite，在使用时，只需要加入 libsqlite3.dylib 依赖以及引入 sqlite3.h 头文件即可。但是，，原生的SQLite API在使用上相当不友好，在使用时，非常不便。于是，开源社区中就出现了一系列将SQLite API进行封装的库，而FMDB (https://github.com/ccgus/fmdb) 则是开源社区中的优秀者。 FMDB是iOS平台的SQLite数据库框架，是以OC的方式封装了SQLite的C语言API。 二、FMDB由三个主要的类 1）FMDatabase 一个FMDatabase对象就代表一个单独的SQLite数据库 用来执行SQL语句 （2）FMResultSet 使用FMDatabase执行查询后的结果集 （3）FMDatabaseQueue 用于在多线程中执行多个查询或更新，它是线程安全的 三、增删改查 1、创建表 //创建一个名为User的表，有两个字段分别为string类型的Name，integer类型的 Age [db executeUpdate:@”CREATE TABLE User (Name text,Age integer)”]; 2、插入数据 //插入数据使用OC中的类型 text对应为NSString integer对应为NSNumber的整形 [db executeUpdate:@”INSERT INTO User (Name,Age) VALUES (?,?)”,@”张三”,[NSNumber numberWithInt:20]]; 3、更新数据 //更新数据 将“张三”更改为“李四” [db executeUpdate:@”UPDATE User SET Name = ? WHERE Name = ? “,@”李四”,@”张三”]; 4、删除数据 //删除数据 [db executeUpdate:@”DELETE FROM User WHERE Name = ?”,@”张三”]; 5、查询数据 //返回数据库中第一条满足条件的结果 NSString *aa=[db stringForQuery:@”SELECT Name FROM User WHERE Age = ?”,@”20”]; //返回全部查询结果 FMResultSet rs=[db executeQuery:@”SELECT FROM User”]; rs=[db executeQuery:@”SELECT * FROM User WHERE Age = ?”,@”20”]; while ([rs next]){ NSLog(@”%@ %@”,[rs stringForColumn:@”Name”],[rs stringForColumn:@”Age”]); } [rs close];

三、具体代码实现。 1、插入数据 -(void)insertSqlData:(NSString )myname andage:(NSString )myage { NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentPath = [paths objectAtIndex:0]; NSString *dbPath = [documentPath stringByAppendingPathComponent:@”goodsdata.db”];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];if (![database open]) {return;}else{NSLog(@”打开数据库成功”);}BOOL insertData = [database executeUpdate:@”insert into goods (name,age)values(?,?)”myname,myage];if (upDate == NO){NSLog(@”数据库更新失败。。。。”);}else{NSLog(@”数据更新成功”);}[database close];

}

2、修改数据 -(void)UpDateSqlData:(NSString )thePiPeiId andAllNumber:(NSString )theAllNumber andAllPrice:(NSString *)theAllPrice { NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentPath = [paths objectAtIndex:0]; NSString *dbPath = [documentPath stringByAppendingPathComponent:@”goodsdata.db”];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];if (![database open]) {return;}else{NSLog(@”打开数据库成功”);}//UPDATE 表名称 SET 字段 = 新值 WHERE 主键 = 值 更新语句BOOL upDate = [database executeUpdate:@”update goods set maxsum=?,AllPrice=? where idstr=?”,theAllNumber,theAllPrice,thePiPeiId];if (upDate == NO){NSLog(@”数据库更新失败。。。。”);}else{NSLog(@”数据更新成功”);}[database close];

} 3、查询数据 //查询数据库的内容 – (void)query { NSLog(@”实现查询”); NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentPath = [paths objectAtIndex:0]; NSString *dbPath = [documentPath stringByAppendingPathComponent:@”goodsdata.db”];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];if (![database open]) {return;}//不需要像Android中那样关闭Cursor关闭FMResultSet，因为相关的数据库关闭时，FMResultSet也会被自动关闭FMResultSet *resultSet = [database executeQuery:@”select * from goods”]; while ([resultSet next]){NSLog(@” yes [resultSet next]”);NSString *gender = [resultSet stringForColumn:@”goodsid”];[dataArray addObject:gender];NSString *name = [resultSet stringForColumn:@”goodsname”];[nameArray addObject:name];NSString *id1 = [resultSet stringForColumn:@”idstr”];[IDArray addObject:id1];NSString *price = [resultSet stringForColumn:@”pricestr”];[priceArray addObject:price];NSString * imgUrl = [resultSet stringForColumn:@”imageUrl”];[imgArray addObject:imgUrl];NSString * GoogsTypeid = [resultSet stringForColumn:@”markprice”];NSString * maxNum = [resultSet stringForColumn:@”maxsum”];NSString * AllpriceSql = [resultSet stringForColumn:@”AllPrice”];NSString * StockSql = [resultSet stringForColumn:@”Stock”];//库存//从数据库里面取出数据SQlBase = [[BaseProct alloc]initGoodsIdstr:gender andGoodsName:name andIdStr:id1 andMarketPrice:GoogsTypeid andMaxSum:maxNum andPrice:price andProductIdStr:StockSql andProperty1:AllpriceSql andProperty2:imgUrl andstock:nil];[SqlBaseArr addObject:SQlBase];}[database close];//这里也不需要release

}

4、删除数据 -(BOOL)DeteSqlData:(NSString *)thePipeiId { NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentPath = [paths objectAtIndex:0]; NSString *dbPath = [documentPath stringByAppendingPathComponent:@”goodsdata.db”];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];if (![database open]) {return NO;}else{NSLog(@”打开数据库成功”);}BOOL delete = [database executeUpdate:@”delete from goods where idstr = ?”,thePipeiId];[database close];return delete;

}

demo下载链接：

会让你的心态更平和更坦然，也会让你心无旁骛，更会让你的心灵得到解脱和抚慰。