如果想更深的理解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.
- //
- /*
- 对SQLITE的封装,主要设计是操作上的习惯。对于轻量级数据量可以。
- 对于在数据量就需要注意内存的开销了。
- */
- #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