UIday1901:IOS中sqlite的使用

 SQLite 嵌入式数据库
 1. ⽀持事件，不需要配置，不需要安装，不需要管理员；
 2. ⽀持⼤部分SQL92;
 3. 完整的数据库保存在磁盘上⾯一个⽂件，同一个数据库⽂件可以在不同机器上⾯使用，最大
 ⽀持数据库到2T
 4. 整个系统少于3万行代码，少于250KB的内存占⽤;
 5. 源代码开发，代码 5%有较好的注释，简单易用的API;

引入libsqlite3.dylib  引入方法：Build Phases ---- Link Binary With Libraries ---- ' + '

ViewController.m

#import "ViewController.h"
#import "DataBaseHandle.h"

@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    
    //创建数据库对象
    DataBaseHandle * dbh = [DataBaseHandle shareDataBase];
    
    //**********************
    //打开数据库
    [dbh openDB];
    
    //创建表
//    [dbh createTableWithName:@"student"];
    
    //插入数据
//    [dbh insertTableWithTableName:@"student" sName:@"王老五" sAge:25];
//    [dbh insertTableWithTableName:@"student" sName:@"324" sAge:20];
//    [dbh insertTableWithTableName:@"student" sName:@"345" sAge:28];
//    [dbh insertTableWithTableName:@"student" sName:@"234" sAge:21];
//    [dbh insertTableWithTableName:@"student" sName:@"ert" sAge:29];
//    [dbh insertTableWithTableName:@"student" sName:@"dg" sAge:35];
//    [dbh insertTableWithTableName:@"student" sName:@"fgh" sAge:55];
    
//    //修改数据
//    [dbh updateTableWithTableName:@"student"];
//    
//    //删除表中的数据
//    [dbh deleteTableWithTableName:@"student"];
    
    //查找数据
    [dbh selectData:30];
    
    //关闭数据库
    [dbh closeDB];
    
    
    
    
}



- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end

DataBaseHandle.h

#import <Foundation/Foundation.h>

@interface DataBaseHandle : NSObject

+(instancetype)shareDataBase;

//打开数据库
-(void)openDB;

//关闭数据库
-(void)closeDB;

//创建表
-(void)createTableWithName:(NSString *)name;

//插入数据
-(void)insertTableWithTableName:(NSString *)tableName sName:(NSString *)sname sAge:(NSInteger)sage;

//更新数据
-(void)updateTableWithTableName:(NSString *)tableName;

//删除数据
-(void)deleteTableWithTableName:(NSString *)tableName;

//查找数据
-(NSArray *)selectData:(NSInteger)sage;

@end

DataBaseHandle.m

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Model.h"

static DataBaseHandle *d = nil;

@implementation DataBaseHandle

+(instancetype)shareDataBase{
    if (d == nil) {
        
        d=[[DataBaseHandle alloc]init];
        
    }
    return d;
}

// 首先导入框架sqlite3

// 创建数据库指针
static sqlite3 *db = NULL;

//打开数据库
-(void)openDB{
    
    //拼接路径
    NSString * filePath = [[self p_doucumnetsPath]stringByAppendingPathComponent:@"DataBase.sqlite"];
    NSLog(@"%@",filePath);
    //打开数据库(根据路径打开，如果没有创建再打开)
    //int类型返回，用于判断语句是否执行成功
    int result = sqlite3_open(filePath.UTF8String, &db);
    
    //SQLITE_OK 宏定义 表示语句执行成功
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }
}



//获取documents文件路径
-(NSString *)p_doucumnetsPath{
    
    NSString * s = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    
    return s;
}

//关闭数据库
-(void)closeDB{
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    }else{
        NSLog(@"数据库关闭失败");
    }
}

//创建表
-(void)createTableWithName:(NSString *)name{
    //准备sql语句
    NSString * sqlStr = [NSString stringWithFormat:@"create table if not exists %@(s_id integer primary key autoincrement not null,s_name text not null,s_age integer) ",name];
    // 接收错误
    char *err = NULL;
    // 执行语句
    int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, &err);
    
    if(result == SQLITE_OK){
        NSLog(@"建表成功");
    }else{
        NSLog(@"建表失败,%s",err);
    }
    
}

//插入数据
-(void)insertTableWithTableName:(NSString *)tableName sName:(NSString *)sname sAge:(NSInteger)sage{
    //准备sql语句
    NSString *sqlStr = [NSString stringWithFormat:@"insert into %@(s_name,s_age) values('%@',%ld)",tableName,sname,sage];
    //接收错误
    char *err = NULL;
    //执行语句
    int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, &err);
    
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败 %s",err);
    }
}

//更新数据
-(void)updateTableWithTableName:(NSString *)tableName{
    NSString * sqlStr = [NSString stringWithFormat:@"update %@ set s_name = '王老六' where s_name = '王老五'",tableName];
    int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"更改成功");
    }else{
        NSLog(@"更改失败");
    }
    
}

//删除数据
-(void)deleteTableWithTableName:(NSString *)tableName{
    NSString * sqlStr = [NSString stringWithFormat:@"delete from %@ where s_age > 35 ",tableName];
    int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

//查找数据
-(NSArray *)selectData:(NSInteger)sage{
    
    //数组
    NSMutableArray * dataArray = [NSMutableArray array];
    
    //1 准备语句
    NSString *sqlStr = @"select * from student where s_age > ?";
    
    //2 伴随指针
    sqlite3_stmt * stmt = nil;
    
    //3 预执行 无符号当中-1是最大位的正整数
    int result = sqlite3_prepare_v2(db, sqlStr.UTF8String, -1, &stmt, NULL);
    
    //4
    if (result == SQLITE_OK) {
        //4 确定参数
        // 第一个参数是返回值
        // 第二个参数是确定要和第几个问号绑定
        // 第三个参数是值
        sqlite3_bind_int64(stmt, 1, sage);
//        sqlite3_bind_text(<#sqlite3_stmt *#>, <#int#>, <#const char *#>, <#int n#>, <#void (*)(void *)#>)
        
        //5 遍历
//        int result = sqlite3_step(stmt);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            NSInteger sid = sqlite3_column_int64(stmt, 0);
            
            NSString * sname =[NSString stringWithUTF8String:sqlite3_column_text(stmt, 1)] ;
            
            NSInteger sage = sqlite3_column_int64(stmt, 2);
            
            //创建Model对象
            Model *m = [[Model alloc]init];
            m.sid = sid;
            m.sage = sage;
            m.sname = sname;
            
            //将model对象存入数组
            [dataArray addObject:m];
            
            NSLog(@"sid = %ld sname = %@ sage = %ld",sid,sname,sage);
        }
        
        //关闭伴随指针(如果不关 数据库也关不上)
        sqlite3_finalize(stmt);
        
    }else{
        NSLog(@"查找失败");
    }
    
    return dataArray;
    
}

@end

Model.h

#import <Foundation/Foundation.h>

@interface Model : NSObject

@property(nonatomic,assign)NSInteger sid;
@property(nonatomic,copy)NSString * sname;
@property(nonatomic,assign)NSInteger sage;

@end

Model.m

#import "Model.h"

@implementation Model

-(void)setValue:(id)value forUndefinedKey:(NSString *)key{
    
}

@end