sqlite資料庫檢索

標籤：des   style   blog   color   io   使用   ar   for   檔案   

1. 資料庫檢索， 得到某欄位下所有值

 1 - (NSArray *)selectWithColumName: (NSString *)columName 2                        tableName: (NSString *)tableName { 3     if ([self openDatabase] == YES) { 4          5         NSString * selectSQL = [NSString stringWithFormat:@"SELECT %@ FROM %@", columName, tableName]; 6         sqlite3_stmt * stmt = nil; 7          8         int preResult = sqlite3_prepare_v2(_db, [selectSQL UTF8String], -1, &stmt, NULL); 9         10         if (preResult == SQLITE_OK) {11             NSMutableArray * array = [NSMutableArray array];12             13             while (sqlite3_step(stmt) == SQLITE_ROW) {14                 [array addObject:[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)]];15             }16             17             sqlite3_finalize(stmt);18             return array;19         } else {20             NSLog(@"check your sqlQuery");21             return nil;22         }23     } else {24         NSLog(@"%@", [self errorWithMessage:@"openDB Failure"]);25         return nil;26     }27 }

2. 通過你儲存的模型以及資料表名得到所有的資料

 1 #pragma mark - select DB 2 - (NSArray *)selectAllMembersWithTableName: (NSString *)tableName 3                                objectModel:(id)object; { 4     if ([self openDatabase] == YES) { 5          6         NSString * selectSQL = [NSString stringWithFormat:@"SELECT * FROM %@", tableName]; 7         sqlite3_stmt * stmt = nil; 8          9         int preResult = sqlite3_prepare_v2(_db, [selectSQL UTF8String], -1, &stmt, NULL);10         11         if (preResult == SQLITE_OK) {12             NSMutableArray * array = [NSMutableArray array];13             14             while (sqlite3_step(stmt) == SQLITE_ROW) {15                 16                 id model = [[[object class] alloc] init];17                 for (int i=0; i<sqlite3_column_count(stmt); i++) {18                     [model setValue:[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, i)] forKey:[NSString stringWithUTF8String:(const char *)sqlite3_column_name(stmt, i)]];19                 }20                 [array addObject:model];21                 [model release];22             }23             24             sqlite3_finalize(stmt);25             return array;26         } else {27             NSLog(@"check your sqlQuery and Model");28             return nil;29         }30     } else {31         NSLog(@"%@", [self errorWithMessage:@"SqlQuery error"]);32         return nil;33     }34 }

3. 通過key：value對應關係的字典為資料庫查詢語句的基礎， 得到需要的值

.h檔案定義以及使用提示

#pragma mark selectWithSqlQueryDict/* get value with sql statement you must give columName(dict key) = value(dict value) - all string type. e.g  dict = {                "name" = "xxdbuser",                "age"  = "19"                }; object: model you want 通過包含有你的約束條件的字典、 通過表名稱、 你所給的模型、 返回包含有若干模型的數組 */- (NSArray *)selectWithSqlQueryDictionary: (NSDictionary *)sqlQueryDictionary                                tableName: (NSString *)tableName                                    model: (id)object;

.m 檔案實現

 1 #pragma mark selectWithSqlQuery 2 - (NSArray *)selectWithSqlQueryDictionary: (NSDictionary *)sqlQueryDictionary 3                                 tableName: (NSString *)tableName 4                                     model: (id)object { 5      6     // getAllKeys 7     NSArray * keyArray = sqlQueryDictionary.allKeys; 8     NSString * sqlQuery = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE ", tableName]; 9     10     if ([self openDatabase] == YES) {11         12         // foreach build sqlQuery13         for (NSString * key in keyArray) {14             sqlQuery = [sqlQuery stringByAppendingString:[NSString stringWithFormat:@"%@ = ‘%@‘ and ", key, sqlQueryDictionary[key]]];15         }16         sqlQuery = [sqlQuery substringToIndex:[sqlQuery length] - 4];17         18         sqlite3_stmt * stmt;19         20         int result = sqlite3_prepare_v2(_db, [sqlQuery UTF8String], -1, &stmt, NULL);21         if (result == SQLITE_OK) {22             NSMutableArray * array = [NSMutableArray array];23             24             while (sqlite3_step(stmt) == SQLITE_ROW) {25                 26                 id model = [[[object class] alloc] init];27                 for (int i=0; i<sqlite3_column_count(stmt); i++) {28                     [model setValue:[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, i)] forKey:[NSString stringWithUTF8String:(const char *)sqlite3_column_name(stmt, i)]];29                 }30                 [array addObject:model];31                 [model release];32             }33             34             sqlite3_finalize(stmt);35             return array;36         } else {37             NSLog(@"check your sqlQuery");38             return nil;39         }40         41     } else {42         NSLog(@"%@", [self errorWithMessage:@"openDB Failure"]);43         return nil;44     }45 }

4. 錯誤資訊的輸出

1 #pragma mark - errorMessage2 - (NSError *)errorWithMessage:(NSString *)message {3     return [NSError errorWithDomain:@"XXDB" code:sqlite3_errcode(_db) userInfo:[NSDictionary dictionaryWithObject:message forKey:NSLocalizedDescriptionKey]];4 }

 

ok 今天就到這裡。 資料庫是資料持久化最常用的辦法。 一定熟練掌握使用者的各種需求實現的辦法

sqlite資料庫檢索