SQLite浅析

对于iOS工程师有一道常考的面试题，即iOS数据存储的方式

标答如下：

Plist（NSArrayNSDictionary）

Preference (偏好设置NSUserDefaults)

NSCoding(NSKeyed ArchiverNSkeyedUnarchiver)

SQLite3

Core Data

今天就跟大家分享一下，SQLite3的基础知识

什么是SQLite

SQLite 是一款轻型的嵌入式关系型数据库，

它速度要强于Mysql，PostgreSQL，而且占用资源少，在嵌入式设备中可能只需要几百K的内存。

下面是创建该数据库的demo（备注我们需要导入libsqlite3.0.tbd的库）

#import "ViewController.h"
#import <sqlite3.h>
@interface ViewController ()

@end
@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    NSString *filename=[[NSSearchPathForDirectoriesInDomains(13, 1, 1)lastObject]stringByAppendingPathComponent:@"newsqlite.sqlite"];
    // Do any additional setup after loading the view, typically from a nib.
    sqlite3 *database=nil;
    //打开数据库,如果数据库不存在就创建
    if( sqlite3_open(filename.UTF8String,&database)==SQLITE_OK)
    {
        NSLog(@"打开成功");
        /**
         *  如果存在就无需创建
         */
        char *errmsg;
    NSString *sql=@"create table if not exists t_student(id integer primary key autoincrement,name text,age integer);";
        sqlite3_exec(database, sql.UTF8String, NULL, NULL, &errmsg);
        if(errmsg)
        {
        
            NSLog(@"创建表失败");
            
        }else{NSLog(@"创建表成功");
        
        }
        /**
         *  关闭数据库
         */
        sqlite3_close(database);
    }
    else{
        NSLog(@"打开数据库失败");
    }
    
}

@end

为了简便，我们把获取沙盒路径封装的类别里代码如下

NSString+Tool.h
#import <Foundation/Foundation.h>

@interface NSString (Tool)

+(NSString * )cachaPathName:(NSString *)fileName;

@end
NSString+Tool.m
#import "NSString+Tool.h"

@implementation NSString (Tool)

+(NSString * )cachaPathName:(NSString *)fileName{
    
    return [[NSSearchPathForDirectoriesInDomains(13, 1, 1) lastObject] stringByAppendingPathComponent:fileName];
}

@end

然后我们来实现一下，数据库中的增删改查四种操作

用故事板拖四个按钮分别是增删改查，在按钮的方法实现中实现对表的增删改查

#import "ViewController.h"
#import <sqlite3.h>
#import "NSString+Tool.h"

static     sqlite3 * dataBase = nil;

@interface ViewController ()

@property (nonatomic ,copy) NSString * filename;

@end

@implementation ViewController
- (IBAction)selectAction:(id)sender {
    
    //1.打开数据库
    if (sqlite3_open(self.filename.UTF8String,&dataBase) == SQLITE_OK) {
        
        //2.执行查询语句
        //准备查询
        
        NSString * sql = @"select * from t_sss;";
        
        //查询句柄
        sqlite3_stmt * stmt;
        
        if (sqlite3_prepare(dataBase, sql.UTF8String, -1, &stmt, NULL) == SQLITE_OK) {
            //查询数据
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                //获取表数据的内容
                NSString * name =  [NSString stringWithCString:(const char *)sqlite3_column_text(stmt, 1) encoding:NSUTF8StringEncoding];
                
                NSLog(@"name = %@",name);
                
                NSUInteger age = sqlite3_column_int(stmt, 2);
                
                NSLog(@"age = %zd",age);
                
            }
        }
        
        //3.关闭数据库
        sqlite3_close(dataBase);
    }
    
}

- (IBAction)insertAction:(id)sender {
    
    //1.打开数据库
    if (sqlite3_open(self.filename.UTF8String,&dataBase) == SQLITE_OK) {
        
        //2.执行插入语句
        for (int i = 0; i < 100; i ++) {
            
            NSString * sql =[NSString stringWithFormat: @"insert into t_sss (name,age) values ('%@',%zd);",@"别昱枢",arc4random_uniform(100)];
            char * errmsg;
            sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, &errmsg);
            if (errmsg) {
                NSLog(@"插入失败");
            }else{
                NSLog(@"插入成功");
            }
            //3.关闭数据库
            sqlite3_close(dataBase);
        }
      
    }
    
}
- (IBAction)deleteAction:(id)sender {
    
    //1.打开数据库
    if (sqlite3_open(self.filename.UTF8String,&dataBase) == SQLITE_OK) {
        
        //2.执行插入语句
        for (int i = 0; i < 100; i ++) {
            
            NSString * sql =@"delete from t_sss where name = 'gaga';";
            char * errmsg;
            sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, &errmsg);
            if (errmsg) {
                NSLog(@"删除失败");
            }else{
                NSLog(@"删除成功");
            }
            //3.关闭数据库
            sqlite3_close(dataBase);
        }
        
    }
    
    
}
- (IBAction)updateAction:(id)sender {
    
    //1.打开数据库
    if (sqlite3_open(self.filename.UTF8String,&dataBase) == SQLITE_OK) {
        
        //2.执行更新语句
        for (int i = 0; i < 100; i ++) {
            
            NSString * sql =@"update  t_sss set name = 'gaga' where age > 50;";
            
            char * errmsg;
            sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL, &errmsg);
            if (errmsg) {
                NSLog(@"更新失败");
            }else{
                NSLog(@"更新成功");
            }
            //3.关闭数据库
            sqlite3_close(dataBase);
        }
        
    }
    
}


    


- (void)viewDidLoad {
    
    [super viewDidLoad];
    
    NSString * filename = [NSString cachaPathName:@"sss.sqlite"];
    
    self.filename = filename;
    
    NSLog(@"%@",filename);
    
    //操作sqlite3
    
    //打开数据库，如果不存在就创建
    //arg1:数据库的沙盒存放地址
    //arg2:数据库的地址，也可以叫句柄
    
    if (sqlite3_open(filename.UTF8String, &dataBase) == SQLITE_OK)
    {
        NSLog(@"数据库打开成功");
        //建表,如果存在表就不建
        NSString * sql = @"create table if not exists t_sss (id integer primary key autoincrement , name text, age integer);";
       
        //执行数据库语句
        char * errmsg;
        sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL,&errmsg);
        
        if (errmsg) {
            NSLog(@"创建表失败");
        }else{
            NSLog(@"创建表成功");
        }
        
        //关闭数据库
        sqlite3_close(dataBase);
        
        
    }else{
        NSLog(@"数据库打开失败");
    }
}

@end

细心的同学可以看出增删改的操作相似，所以我们封装一下

SqliteManager.h
#import <Foundation/Foundation.h>

@interface SqliteManager : NSObject

+(BOOL) sqlite3WithSql :(NSString *)sql;

@end
SqliteManager.m
#import "SqliteManager.h"
#import "NSString+Tool.h"
#import <sqlite3.h>

static     sqlite3 * dataBase = nil;

@interface BYSSqliteManager ()

@property (nonatomic ,copy) NSString * filename;

@end

@implementation BYSSqliteManager

+(BOOL) sqlite3WithSql :(NSString *)sql{
    return NO;
}

+(void)initialize{
    
    NSString * filename = [NSString cachaPathName:@"sss.sqlite"];
    
    NSLog(@"%@",filename);
    
    //操作sqlite3
    
    //打开数据库，如果不存在就创建
    //arg1:数据库的沙盒存放地址
    //arg2:数据库的地址，也可以叫句柄
    
    if (sqlite3_open(filename.UTF8String, &dataBase) == SQLITE_OK)
    {
        NSLog(@"数据库打开成功");
        //建表,如果存在表就不建
        NSString * sql = @"create table if not exists t_sss (id integer primary key autoincrement , name text, age integer);";
        
        //执行数据库语句
        char * errmsg;
        sqlite3_exec(dataBase, sql.UTF8String, NULL, NULL,&errmsg);
        
        if (errmsg) {
            NSLog(@"创建表失败");
        }else{
            NSLog(@"创建表成功");
        }
        
        //关闭数据库
        sqlite3_close(dataBase);
        
        
    }else{
        NSLog(@"数据库打开失败");
    }
}

@end