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

Search for qt and sqlite Databases

Hmm
 
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 ';