Sqlite3__代码操作

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];
        }
    }