SQLite数据库使用
SQLite是一个小型的关系数据库,支持事务(原子性 atomicity、一致性Con sistency、隔离性Isolation和持久性Durability)简称ACID,触发器和大多数复杂的查询。 使用方式: 1、在命令行下或客户端软件如SQLite Database Browser工具创建所需要的数据表和默认数据,创建的文件为*.sqlite3文件,并将该文件引入到xcode的工程中。 2、在项目中配置SQLite的支持。 1)选择项目的根目录。 2)切换到 “Build Phases”标签下,展开“Link Binary With Libraries”。 3)点击加号按钮,选择“libsqlite3.dylib”,将该文件拖到Frameworks组下,即可使用SQLite。 3、初始化数据连接: // 数据库连接 static sqlite3 *database; // 打开数据库(单例) + (id) singleton { return [[[self alloc] init] autorelease]; } -(id) init { if ((self=[super init]) ) { if (database == nil) { [self createEditableCopyOfDatabaseIfNeeded]; [self initDatabaseConnection]; } } return self; } // Creates a writable copy of the bundled default database in the application Documents directory. - (void) createEditableCopyOfDatabaseIfNeeded { // First, test for existence. NSFileManager *fileManager = [NSFileManager defaultManager]; NSString *writableDBPath = [self sqliteDBFilePath]; // NSLog(@"%@", writableDBPath); if ([fileManager fileExistsAtPath: writableDBPath]) { return; } // The writable database does not exist, so copy the default to the appropriate location. NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:kSQLiteFileName]; NSError *error; if (![fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error]) { NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]); } } // 数据库文件路径 - (NSString *) sqliteDBFilePath { NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *path = [documentsDirectory stringByAppendingPathComponent:@"dblite.sqlite3"]; NSLog(@"path = %@", path); return path; } // 初始化数据库连接,打开连接,并返回数据库连接(存放在database中) - (void) initDatabaseConnection { if (sqlite3_open([[self sqliteDBFilePath] UTF8String], &database) != SQLITE_OK) { sqlite3_close(database); NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database)); } } 4、数据的增删改: + (void) editAlbum: (Album *) album { sqlite3_stmt *statement; static char *sql = "UPDATE album SET title = ? WHERE albumid = ?"; //static char *sql = "INSERT INTO album (albumid,title) VALUES (NULL,?)"; // static char *sql = "DELETE FROM album WHERE albumid = ?"; if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) { NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase)); } sqlite3_bind_text(statement, 1, [album.title UTF8String], -1, NULL); sqlite3_bind_int(statement, 2, album.albumid); if (sqlite3_step(statement) == SQLITE_ERROR) { NSAssert1(0, @"Error: failed to edit album with message '%s'.", sqlite3_errmsg(kAlbumDatabase)); } sqlite3_finalize(statement); } 5、数据的查询: + (NSMutableArray *) fetchAlbums { NSMutableArray *albums = [NSMutableArray array]; // Compile the query for retrieving data. if (fetchAlbumsStatement == nil) { const char *sql = "SELECT albumid, title FROM album"; if (sqlite3_prepare_v2(kAlbumDatabase, sql, -1, &fetchAlbumsStatement, NULL) != SQLITE_OK) { NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(kAlbumDatabase)); } } while (sqlite3_step(fetchAlbumsStatement) == SQLITE_ROW) { Album *_album = [[Album alloc] init]; _album.albumid = sqlite3_column_int(fetchAlbumsStatement, 0); _album.title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(fetchAlbumsStatement, 1)]; [albums addObject:_album]; [_album release]; } // Reset the statement for future reuse. sqlite3_reset(fetchAlbumsStatement); return albums; } 6、关闭数据库: // 关闭数据库连接 - (void) closeDatabase { if (sqlite3_close(database) != SQLITE_OK) { NSAssert1(0, @"Error: failed to close database with message '%s'.", sqlite3_errmsg(database)); } }
版版权所有:ForrestWang,转载请注明出处!