分享 ios sqlite 处理

i5ting · March 06, 2013 · 3738 hits

源码

DatabaseService.h

#import <Foundation/Foundation.h>
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"

@interface DatabaseService : NSObject{
    FMDatabase *db;
    FMDatabaseQueue *queue;
}

+ (id)sharedInstance;

@end

DatabaseService.m

//
//  DatabaseService.m
//  gwcl
//
//  Created by sang alfred on 3/5/13.
//  Copyright (c) 2013 sang alfred. All rights reserved.
//

#import "DatabaseService.h"

#define _DBFILE_NAME [NSString stringWithFormat:@"%@",@"cx_db.sqlite"]

#define _DBFILE_DIR  [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

@interface DatabaseService()


@end

@implementation DatabaseService


+ (DatabaseService *)sharedInstance {
    static DatabaseService *_sharedInstance;
    if(!_sharedInstance) {
        static dispatch_once_t oncePredicate;
        dispatch_once(&oncePredicate, ^{
            _sharedInstance = [[super allocWithZone:nil] init];
        });
    }
    return _sharedInstance;
}


+ (id)allocWithZone:(NSZone *)zone {
    return [self sharedInstance];
}


- (id)copyWithZone:(NSZone *)zone {
    return self;
}

#if (!__has_feature(objc_arc))

- (id)retain {
    return self;
}

- (unsigned)retainCount {
    return UINT_MAX;  //denotes an object that cannot be released
}

- (oneway void)release {
    //do nothing
}

- (id)autorelease {
    return self;
}

#endif

#pragma mark - lifecycle

- (id)init {
    if (self = [super init]) {
        NSLog(@"INFO: Begin singleton DataBaseService initialization......");

        NSString *bundlePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:_DBFILE_NAME];

        NSString *dir = _DBFILE_DIR;
        NSString *databasePath = [dir stringByAppendingPathComponent:_DBFILE_NAME];
        NSFileManager *tempFileManager = [NSFileManager defaultManager];
        BOOL isExisted = [tempFileManager fileExistsAtPath:databasePath];
        if (!isExisted) {
            NSLog(@"INFO_OC: 复制数据库文件 database.db from %@ to %@.", bundlePath, databasePath);
            NSError *error = nil;
            BOOL success = [[NSFileManager defaultManager] copyItemAtPath:bundlePath toPath:databasePath error:&error];
            if (!success) {
                NSLog(@"ERROR_OC: Failed to create writable database file with message '%@'.", [error localizedDescription]);
            }
        }

        db = [[FMDatabase databaseWithPath:databasePath] retain];
        //打开sql跟踪日志
        db.traceExecution = YES;
        db.logsErrors=YES;

        if (![db open]) {
            NSLog(@"INFO_OC: Failed to open database.");
            [db release];
            return self;
        }

        NSLog(@"INFO_OC: End singleton DataBaseService initialization......");
    }
    return self;
}

-(void)dealloc{
    [db close];
    [db release];
    [queue release];
    [super dealloc];
}

#pragma mark - Private Methods Implemetions

@end


知识点说明

sqlite

目前来说,大部分人用的都是 sqlite3 数据库,当然 ios 上可以使用其他数据库,如 leveldb 等,主要是由于 ios 的开发语言是 oc,可以无缝集成 c 和 c++ 库。

fmdb

大部分开发人员对 oc 熟悉,对 c 不够熟悉,所以用 sqlite 的时候会选择 oc 写的 sqlite 封装,即 fmdb 库。

如果大家熟悉 db 操作的话,可以发现 fmdb 其实和 dbi::dbd 接口非常相似,简单易用。

https://github.com/ccgus/fmdb

注意:不要把 src 下的 fmdb.m 放到你的项目中。

目录结构

默认情况下,每个沙盒含有 3 个文件夹:Documents, Library 和 tmp。因为应用的沙盒机制,应用只能在几个目录下读写文件

  • Documents:苹果建议将程序中建立的或在程序中浏览到的文件数据保存在该目录下,- iTunes 备份和恢复的时候会包括此目录
  • Library:存储程序的默认设置或其它状态信息;
  • Library/Caches:存放缓存文件,iTunes不会备份此目录,此目录下文件不会在应用退出删除
  • tmp:提供一个即时创建临时文件的地方。

iTunes 在与 iPhone 同步时,备份所有的 Documents 和 Library 文件。 iPhone 在重启时,会丢弃所有的 tmp 文件。

所以我们常用的缓存目录是 Library 和 Documents。

进入目录方法

  00639313-2842-439B-A0E2-5ECB6EB497B0  pwd
/Users/alfred/Library/Application Support/iPhone Simulator/6.0/Applications/00639313-2842-439B-A0E2-5ECB6EB497B0

说明:6.0 是模拟器版本号,一般会存在好几个版本的。 00639313-2842-439B-A0E2-5ECB6EB497B0 是应用目录,具体打开看里面的文件就知道对不对了。

数据库用法

- (id)init {
    if (self = [super init]) {
        NSLog(@"INFO: Begin singleton DataBaseService initialization......");

        NSString *bundlePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:_DBFILE_NAME];

        NSString *dir = [self getDocumentsDir];
        NSString *databasePath = [dir stringByAppendingPathComponent:_DBFILE_NAME];
        NSFileManager *tempFileManager = [NSFileManager defaultManager];
        BOOL isExisted = [tempFileManager fileExistsAtPath:databasePath];
        if (!isExisted) {
            NSLog(@"INFO_OC: 复制数据库文件 database.db from %@ to %@.", bundlePath, databasePath);
            NSError *error = nil;
            BOOL success = [[NSFileManager defaultManager] copyItemAtPath:bundlePath toPath:databasePath error:&error];
            if (!success) {
                NSLog(@"ERROR_OC: Failed to create writable database file with message '%@'.", [error localizedDescription]);
            }
        }

        db = [[FMDatabase databaseWithPath:databasePath] retain];
        //打开sql跟踪日志
        db.traceExecution = YES;
        db.logsErrors=YES;

        if (![db open]) {
            NSLog(@"INFO_OC: Failed to open database.");
            [db release];
            return self;
        }
        [self _db_create];
        NSLog(@"INFO_OC: End singleton DataBaseService initialization......");
    }
    return self;
}

1、首先,把数据库文件拷贝到缓存目录文件 2、创建数据库表结构 3、通过 db 这个全局变量操作数据库

以单例方式操作数据库

这里以 gcd 方式创建单例,控制了自己 alloc 问题,并兼容了 arc 情况。

+ (CXDataService *)sharedInstance { static CXDataService *_sharedInstance; if(!_sharedInstance) { static dispatch_once_t oncePredicate; dispatch_once(&oncePredicate, ^{ _sharedInstance = [[super allocWithZone:nil] init]; }); } return _sharedInstance; }

+ (id)allocWithZone:(NSZone *)zone { return [self sharedInstance]; }

- (id)copyWithZone:(NSZone *)zone { return self;
}

#if (!__has_feature(objc_arc))

- (id)retain {
return self;
}

- (unsigned)retainCount { return UINT_MAX; //denotes an object that cannot be released }

- (oneway void)release { //do nothing }

- (id)autorelease { return self;
}

#endif

这是非常常用的单例。

随便分享一点,困了

补充一个简单的用法

CartDatabaseService 继承 DatabaseService,这样它就可以使用 db 这个共享单例的变量

#import "DatabaseService.h"

@interface CartDatabaseService : DatabaseService
- (NSMutableArray *)shiti_find_all_random;
@end




#import "CartDatabaseService.h"

@implementation CartDatabaseService

- (NSMutableArray *)find_all_in_cart{
    FMResultSet *_rs = [db executeQuery:@"select id,tid,zid,tName,tPicAddress,a1, a2, a3 , a4 , a5 , tanswer, tdesc from tb_shiti where id<30"];
    NSMutableArray *ret_array =  [[NSMutableArray alloc] init];

    if (_rs) {
        while ([_rs next]) {

            NSString *tName = [_rs stringForColumn:@"tName"];
            NSString *a1 = [_rs stringForColumn:@"a1"];
            NSString *a2 = [_rs stringForColumn:@"a2"];
            NSString *a3 = [_rs stringForColumn:@"a3"];
            NSString *a4 = [_rs stringForColumn:@"a4"];
            NSString *a5 = [_rs stringForColumn:@"a5"];

          DM_Object *t = [[DM_Object alloc] initWithName:tName];

           [ret_array addObject:t];
            [t release];
        }
        return [ret_array autorelease];

    }else {
        [ret_array release];
        ret_array = nil;
        return nil;
    }
}

@end

测试

CartDatabaseService *cdbs = [CartDatabaseService sharedInstance];

NSArray *ret = [cdbs find_all_in_cart];

for (NSString *name in ret) {
    LOG_EXPR(name);
}

command +u 跑一下单元测试吧

ok,完成

No Reply at the moment.
You need to Sign in before reply, if you don't have an account, please Sign up first.