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_double
		double 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);