sqlite1-2增 删 改 查方法的简单封装

//  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, 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, [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)closeDB{
    if (db) {
        sqlite3_close(db);
    }
    
}

@end