Objective-c简单操作SQLite

  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 = [_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 = (charchar *)sqlite3_column_text(statement, 0);  
 80             NSString *retDvalue = [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 = nil;  
130                         // fetch according to type  
131                         switch (sqlite3_column_type(stmt, i)) {  
132                             case SQLITE_INTEGER: {  
133                                 int i_value = sqlite3_column_int(stmt, i);  
134                                 value = [NSNumber numberWithInt:i_value];  
135                                 break;  
136                             }  
137                             case SQLITE_FLOAT: {  
138                                 double d_value = sqlite3_column_double(stmt, i);  
139                                 value = [NSNumber numberWithDouble:d_value];  
140                                 break;  
141                             }  
142                             case SQLITE_TEXT: {  
143                                 charchar *c_value = (charchar *)sqlite3_column_text(stmt, i);  
144                                 value = [[NSString alloc] initWithUTF8String:c_value];  
145                                 break;  
146                             }  
147                             case SQLITE_BLOB: {  
148                                 value = 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