SQLite3 iOS代码参考

SQLite3 iOS代码参考

#import "ViewController.h"
#import <sqlite3.h>
@interface ViewController ()
- (IBAction)createTabelAction:(UIButton *)sender;
- (IBAction)insertDataAction:(UIButton *)sender;
- (IBAction)selectFromAction:(UIButton *)sender;

@end

@implementation ViewController
static sqlite3 *_db;

- (void)viewDidLoad {
    [super viewDidLoad];
    
    
    // 不需要创建数据库文件,没有数据库文件,打开会自动创建
    NSString *path = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES).lastObject;
    NSString *dbpath = [path stringByAppendingPathComponent:@"person.sqlite"];
    
    const char *dbpathC = dbpath.UTF8String;
    
    // 打开数据库
    int result = sqlite3_open(dbpathC, &_db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库文件打开成功");
    }else{
        NSLog(@"数据库文件打开失败");
    }
}

/*! 创建表 */
- (IBAction)createTabelAction:(UIButton *)sender {

    NSString *createTableStr = @"create table if not exists t_person (id integer primary key autoincrement,name text not NULL, age integer not NULL)";
    
    char *error = NULL;
    int result = sqlite3_exec(_db, createTableStr.UTF8String,NULL, NULL, &error);
    
    if (result == SQLITE_OK) {
        NSLog(@"创表成功");
    }else{
        NSLog(@"创表失败 %s",error);
    }
    
}

/*! 插入表*/
- (IBAction)insertDataAction:(UIButton *)sender {

    for (int i = 0; i <20; i ++) {
        int temp = arc4random_uniform(20);
        int agetemp = arc4random_uniform(40)+20;
         NSString *insertDataSQL = [NSString stringWithFormat:@"insert into t_person (name,age) values ('apple-%d',%d)",temp,agetemp];
        
        char *error = NULL;
        int result =  sqlite3_exec(_db, insertDataSQL.UTF8String,NULL, NULL, &error);
        
        if (result == SQLITE_OK) {
            NSLog(@"插入成功");
        }else{
            NSLog(@"插入失败 error = %s", error);
        }
        
    }
}



/*! 更新记录*/
- (IBAction)updateDB:(UIButton *)sender {
    
    NSString *name = @"apple-5";
    NSString *updateSQL = [NSString stringWithFormat:@"update t_person set age = 200 where name = '%@'",name];
    
    char *error = NULL;
   int request =  sqlite3_exec(_db, updateSQL.UTF8String, NULL, NULL, &error);
    if (request == SQLITE_OK) {
        
        NSLog(@"更新成功");
    }else{
        NSLog(@"更新失败error = %s",error);
    }
    
}

/*! 删除记录*/
- (IBAction)deleteDBAction:(UIButton *)sender {
    
    NSString *name = @"apple-5";
    NSString *updateSQL = [NSString stringWithFormat:@"delete from t_person where name = '%@'",name];
    
    char *error = NULL;
    int request =  sqlite3_exec(_db, updateSQL.UTF8String, NULL, NULL, &error);
    if (request == SQLITE_OK) {
        
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败error = %s",error);
    }
}


/*! 选择记录 */
- (IBAction)selectDBAction:(UIButton *)sender {
    
    NSString *selectDBSQL = @"select * from t_person";
    
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare_v2(_db, selectDBSQL.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            int ID = sqlite3_column_int(stmt, 0);
            const char *name = (const char *)sqlite3_column_text(stmt, 1);
            int age = sqlite3_column_int(stmt, 2);
            NSLog(@"ID = %d,name = %s,age = %d",ID,name,age);
        }
    }
    sqlite3_finalize(stmt);
}


//
- (IBAction)selectFromAction:(UIButton *)sender {
    
    [self selectWithName:@"apple-3"];
}

// 按条件
- (void)selectWithName:(NSString *)str
{
    NSString *selectDBSQL = [NSString stringWithFormat:@"select * from t_person where name = '%@'",str];
    
    // 创建查询指针
    sqlite3_stmt *stmt = NULL;
    
    // 准备查询
    int result = sqlite3_prepare_v2(_db, selectDBSQL.UTF8String, -1, &stmt, NULL);
    
    // 如果查询结果有
    if (result == SQLITE_OK) {
        
        // 循环查找记录
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 通过每行查找出结果
            int ID = sqlite3_column_int(stmt, 0);
            const char *name = (const char *)sqlite3_column_text(stmt, 1);
            int age = sqlite3_column_int(stmt, 2);
            NSLog(@"ID = %d,name = %s,age = %d",ID,name,age);
        }
    }
    sqlite3_finalize(stmt);
}

@end