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