iOS Sqlite3 Demo 及 FMDB Demo
本文是主要实现了三个函数：
- testSQLite3 是测试系统自带的sqlite3的demo
- testFMDB是测试FMDB存取简单的数据类型的 的demo
- testFMDB2是将任意对象作为一个整体存入到FMDB的Demo
首先先定义了一个Person类，实现了<NSCoding>协议，对Person对象进行字段存取和整体存取
1 //Person.h 2 #import <Foundation/Foundation.h> 3 4 @interface Person : NSObject<NSCoding> 5 @property (nonatomic ,assign)int id; 6 @property (nonatomic ,copy) NSString *name; 7 @property (nonatomic ,assign)int age; 8 @end 9 10 //Person.m 11 #import "Person.h" 12 13 @implementation Person 14 -(NSString *)description{ 15 return [NSString stringWithFormat:@"id:%d--name:%@--age:%d",self.id,self.name,self.age]; 16 } 17 -(instancetype)initWithCoder:(NSCoder *)decoder{ 18 if (self = [super init]) { 19 self.id = [decoder decodeIntForKey:@"id"]; 20 self.name = [decoder decodeObjectForKey:@"name"]; 21 self.age = [decoder decodeIntForKey:@"age"]; 22 } 23 return self; 24 } 25 -(void)encodeWithCoder:(NSCoder *)encoder{ 26 [encoder encodeInt:self.id 27 forKey:@"id"]; 28 [encoder encodeObject:self.name forKey:@"name"]; 29 [encoder encodeInt:self.age forKey:@"age"]; 30 } 31 @end
1 -(void)testSQLite3{ 2 //1. 打开(建立)数据库 3 NSString *cacheDir = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject]; 4 NSString *filename = [cacheDir stringByAppendingPathComponent:@"test.sqlite"]; 5 sqlite3 * db = nil; 6 7 //0表示成功，1表示失败 8 sqlite3_open(filename.UTF8String, &db); 9 if (!db) { 10 NSLog(@"数据库打开失败，建立失败"); 11 return; 12 } 13 char *errmsg = nil; 14 //2.一般用来执行无返回结果的sql 命令,（可以执行任何SQL语句） 15 //创建数据库 16 char * createTableSql = "create table t_person(id integer primary key,name text not null unique,age integer);"; 17 int rs = sqlite3_exec(db, createTableSql, NULL, NULL, &errmsg); 18 19 if (errmsg) { 20 NSLog(@"%s--errorCode:%d",errmsg,rs); 21 } 22 //3.插入数据 23 for(NSUInteger i = 0;i < 100;i ++){ 24 NSString *insertSql = [NSString stringWithFormat:@"insert into t_person(name,age) values('小hong%lu',%d);",(unsigned long)i,arc4random()%100+10]; 25 char *errmsg = nil; 26 sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, &errmsg); 27 if (errmsg) { 28 NSLog(@"insert error:%s",errmsg); 29 } 30 } 31 //4.查询数据 尽量使用const char * 不要用NSString ,容易出错 32 const char* selectSql = "select * from t_person where name like '小明%' and age < 20 order by age desc limit 1,10"; 33 sqlite3_stmt * stmt = nil; 34 sqlite3_prepare(db, selectSql, (int)strlen(selectSql), &stmt, NULL); 35 if (stmt) { 36 while (sqlite3_step(stmt) == SQLITE_ROW) { 37 Person *person = [[Person alloc]init]; 38 person.id = sqlite3_column_int(stmt, 0); 39 person.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)]; 40 person.age = sqlite3_column_int(stmt, 2); 41 NSLog(@"%@",person); 42 } 43 }
1 -(void)testFMDB{ 2 NSString *cacheDir = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject]; 3 NSString *filename = [cacheDir stringByAppendingPathComponent:@"new.sqlite"]; 4 //1.建立数据库对象，一个数据库对应一个数据库对象 5 FMDatabase * db = [FMDatabase databaseWithPath:filename]; 6 //2.打开数据库 7 if (![db open]) { 8 NSLog(@"数据库连接或者创建失败"); 9 return; 10 } 11 //3. 建表 12 NSString *createSql = @"create table if not exists t_person(id integer primary key,name text not null unique,age integer);"; 13 if (![db executeUpdate:createSql]) { 14 NSLog(@"error:%@",[db lastErrorMessage]); 15 } 16 //4.插入数据 17 for (int i = 0; i< 100; i++) { 18 //fmdb 最好自己把你的sql字符串用NSString写出来 19 NSString * insertSql = [NSString stringWithFormat:@"insert into t_person(name,age) values('xiaoming%d',%d)",i,arc4random()%100]; 20 if (![db executeUpdate:insertSql]) { 21 NSLog(@"error:%@",[db lastErrorMessage]); 22 } 23 } 24 //5.修改数据 25 NSString *updateStr = [NSString stringWithFormat:@"update t_person set age = 1000"]; 26 if (![db executeUpdate:updateStr]) { 27 NSLog(@"error:%@",[db lastErrorMessage]); 28 } 29 //6. 删除数据 30 NSString *deleteStr= [NSString stringWithFormat:@"delete from t_person where id =1;"]; 31 if (![db executeUpdate:deleteStr]) { 32 NSLog(@"error:%@",[db lastErrorMessage]); 33 } 34 //7.查询数据 35 NSString *querySql = [NSString stringWithFormat:@"select * from t_person"]; 36 FMResultSet * set = [db executeQuery:querySql]; 37 while (set.next) { 38 Person *person = [[Person alloc]init]; 39 person.id = [set intForColumn:@"id"]; 40 person.name = [set stringForColumn:@"name"]; 41 person.age = [set intForColumn:@"age"]; 42 NSLog(@"%@",person); 43 } 44 }
1 //存取任意对象 2 -(void)testFMDB2{ 3 NSString *cacheDir = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject]; 4 NSString *filename = [cacheDir stringByAppendingPathComponent:@"Person.sqlite"]; 5 //1.建立数据库对象，一个数据库对应一个数据库对象 6 FMDatabase * db = [FMDatabase databaseWithPath:filename]; 7 //2.打开数据库 8 if (![db open]) { 9 NSLog(@"数据库连接或者创建失败"); 10 return; 11 } 12 //3. 建表 13 NSString *createSql = @"create table if not exists t_persons(id integer primary key,person blob not null unique,age integer);"; 14 if (![db executeUpdate:createSql]) { 15 NSLog(@"error:%@",[db lastErrorMessage]); 16 } 17 //4.插入数据 18 for (int i = 0; i< 100; i++) { 19 //将对象作为一个整体存入到数据库中 20 Person *person = [[Person alloc]init]; 21 person.name = [NSString stringWithFormat:@"xiaoming%d",i]; 22 person.age = arc4random()%100; 23 person.id = arc4random(); 24 NSData *data = [NSKeyedArchiver archivedDataWithRootObject:person]; 25 //插入blob 数据一定要使用excuteUpdateWithFormat:， 直接用executeUpdate:会报错的，因为你的数据使用NSLog打印出来会带有<>符号 26 if (![db executeUpdateWithFormat:@"insert into t_persons(person,age) values(%@,%d)",data,person.age]) { 27 NSLog(@"error:%@",[db lastErrorMessage]); 28 } 29 } 30 //5.修改数据 31 NSString *updateStr = [NSString stringWithFormat:@"update t_persons set age = 1000"]; 32 if (![db executeUpdate:updateStr]) { 33 NSLog(@"error:%@",[db lastErrorMessage]); 34 } 35 //6. 删除数据 36 NSString *deleteStr= [NSString stringWithFormat:@"delete from t_persons where id =1;"]; 37 if (![db executeUpdate:deleteStr]) { 38 NSLog(@"error:%@",[db lastErrorMessage]); 39 } 40 //7.查询数据 41 NSString *querySql = [NSString stringWithFormat:@"select * from t_persons "]; 42 FMResultSet * set = [db executeQuery:querySql]; 43 while (set.next) { 44 NSData *data = [set dataForColumn:@"person"]; 45 // NSLog(@"%@",data); 46 Person *person = [NSKeyedUnarchiver unarchiveObjectWithData:data]; 47 person.id = [set intForColumn:@"id"]; 48 NSLog(@"%@",person); 49 } 50 51 }
