sqlite1-1

//
//  ViewController.m
//  SQLiteText
//
//  Created by jerehedu on 15/2/2.
//  Copyright (c) 2015年 baidu. All rights reserved.
//

#import "ViewController.h"
#import <sqlite3.h>  //1. 导入SQLite3头文件

@interface ViewController ()

@end

@implementation ViewController
{
    sqlite3 *db;//2. 声明SQLite3对象
}



- (NSString *)getUserDocumentPath
{
    //3. 转换沙盒路径为C字符串
    NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentPath=[path lastObject];
    return documentPath;
}

- (NSString *)appendingPathComponent:(NSString *)documentPath andFileName:(NSString*)fileName
{
    NSString *dbPath = [documentPath stringByAppendingPathComponent:fileName];
    return dbPath;
}
- (BOOL)openOrCreateSQLiteDBWithDBPath:(NSString*)dbPath
{
    
    const char *p = [dbPath UTF8String];
    
    //4. 打开或创建数据库串
    int res = sqlite3_open(p, &db);
    if (res == SQLITE_OK) {
        return YES;
    }
    
    return NO;
}
#pragma mark 增 删 改
- (BOOL)execSQLNoQueryWith:(NSString*)sql
{
    int insert_res = sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
    if (insert_res == SQLITE_OK) {
        return YES;
    }
    return NO;
}
// <1>.......
- (sqlite3_stmt*)execQueryWithSQL:(NSString*)sql
{
    sqlite3_stmt *stmt;
    
    int pre_res = sqlite3_prepare(db, [sql UTF8String], -1, &stmt, NULL);
    if (pre_res == YES) {
        return stmt;
    }
    return NULL;
}
// <2>.......
- (sqlite3_stmt*)execQueryWithSQL:(NSString*)sql andWithParams:(NSArray*)params
{
    sqlite3_stmt *stmt;
    int pre_res = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
    if (pre_res == SQLITE_OK) {
        //绑定参数
        //先判断是否参数为nil,然后循环绑定多个参数
        if (params != nil)
        {
            for (int i = 0; i <[params count]; i++)
            {
                id obj = params[i];
                if (obj == nil) {
                    sqlite3_bind_null(stmt, i+1);
                }
                else if ([obj respondsToSelector:@selector(objCType)]){
                    //respondsToSelector判断是否有objCType方法,返回BOLL型
                    if (strstr("ilsILS",[obj objCType] )){
                        //strstr (char * ,char*)判断是否在char*中出现过
                        sqlite3_bind_int(stmt, i+1, [obj intValue]);
                    }
                    else if (strstr("fdFD", [obj objCType])){
                        sqlite3_bind_double(stmt, i+1, [obj doubleValue]);
                    }
                    else{
                        stmt = nil;
                    }
                }
                else if([obj respondsToSelector:@selector(UTF8String)]){
                    sqlite3_bind_text(stmt, i+1, [obj UTF8String], -1, NULL);
                    
                }else{
                    stmt = nil;
                }
            }
            return stmt;
        }
    }
    return NULL;
}

- (void)viewDidLoad
{
    [super viewDidLoad];
    UILabel *label = [[UILabel alloc] initWithFrame:CGRectMake(0, 0,320,568)];
    label.font = [UIFont systemFontOfSize:18];
    [label setNumberOfLines:0];
    label.textColor = [UIColor redColor];
    [self.view addSubview:label];
    
//    sqlite3 *db;//2. 声明SQLite3对象

    NSString *documentPath;
    documentPath = [self getUserDocumentPath];
    
    
    NSString *dbPath;
    dbPath = [self appendingPathComponent:documentPath andFileName:@"demo.sqlite"];
    NSLog(@"%@",dbPath);
    
   
    //5. 判断是否打开成功
    if ([self openOrCreateSQLiteDBWithDBPath:dbPath]) {
        NSLog(@"db is opened");
        
        //构造SQL语句
        NSString *sql = @"create table if not exists tempYear(t_id integer primary key autoincrement,t_name varchar(20) not null,t_date date)";
        /*
           第一个参数: sqlite3 对象
           第二个参数: sql语句
           第三个参数: 回调函数
           第四个函数: 回调函数的参数
           第五个参数: 错误信息
         */
       int exec_res = sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
       
        if (exec_res == SQLITE_OK) {
            NSLog(@"table is created");
        }
        //增加
        NSString *insert_sql = @"insert into tempYear(t_name) values('jeack')";
        if ([self execSQLNoQueryWith:insert_sql]) {
            NSLog(@"one recorder is inserted");
        }
        //删除
        NSString *delete_sql = @"delete from tempYear where t_id=2";
        if ([self execSQLNoQueryWith:delete_sql]) {
            NSLog(@"delete id already done");
        }
        //更改
        NSString *update_sql = @"update tempYear set t_name ='title' where t_id=4 " ;
        if ([self execSQLNoQueryWith:update_sql]) {
            NSLog(@"update id already done");
        }

        
        
        //查询
        NSString *search_sql = @"select * from tempYear where t_id>? and t_name like ?";//占位符
        
        int search_t_id=4;
        NSString *search_t_name = @"j%";
        
        //select * from user where username='admin' and pwd ='admin';
        
        
        sqlite3_stmt *stmt;
        /*>>>准备执行查询SQL语句
          第一个参数:sqlite对象
          第二个参数:执行的SQL语句
          第三个参数:sql语句的长度,通常用-1来代表(系统自己计算长度)也可以用strlen([search_sql UTF8String])函数来计算
          第四个参数:sqlite3_stmt对象
          第五个参数:未执行的sql语句
         
         */
//       int prepare_res = sqlite3_prepare_v2(db, [search_sql UTF8String], -1, &stmt, NULL);
        
       
//        if (prepare_res == SQLITE_OK) {
            /*
             sqlite3_bind_int(stmt, 1, search_t_id);
                 绑定参数
             第一个参数:sqlite_stmt对象
             第二个参数:占位符索引,从1开始
             第三个参数:替代占位符的真实参数
             */

//            sqlite3_bind_int(stmt, 1, search_t_id);
//            sqlite3_bind_text(stmt, 2, [search_t_name UTF8String], -1, NULL);
        
        NSArray *array = [NSArray arrayWithObjects:[NSNumber numberWithInt:search_t_id],search_t_name, nil];
        stmt = [self execQueryWithSQL:search_sql andWithParams:array];
        
            while (sqlite3_step(stmt)==SQLITE_ROW) {
                int t_id = sqlite3_column_int(stmt, 0);
                const unsigned char *t_name = sqlite3_column_text(stmt, 1);
                NSLog(@"id=%d,name=%s",t_id,t_name);
        
            }
        //释放stmt
        sqlite3_finalize(stmt);
    }
    //关闭数据库
    sqlite3_close(db);
}

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

@end