iOS数据存储（四）SQLite 3

SQLite是一个开源的嵌入式关系数据库，它的减少应用程序管理数据的开销，SQLite可移植性好，很容易使用，很小，高效而且可靠。

第一步：引入PLDatabase库
第二步：创建.spl文件【create_db.spl】
第三步：
CDSqlManager.h

#import <Foundation/Foundation.h>
#import "PlausibleDatabase.h"

@interface CDSqlManager : NSObject
//sql执行函数
+(id<PLResultSet>) executeQueryWithSqlString: (NSString *) sqlString;

// 不加锁执行sq
+(void) executeUpdateWithoutLockWithSqlString:(NSString *)sqlString;

//执行更新语句
+(BOOL) executeUpdateWithSqlString: (NSString *) sqlString;

//执行sql语句，在初始化表时执行
+(void) executeSqlWithSqlStrings: (NSString *) sqlStrings;

// 判断表是否存在
+(BOOL) dbExists;

//打开数据库
+(void) openDatabase;

// 关闭数据库表
+(void) closeDatabase;

//加锁
+(void) lock;

 函数描述: 解锁
+(void) unlock;

CDSqlManager.m

#import "CDSqlManager.h"
#import "PlausibleDatabase.h"

#define sqlFileName @"CDDemoSqlFile"
@implementation CDSqlManager
// database类型:类静态变量
static PLSqliteDatabase *database = nil;
static NSCondition *condition = nil;

+(NSString *) dbPath
{
    static NSString *dbPath = nil;
    
    if (!dbPath)
    {
        NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];
        dbPath = [[documentPath stringByAppendingString:sqlFileName]retain];
    }
    
    return dbPath;
}

+(BOOL) dbExists
{
    return [[NSFileManager defaultManager] fileExistsAtPath: [self dbPath]];
}

+ (void)openDatabase
{
    if(nil == database)
    {
        database = [[PLSqliteDatabase alloc] initWithPath: [self dbPath]];
        BOOL bOpen = [database open];
        
        NSLog(@"dbPath = %@, bOpen = %d", [self dbPath], bOpen);
        
        if (bOpen)
        {
            NSLog(@"Database opened OK!");
        }
        
        condition = [[NSCondition alloc] init];
    }
}

+(void) closeDatabase
{
    [condition release];
    [database release];
}

+(void) lock
{
    [condition lock];
}

+(void) unlock
{
    [condition unlock];
}

#pragma mark -

+(id<PLResultSet>) executeQueryWithSqlString: (NSString *) sqlString
{
    NSLog(@"%s %d, sqlString = %@", __FUNCTION__, __LINE__, sqlString);

    if (nil == sqlString)
    {
        return nil;
    }
    
//    [self lock];//数据库查询在外层加锁
    
    id<PLResultSet> result = [database executeQuery: sqlString];
    
//    [self unlock];//数据库查询在外层加锁
    
    return result;
}

+(void) executeUpdateWithoutLockWithSqlString:(NSString *)sqlString
{ 
    NSLog(@"%s %d, sqlString = %@", __FUNCTION__, __LINE__, sqlString);
    BOOL bResult = [database executeUpdate: sqlString];
    
    if (!bResult)
    {
        NSLog(@"executeUpdate: %@", bResult ? @"关闭" : @"Failed");
    }
}

+(BOOL) executeUpdateWithSqlString: (NSString *) sqlString
{
//    NSLog(@"%s %d, sqlString = %@", __FUNCTION__, __LINE__, sqlString);
    
    [self lock];

    BOOL bResult = [database executeUpdate: sqlString];

    [self unlock];

    NSLog(@"executeUpdate: %@", bResult ? @"success" : @"Failed");
    
    return bResult;
}

+(void) executeSqlWithSqlStrings: (NSString *) sqlStrings
{
    NSArray *sqlArray = [sqlStrings componentsSeparatedByString: @";"];
    
    for (NSString *sqlString in sqlArray) 
    {
        sqlString = [sqlString stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
        
        if ([sqlString length] > 0)
        {
            NSLog(@"--->\n%@", sqlString);
            
            [self executeUpdateWithSqlString: sqlString];
        }
    }
}

CDSQLService.h 单例 放查询方法

#import <Foundation/Foundation.h>
#import "CDSqlManager.h"
@interface CDSQLService : NSObject
+(CDSQLService *)instance;

+(BOOL)update……
+(BOOL)add……
+(BOOL)delete……
+(id)query

CDSQLService.m

#import "CDSQLService.h"

@implementation CDSQLService
static CDSQLService *instance = nil;

+(CDSQLService *)instance{
   @synchronized(self){
       if (!instance) {
           [CDSqlManager openDatabase];
           instance = [[CDSQLService alloc]init];
       }
       
   }
   return instance;
}

-(id)init{
   self = [super init];
   if (self) {
       [self createTablesIfNotExsits];
   }
   return self;
}

-(void)createTablesIfNotExsits{
   // 打开数据库
   //从未装过或者卸载重装或者杀进程在进来的情况
   NSString *sqlString = [NSString stringWithContentsOfFile: [[NSBundle mainBundle] pathForResource: @"create_db_table" ofType: @"sql"]
                                                   encoding: NSUTF8StringEncoding
                                                      error: nil];
   
   [CDSqlManager executeSqlWithSqlStrings: sqlString];

}