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