@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,&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],sqlITE_TRANSIENT);
        sqlite3_bind_text(statement,2,[password UTF8String],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,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);
    
}