IOS 学习笔记 2015-04-09 0C-SQLite 数据存储

//
//  ViewController.m
//  SQLiteDemo
//
//  Created by wangtouwang on 15/4/9.
//  Copyright (c) 2015年 wangtouwang. All rights reserved.
//

#import "ViewController.h"
#import "WPUser.h"

#define DBNAME @ "mysqlite.sqlite"


@interface ViewController ()
{
    sqlite3 *db;
}
@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    
    [self createSqlDBOrOpenDB];
    
    [self createTable];
    
//    for (int index=0; index<30; index++) {
//         [self insertIntoData];
//    }

    [self updateData:[self searchAllData]];
    
    
}


#pragma mark 更新数据 
-(void)updateData:(NSMutableArray *)array{
    NSString *sql = @"update user set name=?,age=? where id=?";
    sqlite3_stmt *stmt;
    for (int index=0; index<array.count; index++) {
        WPUser *user =  (WPUser *)array[index];
        user.name=@"张建华";
        user.age=31;
        if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL)==SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, [user.name UTF8String], -1, NULL);
            sqlite3_bind_int(stmt, 2, user.age);
            sqlite3_bind_int(stmt, 3, user.ID);
            if (sqlite3_step(stmt)==SQLITE_DONE) {
                NSLog(@"更新成功");
            }
        }
    }
}

#pragma mark 读取数据 
-(NSMutableArray *)searchAllData{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"select id,name,age from user";
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL)==SQLITE_OK) {
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            NSInteger ID =  sqlite3_column_int(stmt, 0);
             char *name = (char*)sqlite3_column_text(stmt, 1);
           NSString *uname = [[NSString alloc] initWithUTF8String:name];
            NSInteger age = sqlite3_column_int(stmt, 2);
            NSLog(@"ID=%lu NAME=%s AGE=%lu",ID,name,age);
            WPUser *user = [WPUser initUserName:uname Age:age];
            user.ID=ID;
            [array addObject:user];
        }
    }
     sqlite3_close(db);
    return array;
}



#pragma mark 新增数据
-(void)insertIntoData{
    NSString *sql = @"insert into user(name,age) values(?,?)";
    WPUser *user = [WPUser initUserName:@"JACK" Age:11];
    sqlite3_stmt *stmt;
    //插入数据前检测语法是否正确
    if ((sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL))==SQLITE_OK ) {
        //绑定参数 参数2:为序号（从1开始），参数3:为字符串值，参数4 为字符串长度。sqlite3_bind_text的第五个参数为一个函数指针
        sqlite3_bind_text(stmt, 1, [user.name UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 2,user.age);
        //真正执行插入 sqlite3_step
        if (sqlite3_step(stmt)==SQLITE_DONE) {
            NSLog(@"INSERT INTO SUCCESS");
        }else{
            NSLog(@"INSERT INTO ERROR");
        }
    }
}

#pragma mark 创建表 
-(void)createTable{
    NSString *sql = @"create table if not exists user (id integer primary key autoincrement,name text,age integer);";
    char *errormsg;
    //执行创建语句 sqlite3_exex 是函数 参数一:数据库的句柄（db），参数二: 为sql语句，参数三 回调参数，是一个指向函数的指针，如果把callback前面的*改成^则就是一个block代码段，
                                //参数四 可以写NULL，第五个参数为错误信息
    //在这里创建需要先判断表是否已存在
    int result = sqlite3_exec(db, [sql UTF8String], NULL, NULL, &errormsg);
    if (result==SQLITE_OK) {
        NSLog(@"创建成功");
    }else{
        NSLog(@"创建失败 愿因是:%s",errormsg);
    }
}


#pragma mark 创建数据库实例或者打开数据实例
-(void)createSqlDBOrOpenDB{
    //获取 项目 Doucment文件目录地址
    NSString  *doc =  [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSLog(@"%@",doc);
    //创建 SQLITE 文件或者获取该文件路径
    NSString *DB_PATH = [doc stringByAppendingPathComponent:DBNAME];
    NSLog(@"%@",DB_PATH);
    //打开或者创建DB实例 这里用到C里面的语法和指针
    int result = sqlite3_open([DB_PATH UTF8String], &db);//sqlite3_open方法名 参数 const filename DB文件路径,第二个参数就是数据库实例化对象 返回参数 int 类型
    if(result!=SQLITE_OK){
        sqlite3_close(db);//关闭实例化
        NSLog(@"打开数据哭实例化对象失败");
    }else{
        NSLog(@"CREATE SUCCESS");
    }
}

@end

//
//  WPUser.m
//  SQLiteDemo
//
//  Created by wangtouwang on 15/4/9.
//  Copyright (c) 2015年 wangtouwang. All rights reserved.
//

#import "WPUser.h"

@implementation WPUser

-(instancetype)initUserName:(NSString *)name Age:(NSInteger)age{
    if (self= [super init]) {
        self.name = name;
        self.age = age;
    }
    return self;
}

+(instancetype)initUserName:(NSString *)name Age:(NSInteger)age{
    return [[WPUser alloc] initUserName:name Age:age];
}

@end