ios Sqlite3的简单使用 增删改查

- (void)viewDidLoad {

    [superviewDidLoad];

    

    // 数据库操作句柄

   sqlite3 *dbHandle;

    

    // 数据库路径

    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)[0];

    NSString *dbPath = [documentPathstringByAppendingPathComponent:@"stock.db"];

    

   //打开数据库获取句柄不存在则会创建

   int result =sqlite3_open([dbPathUTF8String], &dbHandle);

   if (result ==SQLITE_OK) {

       //创建表

        NSString * sql =@"CREATE TABLE IF NOT EXISTS t_stock_info (Number text PRIMARY KEY,Name text NOT NULL,Data text NOT NULL,FinalPrice float NOT NULL);";

       char *error =NULL;

        result =sqlite3_exec(dbHandle, [sql UTF8String],NULL,NULL, &error);

       if (result ==SQLITE_OK) {

            

        }

       else

        {

            [ShowAlertControllershowAlertInVC:self.view.window.rootViewControllermessage:@"Error"title:@"Open / Create table failed!"];

        }

    }

   else

    {

        [ShowAlertControllershowAlertInVC:self.view.window.rootViewControllermessage:@"Error"title:@"Open / Create database failed!"];

    }

    

    [selfinsertDataNumber:@"600582"name:@"天地科技"data:@"2017.2.21"finalPrice:5.4toSqliteDB:dbHandlewithTableName:@"t_stock_info"];

    [selfinsertDataNumber:@"002266"name:@"浙富控股"data:@"2017.2.23"finalPrice:5.8toSqliteDB:dbHandlewithTableName:@"t_stock_info"];

    

    [selfgetInfoFromTable:@"t_stock_info"inSqliteDB:dbHandle];

    

    [selfdeletName:@"Name"realName:@"浙富控股"inSqliteDB:dbHandletbName:@"t_stock_info"];

    

    [selfupdatePrice:5.7name:@"FinalPrice"inSqliteDB:dbHandletbName:@"t_stock_info"];

}

#pragma mark -增

-(void)insertDataNumber:(NSString *)number name:(NSString *)name data:(NSString *)data finalPrice:(float)price toSqliteDB:(sqlite3 *)dbHandle withTableName:(NSString *)tbName

{

    NSString *sql=[NSStringstringWithFormat:@"INSERT INTO %@ (Number,Name,Data,FinalPrice) VALUES ('%@','%@','%@',%f);",tbName,number,name,data,price];

    

   char *errmsg =NULL;

   int result =sqlite3_exec(dbHandle, sql.UTF8String,NULL,NULL, &errmsg);

   if (result ==SQLITE_OK) {

        

    }

   else

    {

        

    }

}

#pragma mark -查

-(void)getInfoFromTable:(NSString *)tbName inSqliteDB:(sqlite3 *)dbHandle

{

    NSString *sql =@"SELECT * FROM t_stock_info WHERE FinalPrice>=0;";

   sqlite3_stmt *stmt =NULL;

   //进行查询前的准备工作

   int result =sqlite3_prepare_v2(dbHandle, [sqlUTF8String], -1, &stmt,NULL);

   if (result ==SQLITE_OK) {//SQL语句没有问题

    // 每调用一次sqlite3_step函数，stmt就会指向下一条记录

    NSLog(@"Begin get info");

   while (sqlite3_step(stmt) ==SQLITE_ROW) {//找到一条记录

    // 取出数据

        char *number = (char *)sqlite3_column_text(stmt,0);

        char *name = (char *)sqlite3_column_text(stmt,1);

        char *data = (char *)sqlite3_column_text(stmt,2);

        float price =sqlite3_column_double(stmt,3);

        

        NSLog(@"number = %@ ,name = %@ ,data %@,price %f",[NSStringstringWithUTF8String:number],[NSStringstringWithUTF8String:name],[NSStringstringWithUTF8String:data],price);

        }

    }

   else

    {

        

    }

}

#pragma mark -改

-(void)updatePrice:(float)price name:(NSString *)name inSqliteDB:(sqlite3 *)dbHandle tbName:(NSString *)tbName

{

    NSString *sql = [NSStringstringWithFormat:@"UPDATE %@ SET %@ = %f WHERE Name == '天地科技';",tbName,name,price];

   sqlite3_stmt *stmt =NULL;

    

   //进行查询前的准备工作

   int result =sqlite3_prepare_v2(dbHandle, [sqlUTF8String], -1, &stmt,NULL);

   if (result ==SQLITE_OK) {//SQL语句没有问题

           if (sqlite3_step(stmt) ==SQLITE_DONE) {

               sqlite3_finalize(stmt);

                

                [selfgetInfoFromTable:tbNameinSqliteDB:dbHandle];

            }

    }

   else

    {

        

    }

}

#pragma mark -删

-(void)deletName:(NSString *)name realName:(NSString *)realName inSqliteDB:(sqlite3 *)dbHandle tbName:(NSString *)tbName

{

   NSString *sql = [NSStringstringWithFormat:@"DELETE FROM %@ WHERE %@ == '%@';",tbName,name,realName];

   sqlite3_stmt *stmt =NULL;

    

   //进行查询前的准备工作

   int result =sqlite3_prepare_v2(dbHandle, [sqlUTF8String], -1, &stmt,NULL);

   if (result ==SQLITE_OK) {//SQL语句没有问题

       if (sqlite3_step(stmt) ==SQLITE_DONE) {

           sqlite3_finalize(stmt);

            

            [selfgetInfoFromTable:tbNameinSqliteDB:dbHandle];

        }

    }

   else

    {

        

    }

}