您现在的位置: IOS教程網 >> IOS系統 >  >> iOS 程式開發 >> iOS 開發綜合

iOS 數據庫:SQLite 與 Objective-C 的增刪改查實戰

編輯:iOS 開發綜合

SQL(Structured Query Language)即結構化查詢語言。本文先用最快速度過一遍常用語句,再通過一個完整的學生信息管理示例,把 SQLite 在 iOS 中的增刪改查走通。

一、常用 SQL 語句

建表

create table if not exists Teacher(
    tea_id integer primary key autoincrement,
    tea_name text,
    tea_gender text,
    tea_age integer,
    tea_salary integer
);

增

insert into Teacher(tea_id, tea_name, tea_gender, tea_age, tea_salary)
values(110, '小韓哥', '男', 18, 1000);

查

select * from Teacher;                                  -- 全部字段、全部數據
select tea_name, tea_gender from Teacher;               -- 指定字段
select * from Teacher where tea_name = '小韓哥';        -- 按條件

改(務必帶唯一標識,否則會影響整表)

update Teacher set tea_gender = '女' where tea_id = 111;

刪

delete from Teacher where tea_id = 110;   -- 刪除指定行
delete from Teacher;                      -- 清空所有數據
drop table Teacher;                       -- 刪除整張表

一句話記住區別:delete 刪數據、表結構還在;drop 連表一起刪。

二、示例工程的界面

布局很簡單:4 個 UITextField(學號、姓名、性別、年齡)+ 5 個按鈕(增、刪、改、查全部、查單個)。

三、模型類 Student

// Student.h
@interface Student : NSObject
@property (nonatomic, assign) NSInteger number;
@property (nonatomic, copy)   NSString *name;
@property (nonatomic, copy)   NSString *gender;
@property (nonatomic, assign) NSInteger age;
@end

// Student.m
@implementation Student

- (void)dealloc
{
    self.name = nil;
    self.gender = nil;
    [super dealloc];
}

- (NSString *)description
{
    return [NSString stringWithFormat:@"%ld %@ %@ %ld",
            (long)self.number, self.name, self.gender, (long)self.age];
}

@end

四、數據庫操作類 DataBaseHandle

把數據庫邏輯集中到一個單例類裡,界面層只調接口,職責清晰。

// DataBaseHandle.h
@class Student;

@interface DataBaseHandle : NSObject

+ (DataBaseHandle *)shareDataBaseHandle;   // 單例
- (void)openDataBase;                      // 打開
- (void)closeDataBase;                     // 關閉

- (void)insertStudent:(Student *)student;                       // 增
- (NSMutableArray *)selectAllStudent;                           // 查全部
- (Student *)selectOneStudentByNumber:(NSInteger)number;        // 查單個
- (void)deleteOneStudentByNumber:(NSInteger)number;             // 刪
- (void)updateStudentGender:(NSString *)gender ByNumber:(NSInteger)number;  // 改

@end

實現文件的核心部分:

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

@implementation DataBaseHandle

static DataBaseHandle *handle = nil;
static sqlite3 *db = nil;

+ (DataBaseHandle *)shareDataBaseHandle
{
    @synchronized (self) {
        if (handle == nil) {
            handle = [[DataBaseHandle alloc] init];
            [handle openDataBase];   // 一創建就具備訪問能力
        }
    }
    return handle;
}

// 數據庫放在 Documents 目錄下
- (NSString *)dataBasePath
{
    return [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]
            stringByAppendingPathComponent:@"student.sqlite"];
}

使用 SQLite 前記得引入 libsqlite3.0.dylib 動態庫並導入 sqlite3.h。建議引用快捷方式而非實體版本號,這樣系統升級時不必改配置。

打開數據庫並建表

- (void)openDataBase
{
    NSString *dbPath = [self dataBasePath];

    // 文件不存在會自動創建,存在則直接打開
    int result = sqlite3_open([dbPath UTF8String], &db);

    if (result == SQLITE_OK) {
        NSString *sqlString = @"create table if not exists Student("
                               "stu_number integer primary key autoincrement,"
                               "stu_name text,"
                               "stu_gender text,"
                               "stu_age integer)";
        sqlite3_exec(db, [sqlString UTF8String], NULL, NULL, NULL);
    } else {
        NSLog(@"數據庫打開失敗");
    }
}

- (void)closeDataBase
{
    int result = sqlite3_close(db);
    NSLog(@"%@", (result == SQLITE_OK) ? @"關閉成功" : @"關閉失敗");
}

增

- (void)insertStudent:(Student *)student
{
    [self openDataBase];

    NSString *sqlString = @"insert into Student(stu_name, stu_gender, stu_age) values(?, ?, ?)";
    sqlite3_stmt *stmt = nil;

    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        // ? 的下標從 1 開始
        sqlite3_bind_text(stmt, 1, [student.name UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2, [student.gender UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 3, (int)student.age);

        sqlite3_step(stmt);   // 真正執行
    }

    sqlite3_finalize(stmt);   // 釋放 statement
    [self closeDataBase];
}

這裡刻意用了參數綁定而不是字符串拼接,既能避免拼接錯誤,也能防止 SQL 注入——即使 demo 簡單,這個習慣也值得從第一次寫起就養成。

查全部

- (NSMutableArray *)selectAllStudent
{
    [self openDataBase];

    NSString *sqlString = @"select * from Student";
    sqlite3_stmt *stmt = nil;

    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        sqlite3_finalize(stmt);
        [self closeDataBase];
        return nil;
    }

    NSMutableArray *array = [NSMutableArray arrayWithCapacity:0];

    // 每調用一次 sqlite3_step 就前進一行,返回 SQLITE_ROW 說明還有數據
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        int number = sqlite3_column_int(stmt, 0);          // 列下標從 0 開始
        NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
        NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
        int age = sqlite3_column_int(stmt, 3);

        Student *stu = [[Student alloc] init];
        stu.number = number;
        stu.name = name;
        stu.gender = gender;
        stu.age = age;
        [array addObject:stu];
        [stu release];
    }

    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;
}

按學號查單個

- (Student *)selectOneStudentByNumber:(NSInteger)number
{
    [self openDataBase];

    NSString *sqlString = @"select * from Student where stu_number = ?";
    sqlite3_stmt *stmt = nil;

    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        sqlite3_finalize(stmt);
        [self closeDataBase];
        return nil;
    }

    sqlite3_bind_int(stmt, 1, (int)number);

    Student *stu = [[Student alloc] init];
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        stu.number = number;
        stu.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
        stu.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
        stu.age = sqlite3_column_int(stmt, 3);
    }

    sqlite3_finalize(stmt);
    [self closeDataBase];
    return [stu autorelease];
}

刪

- (void)deleteOneStudentByNumber:(NSInteger)number
{
    [self openDataBase];

    NSString *sqlString = @"delete from Student where stu_number = ?";
    sqlite3_stmt *stmt = nil;

    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_int(stmt, 1, (int)number);
        sqlite3_step(stmt);
    }

    sqlite3_finalize(stmt);
    [self closeDataBase];
}

改

- (void)updateStudentGender:(NSString *)gender ByNumber:(NSInteger)number
{
    [self openDataBase];

    NSString *sqlString = @"update Student set stu_gender = ? where stu_number = ?";
    sqlite3_stmt *stmt = nil;

    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, [gender UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 2, (int)number);
        sqlite3_step(stmt);
    }

    sqlite3_finalize(stmt);
    [self closeDataBase];
}

五、界面層調用

- (IBAction)insertStudent:(UIButton *)sender
{
    // 有空值就直接返回,避免寫入髒數據
    if (0 == self.nameField.text.length
        || 0 == self.genderField.text.length
        || 0 == self.ageField.text.length) {
        return;
    }

    Student *stu = [[Student alloc] init];
    stu.name = self.nameField.text;
    stu.gender = self.genderField.text;
    stu.age = [self.ageField.text integerValue];

    [[DataBaseHandle shareDataBaseHandle] insertStudent:stu];
    [stu release];
}

- (IBAction)updateStudent:(UIButton *)sender
{
    NSInteger number = [self.numberField.text integerValue];
    NSString *gender = self.genderField.text;
    [[DataBaseHandle shareDataBaseHandle] updateStudentGender:gender ByNumber:number];
}

- (IBAction)deleteStudent:(UIButton *)sender
{
    NSInteger number = [self.numberField.text integerValue];
    [[DataBaseHandle shareDataBaseHandle] deleteOneStudentByNumber:number];
}

- (IBAction)selectAllStudent:(UIButton *)sender
{
    NSMutableArray *contentArray = [[DataBaseHandle shareDataBaseHandle] selectAllStudent];
    for (Student *stu in contentArray) {
        NSLog(@"%@", stu);
    }
}

- (IBAction)selectOneStudent:(id)sender
{
    NSInteger number = [self.numberField.text integerValue];
    Student *stu = [[DataBaseHandle shareDataBaseHandle] selectOneStudentByNumber:number];
    NSLog(@"%@", stu);
}

- (void)dealloc
{
    [_numberField release];
    [_nameField release];
    [_genderField release];
    [_ageField release];
    [super dealloc];
}

六、通用步驟總結

無論增刪改查,流程幾乎一致:

  1. 打開數據庫;
  2. 準備 SQL 語句;
  3. 創建 statement 指針;
  4. 用 sqlite3_prepare_v2 校驗語句;
  5. 校驗通過則綁定參數(? 下標從 1 開始);
  6. 調用 sqlite3_step 執行(查詢時用 while 循環逐行讀取,列下標從 0 開始);
  7. sqlite3_finalize 釋放 statement;
  8. 關閉數據庫。

實踐中還可進一步優化:把「打開 / 關閉」抽成一次性的連接管理,避免每次操作都開關數據庫;把 Frequent 的查詢放到後台隊列,避免阻塞主線程造成界面卡頓。

  1. 上一篇文章:
  2. 下一篇文章:
蘋果刷機越獄教程| IOS教程問題解答| IOS技巧綜合| IOS7技巧| IOS8教程
Copyright © IOS教程網 All Rights Reserved