概覽

在iOS開發(fā)中數(shù)據(jù)存儲(chǔ)的方式可以歸納為兩類：一類是存儲(chǔ)為文件创坞，另一類是存儲(chǔ)到數(shù)據(jù)庫(kù)。例如前面IOS開發(fā)系列—Objective-C之Foundation框架的文章中提到歸檔烹吵、plist文件存儲(chǔ)，包括偏好設(shè)置其本質(zhì)都是存儲(chǔ)為文件，只是說歸檔或者plist文件存儲(chǔ)可以選擇保存到沙盒中枉层，而偏好設(shè)置系統(tǒng)已經(jīng)規(guī)定只能保存到沙盒的Library/Preferences目錄啸臀。當(dāng)然届宠，文件存儲(chǔ)并不作為本文的重點(diǎn)內(nèi)容。本文重點(diǎn)還是說數(shù)據(jù)庫(kù)存儲(chǔ)乘粒，做過數(shù)據(jù)庫(kù)開發(fā)的朋友應(yīng)該知道豌注，可以通過SQL直接訪問數(shù)據(jù)庫(kù)，也可以通過ORM進(jìn)行對(duì)象關(guān)系映射訪問數(shù)據(jù)庫(kù)灯萍。這兩種方式恰恰對(duì)應(yīng)iOS中SQLite和Core Data的內(nèi)容轧铁，在此將重點(diǎn)進(jìn)行分析:

1.SQLite

2.Core Data

3.FMDB

SQLite

SQLite是目前主流的嵌入式關(guān)系型數(shù)據(jù)庫(kù)，其最主要的特點(diǎn)就是輕量級(jí)旦棉、跨平臺(tái)齿风，當(dāng)前很多嵌入式操作系統(tǒng)都將其作為數(shù)據(jù)庫(kù)首選药薯。雖然SQLite是一款輕型數(shù)據(jù)庫(kù)，但是其功能也絕不亞于很多大型關(guān)系數(shù)據(jù)庫(kù)救斑。學(xué)習(xí)數(shù)據(jù)庫(kù)就要學(xué)習(xí)其相關(guān)的定義童本、操作、查詢語(yǔ)言脸候，也就是大家日常說得SQL語(yǔ)句穷娱。和其他數(shù)據(jù)庫(kù)相比，SQLite中的SQL語(yǔ)法并沒有太大的差別运沦，因此這里對(duì)于SQL語(yǔ)句的內(nèi)容不會(huì)過多贅述鄙煤，大家可以參考SQLite中其他SQL相關(guān)的內(nèi)容，這里還是重點(diǎn)講解iOS中如何使用SQLite構(gòu)建應(yīng)用程序茶袒。先看一下SQLite數(shù)據(jù)庫(kù)的幾個(gè)特點(diǎn)：

1.基于C語(yǔ)言開發(fā)的輕型數(shù)據(jù)庫(kù)

2.在iOS中需要使用C語(yǔ)言語(yǔ)法進(jìn)行數(shù)據(jù)庫(kù)操作梯刚、訪問（無法使用ObjC直接訪問，因?yàn)閘ibqlite3框架基于C語(yǔ)言編寫）

3.SQLite中采用的是動(dòng)態(tài)數(shù)據(jù)類型薪寓，即使創(chuàng)建時(shí)定義了一種類型亡资，在實(shí)際操作時(shí)也可以存儲(chǔ)其他類型，但是推薦建庫(kù)時(shí)使用合適的類型（特別是應(yīng)用需要考慮跨平臺(tái)的情況時(shí)）

4.建立連接后通常不需要關(guān)閉連接（盡管可以手動(dòng)關(guān)閉）

要使用SQLite很簡(jiǎn)單向叉，如果在Mac OSX上使用可以考慮到SQLite官方網(wǎng)站下載命令行工具锥腻，也可以使用類似于SQLiteManager、Navicat for SQLite等工具母谎。為了方便大家開發(fā)調(diào)試瘦黑，建議在開發(fā)環(huán)境中安裝上述工具。

在iOS中操作SQLite數(shù)據(jù)庫(kù)可以分為以下幾步（注意先在項(xiàng)目中導(dǎo)入libsqlite3框架）：

1.打開數(shù)據(jù)庫(kù)奇唤，利用sqlite3_open()打開數(shù)據(jù)庫(kù)會(huì)指定一個(gè)數(shù)據(jù)庫(kù)文件保存路徑幸斥，如果文件存在則直接打開，否則創(chuàng)建并打開咬扇。打開數(shù)據(jù)庫(kù)會(huì)得到一個(gè)sqlite3類型的對(duì)象甲葬，后面需要借助這個(gè)對(duì)象進(jìn)行其他操作。

2.執(zhí)行SQL語(yǔ)句懈贺，執(zhí)行SQL語(yǔ)句又包括有返回值的語(yǔ)句和無返回值語(yǔ)句经窖。

3.對(duì)于無返回值的語(yǔ)句（如增加、刪除梭灿、修改等）直接通過sqlite3_exec()函數(shù)執(zhí)行画侣；

4.對(duì)于有返回值的語(yǔ)句則首先通過sqlite3_prepare_v2()進(jìn)行sql語(yǔ)句評(píng)估（語(yǔ)法檢測(cè)），然后通過sqlite3_step()依次取出查詢結(jié)果的每一行數(shù)據(jù)堡妒，對(duì)于每行數(shù)據(jù)都可以通過對(duì)應(yīng)的sqlite3_column_類型()方法獲得對(duì)應(yīng)列的數(shù)據(jù)配乱，如此反復(fù)循環(huán)直到遍歷完成。當(dāng)然，最后需要釋放句柄宪卿。

在整個(gè)操作過程中無需管理數(shù)據(jù)庫(kù)連接，對(duì)于嵌入式SQLite操作是持久連接（盡管可以通過sqlite3_close()關(guān)閉）万栅，不需要開發(fā)人員自己釋放連接佑钾。縱觀整個(gè)操作過程烦粒，其實(shí)與其他平臺(tái)的開發(fā)沒有明顯的區(qū)別休溶，較為麻煩的就是數(shù)據(jù)讀取，在iOS平臺(tái)中使用C進(jìn)行數(shù)據(jù)讀取采用了游標(biāo)的形式扰她，每次只能讀取一行數(shù)據(jù)兽掰，較為麻煩。因此實(shí)際開發(fā)中不妨對(duì)這些操作進(jìn)行封裝：

KCDbManager.h

#import <sqlite3.h>
#import "KCSingleton.h"
@interface KCDbManager : NSObject
singleton_interface(KCDbManager);
#pragma mark - 屬性
#pragma mark 數(shù)據(jù)庫(kù)引用徒役，使用它進(jìn)行數(shù)據(jù)庫(kù)操作
@property (nonatomic) sqlite3 *database;
#pragma mark - 共有方法
/**
 *  打開數(shù)據(jù)庫(kù)
 *
 *  @param dbname 數(shù)據(jù)庫(kù)名稱
 */
-(void)openDb:(NSString *)dbname;
/**
 *  執(zhí)行無返回值的sql
 *
 *  @param sql sql語(yǔ)句
 */
-(void)executeNonQuery:(NSString *)sql;
/**
 *  執(zhí)行有返回值的sql
 *
 *  @param sql sql語(yǔ)句
 *
 *  @return 查詢結(jié)果
 */
-(NSArray *)executeQuery:(NSString *)sql;
@end

KCDbManager.m

#import <sqlite3.h>
#import "KCSingleton.h"
#import "KCAppConfig.h"
#ifndef kDatabaseName
#define kDatabaseName @"myDatabase.db"
#endif
@interface KCDbManager()
@end
@implementation KCDbManager
singleton_implementation(KCDbManager)
#pragma mark 重寫初始化方法
-(instancetype)init{
    KCDbManager *manager;
    if((manager=[super init]))
    {
        [manager openDb:kDatabaseName];
    }
    return manager;
}
-(void)openDb:(NSString *)dbname{
    //取得數(shù)據(jù)庫(kù)保存路徑孽尽，通常保存沙盒Documents目錄
    NSString *directory=[NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES) firstObject];
    NSLog(@"%@",directory);
    NSString *filePath=[directory stringByAppendingPathComponent:dbname];
    //如果有數(shù)據(jù)庫(kù)則直接打開，否則創(chuàng)建并打開（注意filePath是ObjC中的字符串忧勿，需要轉(zhuǎn)化為C語(yǔ)言字符串類型）
    if (SQLITE_OK ==sqlite3_open(filePath.UTF8String, &_database)) {
        NSLog(@"數(shù)據(jù)庫(kù)打開成功!");
    }else{
        NSLog(@"數(shù)據(jù)庫(kù)打開失敗!");
    }
}
-(void)executeNonQuery:(NSString *)sql{
    char *error;
    //單步執(zhí)行sql語(yǔ)句杉女，用于插入、修改鸳吸、刪除
    if (SQLITE_OK!=sqlite3_exec(_database, sql.UTF8String, NULL, NULL,&error)) {
        NSLog(@"執(zhí)行SQL語(yǔ)句過程中發(fā)生錯(cuò)誤熏挎！錯(cuò)誤信息：%s",error);
    }
}
-(NSArray *)executeQuery:(NSString *)sql{
    NSMutableArray *rows=[NSMutableArray array];//數(shù)據(jù)行
     
    //評(píng)估語(yǔ)法正確性
    sqlite3_stmt *stmt;
    //檢查語(yǔ)法正確性
    if (SQLITE_OK==sqlite3_prepare_v2(_database, sql.UTF8String, -1, &stmt, NULL)) {
        //單步執(zhí)行sql語(yǔ)句
        while (SQLITE_ROW==sqlite3_step(stmt)) {
            int columnCount= sqlite3_column_count(stmt);
            NSMutableDictionary *dic=[NSMutableDictionary dictionary];
            for (int i=0; i<columnCount; i++) {
                const char *name= sqlite3_column_name(stmt, i);//取得列名
                const unsigned char *value= sqlite3_column_text(stmt, i);//取得某列的值
                dic[[NSString stringWithUTF8String:name]]=[NSString stringWithUTF8String:(const char *)value];
            }
            [rows addObject:dic];
        }
    }
     
    //釋放句柄
    sqlite3_finalize(stmt);
     
    return rows;
}
@end

在上面的類中對(duì)于數(shù)據(jù)庫(kù)操作進(jìn)行了封裝，封裝之后數(shù)據(jù)操作更加方便晌砾，同時(shí)所有的語(yǔ)法都由C轉(zhuǎn)換成了ObjC坎拐。

下面仍然以微博查看為例進(jìn)行SQLite演示。當(dāng)然實(shí)際開發(fā)中微博數(shù)據(jù)是從網(wǎng)絡(luò)讀取的养匈，但是考慮到緩存問題哼勇，通常會(huì)選擇將微博數(shù)據(jù)保存到本地，下面的Demo演示了將數(shù)據(jù)存放到本地?cái)?shù)據(jù)庫(kù)以及數(shù)據(jù)讀取的過程呕乎。當(dāng)然猴蹂，實(shí)際開發(fā)中并不會(huì)在視圖控制器中直接調(diào)用數(shù)據(jù)庫(kù)操作方法，在這里通常會(huì)引入兩個(gè)概念Model和Service楣嘁。Model自不必多說磅轻，就是MVC中的模型。而Service指的是操作數(shù)據(jù)庫(kù)的服務(wù)層逐虚，它封裝了對(duì)于Model的基本操作方法聋溜，實(shí)現(xiàn)具體的業(yè)務(wù)邏輯。為了解耦叭爱，在控制器中是不會(huì)直接接觸數(shù)據(jù)庫(kù)的撮躁，控制器中只和模型（模型是領(lǐng)域的抽象）、服務(wù)對(duì)象有關(guān)系买雾，借助服務(wù)層對(duì)模型進(jìn)行各類操作把曼，模型的操作反應(yīng)到數(shù)據(jù)庫(kù)中就是對(duì)表中數(shù)據(jù)的操作杨帽。具體關(guān)系如下：

要完成上述功能，首先定義一個(gè)應(yīng)用程序全局對(duì)象進(jìn)行數(shù)據(jù)庫(kù)嗤军、表的創(chuàng)建注盈。為了避免每次都創(chuàng)建數(shù)據(jù)庫(kù)和表出錯(cuò)，這里利用了偏好設(shè)置進(jìn)行保存當(dāng)前創(chuàng)建狀態(tài)（其實(shí)這也是數(shù)據(jù)存儲(chǔ)的一部分）叙赚，如果創(chuàng)建過了數(shù)據(jù)庫(kù)則不再創(chuàng)建老客，否則創(chuàng)建數(shù)據(jù)庫(kù)和表。

KCDatabaseCreator.m

#import "KCDatabaseCreator.h"
#import "KCDbManager.h"
@implementation KCDatabaseCreator
+(void)initDatabase{
    NSString *key=@"IsCreatedDb";
    NSUserDefaults *defaults=[[NSUserDefaults alloc]init];
    if ([[defaults valueForKey:key] intValue]!=1) {
        [self createUserTable];
        [self createStatusTable];
        [defaults setValue:@1 forKey:key];
    }
}
+(void)createUserTable{
    NSString *sql=@"CREATE TABLE User (Id integer PRIMARY KEY AUTOINCREMENT,name text,screenName text, profileImageUrl text,mbtype text,city text)";
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
+(void)createStatusTable{
    NSString *sql=@"CREATE TABLE Status (Id integer PRIMARY KEY AUTOINCREMENT,source text,createdAt date,\"text\" text,user integer REFERENCES User (Id))";
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
@end

其次震叮，定義數(shù)據(jù)模型胧砰，這里定義用戶User和微博Status兩個(gè)數(shù)據(jù)模型類。注意模型應(yīng)該盡量保持其單純性苇瓣，僅僅是簡(jiǎn)單的POCO尉间，不要引入視圖、控制器等相關(guān)內(nèi)容击罪。

KCUser.h

#import <Foundation/Foundation.h>
@interface KCUser : NSObject
#pragma mark 編號(hào)
@property (nonatomic,strong) NSNumber *Id;
#pragma mark 用戶名
@property (nonatomic,copy) NSString *name;
#pragma mark 用戶昵稱
@property (nonatomic,copy) NSString *screenName;
#pragma mark 頭像
@property (nonatomic,copy) NSString *profileImageUrl;
#pragma mark 會(huì)員類型
@property (nonatomic,copy) NSString *mbtype;
#pragma mark 城市
@property (nonatomic,copy) NSString *city;
#pragma mark - 動(dòng)態(tài)方法
/**
 *  初始化用戶
 *
 *  @param name 用戶名
 *  @param city 所在城市
 *
 *  @return 用戶對(duì)象
 */
-(KCUser *)initWithName:(NSString *)name screenName:(NSString *)screenName profileImageUrl:(NSString *)profileImageUrl mbtype:(NSString *)mbtype city:(NSString *)city;
/**
 *  使用字典初始化用戶對(duì)象
 *
 *  @param dic 用戶數(shù)據(jù)
 *
 *  @return 用戶對(duì)象
 */
-(KCUser *)initWithDictionary:(NSDictionary *)dic;
#pragma mark - 靜態(tài)方法
+(KCUser *)userWithName:(NSString *)name screenName:(NSString *)screenName profileImageUrl:(NSString *)profileImageUrl mbtype:(NSString *)mbtype city:(NSString *)city;
@end

KCUser.m

#import "KCUser.h"
@implementation KCUser
-(KCUser *)initWithName:(NSString *)name screenName:(NSString *)screenName profileImageUrl:(NSString *)profileImageUrl mbtype:(NSString *)mbtype city:(NSString *)city{
    if (self=[super init]) {
        self.name=name;
        self.screenName=screenName;
        self.profileImageUrl=profileImageUrl;
        self.mbtype=mbtype;
        self.city=city;
    }
    return self;
}
-(KCUser *)initWithDictionary:(NSDictionary *)dic{
    if (self=[super init]) {
        [self setValuesForKeysWithDictionary:dic];
    }
    return self;
}
+(KCUser *)userWithName:(NSString *)name screenName:(NSString *)screenName profileImageUrl:(NSString *)profileImageUrl mbtype:(NSString *)mbtype city:(NSString *)city{
    KCUser *user=[[KCUser alloc]initWithName:name screenName:screenName profileImageUrl:profileImageUrl mbtype:mbtype city:city];
    return user;
}
@end

KCStatus.h

#import <Foundation/Foundation.h>
#import "KCUser.h"
@interface KCStatus : NSObject
#pragma mark - 屬性
@property (nonatomic,strong) NSNumber *Id;//微博id
@property (nonatomic,strong) KCUser *user;//發(fā)送用戶
@property (nonatomic,copy) NSString *createdAt;//創(chuàng)建時(shí)間
@property (nonatomic,copy) NSString *source;//設(shè)備來源
@property (nonatomic,copy) NSString *text;//微博內(nèi)容
#pragma mark - 動(dòng)態(tài)方法
/**
 *  初始化微博數(shù)據(jù)
 *
 *  @param createAt        創(chuàng)建日期
 *  @param source          來源
 *  @param text            微博內(nèi)容
 *  @param user            發(fā)送用戶
 *
 *  @return 微博對(duì)象
 */
-(KCStatus *)initWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text user:(KCUser *)user;
/**
 *  初始化微博數(shù)據(jù)
 *
 *  @param profileImageUrl 用戶頭像
 *  @param mbtype          會(huì)員類型
 *  @param createAt        創(chuàng)建日期
 *  @param source          來源
 *  @param text            微博內(nèi)容
 *  @param userId          用戶編號(hào)
 *
 *  @return 微博對(duì)象
 */
-(KCStatus *)initWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text userId:(int)userId;
/**
 *  使用字典初始化微博對(duì)象
 *
 *  @param dic 字典數(shù)據(jù)
 *
 *  @return 微博對(duì)象
 */
-(KCStatus *)initWithDictionary:(NSDictionary *)dic;
#pragma mark - 靜態(tài)方法
/**
 *  初始化微博數(shù)據(jù)
 *
 *  @param createAt        創(chuàng)建日期
 *  @param source          來源
 *  @param text            微博內(nèi)容
 *  @param user            發(fā)送用戶
 *
 *  @return 微博對(duì)象
 */
+(KCStatus *)statusWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text user:(KCUser *)user;
/**
 *  初始化微博數(shù)據(jù)
 *
 *  @param profileImageUrl 用戶頭像
 *  @param mbtype          會(huì)員類型
 *  @param createAt        創(chuàng)建日期
 *  @param source          來源
 *  @param text            微博內(nèi)容
 *  @param userId          用戶編號(hào)
 *
 *  @return 微博對(duì)象
 */
+(KCStatus *)statusWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text userId:(int)userId;
@end

KCStatus.m

#import "KCStatus.h"
@implementation KCStatus
-(KCStatus *)initWithDictionary:(NSDictionary *)dic{
    if (self=[super init]) {
        [self setValuesForKeysWithDictionary:dic];
        self.user=[[KCUser alloc]init];
        self.user.Id=dic[@"user"];
    }
    return self;
}
-(KCStatus *)initWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text user:(KCUser *)user{
    if (self=[super init]) {
        self.createdAt=createAt;
        self.source=source;
        self.text=text;
        self.user=user;
    }
    return self;
}
-(KCStatus *)initWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text userId:(int)userId{
    if (self=[super init]) {
        self.createdAt=createAt;
        self.source=source;
        self.text=text;
        KCUser *user=[[KCUser alloc]init];
        user.Id=[NSNumber numberWithInt:userId];
        self.user=user;
    }
    return self;
}
-(NSString *)source{
    return [NSString stringWithFormat:@"來自 %@",_source];
}
+(KCStatus *)statusWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text user:(KCUser *)user{
    KCStatus *status=[[KCStatus alloc]initWithCreateAt:createAt source:source text:text user:user];
    return status;
}
+(KCStatus *)statusWithCreateAt:(NSString *)createAt source:(NSString *)source text:(NSString *)text userId:(int)userId{
    KCStatus *status=[[KCStatus alloc]initWithCreateAt:createAt source:source text:text userId:userId];
    return status;
}
@end

然后乌妒，編寫服務(wù)類，進(jìn)行數(shù)據(jù)的增外邓、刪撤蚊、改、查操作损话，由于服務(wù)類方法同樣不需要過多的配置侦啸，因此定義為單例，保證程序中只有一個(gè)實(shí)例即可丧枪。服務(wù)類中調(diào)用前面封裝的數(shù)據(jù)庫(kù)方法將對(duì)數(shù)據(jù)庫(kù)的操作轉(zhuǎn)換為對(duì)模型的操作光涂。

KCUserService.h

#import <Foundation/Foundation.h>
#import "KCUser.h"
#import "KCSingleton.h"
@interface KCUserService : NSObject
singleton_interface(KCUserService)
/**
 *  添加用戶信息
 *
 *  @param user 用戶對(duì)象
 */
-(void)addUser:(KCUser *)user;
/**
 *  刪除用戶
 *
 *  @param user 用戶對(duì)象
 */
-(void)removeUser:(KCUser *)user;
/**
 *  根據(jù)用戶名刪除用戶
 *
 *  @param name 用戶名
 */
-(void)removeUserByName:(NSString *)name;
/**
 *  修改用戶內(nèi)容
 *
 *  @param user 用戶對(duì)象
 */
-(void)modifyUser:(KCUser *)user;
/**
 *  根據(jù)用戶編號(hào)取得用戶
 *
 *  @param Id 用戶編號(hào)
 *
 *  @return 用戶對(duì)象
 */
-(KCUser *)getUserById:(int)Id;
/**
 *  根據(jù)用戶名取得用戶
 *
 *  @param name 用戶名
 *
 *  @return 用戶對(duì)象
 */
-(KCUser *)getUserByName:(NSString *)name;
@end

KCUserService.m

#import "KCUserService.h"
#import "KCUser.h"
#import "KCDbManager.h"
@implementation KCUserService
singleton_implementation(KCUserService)
-(void)addUser:(KCUser *)user{
    NSString *sql=[NSString stringWithFormat:@"INSERT INTO User (name,screenName, profileImageUrl,mbtype,city) VALUES('%@','%@','%@','%@','%@')",user.name,user.screenName, user.profileImageUrl,user.mbtype,user.city];
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
-(void)removeUser:(KCUser *)user{
    NSString *sql=[NSString stringWithFormat:@"DELETE FROM User WHERE Id='%@'",user.Id];
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
-(void)removeUserByName:(NSString *)name{
    NSString *sql=[NSString stringWithFormat:@"DELETE FROM User WHERE name='%@'",name];
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
-(void)modifyUser:(KCUser *)user{
    NSString *sql=[NSString stringWithFormat:@"UPDATE User SET name='%@',screenName='%@',profileImageUrl='%@',mbtype='%@',city='%@' WHERE Id='%@'",user.name,user.screenName,user.profileImageUrl,user.mbtype,user.city,user.Id];
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
-(KCUser *)getUserById:(int)Id{
    KCUser *user=[[KCUser alloc]init];
    NSString *sql=[NSString stringWithFormat:@"SELECT name,screenName,profileImageUrl,mbtype,city FROM User WHERE Id='%i'", Id];
    NSArray *rows= [[KCDbManager sharedKCDbManager] executeQuery:sql];
    if (rows&&rows.count>0) {
        [user setValuesForKeysWithDictionary:rows[0]];
    }
    return user;
}
-(KCUser *)getUserByName:(NSString *)name{
    KCUser *user=[[KCUser alloc]init];
    NSString *sql=[NSString stringWithFormat:@"SELECT Id, name,screenName,profileImageUrl,mbtype,city FROM User WHERE name='%@'", name];
    NSArray *rows= [[KCDbManager sharedKCDbManager] executeQuery:sql];
    if (rows&&rows.count>0) {
        [user setValuesForKeysWithDictionary:rows[0]];
    }
    return user;
}
@end

KCStatusService.h

#import <Foundation/Foundation.h>
#import "KCSingleton.h"
@class KCStatus;
@interface KCStatusService : NSObject
singleton_interface(KCStatusService)
/**
 *  添加微博信息
 *
 *  @param status 微博對(duì)象
 */
-(void)addStatus:(KCStatus *)status;
/**
 *  刪除微博
 *
 *  @param status 微博對(duì)象
 */
-(void)removeStatus:(KCStatus *)status;
/**
 *  修改微博內(nèi)容
 *
 *  @param status 微博對(duì)象
 */
-(void)modifyStatus:(KCStatus *)status;
/**
 *  根據(jù)編號(hào)取得微博
 *
 *  @param Id 微博編號(hào)
 *
 *  @return 微博對(duì)象
 */
-(KCStatus *)getStatusById:(int)Id;
/**
 *  取得所有微博對(duì)象
 *
 *  @return 所有微博對(duì)象
 */
-(NSArray *)getAllStatus;
@end

KCStatusService.m

#import "KCStatusService.h"
#import "KCDbManager.h"
#import "KCStatus.h"
#import "KCUserService.h"
#import "KCSingleton.h"
@interface KCStatusService(){
     
}
@end
@implementation KCStatusService
singleton_implementation(KCStatusService)
-(void)addStatus:(KCStatus *)status{
    NSString *sql=[NSString stringWithFormat:@"INSERT INTO Status (source,createdAt,\"text\" ,user) VALUES('%@','%@','%@','%@')",status.source,status.createdAt,status.text,status.user.Id];
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
-(void)removeStatus:(KCStatus *)status{
    NSString *sql=[NSString stringWithFormat:@"DELETE FROM Status WHERE Id='%@'",status.Id];
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
-(void)modifyStatus:(KCStatus *)status{
    NSString *sql=[NSString stringWithFormat:@"UPDATE Status SET source='%@',createdAt='%@',\"text\"='%@' ,user='%@' WHERE Id='%@'",status.source,status.createdAt,status.text,status.user, status.Id];
    [[KCDbManager sharedKCDbManager] executeNonQuery:sql];
}
-(KCStatus *)getStatusById:(int)Id{
    KCStatus *status=[[KCStatus alloc]init];
    NSString *sql=[NSString stringWithFormat:@"SELECT Id, source,createdAt,\"text\" ,user FROM Status WHERE Id='%i'", Id];
    NSArray *rows= [[KCDbManager sharedKCDbManager] executeQuery:sql];
    if (rows&&rows.count>0) {
        [status setValuesForKeysWithDictionary:rows[0]];
        status.user=[[KCUserService sharedKCUserService] getUserById:[(NSNumber *)rows[0][@"user"] intValue]] ;
    }
    return status;
}
-(NSArray *)getAllStatus{
    NSMutableArray *array=[NSMutableArray array];
    NSString *sql=@"SELECT Id, source,createdAt,\"text\" ,user FROM Status ORDER BY Id";
    NSArray *rows= [[KCDbManager sharedKCDbManager] executeQuery:sql];
    for (NSDictionary *dic in rows) {
        KCStatus *status=[self getStatusById:[(NSNumber *)dic[@"Id"] intValue]];
        [array addObject:status];
    }
    return array;
}
@end

最后，在視圖控制器中調(diào)用相應(yīng)的服務(wù)層進(jìn)行各類數(shù)據(jù)操作拧烦，在下面的代碼中分別演示了增忘闻、刪、改恋博、查四類操作齐佳。

KCMainViewController.m

#import "KCMainTableViewController.h"
#import "KCDbManager.h"
#import "KCDatabaseCreator.h"
#import "KCUser.h"
#import "KCStatus.h"
#import "KCUserService.h"
#import "KCStatusService.h"
#import "KCStatusTableViewCell.h"
@interface KCMainTableViewController (){
    NSArray *_status;
    NSMutableArray *_statusCells;
}
@end
@implementation KCMainTableViewController
- (void)viewDidLoad {
    [super viewDidLoad];
    [KCDatabaseCreator initDatabase];
     
//    [self addUsers];
//    [self removeUser];
//    [self modifyUserInfo];
     
//    [self addStatus];
     
    [self loadStatusData];
     
}
-(void)addUsers{
    KCUser *user1=[KCUser userWithName:@"Binger" screenName:@"冰兒" profileImageUrl:@"binger.jpg" mbtype:@"mbtype.png" city:@"北京"];
    [[KCUserService sharedKCUserService] addUser:user1];
    KCUser *user2=[KCUser userWithName:@"Xiaona" screenName:@"小娜" profileImageUrl:@"xiaona.jpg" mbtype:@"mbtype.png" city:@"北京"];
    [[KCUserService sharedKCUserService] addUser:user2];
    KCUser *user3=[KCUser userWithName:@"Lily" screenName:@"麗麗" profileImageUrl:@"lily.jpg" mbtype:@"mbtype.png" city:@"北京"];
    [[KCUserService sharedKCUserService] addUser:user3];
    KCUser *user4=[KCUser userWithName:@"Qianmo" screenName:@"阡陌" profileImageUrl:@"qianmo.jpg" mbtype:@"mbtype.png" city:@"北京"];
    [[KCUserService sharedKCUserService] addUser:user4];
    KCUser *user5=[KCUser userWithName:@"Yanyue" screenName:@"炎月" profileImageUrl:@"yanyue.jpg" mbtype:@"mbtype.png" city:@"北京"];
    [[KCUserService sharedKCUserService] addUser:user5];
}
-(void)addStatus{
    KCStatus *status1=[KCStatus statusWithCreateAt:@"9:00" source:@"iPhone 6" text:@"一只雪猴在日本邊泡溫泉邊玩iPhone的照片，獲得了\"2014年野生動(dòng)物攝影師\"大賽特等獎(jiǎng)债沮。一起來為猴子配個(gè)詞" userId:1];
    [[KCStatusService sharedKCStatusService] addStatus:status1];
    KCStatus *status2=[KCStatus statusWithCreateAt:@"9:00" source:@"iPhone 6" text:@"一只雪猴在日本邊泡溫泉邊玩iPhone的照片炼吴，獲得了\"2014年野生動(dòng)物攝影師\"大賽特等獎(jiǎng)。一起來為猴子配個(gè)詞" userId:1];
    [[KCStatusService sharedKCStatusService] addStatus:status2];
    KCStatus *status3=[KCStatus statusWithCreateAt:@"9:30" source:@"iPhone 6" text:@"【我們送iPhone6了 要求很簡(jiǎn)單】真心回饋粉絲疫衩，小編覺得現(xiàn)在最好的獎(jiǎng)品就是iPhone6了硅蹦。今起到12月31日，關(guān)注我們，轉(zhuǎn)發(fā)微博童芹，就有機(jī)會(huì)獲iPhone6(獎(jiǎng)品可能需要等待)涮瞻！每月抽一臺(tái)[鼓掌]。不費(fèi)事假褪，還是試試吧署咽，萬一中了呢" userId:2];
    [[KCStatusService sharedKCStatusService] addStatus:status3];
    KCStatus *status4=[KCStatus statusWithCreateAt:@"9:45" source:@"iPhone 6" text:@"重大新聞：蒂姆庫(kù)克宣布出柜后，ISIS戰(zhàn)士怒扔iPhone嗜价，沙特神職人員呼吁人們換回iPhone 4。[via Pan-Arabia Enquirer]" userId:3];
    [[KCStatusService sharedKCStatusService] addStatus:status4];
    KCStatus *status5=[KCStatus statusWithCreateAt:@"10:05" source:@"iPhone 6" text:@"小伙伴們幕庐，有誰知道怎么往Iphone4S里倒東西久锥？倒入的東西又該在哪里找？用了Iphone這么長(zhǎng)時(shí)間异剥，還真的不知道怎么弄瑟由！有誰知道啊冤寿？謝謝歹苦！" userId:4];
    [[KCStatusService sharedKCStatusService] addStatus:status5];
    KCStatus *status6=[KCStatus statusWithCreateAt:@"10:07" source:@"iPhone 6" text:@"在音悅臺(tái)iPhone客戶端里發(fā)現(xiàn)一個(gè)悅單《Infinite 金明洙》，推薦給大家! " userId:1];
    [[KCStatusService sharedKCStatusService] addStatus:status6];
    KCStatus *status7=[KCStatus statusWithCreateAt:@"11:20" source:@"iPhone 6" text:@"如果sony吧mp3播放器產(chǎn)品發(fā)展下去督怜，不貪圖手頭節(jié)目源的現(xiàn)實(shí)利益殴瘦，就木有蘋果的ipod，也就木有iphone号杠◎揭福柯達(dá)類似的現(xiàn)實(shí)利益，不自我革命的案例也是一種巨頭的宿命姨蟋。" userId:2];
    [[KCStatusService sharedKCStatusService] addStatus:status7];
    KCStatus *status8=[KCStatus statusWithCreateAt:@"13:00" source:@"iPhone 6" text:@"【iPhone 7 Plus】新買的iPhone 7 Plus 屉凯，如何？夠酷炫么眼溶？" userId:2];
    [[KCStatusService sharedKCStatusService] addStatus:status8];
    KCStatus *status9=[KCStatus statusWithCreateAt:@"13:24" source:@"iPhone 6" text:@"自拍神器#卡西歐TR500#悠砚，tr350S～價(jià)格美麗，行貨堂飞，全國(guó)聯(lián)惫嗑桑～iPhone6 iPhone6Plus卡西歐TR150 TR200 TR350 TR350S全面到貨 招收各種代理！[給力]微信：39017366" userId:3];
    [[KCStatusService sharedKCStatusService] addStatus:status9];
    KCStatus *status10=[KCStatus statusWithCreateAt:@"13:26" source:@"iPhone 6" text:@"猜到猴哥玩手機(jī)時(shí)所思所想者绰筛，再獎(jiǎng)iPhone一部节榜。（獎(jiǎng)品由“2014年野生動(dòng)物攝影師”評(píng)委會(huì)頒發(fā)）" userId:3];
    [[KCStatusService sharedKCStatusService] addStatus:status10];
}
-(void)removeUser{
    //注意在SQLite中區(qū)分大小寫
    [[KCUserService sharedKCUserService] removeUserByName:@"Yanyue"];
}
-(void)modifyUserInfo{
    KCUser *user1= [[KCUserService sharedKCUserService]getUserByName:@"Xiaona"];
    user1.city=@"上海";
    [[KCUserService sharedKCUserService] modifyUser:user1];
     
    KCUser *user2= [[KCUserService sharedKCUserService]getUserByName:@"Lily"];
    user2.city=@"深圳";
    [[KCUserService sharedKCUserService] modifyUser:user2];
}
#pragma mark 加載數(shù)據(jù)
-(void)loadStatusData{
    _statusCells=[[NSMutableArray alloc]init];
    _status=[[KCStatusService sharedKCStatusService]getAllStatus];
    [_status enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
        KCStatusTableViewCell *cell=[[KCStatusTableViewCell alloc]init];
        cell.status=(KCStatus *)obj;
        [_statusCells addObject:cell];
    }];
    NSLog(@"%@",[_status lastObject]);
}
#pragma mark - Table view data source
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
    return 1;
}
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
    return _status.count;
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    static NSString *identtityKey=@"myTableViewCellIdentityKey1";
    KCStatusTableViewCell *cell=[self.tableView dequeueReusableCellWithIdentifier:identtityKey];
    if(cell==nil){
        cell=[[KCStatusTableViewCell alloc]initWithStyle:UITableViewCellStyleValue1 reuseIdentifier:identtityKey];
    }
    cell.status=_status[indexPath.row];
    return cell;
}
-(CGFloat)tableView:(UITableView *)tableView heightForRowAtIndexPath:(NSIndexPath *)indexPath{
    return ((KCStatusTableViewCell *)_statusCells[indexPath.row]).height;
}
-(CGFloat)tableView:(UITableView *)tableView heightForHeaderInSection:(NSInteger)section{
    return 20.0f;
}
@end

摘自：http://www.cocoachina.com/ios/20141111/10190.html

補(bǔ)充：

SQLite添加新的字段

通過alter添加新的字段SQL語(yǔ)句

"ALTER TABLE 'DiHKChatMessage' ADD  'phoneNum' varchar";

但是如果這個(gè)字段已經(jīng)存在的話，運(yùn)行程序直接會(huì)崩潰别智，那怎么解決宗苍？
我們可以在添加字段之前，對(duì)數(shù)據(jù)庫(kù)進(jìn)行判斷看是否已經(jīng)存在該字段了，方法如下：

/**
 * 方法1：檢查某表列是否存在
 * @param db
 * @param tableName 表名
 * @param columnName 列名
 * @return
 */
private static boolean checkColumnExist1(SQLiteDatabase db, String tableName
        , String columnName) {
    boolean result = false ;
    Cursor cursor = null ;
    try{
        //查詢一行
        cursor = db.rawQuery( "SELECT * FROM " + tableName + " LIMIT 0", null );
        result = cursor != null && cursor.getColumnIndex(columnName) != -1 ;
    }catch (Exception e){
        LogUtil.logErrorMessage("checkColumnExists1..." + e.getMessage());
    }finally{
        if(null != cursor && !cursor.isClosed()){
            cursor.close() ;
        }
    }

    return result ;
}


/**
 * 方法2：檢查表中某列是否存在
 * @param db
 * @param tableName 表名
 * @param columnName 列名
 * @return
 */
private static boolean checkColumnExists2(SQLiteDatabase db, String tableName, String columnName) {
    boolean result = false ;
    Cursor cursor = null ;

    try{
        cursor = db.rawQuery( "select * from sqlite_master where name = ? and sql like ?"
                , new String[]{tableName , "%" + columnName + "%"} );
        result = null != cursor && cursor.moveToFirst() ;
    }catch (Exception e){
        LogUtil.logErrorMessage("checkColumnExists2..." + e.getMessage());
    }finally{
        if(null != cursor && !cursor.isClosed()){
            cursor.close() ;
        }
    }

    return result ;
}

摘自：http://blog.csdn.net/lu1024188315/article/details/51752689