sqlite数据库检索,sqlite数据库

  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_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 }
  1. 错误信息的输出

    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 今天就到这里。 数据库是数据持久化最常用的办法。
一定熟练掌握用户的各种需求实现的办法

qt与sqlite数据库检索

嗯嗯
 

怎在sqlite数据库中查找某年某月下的所有数据

db_xxx下的field_yyy字段为日期型字段
select * from db_xxx where substr(date(field_yyy),1,7) = ‘2012-07’;
 

1. 数据库检索,
得到某字段下所有值 1 – (NSArray *)selectWithColumName: (NSString *
)columName 2 tableName: (NSString * )table…

相关文章