iOS學習筆記（十五）——資料庫操作(SQLite)

 http://www.sqlite.org/docs.html

 

 

-(BOOL) openDB{   //擷取資料庫路徑    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);    NSString *documents = [paths objectAtIndex:0];    NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];        //如果資料庫存在，則用sqlite3_open直接開啟（不要擔心，如果資料庫不存在sqlite3_open會自動建立）    //開啟資料庫，這裡的[path UTF8String]是將NSString轉換為C字串，因為SQLite3是採用可移植的C(而不是    //Objective-C)編寫的，它不知道什麼是NSString.    if (sqlite3_open([database_path UTF8String], &db) == SQLITE_OK) {        return YES;    }else{        return NO;        NSLog(@"資料庫開啟失敗");        sqlite3_close(db);    }}

 

 

-(void)execSql:(NSString *)sql{        if ([self openDB]) {        char *err;        if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {            NSLog(@"資料庫操作資料失敗!");        }else{            NSLog(@"%@",sql);        }        sqlite3_close(db);    }    }

 

 

-(void) insertData{    NSString *insertSql1= [NSString stringWithFormat:                      @"INSERT INTO '%@' ('%@', '%@', '%@') VALUES ('%@', '%@', '%@')",                      TABLENAME, NAME, AGE, ADDRESS, @"張三", @"13", @"濟南"];    [self execSql:insertSql1];        NSString *insertSql2 = [NSString stringWithFormat:                      @"INSERT INTO '%@' ('%@', '%@', '%@') VALUES ('%@', '%@', '%@')",                      TABLENAME, NAME, AGE, ADDRESS, @"李四", @"12", @"濟南"];    [self execSql:insertSql2];    }

 

 

 

-(void) updateData{    NSString *updateSql = [NSString stringWithFormat:                      @"UPDATE '%@' SET '%@' = '%@' WHERE '%@' = '%@'",                      TABLENAME,   AGE,  @"15" ,AGE,  @"13"];    [self execSql:updateSql];}

         刪除表內容：

 

 

-(void) deleteData{    NSString *sdeleteSql = [NSString stringWithFormat:                      @"delete from %@ where %@ = '%@'",                      TABLENAME, NAME, @"張三"];    [self execSql:sdeleteSql];}

上面實現了表內容的增、改、刪，下面實現表內容查詢。

 

 

-(void) selectData{    [self openDB];    NSString *sqlQuery = [NSString stringWithFormat:                      @"SELECT * FROM %@",TABLENAME];    sqlite3_stmt * statement;        if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {                //查詢結果集中一條一條的遍曆所有的記錄，這裡的數字對應的是列值,注意這裡的列值        while (sqlite3_step(statement) == SQLITE_ROW) {            char *name = (char*)sqlite3_column_text(statement, 1);            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];                        int age = sqlite3_column_int(statement, 2);                        char *address = (char*)sqlite3_column_text(statement, 3);            NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];                        NSLog(@"name:%@  age:%d  address:%@",nsNameStr,age, nsAddressStr);        }    }else{        NSLog(@"select error:%@",sqlQuery);    }    sqlite3_close(db);}

 

 

 

 

@張興業TBOW