FMDB 是对SQlite的封装，面向对象
FMDatabase： 用来执行(增、删、改、查)的SQL语句
FMResultSet：表示在' <FMDatabase> '上执行查询的结果
使用步骤分为：创建数据库——创建表——插入数据——修改数据——查询数据——删除
1、创建并打开数据库
- (void)openSqlite {
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *path = [docPath stringByAppendingPathComponent:@"Person.fmdb"];
self.db = [FMDatabase databaseWithPath:path];
[self.db open];
}
2、创建表：使用runtime获取自定义类对象的所有成员变量，依次赋值，由于调用了C语言的API，需要手动释放free()
-(void)createTableWithClass:(Class)clazz
{
[self openSqlite];
NSString *sql = @"CREATE TABLE IF NOT EXISTS ";
const char *tableName = class_getName(clazz);
sql = [sql stringByAppendingString:[[NSString alloc] initWithUTF8String:tableName]];
// sql = [sql stringByAppendingString:@"(ID INTEGER PRIMARY KEY AUTOINCREMENT,"];
sql = [sql stringByAppendingString:@" ("];
unsigned int count = 0;
objc_property_t *propertys = class_copyPropertyList(clazz, &count);
for (int i=0; i<count; i++)
{
const char *propertyName = property_getName(propertys[i]);
NSString *key = [NSString stringWithUTF8String:propertyName];
sql = [sql stringByAppendingString:[NSString stringWithFormat:@"%@ TEXT NOT NULL",key]];
if(i!=(count-1))
{
sql = [sql stringByAppendingString:@","];
}
}
free(propertys);
sql = [sql stringByAppendingString:@")"];
BOOL res = [self.db executeUpdate:sql];
if (res) {
NSLog(@"建表成功");
}else {
NSLog(@"建表失败");
}
}
3、插入一个自定义对象
obj为自定义的模型对象
-(void)insertWithObj:(id)obj
{
if (![self.db open]) {
return;
}
const char *tableName = class_getName([obj class]);
NSString *tableNameStr =[[NSString alloc] initWithUTF8String:tableName];
NSString *sql = [NSString stringWithFormat:@"insert into %@ (",tableNameStr];
NSString *sqlValues =@"(";
unsigned int count = 0;
objc_property_t *propertys = class_copyPropertyList([obj class], &count);
for (int i=0; i<count; i++)
{
const char *propertyName = property_getName(propertys[i]);
NSString *key = [NSString stringWithUTF8String:propertyName];
sql = [sql stringByAppendingString:[NSString stringWithFormat:@"%@",key]];
sqlValues =[sqlValues stringByAppendingString:[NSString stringWithFormat:@"'%@'",[obj valueForKey:key]]];
if(i!=(count-1))
{
sql = [sql stringByAppendingString:@","];
sqlValues = [sqlValues stringByAppendingString:@","];
}
}
sql = [NSString stringWithFormat:@"%@) values %@)",sql,sqlValues];
free(propertys);
BOOL res = [self.db executeUpdate:sql];
if (res) {
NSLog(@"插入数据成功");
}else {
NSLog(@"插入数据失败");
}
}
4、更新一条数据：
obj为表中存储的模型对象
keyName为模型对象的属性
value为模型对象中属性对应的值
-(void)updateWithObj:(id)obj andKey:(NSString*)keyName isEqualValue:(NSString*)value
{
if (![self.db open]) {
return;
}
const char *tableName = class_getName([obj class]);
NSString *tableNameStr =[[NSString alloc] initWithUTF8String:tableName];
NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET ",tableNameStr];
unsigned int count = 0;
objc_property_t *propertys = class_copyPropertyList([obj class], &count);
for (int i=0; i<count; i++)
{
const char *propertyName = property_getName(propertys[i]);
NSString *key = [NSString stringWithUTF8String:propertyName];
if(![keyName isEqualToString:key])
{
sql = [sql stringByAppendingString:[NSString stringWithFormat:@"%@ = '%@'",key,[obj valueForKey:key]]];
sql = [sql stringByAppendingString:@","];
}
}
sql = [sql substringToIndex:sql.length-1];
free(propertys);
if (keyName!=nil)
{
sql = [sql stringByAppendingString:@" WHERE "];
sql = [NSString stringWithFormat:@"%@ %@ = '%@' ",sql,keyName,value];
}
BOOL res = [self.db executeUpdate:sql];
if (res) {
NSLog(@"更新数据成功");
}else {
NSLog(@"更新数据失败");
}
}
5、根据条件删除记录:
params示例：@"ID = 1"
-(void)deleteOneRecordWithClass:(Class)clazz params:(NSString*)params
{
if (params==nil)
{
params = @" 1=1";
}
const char *tableName = class_getName(clazz);
NSString *tableNameStr =[[NSString alloc] initWithUTF8String:tableName];
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@",tableNameStr,params];
BOOL res = [self.db executeUpdate:sql];
if (res) {
NSLog(@"删除成功");
}else {
NSLog(@"删除失败");
}
}
6、查询当前表中所有数据
-(NSMutableArray *)selectAllWithClass:(Class)clazz
{
if (![self.db open]) {
return [NSMutableArray array];
}
NSMutableArray *data = [NSMutableArray array];
const char *tableName = class_getName(clazz);
NSString *tableNameStr =[[NSString alloc] initWithUTF8String:tableName];
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE 1=1",tableNameStr];
FMResultSet *rs = [self.db executeQuery:sql];
unsigned int count = 0;
objc_property_t *propertys = class_copyPropertyList(clazz, &count);
while ([rs next]) {
id obj = [[clazz alloc] init];
for (int i=0; i<count; i++) {
const char *propertyName = property_getName(propertys[i]);
NSString *keyStr = [NSString stringWithUTF8String:propertyName];
NSString *valueStr = [rs stringForColumn:keyStr];
if (valueStr) {
[obj setValue:valueStr forKeyPath:keyStr];
}
}
[data addObject:obj];
}
free(propertys);
return data;
}
使用示例：
1、新建FmdbTool单例类包含以上方法，管理数据
2、自定义模型PersonVO
代码：
//创建数据库和表
[[FmdbTool shareManager] createTableWithClass:[PersonVO class]];
NSMutableArray *temArr = [NSMutableArray array];
for (int i=0; i<5; i++) {
PersonVO *p = [PersonVO new];
p.goodsName = @"魏无羡";
p.shopNo = @"123";
p.ID = [NSString stringWithFormat:@"%d",i];
//插入数据
[[FmdbTool shareManager] insertWithObj:p];
[temArr addObject:p];
}
PersonVO *p1 = temArr[1];
p1.goodsName = @"你好";
p1.shopNo = @"111";
//更新temArr中的第2条数据
[[FmdbTool shareManager] updateWithObj:p1 andKey:@"ID" isEqualValue:p1.ID];
//删除ID==4的数据
[[FmdbTool shareManager] deleteOneRecordWithClass:[PersonVO class] params:@"ID = 4"];
NSMutableArray *arr = [[FmdbTool shareManager] selectAllWithClass:[PersonVO class]];
for (PersonVO *p in arr) {
NSLog(@"获取ID--%@,shopNo--%@,goodsName--%@",p.ID,p.shopNo,p.goodsName);
}
打印：
2022-03-03 09:27:04.139699+0800 OCTest[2163:36965] 建表成功
2022-03-03 09:27:04.141687+0800 OCTest[2163:36965] 插入数据成功
2022-03-03 09:27:04.143948+0800 OCTest[2163:36965] 插入数据成功
2022-03-03 09:27:04.146022+0800 OCTest[2163:36965] 插入数据成功
2022-03-03 09:27:04.148426+0800 OCTest[2163:36965] 插入数据成功
2022-03-03 09:27:04.150843+0800 OCTest[2163:36965] 插入数据成功
2022-03-03 09:27:04.152985+0800 OCTest[2163:36965] 更新数据成功
2022-03-03 09:27:04.154903+0800 OCTest[2163:36965] 删除成功
2022-03-03 09:27:04.155475+0800 OCTest[2163:36965] 获取ID--0,shopNo--123,goodsName--魏无羡
2022-03-03 09:27:04.155674+0800 OCTest[2163:36965] 获取ID--1,shopNo--111,goodsName--你好
2022-03-03 09:27:04.155839+0800 OCTest[2163:36965] 获取ID--2,shopNo--123,goodsName--魏无羡
2022-03-03 09:27:04.155984+0800 OCTest[2163:36965] 获取ID--3,shopNo--123,goodsName--魏无羡
