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