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 sqlQuery 13 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 - errorMessage 2 - (NSError *)errorWithMessage:(NSString *)message { 3 return [NSError errorWithDomain:@"XXDB" code:sqlite3_errcode(_db) userInfo:[NSDictionary dictionaryWithObject:message forKey:NSLocalizedDescriptionKey]]; 4 }
ok 今天就到这里。 数据库是数据持久化最常用的办法。 一定熟练掌握用户的各种需求实现的办法