//  sqliteHelper.m
//  sqliteText3
//
//  Created by jerehedu on 15/2/3.
//  Copyright (c) 2015年 baidu. All rights reserved.
//
 
#import "sqliteHelper.h"
#import <sqlite3.h>
 
 
#define  DBNAME @"demo.sqlite"
 
@implementation sqliteHelper
{
    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)openOrCreatesqliteDBWithDBPathWithFileName:(NSString*)fileName
{
    NSString *documentPath = [self getUserDocumentPath];
    NSString *sqlitePath = [self appendingPathComponent:documentPath andFileName:fileName];
 
    const char *p = [sqlitePath UTF8String];
    
    //4. 打开或创建数据库串
    int res = sqlite3_open(p,&db);
    if (res == sqlITE_OK) {
        return YES;
    }
    return NO;
}
#pragma mark 增 删 改
- (BOOL)execsqlNoQueryWith:(NSString*)sql
{
    [self openOrCreatesqliteDBWithDBPathWithFileName:DBNAME];
    BOOL isExecSuccess = NO;
    int insert_res = sqlite3_exec(db,[sql UTF8String],NULL,NULL);
    if (insert_res == sqlITE_OK) {
        isExecSuccess =YES;
    }
    sqlite3_close(db);
    return isExecSuccess;
}
 
- (BOOL)execsqlNoQueryWith:(NSString*)sql andWithParams:(NSArray*)params
{
    BOOL isExecSuccess = NO;
    sqlite3_stmt *stmt = [self prepareStatementWithsql:sql andWithParams:params];
    if (sqlite3_step(stmt)==sqlITE_DONE) {
        isExecSuccess = YES;
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return isExecSuccess;
}
 
// <2>.......
- (sqlite3_stmt*)prepareStatementWithsql:(NSString*)sql andWithParams:(NSArray*)params
{
    [self openOrCreatesqliteDBWithDBPathWithFileName:DBNAME];
    sqlite3_stmt *stmt;
    int pre_res = sqlite3_prepare_v2(db,-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,[obj doubleValue]);
                    }
                    else{
                        stmt = nil;
                    }
                }
                else if([obj respondsToSelector:@selector(UTF8String)]){
                    sqlite3_bind_text(stmt,[obj UTF8String],NULL);
                    
                }else{
                    stmt = nil;
                }
            }
        }
        return stmt;
    }
    return NULL;
}
 
- (void)closeDB{
    if (db) {
        sqlite3_close(db);
    }
    
}
 
@end