iOS Sqlist 数据库增、删、查 libsqlite3.0.dylib

//首先添加framework-------    libsqlite3.0.dylib
//头文件
#import "/usr/include/sqlite3.h" 

@interface ViewController : UIViewController<UIPopoverControllerDelegate, UISplitViewControllerDelegate>


- (void)viewDidLoad
{
    [super viewDidLoad];
    
    //打开 如果没有。则创建。
    [self openDatabase];
    //创建表
    [self createDatabaseTable];
    //在表中插入数据
    [self insertDatabaseTable];
    //查询
    [self queryDatabaseTable];
    //删
    [self deleteDatabaseTable];
}


//打开 如果没有。则创建。
-(void)openDatabase
{
    NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory   
                                                                , NSUserDomainMask    
                                                                , YES);    
    NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:@"mydb.sql"];   
    
    if (sqlite3_open([databaseFilePath UTF8String], &database)==SQLITE_OK)    
    {    
        NSLog(@"open sqlite mydb ok.");    
    }   
    else    
    {   
        NSLog( @"can not open sqlite mydb " );   
        //close database   
        sqlite3_close(database);    
    }   
}

//创建表
-(void)createDatabaseTable
{
    char *errorMsg;   
    const char *createSql="create table if not exists persons (id integer primary key autoincrement,name text)";   
    
    if (sqlite3_exec(database, createSql, NULL, NULL, &errorMsg)==SQLITE_OK)    
    {    
        NSLog(@"create ok.");    
    }   
    else    
    {   
        NSLog( @"can not create table" );   
        //[self ErrorReport:(NSString *)createSql];   
    }   
}

//在表中插入数据
- (void)insertDatabaseTable   
{   
    char *errorMsg;   
    
    const char *insertSql="insert into persons (name) values ('qingjoin')";    
    if (sqlite3_exec(database, insertSql, NULL, NULL, &errorMsg)==SQLITE_OK)    
    {    
        NSLog(@"insert ok.");    
    }   
    else    
    {   
        NSLog( @"can not insert it to table" );   
        //[self ErrorReport: (NSString *)insertSql];   
    }   
}   

- (void)ErrorReport: (NSString *)item   
{   
    char *errorMsg;   
    
    if (sqlite3_exec(database, (const char *)item, NULL, NULL, &errorMsg)==SQLITE_OK)   
    {    
        NSLog(@"%@ ok.",item);    
    }   
    else    
    {    
        NSLog(@"error: %s",errorMsg);    
        sqlite3_free(errorMsg);    
    }   
} 

//查询
- (void)queryDatabaseTable   
{   
    const char *selectSql="select id,name from persons";    
    sqlite3_stmt *statement;    
    if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil)==SQLITE_OK)   
    {    
        NSLog(@"select ok.");    
        while (sqlite3_step(statement)==SQLITE_ROW)//SQLITE_OK SQLITE_ROW   
        {    
            int _id=sqlite3_column_int(statement, 0);    
            NSString *name=[[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];    
            NSLog(@"row>>id %i, name>> %@",_id,name);    
        }   
        
    }   
    else    
    {   
        //error   
        [self ErrorReport: (NSString *)selectSql];   
    }   
    
    sqlite3_finalize(statement);   
}   

//删除
- (void)deleteDatabaseTable   
{      
    char *errorMsg;   
    [self openDatabase];   
    
    const char *sql = "DELETE FROM persons where id=24";   
    if (sqlite3_exec(database, sql, NULL, NULL, &errorMsg)==SQLITE_OK)    
    {    
        NSLog(@"delete ok.");    
    }   
    else    
    {   
        NSLog( @"can not delete it" );   
        [self ErrorReport: (NSString *)sql];   
    }   
    
}   


/*结果显示：   2012-10-11 17:31:13.401 sqlistDemo[3960:f803] open sqlite mydb ok.
　　　　　　　　2012-10-11 17:31:13.403 sqlistDemo[3960:f803] create ok.
　　　　　　　　2012-10-11 17:31:13.406 sqlistDemo[3960:f803] insert ok.
　　　　　　　　2012-10-11 17:31:13.407 sqlistDemo[3960:f803] select ok.
　　　　　　　　2012-10-11 17:31:13.407 sqlistDemo[3960:f803] row>>id 1, name>> qingjoin
　　　　　　　　2012-10-11 17:31:13.411 sqlistDemo[3960:f803] delete ok.
*/