ios - 如何理解 count number sqlite 行数？

Question

我在文档文件夹应用程序中有一个 sqlite 数据库。我将来自 NSDictionary 的数据存储在 sqlite 数据库中。我想知道如何了解 sqlite 数据库中有多少行并将其放入 NSString 变量中。

这是我在 sqlite 中存储数据的代码(在文档文件夹中):

#define DatabaseName @"data.sqlite"
@implementation ViewController
- (void)viewDidLoad
{
    //NSString *rowNumber = I dont know how to Obtain number of rows...
    [super viewDidLoad];
    NSArray *idd = [NSArray arrayWithObjects:@"122",@"234",@"453", nil];
    NSArray *name = [NSArray arrayWithObjects:@"janatan",@"fred",@"john", nil];
    NSArray *age = [NSArray arrayWithObjects:@"23",@"35",@"12", nil];
    NSArray *sex = [NSArray arrayWithObjects:@"male",@"male",@"male", nil];
    NSString *query2  = [NSString stringWithFormat:@"select * from table1 where name = 'john'"];
    NSLog(@"query : %@",query2);
    BOOL recordExist = [self recordExistOrNot:query2];
    if (!recordExist) {
        for (int i = 0; i < [idd count]; i++) {
            NSString * a = [idd objectAtIndex:i];
            NSString * b = [name objectAtIndex:i];
            NSString * c = [age objectAtIndex:i];
            NSString * d = [sex objectAtIndex:i];
            NSDictionary * dic = [NSDictionary dictionaryWithObjectsAndKeys:a,@"id",b,@"name",c,@"age",d,@"sex", nil];
            NSString *query = [NSString stringWithFormat:@"insert into table1 (id,name,age,sex) values('%@','%@','%@','%@')",[dic objectForKey:@"id"],[dic objectForKey:@"name"],[dic objectForKey:@"age"],[dic objectForKey:@"sex"]];
            NSLog(@"%@",query);
            [self executeQuery:query];
        }
    }
}
-(NSString *) dataFilePath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    //NSLog(@"PATH %@",[documentsDirectory stringByAppendingPathComponent:DatabaseName]);
    return [documentsDirectory stringByAppendingPathComponent:DatabaseName];
}
/*==================================================================
 METHOD FOR CHECKING WHETHER RECORD EXISTS OR NOT IN DATABASE
 ==================================================================*/
-(BOOL)recordExistOrNot:(NSString *)query{
    BOOL recordExist=NO;
    if(sqlite3_open([[self dataFilePath] UTF8String], &database) == SQLITE_OK) {
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil)==SQLITE_OK) {
            if (sqlite3_step(statement)==SQLITE_ROW) {
                recordExist=YES;
            } else {
                //////NSLog(@"%s,",sqlite3_errmsg(database));
            }
            sqlite3_finalize(statement);
            sqlite3_close(database);
        }
    }
    return recordExist;
}
/*==================================================================
 METHOD FOR INSERTING DATA IN DATABASE
 ==================================================================*/
-(void)executeQuery:(NSString *)query {
    sqlite3_stmt *statement;
    if(sqlite3_open([[self dataFilePath] UTF8String], &database) == SQLITE_OK) {
        if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, NULL) == SQLITE_OK) {
            if (sqlite3_step(statement) != SQLITE_DONE) {
                sqlite3_finalize(statement);
            }
        } else {
            NSLog(@"query Statement Not Compiled");
        }
        sqlite3_finalize(statement);
        sqlite3_close(database);
    } else {
        NSLog(@"Data not Opened");
    }
}
@end

你猜 top NSArray 有多少个值(不是四个)。

Answer 1

请尝试使用这个

执行这个查询

select count(*) from table_name;

- (int) getNumberOfRecord
{
    int count = 0;
    if (sqlite3_open([self.dataBasePath UTF8String], &database) == SQLITE_OK)
    {
        const char* sqlStatement = "SELECT COUNT(*) FROM table1";
        sqlite3_stmt *statement;

        if( sqlite3_prepare_v2(database, sqlStatement, -1, &statement, NULL) == SQLITE_OK )
        {
            //Loop through all the returned rows (should be just one)
            while( sqlite3_step(statement) == SQLITE_ROW )
            {
                count = sqlite3_column_int(statement, 0);
            }
        }
        else
        {
            NSLog( @"Failed from sqlite3_prepare_v2. Error is:  %s", sqlite3_errmsg(database) );
        }

        // Finalize and close database.
        sqlite3_finalize(statement);
        sqlite3_close(database);
    }

    return count;
}

希望对你有所帮助