//

//  ViewController.m

//  sqlitedemo

//

//  Created by lam_TT on 15-4-11.

//  Copyright (c) 2015年 lam_TT. All rights reserved.

//

#import "ViewController.h"

#import <sqlite3.h>

@interface ViewController () {

     sqlite3 *_db;

}

@end

@implementation ViewController

- (void)viewDidLoad {

    [super viewDidLoad];

    [self openSQLite];

    [self initButton];

}

- (void)initButton {

    NSArray *array = [[NSArray alloc]initWithObjects:@"增",@"删",@"改",@"查", nil];

    for (int i = 0; i < 4; i ++) {

        UIButton *button = [[UIButton alloc]init];

        button.bounds = CGRectMake(0, 0, 150, 30);

        button.tag = 10 + i;

        [button setTitle:array[i] forState:UIControlStateNormal];

        button.backgroundColor = [UIColor grayColor];

        [button addTarget:self action:@selector(buttonPressed:) forControlEvents:UIControlEventTouchUpInside];

        button.center = CGPointMake(CGRectGetWidth(self.view.bounds)/2, 100 + i * 50);

        [self.view addSubview:button];

    }

}

#pragma mark - 按钮点击事件

- (void)buttonPressed:(UIButton *)sender {

    NSInteger index = sender.tag - 10;

    switch (index) {

        case 0:

            [self initInsertData];

            break;

        case 1:

            [self initDelete];

            break;

        case 2:

            [self initUpdata];

            break;

        case 3:

            [self initSelect];

            break;

    }

}

#pragma mark - 创建数据库

- (void)openSQLite {

//    sqlite3 *db;

    //路径

    NSString *string = [NSString stringWithFormat:@"%@/Documents",NSHomeDirectory()];

    NSString *fileName = [string stringByAppendingString:@"/students.sqlite"];

    //OC-C

    const char *cfileName = fileName.UTF8String;

    //打开数据库，没有就自动创建

    int result = sqlite3_open(cfileName, &_db);

    if (result == SQLITE_OK) {

        NSLog(@"成功打开数据库");

    }else {

        NSLog(@"打开数据库失败");

    }

    //可用sqliteManger查看

#pragma mark - 创建表

    const char  *sql="CREATE TABLE IF NOT EXISTS t_students (id integer PRIMARY KEY AUTOINCREMENT,name text NOT NULL,age integer NOT NULL);";

    char *errmsg = NULL;

    //参数：第一个参数为数据库的句柄（db），第二个参数为sql语句，第三个参数为回调参数，是一个指向函数的指针，如果把callback前面的*改成^则就是一个block代码段，第四个参数可以写NULL，第五个参数为错误信息，用以代码调试。

    result = sqlite3_exec(_db, sql, NULL, NULL, &errmsg);

    if (result == SQLITE_OK) {

        NSLog(@"创建表成功");

    }else {

        printf("创表失败---%s----%s---%d",errmsg,__FILE__,__LINE__);

    }

}

#pragma mark - 插入数据

- (void)initInsertData {

    for (int i = 0; i < 20; i ++) {

        //1.拼接sql语句

        NSString *name = [NSString stringWithFormat:@"数据--%d",arc4random_uniform(100)];

        int age = arc4random_uniform(20) + 10;

        NSString *sql=[NSString stringWithFormat:@"INSERT INTO t_students (name,age) VALUES ('%@',%d);",name,age];

        //2.执行sql语句

        char *errmsg = NULL;

        sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errmsg);

        if (errmsg) { //如果有错误信息

            NSLog(@"插入数据失败-- %s",errmsg);

        }else {

            NSLog(@"插入数据成功");

        }

    }

}

#pragma mark - 选择(select)查询操作

- (void)initSelect {

    //从这个表里面查找年龄小于20得数据

    const char *sql="SELECT id,name,age FROM t_students WHERE age<20;";

    sqlite3_stmt *stmt = NULL;

    //进行查询前得准备工作

    if (sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL) == SQLITE_OK) { //SQL语句没有问题

        NSLog(@"SQL语句没有问题");

        //每调用一次sqlite_step函数，stmt就会指向下一条记录

        while (sqlite3_step(stmt) == SQLITE_ROW) {

            //取出数据

            //(1)取出第0列字段得值(int类型得值)

            int ID = sqlite3_column_int(stmt, 0);

            //(2)取出第2列字段得值（text类型得值）

            const unsigned char *name = sqlite3_column_text(stmt, 1);

            //(3)取出第2列字段得值

            int age = sqlite3_column_int(stmt, 2);

            printf("%d %s %d\n",ID,name,age);

        }

    }else {

        NSLog(@"查询语句有问题，或者没有数据");

    }

}

#pragma mark - 删除数据

- (void)initDelete {

    const char *sql = "delete from t_students";

    char *errmsg = NULL;

    sqlite3_exec(_db, sql, NULL, NULL, &errmsg);

    if (errmsg) {

        NSLog(@"删除失败");

    }else {

        NSLog(@"删除成功");

    }

}

- (void)initUpdata {

    const char *sql = "update t_students set name = '小明' , age = 16 ;";

    char *errmsg = NULL;

    sqlite3_exec(_db, sql, NULL, NULL, &errmsg);

    if (errmsg) {

        NSLog(@"修改失败");

    }else {

        NSLog(@"修改成功");

    }

}

@end