下面是本人写的sqlite数据库在IOS中使用的通用代码，欢迎大家拍砖指正。
在使用前需要先添加libsqlite3.0.dylib
DataInit.h
#import <Foundation/Foundation.h>
#import "sqlite3.h"
@interface DataInit : NSObject
{
NSMutableArray *dataArray;//该数组用于存放从数据库中读取的数据
sqlite3 *database;
}
@property (nonatomic,retain) NSMutableArray *dataArray;
//建立的DataInit单例模型
+(DataInit*)sharedDataInit;
//添加数据到sqlite数据库中
//sql:所要执行的sql插入语句
//_valueString:要插入的数据串
//_indexString:要插入的数据串在数据库中每个数据所在的列的位置
-(BOOL)addDataWithsqlClause:(char *)sql withString:(NSString *)_valueString withIndexString:(NSString *)_indexString;
-(BOOL)insertWithsqlClause:(char *)sqlInsert withString:(NSString *)_valueString withIndexString:(NSString *)_indexString;
//通过程序代码创建sqlite数据库
- (BOOL)createEditableCopyOfDatabaseIfNeeded ;
// 查询数据库，并存储数据到数组中
- (void)initializeDatabaseTableSelectsql:(const char *)sql getDataSQL:(const char *)sql1 withIndexCount:(int)_indexCount;
-(NSString *)getStringValueWithPrimaryKey:(NSInteger)pk withSelectSQL:(const char *)sql withIndexCount:(int)_indexCount;
//更新数据库纪录
-(BOOL)updateDataWithSQLClause:(constchar*)sql;
-(BOOL)updateWithsqlClause:(constchar *)sqlupdate;
@end
DataInit.m
#import "DataInit.h"
#import "DataFile.h"
static sqlite3_stmt *insert_statement = nil;
static sqlite3_stmt *init_statement = nil;
static sqlite3_stmt *delete_statement = nil;
static sqlite3_stmt *update_statement = nil;
@implementation DataInit
@synthesize dataArray;
static DataInit *initdatabase = nil;
+(DataInit*)sharedDataInit
{
@synchronized(self)
{
if(initdatabase==nil)
{
[[self alloc]init];
}
}
returninitdatabase;
}
+ (id)allocWithZone:(NSZone *)zone
{
@synchronized(self)
{
if (initdatabase == nil)
{
initdatabase = [super allocWithZone:zone];
returninitdatabase; // assignment and return on first allocation
}
}
returnnil; //on subsequent allocation attempts return nil
}
- (id)copyWithZone:(NSZone *)zone
{
returnself;
}
- (id)retain
{
returnself;
}
- (unsigned)retainCount
{
returnUINT_MAX; //denotes an object that cannot be released
}
- (void)release
{
//do nothing
}
- (id)autorelease
{
returnself;
}
///*
- init
{
if (self = [super init])
{
self.dataArray = [[NSMutableArrayalloc]init];
}
returnself;
}
-(void)dealloc
{
[dataArrayremoveAllObjects];
[dataArrayrelease];
[superdealloc];
}
//*/
// Creates a writable copy of the bundled default database in the application Documents directory.
- (BOOL)createEditableCopyOfDatabaseIfNeeded
{
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManagerdefaultManager];
//NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:DATABASE];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success)
returnYES;
success = [fileManager createFileAtPath:writableDBPath contents:nil attributes:nil];
//*
//Create tables for the database in the first time
int isexecuate = -1;
if(success)
{
if(sqlite3_open([writableDBPath UTF8String], &database) == SQLITE_OK)
{
constchar *sql = "create table songlist (songlist_id integer primary key autoincrement,net_id nvarchar(48),email nvarchar(48),title nvarchar(48),body text(48),created nvarchar(48),downloadtimes nvarchar(48),remember nvarchar(48),favorite nvarchar(48) )";
isexecuate = sqlite3_exec(database, sql,NULL,NULL,NULL);
if(isexecuate!=SQLITE_OK)
{
#ifTARGET_IPHONE_SIMULATOR
NSAssert1(0,@"failed to create table songlist with message '%@'.",sqlite3_errmsg(database));
#endif
}
constchar *sql1 = "create table playsongs (songlist_id integer primary key autoincrement,title nvarchar(48),soundwords text(48))";
isexecuate = sqlite3_exec(database, sql1,NULL,NULL,NULL);
if(isexecuate!=SQLITE_OK)
{
#ifTARGET_IPHONE_SIMULATOR
NSAssert1(0,@"failed to create table playsongs with message '%@'.",sqlite3_errmsg(database));
#endif
}
}
}
if (isexecuate==SQLITE_OK)
{
#if TARGET_IPHONE_SIMULATOR
NSLog(@"create success");
#endif
returnYES;
}
else
{
#ifTARGET_IPHONE_SIMULATOR
NSLog(@"create fail");
#endif
returnNO;
}
}
#pragma mark 数据库操作
+(void)finalizeStatements
{
if(insert_statement)
sqlite3_finalize(insert_statement);
if(init_statement)
sqlite3_finalize(init_statement);
if(delete_statement)
sqlite3_finalize(delete_statement);
}
- (void)initializeDatabaseTableSelectsql:(const char *)sql getDataSQL:(const char *)sql1 withIndexCount:(int)_indexCount
{
[self.dataArrayremoveAllObjects];
database = nil;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:DATABASE];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
{
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK)
{
// We "step" through the results - once for each row.
while (sqlite3_step(statement) == SQLITE_ROW)
{
int primaryKey = sqlite3_column_int(statement, 0);
NSString *valueString = [selfgetStringValueWithPrimaryKey:primaryKey withSelectSQL:sql1 withIndexCount:_indexCount];
[dataArray addObject:valueString];
}
}
sqlite3_finalize(statement);
sqlite3_close(database);
}
else
{
// Even though the open failed, call close to properly clean up resources.
sqlite3_close(database);
#ifTARGET_IPHONE_SIMULATOR
NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
#endif
}
}
-(BOOL)addDataWithsqlClause:(char *)sql withString:(NSString *)_valueString withIndexString:(NSString *)_indexString
{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:DATABASE];
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
{
return [selfinsertWithsqlClause:sql withString:_valueString withIndexString:_indexString];
}
returnNO;
}
-(BOOL)updateDataWithSQLClause:(const char*)sql
{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:DATABASE];
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
{
return [selfupdateWithsqlClause:sql];
}
returnNO;
}
-(NSString *)getStringValueWithPrimaryKey:(NSInteger)pk withSelectSQL:(const char *)sql withIndexCount:(int)_indexCount
{
int success = -1;
NSString *returnString = @"";
success = sqlite3_prepare_v2(database, sql, -1, &init_statement, NULL);
if (success!=SQLITE_OK)
{
#if TARGET_IPHONE_SIMULATOR
NSAssert1(0,@"Error: failed to prepare statement with message '%s'.",sqlite3_errmsg(database));
#endif
returnString = @"-1";
return returnString;
}
if (sqlite3_column_count(init_statement)<_indexCount)
{
returnString = @"-2";
return returnString;
}
sqlite3_bind_int(init_statement, 1, pk);
if(sqlite3_step(init_statement)==SQLITE_ROW)
{
for (int i=0; i<_indexCount; i++)
{
if ([returnString isEqualToString:@""])
{
returnString =[NSStringstringWithFormat:@"%@",[NSStringstringWithUTF8String:(char *)sqlite3_column_text(init_statement, i)]];
}
else
{
returnString =[NSStringstringWithFormat:@"%@,%@",returnString,[NSStringstringWithUTF8String:(char *)sqlite3_column_text(init_statement, i)]];
}
//NSLog(@"returnString = %@",returnString);
}
}
sqlite3_reset(init_statement);
return returnString;
}
-(BOOL)insertWithsqlClause:(char *)sqlInsert withString:(NSString *)_valueString withIndexString:(NSString *)_indexString
{
int success = -1;
if(insert_statement == nil)
{
success = sqlite3_prepare_v2(database, sqlInsert, -1, &insert_statement, NULL);
if(success!=SQLITE_OK)
{
#if TARGET_IPHONE_SIMULATOR
NSAssert1(0,@"Error: failed to prepare statement with message '%s .",sqlite3_errmsg(database));
#endif
}
}
if (success!=SQLITE_OK)
{
#ifTARGET_IPHONE_SIMULATOR
NSLog(@"prepare fail");
#endif
returnNO;
}
NSArray *valueArray = [_valueString componentsSeparatedByString:@","];
NSArray *indexArray = [_indexString componentsSeparatedByString:@","];
if (valueArray.count==indexArray.count)
{
for (int i=0; i<valueArray.count; i++)
{
int index = [[indexArray objectAtIndex:i]intValue];
NSString *tempString = [valueArray objectAtIndex:i];
sqlite3_bind_text(insert_statement,index,[tempString UTF8String],-1,SQLITE_TRANSIENT);
}
success = sqlite3_step(insert_statement);
if (success==SQLITE_ERROR)
{
sqlite3_reset(insert_statement);
sqlite3_finalize(insert_statement);
sqlite3_close(database);
insert_statement = nil;
database = nil;
returnNO;
}
sqlite3_reset(insert_statement);
sqlite3_finalize(insert_statement);
sqlite3_close(database);
insert_statement = nil;
database = nil;
}//*/
returnYES;
}
-(BOOL)updateWithsqlClause:(const char *)sqlupdate
{
int success = -1;
if(update_statement == nil)
{
success = sqlite3_prepare_v2(database, sqlupdate, -1, &update_statement, NULL);
if(success!=SQLITE_OK)
{
#if TARGET_IPHONE_SIMULATOR
NSAssert1(0,@"Error: failed to prepare statement with message '%s .",sqlite3_errmsg(database));
#endif
}
}
if (success!=SQLITE_OK)
{
#ifTARGET_IPHONE_SIMULATOR
NSLog(@"prepare fail");
#endif
returnNO;
}
success = sqlite3_step(update_statement);
if (success==SQLITE_DONE)
{
sqlite3_finalize(update_statement);
sqlite3_close(database);
update_statement = nil;
database = nil;
}
if (success==SQLITE_ERROR)
{
sqlite3_reset(update_statement);
sqlite3_finalize(update_statement);
sqlite3_close(database);
update_statement = nil;
database = nil;
returnNO;
}
returnYES;
}
