sqlite基本用法（待总结）

@implementation CHViewController
//打开数据库
-(void)openDB
{
    NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory=[paths objectAtIndex:0];
    NSString *dbFileName=@"sample.db";
    NSString *dataFilePath=[documentsDirectory stringByAppendingPathComponent:dbFileName];
    
    if(sqlite3_open([dataFilePath UTF8String],&database)!=SQLITE_OK)
    {
        sqlite3_close(database);
        NSLog(@"打开数据库失败!");
    }
}
//创建表格
-(void)createTable
{
    char *errorMessage;
    NSString *sql=@"create table if not exists user(user_id integer primary key,username text,password text);";
    if(sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMessage)!=SQLITE_OK)
    {
        sqlite3_close(database);
        NSLog(@"创建表失败!");    }
}
//插入数据
-(void)insertData:(NSString *)username andPassword:(NSString *)password
{
    char *updateSql="insert or replace into user(username,password)values(?,?)";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(database, updateSql, -1, &statement, nil)==SQLITE_OK)
    {
        sqlite3_bind_text(statement, 1, [username UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [password UTF8String], -1, SQLITE_TRANSIENT);
    }
    
    if(sqlite3_step(statement)!=SQLITE_DONE)
    {
        NSLog(@"数据库插入数据失败");
    }
    else
    {
        NSLog(@"数据库插入数据ok");
    }
    

    
    sqlite3_finalize(statement);
    sqlite3_close(database);
    
}
//插入数据
- (IBAction)insertDataAction:(id)sender {
    [self openDB];
    [self createTable];
    [self insertData:@"testname4" andPassword:@"testpassword4"];
}
//查询数据
- (IBAction)queryDataAction:(id)sender {
    [self openDB];
    [self createTable];
    sqlite3_stmt *statement=nil;
    char *sql="select * from user";
    if(sqlite3_prepare_v2(database, sql, -1, &statement, NULL)==SQLITE_OK)
    {
        NSLog(@"faile query 1");
    }
    
    while(sqlite3_step(statement)==SQLITE_ROW)
    {
        char *tempName=(char *)sqlite3_column_text(statement,1);
        char *tempPassword=(char *)sqlite3_column_text(statement,2);
        NSString *userName=[NSString stringWithUTF8String:tempName];
        NSString *userPassword=[NSString stringWithUTF8String:tempPassword];
        
        NSLog(@"%@,%@",userName,userPassword);
    }
    
    sqlite3_finalize(statement);
    sqlite3_close(database);
    
}