1 /    2 //  LSQLiteDB.m    3 //  lua-hello    4 //    5 //  Created by leiwuluan on 14-2-13.    6 //  Copyright (c) 2014年 hello. All rights reserved.    7 //    8     9 #import "LSQLiteDB.h"   10 #import "NSStringExtend.h"   11 #import "NSDataAdditions.h"   12 #import "NSURLAdditions.h"   13    14 @implementation LSQLiteDB   15    16 // 打开 或 创建一个数据库   17 - (int) openDBByDBName: (NSString *) _DBName {   18     NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);   19     NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent: _DBName];   20        21     if (sqlite3_open([databaseFilePath UTF8String], &database)==SQLITE_OK) {   22            23         // 创建一个缓存表   24         NSString *_sql = @"create table if not exists dataCache (dkey varchar(100) PRIMARY KEY, dvalue TEXT, ctype INTEGER, utime timestamp)";   25            26         charchar *errorMsg;   27         if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {   28             NSLog(@"Create dataCache Success.");   29         }else {   30             NSLog(@"Create dataCache Failure %s",errorMsg);   31         }   32    33         NSLog(@"SQLites is opened.");   34         return YES;   35     }else {   36         NSLog(@"SQLites open Error.");   37         return NO;   38     }   39    40 }   41    42 // 执行一条slq   43 - (int) execBySql: (NSString *) _sql {   44     // create table if not exists dataCache (dkey varchar(100) PRIMARY KEY, dvalue TEXT, utime timestamp)   45        46     charchar *errorMsg;   47     if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {   48         NSLog(@"Create Success.");   49         return YES;   50     }else {   51         NSLog(@"Create Failure %s",errorMsg);   52         return NO;   53     }   54 }   55    56 // 通过键值更新   57 - (int) updateRowData:(NSString *) _rowData forKey: (NSString *) _key ctype: (NSInteger) _ctype {   58        59     _rowData = http://www.mamicode.com/[_rowData stringByReplacingOccurrencesOfString:@"‘" withString:@"‘"];   60     NSString *_sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO dataCache(dkey, dvalue, utime, ctype) values(‘%@‘, ‘%@‘, datetime(‘now‘), %d)", _key, _rowData, _ctype];   61     charchar *errorMsg;   62     if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {   63         NSLog(@"Insert Success.");   64         return YES;   65     }else {   66         NSLog(@"Insert Failure %s, |%@",errorMsg , _sql);   67         return NO;   68     }   69        70 }   71    72 // 缓存获取值   73 - (NSString *) findRowDataForKey: (NSString *) _key {   74     NSString *query = [NSString stringWithFormat:@"select dvalue from dataCache where dkey=‘%@‘", _key];   75     sqlite3_stmt *statement;   76     if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {   77         if (sqlite3_step(statement) == SQLITE_ROW) {   78             //get data   79             charchar *dvalue = http://www.mamicode.com/(charchar *)sqlite3_column_text(statement, 0);   80             NSString *retDvalue =http://www.mamicode.com/ [NSString stringWithCString:dvalue encoding:NSUTF8StringEncoding];   81            82             return retDvalue;   83         }   84         sqlite3_finalize(statement);   85     }   86     return nil;   87 }   88    89 // 清除过期数据   90 - (int) clearCAData: (NSString *) cacheDate {   91     NSString *_sql = [NSString stringWithFormat:@"delete from dataCache where ctype = 1 and utime < ‘%@‘", cacheDate];   92     charchar *errorMsg;   93     if (sqlite3_exec(database, [_sql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {   94         NSLog(@"Insert Success.");   95         return YES;   96     }else {   97         NSLog(@"Insert Failure %s, |%@",errorMsg , _sql);   98         return NO;   99     }  100 }  101   102 // 关闭数据库  103 -(int) closeDatabase {  104     sqlite3_close(database);  105     return YES;  106 }  107   108 -(void) dealloc{  109     [super dealloc];  110     [self closeDatabase];  111 }  112   113   114 -(NSMutableArray*) queryBySQL:(NSString *) sql  115 {  116     NSMutableArray *result = [[NSMutableArray alloc]init];  117     sqlite3_stmt *stmt;  118     if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {  119 //        int num_cols = sqlite3_data_count(stmt);  120         while (sqlite3_step(stmt)==SQLITE_ROW) {  121             int num_cols = sqlite3_column_count(stmt);  122             NSMutableDictionary *dict = [NSMutableDictionary dictionaryWithCapacity:num_cols];  123             if (num_cols > 0) {  124                 int i;  125                 for (i = 0; i < num_cols; i++) {  126                     const charchar *col_name = sqlite3_column_name(stmt, i);  127                     if (col_name) {  128                         NSString *colName = [NSString stringWithUTF8String:col_name];  129                         id value =http://www.mamicode.com/ nil;  130                         // fetch according to type  131                         switch (sqlite3_column_type(stmt, i)) {  132                             case SQLITE_INTEGER: {  133                                 int i_value =http://www.mamicode.com/ sqlite3_column_int(stmt, i);  134                                 value =http://www.mamicode.com/ [NSNumber numberWithInt:i_value];  135                                 break;  136                             }  137                             case SQLITE_FLOAT: {  138                                 double d_value =http://www.mamicode.com/ sqlite3_column_double(stmt, i);  139                                 value =http://www.mamicode.com/ [NSNumber numberWithDouble:d_value];  140                                 break;  141                             }  142                             case SQLITE_TEXT: {  143                                 charchar *c_value = http://www.mamicode.com/(charchar *)sqlite3_column_text(stmt, i);  144                                 value =http://www.mamicode.com/ [[NSString alloc] initWithUTF8String:c_value];  145                                 break;  146                             }  147                             case SQLITE_BLOB: {  148                                 value =http://www.mamicode.com/ sqlite3_column_blob(stmt, i);  149                                 break;  150                             }  151                         }  152                         // save to dict  153                         if (value) {  154                             [dict setObject:value forKey:colName];  155                         }  156                     }  157                 }  158             }  159             [result addObject:dict];  160         }  161         /* 162         while (sqlite3_step(stmt)==SQLITE_ROW) { 163             char *name = (char *)sqlite3_column_text(stmt, 1); 164             NSString *nameString = [[NSString alloc] initWithUTF8String:name]; 165             NSLog(@"%@", nameString); 166         }*/  167         sqlite3_finalize(stmt);  168     }  169     return result;  170 }  171   172 // 判断表是否存在  173 -(int) tableIsExists:(NSString*) tableName  174 {  175     NSString *sql = [NSString stringWithFormat:@"SELECT count(*) as count_num FROM sqlite_master WHERE type=\"table\" AND name = \"%@\"", tableName ];  176     NSMutableArray *arr = [self queryBySQL: sql];  177     NSMutableDictionary *dic = [arr objectAtIndex:0];  178    return (int)[dic valueForKey:@"count_num"];  179 }  180 @end