SQLite Database Retrieval

1. Database retrieval, get all values under a field

 
 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. Get all the data from your stored model and data table name

 
 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. Use the Dictionary of Key:value correspondence to base the database query statement, get the desired value

. h file definitions and usage hints

 
#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
  By a dictionary containing your constraints, by the table name, the model you gave, returning an array containing several models
  * /
-(NSArray *) selectWithSqlQueryDictionary: (NSDictionary *) sqlQueryDictionary
                                 tableName: (NSString *) tableName
                                     model: (id) object;

. m file implementations

 
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 }

4. Output of error messages

 
 
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, get here today. Databases are the most common way to persist data. Must be proficient in the implementation of the various needs of the user approach

SQLite Database Retrieval