Sqlite database retrieval, sqlite Database

Sqlite database retrieval, sqlite Database

1. Retrieve all values in a field from the database

 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 through the model and data table name you store.

 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. The Dictionary of the key: value ing relationship serves as the basis of the database query statement to obtain the required value.

. H file definition and usage tips

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

. M file implementation

 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. Output of error messages

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. It's here today. Database is the most common method for data persistence. Be familiar with the implementation of various user needs

How to search for all data in a month of a year in the sqlite Database

The field_yyy field in db_xxx is a date field.
Select * from db_xxx where substr (date (field_yyy), 2012) = '2017-07 ';
 
Search for qt and sqlite Databases

Hmm