SQLite的使用

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

@interface ViewController ()
{
    // 用于存儲(chǔ)數(shù)據(jù)model
    NSMutableArray *_arrayM;
    
    // 數(shù)據(jù)庫(kù)的句柄（在c語(yǔ)言中，通常把用于控制的類叫做句柄）
    sqlite3 *_dataBase;
}
@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    
    _arrayM = [NSMutableArray array];
    
    // 1. 打開(kāi)數(shù)據(jù)庫(kù)
    [self openDataBase];
    
    // 2. 創(chuàng)建數(shù)據(jù)表
    [self creatTable];
    
    // 3. 給數(shù)據(jù)表添加數(shù)據(jù)
    [self addModelToTable];
    
    // 4. 從數(shù)據(jù)庫(kù)中讀取數(shù)據(jù)
    [self getModel];
}

- (void)openDataBase
{
    // 1. 設(shè)置數(shù)據(jù)庫(kù)的路徑
    NSString *path = [NSString stringWithFormat:@"%@/Documents/model.db",NSHomeDirectory()];
    NSLog(@"%@",path);
    
    // 2. 打開(kāi)數(shù)據(jù)庫(kù)（系統(tǒng)會(huì)檢測(cè)，如果這個(gè)路徑不存在在這個(gè)數(shù)據(jù)庫(kù)，就創(chuàng)建一個(gè)并打開(kāi)，如果有的話，直接打開(kāi)）
    if (SQLITE_OK == sqlite3_open([path UTF8String], &_dataBase)) {
        NSLog(@"數(shù)據(jù)庫(kù)打開(kāi)成功~");
    }else{
       NSLog(@"數(shù)據(jù)庫(kù)打開(kāi)失敗~");
    }
}

- (void)creatTable
{
    // 創(chuàng)建數(shù)據(jù)表
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS T_Model (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT, color TEXT,  age INTEGER, length REAL)";
    
    // 執(zhí)行sql 語(yǔ)句
    [self sqlExecWithSQL:sqlStr message:@""];
}

- (void)addModelToTable
{
    // 插入數(shù)據(jù)（如果不設(shè)置主鍵，系統(tǒng)會(huì)幫助我們?cè)O(shè)置主鍵，按次序增長(zhǎng)）
    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
{
    // 搜索數(shù)據(jù)
    NSString *sqlStr = @"SELECT ID,name,color,age,length FROM T_Model";
    
    // 檢測(cè)sql語(yǔ)法
    sqlite3_stmt *stmt = NULL;
    if (SQLITE_OK == sqlite3_prepare_v2(_dataBase, [sqlStr UTF8String], -1, &stmt, NULL)) {
        NSLog(@"搜索數(shù)據(jù)成功");
        
        // 逐一查詢符合條件的數(shù)據(jù)
        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);
            // 長(zhǎng)度
            CGFloat length = sqlite3_column_double(stmt, 4);
            
            // 獲取數(shù)據(jù)模型
            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(@"搜索數(shù)據(jù)失敗");
    }
    
}

#pragma mark - 執(zhí)行sql語(yǔ)句方法
- (void)sqlExecWithSQL:(NSString *)sqlStr message:(NSString *)message
{
    char *error = NULL;
    
    // 1. 句柄
    // 2. 要執(zhí)行的sql語(yǔ)句
    // 3. 4. 傳NULl
    // 5. sql語(yǔ)句執(zhí)行完成后返回的信息
    if (SQLITE_OK == sqlite3_exec(_dataBase, [sqlStr UTF8String], NULL, NULL, &error)) {
        NSLog(@"SQL 語(yǔ)句執(zhí)行成功");
    }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;

/**
 *  主鍵（區(qū)分不同的數(shù)據(jù)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