sqlite--代碼操作

標籤：

1.建立資料庫

        NSString * docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];

        NSLog(@"%@",docPath);

        NSString * filePath = [docPath stringByAppendingPathComponent:@"stu.sql"];

        if (sqlite3_open([filePath UTF8String], &_mySQL)!=SQLITE_OK) {

            NSLog(@"open error");

            return 0;

        }

        NSLog(@"open ok");

 2.建立表       

        NSString *createTablesql = @"create TABLE IF NOT EXISTS Student (id integer primary key autoincrement,name varchar(20),address varchar(20),icon blob)";

        char *error = nil;

        if (sqlite3_exec(_mySQL, [createTablesql UTF8String], nil, nil, &error)!=SQLITE_OK) {

            NSLog(@"creat table error");

            return 0;

        }

        NSLog(@"creat table ok");

3.插入資料

    NSString *insertSql =  @"insert into Student (name,address,icon) values (?,?,?)";

    sqlite3_stmt *statement;

    if (sqlite3_prepare_v2(mysql, [insertSql  UTF8String], -1, &statement, NULL) != SQLITE_OK) {

        NSLog(@"insert prepare error");

        return;

    }else{

        NSLog(@"insert prepare ok");

        NSData * imageData = UIImagePNGRepresentation(self.iconImageView.image);

        sqlite3_bind_text(statement, 1, [_nameTextField.text UTF8String], -1, NULL);

        sqlite3_bind_text(statement, 2, [_addTextField.text UTF8String], -1, NULL);

        sqlite3_bind_blob(statement, 3, [imageData bytes], (int)[imageData length], NULL);

        if(sqlite3_step(statement)!=SQLITE_DONE){

            NSLog(@"insert error");

        }else{

            NSLog(@"insert ok");

        }

    }    

    sqlite3_finalize(statement);

4.查詢資料

    NSString *selectSql = @"select * from Student";

    sqlite3_stmt *statement; 

    if (sqlite3_prepare_v2(mysql, [selectSql UTF8String], -1, &statement, NULL) != SQLITE_OK) {

        NSLog(@"read prepare error");

        return;

    }else{

        NSLog(@"read prepare ok");

        while (sqlite3_step(statement)==SQLITE_ROW) {

            Student * stu = [[Student alloc]init];

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

            char * address = (char *)sqlite3_column_text(statement, 2);

            Byte * bytes = (Byte *)sqlite3_column_blob(statement, 3);

            int length = sqlite3_column_bytes(statement, 3);

            NSData * data = [NSData dataWithBytes:bytes length:length];

            UIImage * image = [UIImage imageWithData:data];

            stu.name = [NSString stringWithUTF8String:name];

            stu.address = [NSString stringWithUTF8String:address];

            stu.icon = image;

            [_studentArray addObject:stu];

        }

    }

sqlite--代碼操作