IOS -- Sqlite

  每一个应用都涉及到了数据库，而数据库的方法，又有好几种不同的方法。

 在使用Sqlite的时候，一条一条的写，岂不是很麻烦？

  回答，充满鄙视：：这还叫麻烦？

  哈哈……言归正传。。今天回顾一下，以前讲的Sqlite。。

  首先，先创建一个表。。在这里呢，我是下载的火狐浏览器的一个SQLite Manager。要下载的时候，点击火狐文件夹里面的“工具”===》“附加组件”===》搜索“SQlite”===》点击安装。下次再打开工具的时候，就有SQLite Manager这一项了。

  点击SQLite Manager，在里面创建一个新表，再定义几个属性，比如”pID,Name.Age,addess等，它就会自动地创建好一个表。。创建的时候，会选择一个保存的地点，这时候填写，xCode的文件就可以。创建好以后，将它引入到项目里面。。

  首先，简历一个关于数据库的文件夹。用来控制数据库。。。

#import <Foundation/Foundation.h>
#import "sqlite3.h"


@interface DBHelper : NSObject
//打开数据库，需要传值，所以有返回值
+(sqlite3 *)open;

//关闭数据库
+(void)close;

@end

#import "DBHelper.h"


@implementation DBHelper
//创建全局的sqlite对象
static sqlite3 * db = nil;
+(sqlite3 *)open{
    //打开之前，进行判断。如果存在，直接返回；否则创建新的
    if (db) {
        return db;
    }
    //获取Person.sqlite的文件路径
    NSArray * document = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString * documentPath = [document objectAtIndex:0];
    NSString * path = [documentPath stringByAppendingPathComponent:@"Person.sqlite"];
    
    //获取bundle下的Person.sqlite
    NSString * sourcePath = [[NSBundle mainBundle]pathForResource:@"Person" ofType:@"sqlite"];
    
    NSFileManager * fileManager = [NSFileManager defaultManager];
    BOOL success = YES;
    NSError * error = nil;
    //将bundle下的person.sqlite拷贝到document下面
    if (![fileManager fileExistsAtPath:path]) {
        success = [fileManager copyItemAtPath:sourcePath toPath:path error:&error];
        if (success == NO) {
            NSLog(@"error = %@",[error localizedDescription]);
        }
    }
    if (success == YES) {
        //将NSString 转化成C语言
        sqlite3_open([path UTF8String], &db);
        //创建当前的表，以备更新
        const char * sqlForCreatePerson = "CREAT TABLE Person(ID INTEGER PRIMARY KEY NOT NULL,Name TEXT,Age INTEGER,address TEXT)";
        char * errorMsg = nil;
        sqlite3_exec(db, sqlForCreatePerson, NULL, NULL, &errorMsg);
    }
    return db;
    
    
}

+(void)close{
    if (db) {
        sqlite3_close(db);
        db = nil;
    }
}



@end

然后建立一个和数据库相关的文件，也就是在火狐浏览器上弄得那个东西一致，。。尤其是各个属性的名字，应该写一样。否则，编译的时候，就会找不到那个类。。

#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "DBHelper.h"

@interface Person : NSObject
@property(nonatomic, assign)int pID;
@property(nonatomic, retain)NSString * name;
@property(nonatomic,assign)int age;
@property(nonatomic, retain)NSString * address;

+(BOOL)addPerson:(Person *)per;
+(NSMutableArray *)findAllPersons;
+(BOOL)deletePerson:(int)pID;
+(BOOL)updatePerson:(Person *)per;




@end

#import "Person.h"

@implementation Person

@synthesize pID;
@synthesize name;
@synthesize age;
@synthesize address;

//插入一条数据
+(BOOL)addPerson:(Person *)per{
    //打开
    sqlite3 * db = [DBHelper open];
    sqlite3_stmt * stmt;//定义游标
    BOOL success = NO;
    //执行insert语句
    const char * insertPerson = "insert into Person(ID,Name,Age,address)values(?,?,?,?)";
    int result = sqlite3_prepare(db, insertPerson, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //进行参数绑定
        sqlite3_bind_int(stmt, 1, per.pID);
        sqlite3_bind_text(stmt, 2, [per.name UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 3, per.age);
        sqlite3_bind_text(stmt, 4, [per.address UTF8String], -1, NULL);
        sqlite3_step(stmt);
        
        sqlite3_finalize(stmt);//释放stmt
        [DBHelper close];
        success = YES;
    }
    NSLog(@"数据插入%d",success);
    return success;
}


+(NSMutableArray *)findAllPersons{
    sqlite3 * db = [DBHelper open];
    sqlite3_stmt * stmt = nil;
    int result = sqlite3_prepare(db, "select * from Person", -1, &stmt, NULL);
    if (SQLITE_OK == result) {
        NSMutableArray * arr = [NSMutableArray arrayWithCapacity:0];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            Person * per = [[Person alloc]init];
            int pID = sqlite3_column_int(stmt, 0);
            const char * name = (const char *)sqlite3_column_text(stmt, 1);
            int age = sqlite3_column_int(stmt, 2);
            const char * address = (const char *)sqlite3_column_text(stmt, 3);
            
            per.pID = pID;
            per.name = [NSString stringWithUTF8String:name];
            per.age = age;
            per.address = [NSString stringWithUTF8String:address];
            [arr addObject:per];
        }
        NSLog(@"数据读取成功！");
        sqlite3_finalize(stmt);
        [DBHelper close];
        return arr;
    }
    else{
        [DBHelper close];
        return  nil;
    }
}

+(BOOL)deletePerson:(int)pID{
    sqlite3 * db = [DBHelper open];
    sqlite3_stmt * stmt = nil;
    BOOL success = NO;
    int result = sqlite3_prepare(db, "delete from Person where ID = ?", -1, &stmt, NULL);
    if (SQLITE_OK == result) {
        sqlite3_bind_int(stmt, 1, pID);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
        success = YES;
        NSLog(@"删除成功");
    }
    [DBHelper close];
    return success;
    
}

+(BOOL)updatePerson:(Person *)per{
    sqlite3 * db = [DBHelper open];
    sqlite3_stmt * stmt = nil;
    BOOL success = NO;
    int result = sqlite3_prepare(db, "update Person set Name = ?,Age = ?,address = ?where ID = ?", -1, &stmt, NULL);
    if (SQLITE_OK == result) {
        sqlite3_bind_text(stmt, 1, [per.name UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 2, per.age);
        sqlite3_bind_text(stmt, 3, [per.address UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 4, per.pID);
        
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
        success = YES;
        NSLog(@"数据修改成功!");
    }
    [DBHelper close];
    return success;
}

@end

这样，就可以通过调用这个类，来调用类里面的数据库。。

环境都搭好了，可以创建一个viewController了。。

#import <UIKit/UIKit.h>
#import "Person.h"

@interface RootViewController : UIViewController

@end

#import "RootViewController.h"

@interface RootViewController ()

@end

@implementation RootViewController

static int perIndex = 1;


- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
    }
    return self;
}

- (void)viewDidLoad
{
    [super viewDidLoad];
	UIButton * button = [UIButton buttonWithType:UIButtonTypeRoundedRect];
    [button setTitle:@"Insert" forState:UIControlStateNormal];
    button.frame = CGRectMake(50, 50, 100, 50);
    [button addTarget:self action:@selector(insertOnePerson:) forControlEvents:UIControlEventTouchUpInside];
    [self.view addSubview:button];
    
    button = [UIButton buttonWithType:UIButtonTypeRoundedRect];
    [button setTitle:@"FindAll" forState:UIControlStateNormal];
    button.frame = CGRectMake(180, 50, 100, 50);
    [button addTarget:self action:@selector(findAllPerson:) forControlEvents:UIControlEventTouchUpInside];
    [self.view addSubview:button];
    
    button = [UIButton buttonWithType:UIButtonTypeRoundedRect];
    [button setTitle:@"DeleteOne" forState:UIControlStateNormal];
    button.frame = CGRectMake(50, 200, 100, 50);
    [button addTarget:self action:@selector(deletePerson:) forControlEvents:UIControlEventTouchUpInside];
    [self.view addSubview:button];
    
    button = [UIButton buttonWithType:UIButtonTypeRoundedRect];
    [button setTitle:@"UpdateOne" forState:UIControlStateNormal];
    button.frame = CGRectMake(180, 200, 100, 50);
    [button addTarget:self action:@selector(updatePerson:) forControlEvents:UIControlEventTouchUpInside];
    [self.view addSubview:button];
}

-(void)insertOnePerson:(UIButton *)aBtn{
    Person * per = [[Person alloc]init];
    per.pID = perIndex;
    per.name = [NSString stringWithFormat:@"kenvin_%d",perIndex];
    per.age = 22 + perIndex;
    per.address = [NSString stringWithFormat:@"bokan_%d",perIndex];
    [Person addPerson:per];
    perIndex ++;
}

-(void)findAllPerson:(UIButton *)aBtn{
    NSMutableArray * arr = [Person findAllPersons];
    for (Person * per  in arr) {
        NSLog(@"per.ID = %d",per.pID);
        NSLog(@"per.Name = %@",per.name);
    }
}

-(void)deletePerson:(UIButton *)aBtn {
    NSLog(@"id = %d",perIndex);
    [Person deletePerson:perIndex];
    perIndex++;
}

-(void)updatePerson:(UIButton *)aBtn{
    Person * per = [[Person alloc]init];
    per.pID = 1;
    per.name = @"BackHam";
    per.age = 39;
    per.address = @"England";
    [Person updatePerson:per];
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end

这样运行就可以成功了。。。

2013-08-01 10:18:19.251 Sqlite_exercise[586:c07] 数据插入0
2013-08-01 10:18:20.883 Sqlite_exercise[586:c07] 数据读取成功！
2013-08-01 10:18:20.884 Sqlite_exercise[586:c07] per.ID = 1
2013-08-01 10:18:20.884 Sqlite_exercise[586:c07] per.Name = tang
2013-08-01 10:18:22.827 Sqlite_exercise[586:c07] id = 2

数据库很重要啊。。感谢我昨天晚上的辛勤劳动，一定要再接再厉哦！