//
//  ViewController.m
//  sqlLite
//
//  Created by 麦子 on 15/6/25.
//  Copyright (c) 2015年 麦子. All rights reserved.
//

#import "ViewController.h"
#import <sqlite3.h> // 系统目录下，

@interface ViewController ()

@end

@implementation ViewController{

   sqlite3_stmt *stmt;
}

- (void)viewDidLoad {
    [super viewDidLoad];
     self.title = @"sqlLite学习";
    self.view.backgroundColor = [UIColor whiteColor];
    [self sqlLiteHandle];
}

/****
    数据库操作  和 java的一样，同时也有预处理，只是这些操作都是用C函数来进行处理
 
    都在sqlite3这个包里面。
 
 */
- (void)sqlLiteHandle{
    // 打开数据库
    NSString *path = NSHomeDirectory();
    path = [path stringByAppendingPathComponent:@"mydb.sqlite"];//不存在就创建，然后打开
    NSLog(@"%@",path); // 每次创建都不一样的路径
    sqlite3 *db;
    int result = sqlite3_open([path UTF8String],&db);
    if (result != sqlITE_OK) {
        NSLog(@"数据库打开失败");
    }
    // 操作数据库
    [self createTable:db];
    
    // 删除
    [self tableDel:db];
    
    // 添加
    [self tableAdd:db];
    
    // 修改
    [self tableUpdate:db];
    
    // 查找
    [self tableFind:db];
    
    // 关闭数据库
    sqlite3_finalize(stmt);
    sqlite3_close(db);

}

// 创建表
- (void)createTable:(sqlite3 *)sqliteDb{
   NSString *sql = @"create table if not exists student(name text,sex text,age integer)";
    char *err; // 出错对象
    int result = sqlite3_exec(sqliteDb,[sql UTF8String],NULL,&err);
    if (result == sqlITE_OK) {
        NSLog(@"创建表成功....");
        
    }
}


// 增加
- (void)tableAdd:(sqlite3 *)sqliteDb{
    NSString *sql = @"insert into student (name,sex,age) values ('小强','男',25)";
    char *err; // 出错对象
    int result = sqlite3_exec(sqliteDb,&err);
    if (result == sqlITE_OK) {
        NSLog(@"添加成功....");
    }

}


// 删除
- (void)tableDel:(sqlite3 *)sqliteDb{
    NSString *sql = @"delete from student";
    char *err; // 出错对象
    int result = sqlite3_exec(sqliteDb,&err);
    if (result == sqlITE_OK) {
        NSLog(@"删除成功....");
    }
}


//修改
- (void)tableUpdate:(sqlite3 *)sqliteDb{
    NSString *sql = @"update student set name = '麦子' ";
    char *err; // 出错对象
    int result = sqlite3_exec(sqliteDb,&err);
    if (result == sqlITE_OK) {
        NSLog(@"修改成功....");
    }
}

// 查找
- (NSArray *)tableFind:(sqlite3 *)sqliteDb{
    NSString *sql = @"select * from student";
    
    int result = sqlite3_prepare_v2(sqliteDb,-1,&stmt,NULL);
    if (result == sqlITE_OK) {
        NSLog(@"查询成功....");
        // 循环  重这个结果集里面获取数据，也是以二维数组的形式来获取
        while (sqlite3_step(stmt) == sqlITE_ROW) {
            char *name = (char *)sqlite3_column_text(stmt,0);
            char *sex = (char *)sqlite3_column_text(stmt,1);
            int  age = sqlite3_column_int(stmt,2);
            NSString *strName = [NSString stringWithUTF8String:name];
            NSString *strSex = [NSString stringWithUTF8String:sex];
            NSLog(@"name=%@-->sex=%@-->age=%d",strName,strSex,age);
            
        }
    }
    return nil;
}



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

@end