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

Person

 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     }

testSQLite3

 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 }

testFMDB

 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 }

testFMDB2

转载于:https://www.cnblogs.com/peaceWang/p/5150121.html