sqlite 操作与封装

如果想更深的理解SQLITE的封请学习FMDB，FMDB是对SQLITE的线程访问封装。

 

以下是本人以个人习惯简单的封装。

符合D开发的操作者习惯。

 

ocsqlite.h

//
//  OCSqlite.m
//  sqlite
//
//  Created by fengsh on 12-12-3.
//  Copyright (c) 2012年 sqlite_Lib. All rights reserved.
//
/*
 对ＳＱＬＩＴＥ的封装，主要设计是操作上的习惯。对于轻量级数据量可以。
 对于在数据量就需要注意内存的开销了。
 */

#import <Foundation/Foundation.h>
#import <sqlite3.h>

enum fieldtype
{
    ftInt,ftFloat,ftDouble,ftString,ftBlob,ftBool,ftDate,ftTime,ftDateTime,ftBinary
};

/*
 字段类
 作用：主要用于与数据库中的字段属性进行对应
 字段名，字段类型，字段值，字段索引号
 */
@interface OCField : NSObject 
{    
    NSString* fieldName;
    id fieldValue;
    enum fieldtype mtype;
    int seq_column;
}

-(NSString*)toString;
-(NSInteger)toInteger;
-(NSDate*)toDate;
-(NSString*)toDateString;
-(NSString*)toTimeString;
-(NSString*)toDateTimeString;
-(NSNumber*)toNumber;

-(enum fieldtype)getFieldType;

@property (nonatomic) int seq_column;

@end

/*
 数据集类
 作用：
 类似于数据源的集合，带游标，可访问数据源中的数据
 */
@interface OCDataset : NSObject
{
    NSMutableArray* records;
    NSInteger cursor;
}

-(void)clear;
-(NSInteger)count;
-(BOOL)next;
-(BOOL)first;
-(BOOL)move:(NSInteger) index;
-(OCField*)fieldbyname:(NSString*) fieldname;
-(OCField*)indexOffield:(NSInteger) index;

@end

/*
简单的数据定义语言操作及数据库查询的封装
 未支持参数绑定，因此在处理blob上还需要扩展代码。
 后续完善
 */
@interface OCSqlite : NSObject
{
    sqlite3* db;
    OCDataset* dataset;
}

-(id)init;

-(BOOL)ConnectToDB:(NSString*) dbfilepath;
-(void)DisconnectDB;

-(BOOL)startTranslation;
-(BOOL)commitTranslation;
-(BOOL)rollbackTranslation;

-(BOOL)excesql:(NSString*) ddlsql;
-(BOOL)query:(NSString*) qysql;

@property (nonatomic,readonly) OCDataset* dataset;

@end

ocsqlite.m

//
//  OCSqlite.m
//  sqlite
//
//  Created by fengsh on 12-12-3.
//  Copyright (c) 2012年 sqlite_Lib. All rights reserved.
//

#import "OCSqlite.h"


@implementation OCField

@synthesize seq_column;

-(id)init
{
    self = [super init];
    if (self) {
        fieldValue = NULL;
        return self;
    }
    return nil;
}

-(void)setfield:(NSString*) name withvalue:(sqlite3_value*) value withtype:(NSString*) tp
{
    fieldName = name;
    
    NSString* result = @"";
    
    if ([tp isEqualToString:@"integer"]||[tp isEqualToString:@"smallint"])
    {
        mtype = ftInt;
        fieldValue = (id)sqlite3_value_int(value);
        return;
    }
    else if ([tp isEqualToString:@"boolean"])
    {
        mtype = ftBool;
    }
    else if ([tp isEqualToString:@"float"])
    {
        mtype = ftFloat;

    }
    else if ([tp isEqualToString:@"double"]||[tp isEqualToString:@"real"])
    {
        mtype = ftDouble;
    }
    else if ([tp isEqualToString:@"text"]||[tp isEqualToString:@"varchar"])
    {
        mtype = ftString;
    }
    else if ([tp isEqualToString:@"blob"])
    {
        mtype = ftBlob;
        return;
    }
    else if ([tp isEqualToString:@"date"])
    {
        mtype = ftDate;
    }
    else if ([tp isEqualToString:@"time"])
    {
        mtype = ftTime;

    }
    else if ([tp isEqualToString:@"timestamp"])
    {
        mtype = ftDateTime;

    }
    else if ([tp isEqualToString:@"binary"])
    {
        mtype = ftBinary;
        return;
    }
    
    char* floatstr = (char*)sqlite3_value_text(value);
    if (floatstr) {
        result = [NSString stringWithCString:floatstr encoding:NSUTF8StringEncoding];
    }
    fieldValue = result;
}

-(NSString*)toString
{
    return (NSString*)fieldValue;
}

-(NSInteger)toInteger
{
    return (int)fieldValue;
}

-(NSNumber*)toNumber
{
    switch (mtype) {
        case ftFloat:
            return [NSNumber numberWithFloat:[(NSString*)fieldValue floatValue]];
            break;
            
        case ftDouble:
            return [NSNumber numberWithDouble:[(NSString*)fieldValue doubleValue]]; 
            break;
        
        case ftBool:
            return [NSNumber numberWithBool:[(NSString*)fieldValue boolValue]];
            break;
        default:
            return [NSNumber numberWithInt:[(NSString*)fieldValue intValue]];
            break;
    }
    
}

-(NSString*)toDateString
{
    NSDateFormatter* fmt = [[[NSDateFormatter alloc]init]autorelease];

    [fmt setDateFormat:@"yyyy-mm-dd"];
    NSDate* date = [fmt dateFromString:fieldValue];

    NSString* datestr = [fmt stringFromDate:date];

    return (datestr==nil) ? @"":datestr;
}

-(NSString*)toTimeString
{
    NSDateFormatter* fmt = [[[NSDateFormatter alloc]init]autorelease];
    
    [fmt setDateFormat:@"HH:mm:ss"];//H为0－23，h为1-12

    NSDate* time = [fmt dateFromString:fieldValue];
    
    NSString* timestr = [fmt stringFromDate:time];
    
    return (timestr==nil) ? @"":timestr;
}

-(NSString*)toDateTimeString;
{
    NSDateFormatter* fmt = [[[NSDateFormatter alloc]init]autorelease];
    
    [fmt setDateFormat:@"yyyy-MM-dd HH:mm:ss"];//H为0－23，h为1-12
    
    NSDate* datetime = [fmt dateFromString:fieldValue];
    
    NSString* dtimestr = [fmt stringFromDate:datetime];
    
    return (dtimestr==nil) ? @"":dtimestr;
}

-(NSDate*)toDate
{
    
     NSDateFormatter* fmt = [[NSDateFormatter alloc]init];
     
     [fmt setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
     
     NSDate* date = [fmt dateFromString:fieldValue];
    
     return date;
      
}


-(enum fieldtype)getFieldType
{
    return mtype;
}

@end

@implementation OCDataset

-(id)init
{
    self = [super init];
    if (self) {
        cursor = -1;
        records = [[NSMutableArray alloc]init];
        return self;
    }
    return nil;
}

-(void)dealloc
{
    [records release];
    [super dealloc];
}

-(void)reset
{
    cursor = 0;
}

-(void)fillData:(sqlite3_stmt*) cmd
{
    NSInteger colcount = sqlite3_column_count(cmd);
    
    NSMutableDictionary* fields = [[[NSMutableDictionary alloc]init]autorelease];
    for (NSInteger i = 0; i < colcount; i++) {
        char* fieldname = (char*)sqlite3_column_name(cmd, i);
        NSString* strfieldname = [NSString stringWithCString:fieldname encoding:NSUTF8StringEncoding];
        sqlite3_value* mvalue = sqlite3_column_value(cmd, i);

        char* ity = (char*)sqlite3_column_decltype(cmd, i);
        
        NSString* stype = [NSString stringWithCString:ity encoding:NSUTF8StringEncoding];
        //int type = sqlite3_column_type(cmd, i);
    
        OCField* field = [[OCField alloc]init];

        [field setfield:strfieldname withvalue:mvalue withtype:stype];
        field.seq_column = i;

        
        [fields setObject:field forKey:strfieldname];
    }
    [records addObject:fields];
}

-(void)clear
{
    [records removeAllObjects];
    cursor = -1;
}

-(NSInteger)count
{
    return [records count];
}

-(OCField*)fieldbyname:(NSString*) fieldname
{
    NSMutableDictionary* dic = [records objectAtIndex:cursor];
    
    return (OCField*)[dic objectForKey:fieldname];
}

-(BOOL)next
{
    ++cursor;
    int rcount = [records count];
    if (cursor <= rcount) {
        return YES;
    }
    else
    {
        cursor = rcount+1;
        return NO;
    }
}

-(BOOL)first
{
    if ([records count]>0) {
        cursor = 0;
        return YES;
    }
    else
    {
        cursor = -1;
        return NO;
    }
}

-(BOOL)move:(NSInteger) index
{
    int idx = index -1;
    if (-1 < idx < [records count]) {
        cursor = idx;
        return YES;
    }
    return NO;
}

-(OCField*)indexOffield:(NSInteger) index
{
    OCField* ret = nil;
    int ct = 0;
    NSMutableDictionary* dic = [records objectAtIndex:cursor];
    for (NSString* key in dic)
    {
        ret = [dic objectForKey:key];
        
        if (index == ct)
            break;
        else
            ct++;
        
    }
    return ret;
}

@end



@implementation OCSqlite

@synthesize dataset;

-(id)init
{
    self = [super init];
    if (self) {
        
        dataset = [[OCDataset alloc]init];
        return self;
    }
    return nil;
}

-(void)dealloc
{
    [dataset release];
    sqlite3_close(db);
    [super dealloc];
}

-(BOOL)ConnectToDB:(NSString*) dbfilepath
{
    BOOL successful;
    successful = sqlite3_open([dbfilepath UTF8String], &db)==SQLITE_OK;
    
    if (!successful) {
        sqlite3_close(db);
        return NO;
    }

    return YES;
}

-(void)DisconnectDB
{
    sqlite3_close(db); 
}

-(BOOL)excesql:(NSString*) ddlsql
{
    char* err;
    if (sqlite3_exec(db, [ddlsql UTF8String], NULL, NULL, &err)!=SQLITE_OK)
    {
        return NO;
    }
    return YES;
}

-(BOOL)query:(NSString*) qysql
{
    sqlite3_stmt* cmd;
    
    if (sqlite3_prepare_v2(db, [qysql UTF8String], -1, &cmd, nil)!=SQLITE_OK)
    {
        return NO;
    }
    
    [dataset clear];
    
    while(sqlite3_step(cmd)==SQLITE_ROW)
    {
        [dataset fillData:cmd];
    }
    
    [dataset reset];
    
    sqlite3_finalize(cmd);
    
    return YES;
}


-(BOOL)startTranslation
{
    char* err;
    if (sqlite3_exec(db, "begin transaction",NULL, NULL, &err)!=SQLITE_OK)
    {
        return NO;
    }
    return YES;
}

-(BOOL)commitTranslation
{
    char* err;
    if (sqlite3_exec(db, "commit transaction",NULL, NULL, &err)!=SQLITE_OK)
    {
        return NO;
    }
    return YES;
}

-(BOOL)rollbackTranslation
{
    char* err;
    if (sqlite3_exec(db, "rollback transaction",NULL, NULL, &err)!=SQLITE_OK)
    {
        return NO;
    }
    return YES;
}


@end