//
//  BaseDB.h
//  SqliteDemo
//
//  Created by 赵超 on 14-8-26.
//  Copyright (c) 2014年 赵超. All rights reserved.
//

#import <Foundation/Foundation.h>
#import "sqlite3.h"

@interface BaseDB : NSObject

/**
 *  创建一个表
 *  sql:执行的SQL语句
 *  dataName:数据库名称
 */

-(void) createTable:(NSString*)sql dataBaseName:(NSString*) dataName;
/**
 *  执行SQL语句，主要完成增加、修改、删除
 *  sql:执行的SQL语句
 *  params:SQL语句中的参数
 *  dataName:数据库名称
 */
-(BOOL) execSql:(NSString*)sql parmas:(NSArray*) params dataBaseName:(NSString*)dataName;
/**
 *  选择数据
 *  sql:查询的SQL语句
 *  params:查询SQL语句中的参数
 *  dataName:查询数据库名称
 */

-(NSMutableArray*) selectSql:(NSString*)sql parmas:(NSArray*) params dataBaseName:(NSString*)dataName;



@end

3.BaseDB.m

//
//  BaseDB.m
//  SqliteDemo
//
//  Created by 赵超 on 14-8-26.
//  Copyright (c) 2014年 赵超. All rights reserved.
//

#import "BaseDB.h"
@implementation BaseDB

/*
 *  获取沙盒目录
 *  name:追加的目录aa
 *
 */
-(NSString*) DataBaseName:(NSString *) name {
     NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",name];
    return fileName;
}
/**
 *  选择数据
 *  sql:查询的SQL语句
 *  params:查询SQL语句中的参数
 *  dataName:查询数据库名称
 */
 
-(NSMutableArray*) selectSql:(NSString *)sql parmas:(NSArray *)params dataBaseName:(NSString *)dataName{
    
    sqlite3 *sqlite=nil;
    sqlite3_stmt *stmt=nil;
    //打开数据库
    NSString *fileName=[self DataBaseName:dataName];
    int result= sqlite3_open([fileName UTF8String], &sqlite);
    if (result!=SQLITE_OK) {
        NSLog(@"打开失败");
        return nil;
    }
    const char* sqlCh=[sql UTF8String];
    //编译SQL语句
    sqlite3_prepare_v2(sqlite, sqlCh, -1, &stmt, NULL);
    //绑定参数
    for (int i=0; i<params.count; i++) {
        NSString *param=[params objectAtIndex:i];
        sqlite3_bind_text(stmt, i+1, [param UTF8String], -1, NULL);
    }

    //执行查询语句
    result=sqlite3_step(stmt);
    
    NSMutableArray *resultData=http://www.mamicode.com/[NSMutableArray array];>
4.调用格式

    
    BaseDB *db=[[BaseDB alloc] init];
    //创建表
    NSString *dbCreate=@"create table zhaochao( username text primary key,userPasswd test)";
    NSString *dbName=@"zhaochao.sqlite";
   // [db createTable:dbCreate dataBaseName:dbName];
    
    //添加数据
    NSString *insertTable=@"insert into zhaochao (username,userPasswd) values (?,?)";
    NSArray  *insertParmas=@[@"acasdfaa",@"bb"];
   // [db execSql:insertTable parmas:insertParmas dataBaseName:@"zhaochao.sqlite"];
    
    //修改数据
    NSString *updateTable=@"update zhaochao set username=? where username=?";
    NSArray  *updateParams=@[@"admin",@"zhaochao"];
   // [db execSql:updateTable parmas:updateParams dataBaseName:@"zhaochao.sqlite"];
    
    //删除数据
    NSString *deleteTable=@"delete from zhaochao where username=?";
    NSArray   *deleteParams=@[@"aa"];
   // [db execSql:deleteTable parmas:deleteParams dataBaseName:@"zhaochao.sqlite"];
    
    //查询数据
    NSString *selectTable=@"select username,userPasswd from zhaochao where userPasswd=?";
    NSString *selectParam=@[@"bb"];
    NSArray *result=[db selectSql:selectTable parmas:selectParam dataBaseName:@"zhaochao.sqlite"];
    for (int i=0; i<result.count; i++) {
        NSMutableDictionary *arr=[result objectAtIndex:i];
        NSLog(@"%@",arr);
    }