iOS 数据持久化(四)SQLite3

iOS中有五种持久化数据的方式：属性列表、对象归档、NSUserDefaults、SQLite3和Core Data

SQLite3的数据类型

数据库在应用中最常用的，方便说数据的存取。

所有存在Sqlite 3.0版本当中的数据都拥有以下之一的数据类型：
空（NULL）：该值为空
整型（INTEGEER）：有符号整数，按大小被存储成1,2,3,4,6或8字节。
实数（REAL）：浮点数，以8字节指数形式存储。
文本（TEXT）：字符串，以数据库编码方式存储（UTF-8, UTF-16BE 或者 UTF-16-LE）。
BLOB：BLOB数据不做任何转换，以输入形式存储。

在关系数据库中，CLOB和BLOB类型被用来存放大对象。BOLB表示二进制大对象，这种数据类型通过用来保存图片，图象，视频等。CLOB表示字符大对象，能够存放大量基于字符的数据。

iOS中数据库SQLite3的使用

具体使用方法如下:

1.添加开发包libsqlite3.0.dylib

首先是设置项目文件，在项目中添加iPhone版的sqlite3的数据库的开发包，在项目下的Frameworks点击右键，然后选择libsqlite3.0.dylib文件。

libsqlite3.0.dylib文件地址: 
/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.2.sdk/usr/lib/libsqlite3.0.dylib

2.有码有真相：

加入FMDB这个第三方库文件，项目编程开始。

AppDelegate.h文件

////  AppDelegate.h//  SQLite3Demo////  Created by 李振杰 on 13-11-22.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import <UIKit/UIKit.h>@interface AppDelegate : UIResponder <UIApplicationDelegate>@property (strong, nonatomic) UIWindow *window;/** *  @brief  在状态栏处显示的提示框 * *  @param string   显示的字符串 *  @param duration 显示的时长 */+ (void)showStatusWithText:(NSString *)string duration:(NSTimeInterval)duration;@end

AppDelegate.m文件

////  AppDelegate.m//  SQLite3Demo////  Created by 李振杰 on 13-11-22.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import "AppDelegate.h"#import "RootViewController.h"@interface AppDelegate ()@property (strong, nonatomic) UIWindow *statusWindow;@property (retain, nonatomic) UILabel *statusLabel;- (void)dismissStatusLabel;@end@implementation AppDelegate- (void)dealloc{    [self.statusWindow release];    [self.statusLabel release];        [_window release];    [super dealloc];}- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions{    self.window = [[[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]] autorelease];     self.statusWindow = [[UIWindow alloc] initWithFrame:CGRectZero];    self.statusWindow.backgroundColor = [UIColor clearColor];    self.statusWindow.windowLevel = UIWindowLevelStatusBar + 1;    self.statusLabel = [[UILabel alloc] initWithFrame:CGRectZero];    self.statusLabel.backgroundColor = [UIColor clearColor];    self.statusLabel.textColor = [UIColor whiteColor];    self.statusLabel.font = [UIFont systemFontOfSize:13];    [self.statusWindow addSubview:self.statusLabel];    [self.statusWindow makeKeyAndVisible];        RootViewController *rootVC = [[RootViewController alloc] initWithNibName:@"RootViewController" bundle:nil];    UINavigationController *rootNav = [[UINavigationController alloc] initWithRootViewController:rootVC];    [rootVC release];        self.window.rootViewController = rootNav;    [rootNav release];        // Override point for customization after application launch.    self.window.backgroundColor = [UIColor whiteColor];    [self.window makeKeyAndVisible];    return YES;}#pragma mark - Custom methods- (void)dismissStatusLabel{    CGRect rect = self.statusWindow.frame;    rect.origin.y -= rect.size.height;    [UIView animateWithDuration:0.8 animations:^{        self.statusWindow.frame = rect;    }];}/** *  @brief  在状态栏处显示的提示框 * *  @param string   显示的字符串 *  @param duration 显示的时长 */+ (void)showStatusWithText:(NSString *)string duration:(NSTimeInterval)duration{    AppDelegate *delegate = (AppDelegate *)[UIApplication sharedApplication].delegate;        delegate.statusLabel.text = string;    [delegate.statusLabel sizeToFit];    CGRect rect = [UIApplication sharedApplication].statusBarFrame;    CGFloat width = delegate.statusLabel.frame.size.width;    CGFloat height = rect.size.height;    rect.origin.x = rect.size.width = width - 5;    rect.size.width = width;    delegate.statusWindow.frame = rect;    delegate.statusLabel.backgroundColor = [UIColor blackColor];    delegate.statusLabel.frame = CGRectMake(100, 0, width, height);    duration = (duration < 1.0 ? 1.0 : duration);    duration = (duration > 4.0 ? 4.0 : duration);    [delegate performSelector:@selector(dismissStatusLabel)];}@end

使用FMDB第三方库，数据库管理文件

////  DBManager.h//  SQLite3Demo////  Created by 李振杰 on 13-11-26.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import <Foundation/Foundation.h>#import "FMDatabaseAdditions.h"@class FMDatabase;/** *  @brief 对数据连接进行管理，包括连接，关闭连接 *  可以建立长连接 * */@interface DBManager : NSObject{    NSString *_dbPath;}@property (readonly, nonatomic) FMDatabase *dataBase;//单例模式+ (DBManager *)defaultDBManager;//关闭数据库- (void)close;@end

////  DBManager.m//  SQLite3Demo////  Created by 李振杰 on 13-11-26.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import "DBManager.h"#import "FMDatabase.h"//#define kDefaultDBName @"user.sqlite"#define kDefaultDBName @"user.db"@interface DBManager ()@endstatic DBManager *_sharedDBManager;@implementation DBManager- (void)dealloc{    [self close];        [super dealloc];}- (id)init{    if (self = [super init]) {        int state = [self initializeDBWithName:kDefaultDBName];        if (-1 == state) {            NSLog(@"数据库初始化失败！");        }else {            NSLog(@"数据库初始化成功！");        }    }    return self;}- (int)initializeDBWithName:(NSString *)name{    if (!name) {        return -1;  //数据库创建失败    }        //沙盒Doc目录    NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];    _dbPath = [doc stringByAppendingString:[NSString stringWithFormat:@"/%@", name]];    NSFileManager *fileManager = [NSFileManager defaultManager];    BOOL exist = [fileManager fileExistsAtPath:_dbPath];    [self connect];    return (exist == YES ? 0 : 1);}//连接数据库- (void)connect{    if (!_dataBase) {        _dataBase = [[FMDatabase alloc] initWithPath:_dbPath];    }    if (![_dataBase open]) {        NSLog(@"不能打开数据库");    }}- (void)close{    [_dataBase close];    [_sharedDBManager release];    _sharedDBManager = nil;}+ (DBManager *)defaultDBManager{    if (!_sharedDBManager) {        _sharedDBManager = [[DBManager alloc] init];    }    return _sharedDBManager;}@end

向创建的数据库文件中添加数据表，对表的增删改查

////  UserDB.h//  SQLite3Demo////  Created by 李振杰 on 13-11-26.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import <Foundation/Foundation.h>#import "DBManager.h"#import "User.h"@interface UserDB : NSObject{    FMDatabase *_db;}//创建数据表- (void)createTable;//增：添加用户- (void)updateTableWithUser:(User *)user;//删：删除用户- (void)deleteTableWithUserID:(NSString *)uid;//改：修改用户信息- (void)modifyTableWithUser:(User *)user;//查：查找用户- (NSArray *)queryTableWithUid:(NSString *)uid count:(int)userCount;@end

////  UserDB.m//  SQLite3Demo////  Created by 李振杰 on 13-11-26.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import "UserDB.h"#import "AppDelegate.h"#define kUserTableName                             @"User"@implementation UserDB- (id)init{    self = [super init];    if (self) {        //首先查看有没有数据库文件，没有就创建        _db = [DBManager defaultDBManager].dataBase;    }    return self;}//创建数据表- (void)createTable{    FMResultSet *set = [_db executeQuery:[NSString stringWithFormat:@"select count(*) from sqlite_master where type = 'table' and name = '%@'", kUserTableName]];    [set next];        NSInteger count = [set intForColumnIndex:0];    BOOL existTable = !!count;    if (existTable) {        //更新数据库        [AppDelegate showStatusWithText:@"数据库已经存在！" duration:2];    } else {        //插入到数据库        NSString *sqlStr = @"CREATE TABLE User (uid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name VARCHAR(50), description VARCHAR(100))";        BOOL res = [_db executeUpdate:sqlStr];        if (!res) {            [AppDelegate showStatusWithText:@"数据库创建失败！" duration:2];        } else {            [AppDelegate showStatusWithText:@"数据库创建成功！" duration:2];        }            }}//保存数据- (void)updateTableWithUser:(User *)user{    NSMutableString *query = [NSMutableString stringWithFormat:@"INSERT INTO User"];    NSMutableString *keys = [NSMutableString stringWithFormat:@" ("];    NSMutableString *values = [NSMutableString stringWithFormat:@" ( "];    NSMutableArray *arguments = [NSMutableArray arrayWithCapacity:5];    if (user.name) {        [keys appendString:@"name,"];        [values appendString:@"?,"];        [arguments addObject:user.name];    }    if (user.description) {        [keys appendString:@"description,"];        [values appendString:@"?,"];        [arguments addObject:user.description];    }        [keys appendString:@")"];    [values appendString:@")"];    [query appendFormat:@" %@ VALUES%@", [keys stringByReplacingOccurrencesOfString:@",)" withString:@")"], [values stringByReplacingOccurrencesOfString:@",)" withString:@")"]];    NSLog(@"插入一条语句：%@", query);    [AppDelegate showStatusWithText:@"插入一条数据!" duration:2];    [_db executeUpdate:query withArgumentsInArray:arguments];    }//删除用户信息- (void)deleteTableWithUserID:(NSString *)uid{    NSString *query = [NSString stringWithFormat:@"DELETE FROM User WHERE uid = '%@'", uid];    [AppDelegate showStatusWithText:@"删除一条数据!" duration:2];    [_db executeUpdate:query];}//修改用户信息- (void)modifyTableWithUser:(User *)user{    if (!user.uid) {        return;    }    NSString *query = @"UPDATE User SET";    NSMutableString *temp = [NSMutableString stringWithCapacity:0];    if (user.name) {        [temp appendFormat:@"name = '%@',", user.name];    }    if (user.description) {        [temp appendFormat:@"description = '%@',", user.description];        [temp appendFormat:@")"];        query = [query stringByAppendingFormat:@"%@ WHERE uid = '%@'", [temp stringByReplacingOccurrencesOfString:@",)" withString:@""], user.uid];        NSLog(@"修改一条数据：%@", query);        [AppDelegate showStatusWithText:@"修改了一条数据!" duration:2];        [_db executeUpdate:query];    }}//查找用户- (NSArray *)queryTableWithUid:(NSString *)uid count:(int)userCount{    NSString *query = @"SELECT uid, name, description FROM User";    if (!uid) {        query = [query stringByAppendingFormat:@" ORDER BY uid DESC limit %d", userCount];    } else {        query = [query stringByAppendingFormat:@" WHERE uid > %@ ORDER BY uid DESC limit %d", uid, userCount];    }    NSLog(@"query = %@", query);    FMResultSet *rs = [_db executeQuery:query];    NSMutableArray *array = [NSMutableArray arrayWithCapacity:[rs columnCount]];    while ([rs next]) {        User *user = [[User alloc] init];        user.uid = [rs stringForColumn:@"uid"];        user.name = [rs stringForColumn:@"name"];        user.description = [rs stringForColumn:@"description"];        NSLog(@"user.uid = %@\nuser.name = %@\nuser.des = %@\n", user.uid, user.name, user.description);        [array addObject:user];        [user release];    }    [rs close];    NSLog(@"array = %@\n", array);    return array;}@end

数据模型类

////  User.h//  SQLite3Demo////  Created by 李振杰 on 13-11-26.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import <Foundation/Foundation.h>@interface User : NSObject@property (copy, nonatomic) NSString *uid;@property (copy, nonatomic) NSString *name;@property (copy, nonatomic) NSString *description;@end

////  User.m//  SQLite3Demo////  Created by 李振杰 on 13-11-26.//  Copyright (c) 2013年 swplzj. All rights reserved.//#import "User.h"@implementation User@synthesize uid, name, description;@end

关于数据库的操作在上面代码中基本都包含，你也可以点击下载SQLite3Demo源码。