IOS成長之路-資料庫的資料添加和查詢

/*= = = - - - = = =     sqlite3 資料庫裡添加資料     = = = - - - = = = */        //1.定義一個sqlite3的指標    sqlite3 *db;    //2.用來接收讀寫資料庫操作是否成功的傳回值    int dbrc;    //3.轉換資料庫路徑為UTF8的c風格字串    const char* dbFilePathUTF8 = [dbFilePath UTF8String];    //4.開啟資料庫    dbrc = sqlite3_open(dbFilePathUTF8, &db);    //判斷是否成功開啟    if (dbrc == SQLITE_OK) {        NSLog(@"成功開啟資料庫");    }    //5.定義執行sql語句的指標    sqlite3_stmt *dbps;    //6.定義sql語句    NSString *insertStatementNS = [NSString stringWithFormat:                                   @"insert into \"shopping\"\   (item, price, groupid, dateadd)\   values (\"%@\", %@, %d, DATETIME('NOW'))",   @"lll",   @"350",                                   2];    //7.將定義的NSString的sql語句，轉換成UTF8的c風格的字串    const char *insertStatement = [insertStatementNS UTF8String];            //8.sql語句的預執行。（判斷sql語句是否符合文法）        dbrc = sqlite3_prepare_v2     (db, insertStatement, -1, &dbps, NULL);        //9.執行sql語句    dbrc = sqlite3_step (dbps);        NSLog(@"dbrc = %d",dbrc);                    /*= = = - - - = = =     資料庫裡讀取資料     = = = - - - = = =*/        //1.定義一個查詢語句        NSString *queryStatementNS =@"select key,item,price,groupid,dateadd from shopping";        //2.將定義的NSString的查詢語句轉換成UTF8的c字串        const char *queryStatement = [queryStatementNS UTF8String];        //3.sql語句的預先處理        dbrc = sqlite3_prepare_v2 (db, queryStatement, -1, &dbps, NULL);NSLog (@"prepared statement=%d",dbrc);        //4.擷取值，要用相匹配的方法去擷取相應類型的值，並要轉換類型    while ((dbrc = sqlite3_step (dbps)) == SQLITE_ROW)    {               //擷取主健的值 int型的 用 sqlite3_column_int        int primaryKeyValueI = sqlite3_column_int(dbps, 0);        //轉換成 NSNumber 類型的NSNumber *primaryKeyValue = [[NSNumber alloc] initWithInt: primaryKeyValueI];                NSLog(@"primaryKeyValue = %@",primaryKeyValue);        //擷取item的值 字串用 sqlite3_column_text  NSString *itemValue = [[NSString alloc]   initWithUTF8String: (char*) sqlite3_column_text (dbps, 1)];                NSLog(@"itemValue = %@",itemValue);                //擷取price的值  double類型用 sqlite3_column_doubledouble priceValueD = sqlite3_column_double (dbps, 2);                NSLog(@"priceValueD = %lf",priceValueD);        //轉換類型NSNumber *priceValue = [[NSNumber alloc]initWithDouble: priceValueD];                NSLog(@"priceValue =  %@",priceValue);                //擷取guoupid的值int groupValueI = sqlite3_column_int(dbps, 3);                NSLog(@"groupValueI =  %d",groupValueI);        NSNumber *groupValue = [[NSNumber alloc]initWithInt: groupValueI];                NSLog(@"groupValue =  %@",groupValue);                //擷取dateadd的值NSString *dateValueS = [[NSString alloc] initWithUTF8String:(char*)sqlite3_column_text (dbps, 4)];                NSLog(@"dateValueS =  %@",dateValueS);                    }            //10.釋放資源    sqlite3_finalize (dbps);        //11.關閉資料庫        sqlite3_close(db);