iOS - FMDB存储数组

1.创建数据库

NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *filePath = [[path lastObject] stringByAppendingPathComponent:@"yourName.sqlite"];

//创建数据库
FMDatabase *db = [FMDatabase databaseWithPath:filePath];

//打开数据库
 if ([db open]) {
    NSLog(@"打开数据库成功");
}else{
    NSLog(@"打开数据库失败");
}

2.建表

/*
     如果t_student这个表不存在就创建，并且data不能为null，id为自增的主键
*/
NSString *createTable = @"create table if not exists t_student (id integer primary key autoincrement,data blob not null)";
[db executeUpdate:createTable];

不同类型的判空

//字符串name不能为空
name text not null
//数据类型age不能为空
age integer not null
// 对象类型data不能为空
data blob not null

对数据库中存储的每一个值都有一个类型
NULL 这个值为空值
INTEGER 值被标识为整数，依据值的大小可以依次被存储1～8个字节
REAL 所有值都是浮动的数值
TEXT 值为文本字符串
BLOB 值为blob数据

3.插入数据

/*
    向数据库的t_student这个表中的name和age两个字段插入数据
*/
  NSArray *ary = @[
        @{@"name":@"porsche",@"age":@"22"},
        @{@"name":@"kinn",@"age":@"29"},
 ];
    

1）存储字符串

for (NSDictionary *dic in ary) {
    NSString *insertData = @"insert into t_student (name,age) values (?,?)";
    [db executeUpdate:insertData,dic[@"name"],dic[@"age"]];
}

2）存储数组

NSError *err = nil;
NSData *jsonData = [NSJSONSerialization dataWithJSONObject:ary options:NSJSONWritingPrettyPrinted error:&err];
NSString *jsonStr = [[NSString alloc] initWithData:jsonData encoding:NSUTF8StringEncoding];
NSString *insertData = @"insert into t_student (data) values (?)";
[db executeUpdate:insertData,jsonStr];

4.查询数据

1）查询字符串

NSString *sql = @"select id,name,age FROM t_student";
FMResultSet *rs = [db executeQuery:sql];

NSMutableArray *saveAry = [@[] mutableCopy];

 while ([rs next]) {
    int id = [rs objectForColumnIndex:0];
    NSString *name = [rs stringForColumnIndex:1];
    NSString *age = [rs stringForColumnIndex:2];
    NSDictionary *studentDict = [NSDictionary dictionaryWithObjectsAndKeys: name, @"name",age,@"age", nil];
    [saveAry addObject:studentDic];
}

2）查询数组

NSString *sql = @"select * FROM t_student";
FMResultSet *rs = [db executeQuery:sql];
NSMutableArray *saveAry = [@[] mutableCopy];
while ([rs next]) {
    int id = [rs objectForColumnIndex:0];
    id data = [rs objectForColumnIndex:1];
    [saveAry addObject:data];
}

3）数组去重

NSSet *set = [NSSet setWithArray:saveAry];
NSArray *newArray = [set allObjects];

5.删除表

if (![db open]) {
    return;
}
NSString *deleteSql = @"DELETE FROM t_student";
BOOL res = [db executeUpdate:deleteSql];
if (res) {
  NSLog(@"删除成功");
}else{
  NSLog(@"删除失败");
}