SQLite的使用

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

@interface ViewController ()
{
    // 用于存储数据model
    NSMutableArray *_arrayM;
    
    // 数据库的句柄（在c语言中，通常把用于控制的类叫做句柄）
    sqlite3 *_dataBase;
}
@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    
    _arrayM = [NSMutableArray array];
    
    // 1. 打开数据库
    [self openDataBase];
    
    // 2. 创建数据表
    [self creatTable];
    
    // 3. 给数据表添加数据
    [self addModelToTable];
    
    // 4. 从数据库中读取数据
    [self getModel];
}

- (void)openDataBase
{
    // 1. 设置数据库的路径
    NSString *path = [NSString stringWithFormat:@"%@/Documents/model.db",NSHomeDirectory()];
    NSLog(@"%@",path);
    
    // 2. 打开数据库（系统会检测，如果这个路径不存在在这个数据库，就创建一个并打开，如果有的话，直接打开）
    if (SQLITE_OK == sqlite3_open([path UTF8String], &_dataBase)) {
        NSLog(@"数据库打开成功~");
    }else{
       NSLog(@"数据库打开失败~");
    }
}

- (void)creatTable
{
    // 创建数据表
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS T_Model (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT, color TEXT,  age INTEGER, length REAL)";
    
    // 执行sql 语句
    [self sqlExecWithSQL:sqlStr message:@""];
}

- (void)addModelToTable
{
    // 插入数据（如果不设置主键，系统会帮助我们设置主键，按次序增长）
    NSString *sqlStr1 = [NSString stringWithFormat:@"INSERT INTO T_Model(name,age,color,length) VALUES ('花花',10,'花色',100.4)"];
    [self sqlExecWithSQL:sqlStr1 message:@""];
    
    NSString *sqlStr2 = [NSString stringWithFormat:@"INSERT INTO T_Model(name,age,color,length) VALUES ('花花',10,'花色',100.4)"];
    [self sqlExecWithSQL:sqlStr2 message:@""];
    
    NSString *sqlStr3 = [NSString stringWithFormat:@"INSERT INTO T_Model(name,age,color,length) VALUES ('花花',10,'花色',100.4)"];
    [self sqlExecWithSQL:sqlStr3 message:@""];
}

- (void)getModel
{
    // 搜索数据
    NSString *sqlStr = @"SELECT ID,name,color,age,length FROM T_Model";
    
    // 检测sql语法
    sqlite3_stmt *stmt = NULL;
    if (SQLITE_OK == sqlite3_prepare_v2(_dataBase, [sqlStr UTF8String], -1, &stmt, NULL)) {
        NSLog(@"搜索数据成功");
        
        // 逐一查询符合条件的数据
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            
            // 取出ID
            int ID = sqlite3_column_int(stmt, 0);
            // 取出名字
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            // 取出age
            int age = sqlite3_column_int(stmt, 3);
            // 颜色
            const unsigned char *color = sqlite3_column_text(stmt, 2);
            // 长度
            CGFloat length = sqlite3_column_double(stmt, 4);
            
            // 获取数据模型
            Model *model = [Model modelWith:ID name:[NSString stringWithUTF8String:(const char *)name] age:age color:[NSString stringWithUTF8String:(const char *)color] length:length];
            [_arrayM addObject:model];
        }
        
        
    }else{
        NSLog(@"搜索数据失败");
    }
    
}

#pragma mark - 执行sql语句方法
- (void)sqlExecWithSQL:(NSString *)sqlStr message:(NSString *)message
{
    char *error = NULL;
    
    // 1. 句柄
    // 2. 要执行的sql语句
    // 3. 4. 传NULl
    // 5. sql语句执行完成后返回的信息
    if (SQLITE_OK == sqlite3_exec(_dataBase, [sqlStr UTF8String], NULL, NULL, &error)) {
        NSLog(@"SQL 语句执行成功");
    }else{
        NSLog(@"%s",error);
    }
}
@end

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

@interface Model : NSObject
+(instancetype)modelWith:(NSInteger )ID name:(NSString *)name age:(NSInteger)age color:(NSString *)color length:(CGFloat )length;

/**
 *  主键（区分不同的数据model）
 */
@property(nonatomic, assign)NSInteger ID;
@property (nonatomic, copy)NSString *name;
@property (nonatomic, assign)NSInteger age;
@property (nonatomic, copy)NSString *color;
@property (nonatomic, assign)CGFloat length;
@end

#import "Model.h"

@implementation Model

+ (instancetype)modelWith:(NSInteger)ID name:(NSString *)name age:(NSInteger)age color:(NSString *)color length:(CGFloat)length
{
    Model *model = [[Model alloc] init];
    model.ID = ID;
    model.name = name;
    model.age = age;
    model.color = color;
    model.length = length;
    return model;
}
@end

#import "Model.h"

@implementation Model

+ (instancetype)modelWith:(NSInteger)ID name:(NSString *)name age:(NSInteger)age color:(NSString *)color length:(CGFloat)length
{
    Model *model = [[Model alloc] init];
    model.ID = ID;
    model.name = name;
    model.age = age;
    model.color = color;
    model.length = length;
    return model;
}
@end

SQLite的使用.png