OC中数据库操作（类方法）

1.创建表格

+(sqlite3 *)getMySQL{

    sqlite3 *mySQL;

    //打开数据库，如果没有就创建

　　//拼接路径

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

        NSString *filePath = [docPath stringByAppendingPathComponent:@"Worker.SQL"];

        NSLog(@"%@",filePath);

        //调用open方法

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

            NSLog(@"open error");

            return 0;

        }

        NSLog(@"open OK");

        //3.创建表的sql语句

        NSString *createTableSql = @"create table if not exists Workers (id integer primary key autoincrement,name varchar(30),address varchar(40),image blob)";

        char *errMsg = NULL;

　　//写入数据库

        if (sqlite3_exec(mySQL, [createTableSql UTF8String], NULL, NULL, &errMsg)) {

            NSLog(@"%s",errMsg);

            return 0;

        }

        NSLog(@"create table ok");

    return mySQL;

}

 2.表格里插入语句

+(void)insertSqlWithName:(NSString *)name Address:(NSString *)address Image:(UIImage *)image{

    sqlite3 *database = [SQLFactory getMySQL];

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

    sqlite3_stmt *statement;

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

        NSLog(@"insert prepare error");

        return;

    }else{

        NSLog(@"insert prepare OK");

        NSData *imgData = UIImagePNGRepresentation(image);

        sqlite3_bind_text(statement, 1, [name UTF8String], -1, NULL);

        sqlite3_bind_text(statement, 2, [address UTF8String], -1, NULL);

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

        if (sqlite3_step(statement) != SQLITE_DONE) {

            NSLog(@"执行插入语句失败");

        }else{

            NSLog(@"执行插入语句成功");

        }

    }

}

 3，读取数据

+(NSMutableArray *)showSqlWithSeletType:(NSString *)selectType{

    NSMutableArray *workerModelsArray = [NSMutableArray array];

    sqlite3 *sql = [SQLFactory getMySQL];

    sqlite3_stmt *statement;

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

        NSLog(@"read prepare error");

        return 0;

    }else{

        NSLog(@"read prepare OK");

        while (sqlite3_step(statement) == SQLITE_ROW) {

            WorkerModel *model = [[WorkerModel alloc]init];

 

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

            model.workerName = [NSString stringWithUTF8String:name];

            

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

            model.workerAddress = [NSString stringWithUTF8String:address];

            

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

            int length = sqlite3_column_bytes(statement, 3);

            

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

            UIImage *img = [UIImage imageWithData:imgData];

            model.workerIconImage = img;

            [workerModelsArray addObject:model];

        }

    }

    return workerModelsArray;

}

 

转载于:https://www.cnblogs.com/yangqinglong/p/5367136.html