iOS SQLite3的使用

1.创建可修改的数据库文件

//应用包内的内容是不可写的，所以需要把应用包内的数据库拷贝一个副本到资源路径去
- (void)createEditableDatabase{
    
    BOOL success;
    NSFileManager *manager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDir = [paths objectAtIndex:0];
    NSString *writableDB = [documentDir stringByAppendingPathComponent:@"catalog.db"];
    
    success = [manager fileExistsAtPath:writableDB];
    
    if (success) {
        NSLog(@"已经存在");
        return;
    }
    
    NSString *defaultPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"catalog.db"];
    success = [manager copyItemAtPath:defaultPath toPath:writableDB error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file:'%@'.", [error localizedDescription]);
    }else NSLog(@"成功写入");
}

2.初始化数据库

- (void)initDatabase{
    
    NSString *path = [[NSBundle mainBundle] pathForResource:@"catalog" ofType:@"db"];
    //NSLog(@"bundle = %@
path = %@",[NSBundle mainBundle],path);
    if (sqlite3_open([path UTF8String],&database) == SQLITE_OK) {
        NSLog(@"Opening Database");
    }else{
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database:'%s'.", sqlite3_errmsg(database));
    }
}

3.查询

//execute sql statement
- (NSMutableArray *)getAllProducts{
    
    NSMutableArray *products = [NSMutableArray new];
    //    const char *sql = "SELECT product.ID,product.Name,Manufacturer.name,product.details,product.price,product.quantityonhand,country.country,product.image FROM Product,Manufacturer,Country WHERE manufacturer.manufacturerID = product.manufacturerID and product.countryoforiginID = ?";
    //配合sqlite3_bind_int(stmt,1,2)可使用参数化sql语句查询
    
    const char *sql = "SELECT product.ID,product.Name,Manufacturer.name,product.details,product.price,product.quantityonhand,country.country,product.image FROM Product,Manufacturer,Country WHERE manufacturer.manufacturerID = product.manufacturerID and product.countryoforiginID = country.countryID";
    
    NSLog(@"
sql = %s",sql);
    
    sqlite3_stmt *stmt;
    int sqlResult = sqlite3_prepare_v2(database,sql,-1,&stmt,NULL);
    //sqlite3_bind_int(stmt,1,2);//sql语句参数查询，语句、参数索引、参数值
    
    if (sqlResult == SQLITE_OK) {
        NSLog(@"Ready to print sth");
        int time = 0;
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            Product *product = [Product new];
            char *name = (char*)sqlite3_column_text(stmt,1);
            char *manufacturer = (char*)sqlite3_column_text(stmt,2);
            char *details = (char*)sqlite3_column_text(stmt, 3);
            char *countryOfOrigin = (char*)sqlite3_column_text(stmt, 6);
            char *image = (char*)sqlite3_column_text(stmt, 7);
            
            NSLog(@"%d,name = %s 
",time++,name);
            
            product.ID = sqlite3_column_int(stmt,0);
            product.name = (name)?[NSString stringWithUTF8String:name]:@"";
            product.manufacturer = (manufacturer)?[NSString stringWithUTF8String:manufacturer]:@"";
            product.details = (details)?[NSString stringWithUTF8String:details]:@"";
            product.price = sqlite3_column_double(stmt,4);
            product.quantity = sqlite3_column_int(stmt,5);
            product.countryOfOrigin = (countryOfOrigin)?[NSString stringWithUTF8String:countryOfOrigin]:@"";
            product.image = (image)?[NSString stringWithUTF8String:image]:@"";
            
            [products addObject:product];
        }
        
        sqlite3_finalize(stmt);
    }else{
        NSLog(@"read failed:%d",sqlResult);
    }
    
    return products;
}

4.关闭数据库

- (void)closeDatabase{
    if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error:failed to close database:'%s'.", sqlite3_errmsg(database));
    }
}