iOS项目笔记-sqlite数据库操作

支持数据库创建,表创建,增,删,查,改,清空表功能,并且使用statement支持字段防注入,可以直接复制使用

头文件:

//
//  SqliteHelper.h
//  Speaking
//
//  Created by dragon on 14-4-18.
//  Copyright (c) 2014年 dragon. All rights reserved.
//

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

//数据库名
#define     DB_SPEAKING   @"speaking.sqlite"

//历史数据
#define     TB_MESSAGE    @"message"

#define     TIME          @"msgTime"
#define     AVATAR        @"avatar"
#define     SPEAKER       @"speaker"

@interface SqliteHelper : NSObject

//获取实例
+(id)ShareSqlite;

/*******************************************************************************************************
 数据库操作方法
 *******************************************************************************************************/
/**
 * 功能:构造方法,获取db句柄
 * 参数:dbName-----NSString:数据库名称
 * 返回值类型:sqlite3----返回数据库句柄
 */
-(BOOL)OpenDB:(NSString*)dbName;

/**
 * 功能:关闭数据库
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)CloseDB;

/**
 * 功能:创建数据库表
 * 参数:sql-----NSString:创建数据表字符串
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)createTable:(NSString*)sql;

/**
 * 功能:清除数据库表数据
 * 参数:tableName-----NSString:清除数据表名称
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)cleanTable:(NSString*)tableName;


/**
 * 功能:删除数据表
 * 参数:tableName-----NSString:删除数据表名称
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)deleteTable:(NSString*)tableName;

/**
 * 功能:准备sql
 * 参数:sql-----NSString:插入数据表字符串
 * 返回值类型:sqlite3_stmt----返回statement
 */
-(sqlite3_stmt *)prepareSql:(NSString*)sql;

/**
 * 功能:提交sql,执行sql
 * 参数:sql-----NSString:插入数据表字符串
 * 返回值类型:sqlite3_stmt----返回statement
 */
-(BOOL)commitSql:(sqlite3_stmt *)stmt;


/*
 *  绑定 stamement
 */
-(void)bindData:(NSData *)data forIndex:(int)index andStmt:(sqlite3_stmt *)stmt;
-(void)bindString:(NSString *)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt;
-(void)bindInt32:(int)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt;
-(void)bindInt64:(long long)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt;
-(void)bindDouble:(double)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt;


-(void)beginTransaction;       //开始事务
-(void)commitTransaction;      //提交事务

@property(nonatomic,assign)NSInteger    DBStatus;

@end

//源文件

//
//  SqliteHelper.m
//  Speaking
//
//  Created by dragon on 14-4-18.
//  Copyright (c) 2014年 dragon. All rights reserved.
//

#import "SqliteHelper.h"

//延展（匿名类目）
@interface SqliteHelper()<NSCopying>
{
    sqlite3 *db;
}
@end

@implementation SqliteHelper

static SqliteHelper *sqlite =nil;
//获取实例
+(id)ShareSqlite
{
    @synchronized(self){
        if (!sqlite) {
            sqlite = [[SqliteHelper alloc]init];
        }
    }
    return sqlite;
}


/*******************************************************************************************************
 数据库操作方法
 *******************************************************************************************************/
/**
 * 功能:构造方法,获取db句柄
 * 参数:dbName-----NSString:数据库名称
 * 返回值类型:sqlite3----返回数据库句柄
 */
-(BOOL)OpenDB:(NSString*)dbName
{
    NSArray  *paths         = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documents     = [paths objectAtIndex:0];//存储路径
    
    NSString *database_path = [documents stringByAppendingPathComponent:dbName];//可以用dbName
    NSLog(@"数据库路径:[%@]",database_path);
    if (SQLITE_OK != sqlite3_open([database_path UTF8String], &db)) {
        sqlite3_close(db);
        self.DBStatus = -1;
        return NO;
    }
    self.DBStatus = 1;
    return YES;
}

/**
 * 功能:关闭数据库
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)CloseDB
{
    sqlite3_close(db);//关闭数据库
    self.DBStatus = -1;
    return YES;
}

/**
 * 功能:创建数据库表
 * 参数:sql-----NSString:创建数据表字符串
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)createTable:(NSString*)sql
{
    return [self execSql:sql];
}

/**
 * 功能:清除数据库表数据
 * 参数:tableName-----NSString:清除数据表名称
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)cleanTable:(NSString*)tableName
{
    NSString *sql = [[[NSString alloc]initWithFormat:@"delete from %@",tableName] autorelease];
    return [self execSql:sql];
}

/**
 * 功能:删除数据表
 * 参数:tableName-----NSString:删除数据表名称
 * 返回值类型:BOOL----是否成功
 */
-(BOOL)deleteTable:(NSString*)tableName
{
    NSString *sql = [[[NSString alloc]initWithFormat:@"drop table %@",tableName] autorelease];
    return [self execSql:sql];
}


/**
 * 功能:准备sql
 * 参数:sql-----NSString:插入数据表字符串
 * 返回值类型:sqlite3_stmt----返回statement
 */
-(sqlite3_stmt *)prepareSql:(NSString*)sql
{
    if (nil == sql) {
        return NO;
    }
    
    if (1 != self.DBStatus) {
        NSLog(@"数据库是关闭的");
        [self OpenDB:DB_SPEAKING];
        return NO;
    }
    
    //1.将SQL语句放入sqlite3_stmt中
    sqlite3_stmt *statement;
    
    int success = sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, NULL);
    if (SQLITE_OK != success) {
        NSLog(@"插入数据库失败");
        sqlite3_close(db);
        self.DBStatus = -1;
        return NO;
    }

    return statement;
}

/*
 *  执行statement
 */
-(BOOL)commitSql:(sqlite3_stmt *)stmt
{
    
    //2.执行SQL语
    int success = sqlite3_step(stmt);
    //3.释放
    sqlite3_finalize(stmt);
    
    if (SQLITE_ERROR == success) {
        NSLog(@"操作数据库失败");
        //关闭数据库
        sqlite3_close(db);
        self.DBStatus = -1;
        return NO;
    }
    return YES;
}


/*
 *  绑定 stamement
 */
-(void)bindData:(NSData *)data forIndex:(int)index andStmt:(sqlite3_stmt *)stmt
{
    sqlite3_bind_blob(stmt, index, data.bytes, data.length, SQLITE_TRANSIENT);
}
-(void)bindString:(NSString *)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt
{
    NSLog(@"stmt[%p]",stmt);
    sqlite3_bind_text(stmt, index,[value UTF8String] , -1, SQLITE_TRANSIENT);
}
-(void)bindInt32:(int)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt
{
    sqlite3_bind_int(stmt, index,value);
}
-(void)bindInt64:(long long)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt
{
    sqlite3_bind_int64(stmt, index, value);
}
-(void)bindDouble:(double)value forIndex:(int)index andStmt:(sqlite3_stmt *)stmt
{
    sqlite3_bind_double(stmt, index, value);
}

//事务处理
-(void)beginTransaction
{
    char *errMessage;
    sqlite3_exec(db, "BEGIN", nil, nil, &errMessage);
}

-(void)commitTransaction
{
    char *errMessage;
    sqlite3_exec(db, "COMMIT", nil, nil, &errMessage);
}



//-----私有方法

//执行数据库
-(BOOL)execSql:(NSString *)sql
{
    char *err;
    if (SQLITE_OK != sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err)){
        sqlite3_close(db);
        NSLog(@"数据库操作数据失败!错误代码:%p",err);
        self.DBStatus = -1;
        return NO;
    }
    return YES;
}
/*******************************************************************************************************
 数据库操作方法
 *******************************************************************************************************/


#pragma mark 下面方法确保只有一个实例
//当调用alloc
+(id)allocWithZone:(NSZone *)zone
{
    if (!sqlite) {
        sqlite = [super allocWithZone:zone];
    }
    
    return sqlite;
}
//覆盖copy协议
-(id)copyWithZone:(NSZone *)zone
{
    return sqlite;
}

-(void)dealloc
{
    [sqlite release];
    sqlite = nil;
    db     = nil;
    [super dealloc];
}


@end

使用步骤:

1.打开数据库

2.执行数据库操作

3.关闭数据库

 sqlite = [SqliteHelper ShareSqlite];
    //1.创建或打开数据库
    [sqlite OpenDB:DB_SPEAKING];
    
    //聊天记录
    NSString *msgsql = [NSString stringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement, %@ TEXT, %@ TEXT,%@ TEXT)",TB_MESSAGE,TIME,AVATAR,SPEAKER];
//    NSLog(@"msgsql = [%@]",msgsql);
    BOOL msgFlag = [sqlite createTable:msgsql];
    if (msgFlag) {
        NSLog(@"数据库创建成功，成功创建表");
    }
    //插入
    NSString *insert = @"insert into message('msgTime','avatar','speaker') values(?,?,?)";
    sqlite3_stmt *statement = [sqlite prepareSql:insert];
    [sqlite bindString:@"2014xxx0510" forIndex:1 andStmt:statement];
    [sqlite bindString:@"avatd'ddar" forIndex:2 andStmt:statement];
    [sqlite bindString:@"dragon\"" forIndex:3 andStmt:statement];
    BOOL insertflag = [sqlite commitSql:statement];
    if (insertflag) {
        NSLog(@"插入成功");
    }


    //修改,更新多条语句使用,隔开
    NSString *update = @"update message set msgTime=?,speaker=? where id=?";
    statement = [sqlite prepareSql:update];
    [sqlite bindString:@"20140510" forIndex:1 andStmt:statement];
    [sqlite bindString:@"dragon" forIndex:2 andStmt:statement];
    [sqlite bindInt32:3 forIndex:3 andStmt:statement];
    if([sqlite commitSql:statement]){
        NSLog(@"修改成功");
    }
    

    
    //删除
    NSString *delete = @"delete from message where speaker=?";
    statement = [sqlite prepareSql:delete];
    [sqlite bindString:@"xinxsd\"ddin" forIndex:1 andStmt:statement];
    if([sqlite commitSql:statement]){
        NSLog(@"删除成功");
    }
    
    
    //清除数据表
    if([sqlite cleanTable:@"message"]){
        NSLog(@"清除成功");
    }

    
    //查询
    NSString *select = @"select * from message";
    statement = [sqlite prepareSql:select];
    
    while(SQLITE_ROW == sqlite3_step(statement)){
        int myid = sqlite3_column_int(statement, 0);
        
        char *_msgTime = (char*)sqlite3_column_text(statement, 1);
        NSString *msgTime = [[NSString alloc]initWithUTF8String:_msgTime];
        
        char *_avatar = (char*)sqlite3_column_text(statement, 2);
        NSString *avatar = [[NSString alloc]initWithUTF8String:_avatar];
        
        char *_speaker = (char*)sqlite3_column_text(statement, 3);
        NSString *speaker = [[NSString alloc]initWithUTF8String:_speaker];
        
        NSLog(@"id=[%d]---time=[%@]---avatar=[%@]---speaker=[%@]",myid,msgTime,avatar,speaker);
        
    }
    sqlite3_finalize(statement);
    
    //关闭数据库
    [sqlite CloseDB];