Objective-c简单操作SQLite

//
//  LSQLiteDB.m
//  lua-hello
//
//  Created by leiwuluan on 14-2-13.
//  Copyright (c) 2014年 hello. All rights reserved.
//

#import "LSQLiteDB.h"
#import "NSStringExtend.h"
#import "NSDataAdditions.h"
#import "NSURLAdditions.h"

@implementation LSQLiteDB

// 打开 或 创建一个数据库
- (int) openDBByDBName: (NSString *) _DBName {
    NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent: _DBName];
    
    if (sqlite3_open([databaseFilePath UTF8String], &database)==SQLITE_OK) {
        
        // 创建一个缓存表
        NSString *_sql = @"create table if not exists dataCache (dkey varchar(100) PRIMARY KEY, dvalue TEXT, ctype INTEGER, utime timestamp)";
        
        char *errorMsg;
        if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {
            NSLog(@"Create dataCache Success.");
        }else {
            NSLog(@"Create dataCache Failure %s",errorMsg);
        }

        NSLog(@"SQLites is opened.");
        return YES;
    }else {
        NSLog(@"SQLites open Error.");
        return NO;
    }

}

// 执行一条slq
- (int) execBySql: (NSString *) _sql {
    // create table if not exists dataCache (dkey varchar(100) PRIMARY KEY, dvalue TEXT, utime timestamp)
    
    char *errorMsg;
    if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {
        NSLog(@"Create Success.");
        return YES;
    }else {
        NSLog(@"Create Failure %s",errorMsg);
        return NO;
    }
}

// 通过键值更新
- (int) updateRowData:(NSString *) _rowData forKey: (NSString *) _key ctype: (NSInteger) _ctype {
    
    _rowData = [_rowData stringByReplacingOccurrencesOfString:@"'" withString:@"‘"];
    NSString *_sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO dataCache(dkey, dvalue, utime, ctype) values('%@', '%@', datetime('now'), %d)", _key, _rowData, _ctype];
    char *errorMsg;
    if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {
        NSLog(@"Insert Success.");
        return YES;
    }else {
        NSLog(@"Insert Failure %s, |%@",errorMsg , _sql);
        return NO;
    }
    
}

// 缓存获取值
- (NSString *) findRowDataForKey: (NSString *) _key {
    NSString *query = [NSString stringWithFormat:@"select dvalue from dataCache where dkey='%@'", _key];
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
        if (sqlite3_step(statement) == SQLITE_ROW) {
            //get data
            char *dvalue = (char *)sqlite3_column_text(statement, 0);
            NSString *retDvalue = [NSString stringWithCString:dvalue encoding:NSUTF8StringEncoding];
        
            return retDvalue;
        }
        sqlite3_finalize(statement);
    }
    return nil;
}

// 清除过期数据
- (int) clearCAData: (NSString *) cacheDate {
    NSString *_sql = [NSString stringWithFormat:@"delete from dataCache where ctype = 1 and utime < '%@'", cacheDate];
    char *errorMsg;
    if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {
        NSLog(@"Insert Success.");
        return YES;
    }else {
        NSLog(@"Insert Failure %s, |%@",errorMsg , _sql);
        return NO;
    }
}

// 关闭数据库
-(int) closeDatabase {
    sqlite3_close(database);
    return YES;
}

-(void) dealloc{
    [super dealloc];
    [self closeDatabase];
}


-(NSMutableArray*) queryBySQL:(NSString *) sql
{
    NSMutableArray *result = [[NSMutableArray alloc]init];
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {
//        int num_cols = sqlite3_data_count(stmt);
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            int num_cols = sqlite3_column_count(stmt);
            NSMutableDictionary *dict = [NSMutableDictionary dictionaryWithCapacity:num_cols];
            if (num_cols > 0) {
                int i;
                for (i = 0; i < num_cols; i++) {
                    const char *col_name = sqlite3_column_name(stmt, i);
                    if (col_name) {
                        NSString *colName = [NSString stringWithUTF8String:col_name];
                        id value = nil;
                        // fetch according to type
                        switch (sqlite3_column_type(stmt, i)) {
                            case SQLITE_INTEGER: {
                                int i_value = sqlite3_column_int(stmt, i);
                                value = [NSNumber numberWithInt:i_value];
                                break;
                            }
                            case SQLITE_FLOAT: {
                                double d_value = sqlite3_column_double(stmt, i);
                                value = [NSNumber numberWithDouble:d_value];
                                break;
                            }
                            case SQLITE_TEXT: {
                                char *c_value = (char *)sqlite3_column_text(stmt, i);
                                value = [[NSString alloc] initWithUTF8String:c_value];
                                break;
                            }
                            case SQLITE_BLOB: {
                                value = sqlite3_column_blob(stmt, i);
                                break;
                            }
                        }
                        // save to dict
                        if (value) {
                            [dict setObject:value forKey:colName];
                        }
                    }
                }
            }
            [result addObject:dict];
        }
        /*
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            char *name = (char *)sqlite3_column_text(stmt, 1);
            NSString *nameString = [[NSString alloc] initWithUTF8String:name];
            NSLog(@"%@", nameString);
        }*/
        sqlite3_finalize(stmt);
    }
    return result;
}

// 判断表是否存在
-(int) tableIsExists:(NSString*) tableName
{
    NSString *sql = [NSString stringWithFormat:@"SELECT count(*) as count_num FROM sqlite_master WHERE type=\"table\" AND name = \"%@\"", tableName ];
    NSMutableArray *arr = [self queryBySQL: sql];
    NSMutableDictionary *dic = [arr objectAtIndex:0];
   return (int)[dic valueForKey:@"count_num"];
}
@end