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 = %@\npath = %@",[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(@"\nsql = %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 \n",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));    }}

 

iOS SQLite3的使用