DB类之.h文件

#import <Foundation/Foundation.h>

#import <sqlite3.h>

@interface DB : NSObject

+(sqlite3 *)openDB;//打开数据库

-(void)closeDB;//关闭数据库

@end

DB类之.m文件

#import "DB.h"

#import <sqlite3.h>

static sqlite3 *db = nil;

@implementation DB

+(sqlite3 *)openDB

{

if(db)

{

return db;

}

//目标路径

NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDirectory, YES)objectAtIndex:0];

//原始路径

NSString *filePath = [docPath stringByAppendingPathComponent:@"db.sqlite"];

NSFileManager *fm = [NSFileManager defaultManager];

if ([fm fileExistsAtPath:filePath] == NO)//如果doc下没有数据库，从bundle里面拷贝过来

{

NSString *bundle = [[NSBundle mainBundle]pathForResource:@"classDB" ofType:@"sqlite"];

NSError *err = nil;

if ([fm copyItemAtPath:bundle toPath:filePath error:&err] == NO) //如果拷贝失败

{

NSLog(@" localizedDescription]);

}

}

sqlite3_open([filePath UTF8String], &db);

return db;

}

-(void)closeDB

{

if (db)

{

sqlite3_close(db);

}

}

@end

Person类.h文件

#import <Foundation/Foundation.h>

@interface Person : NSObject

@property(nonatomic,retain)NSString *name,*phone;

@property(nonatomic,assign)int age,ID;

-(id)initWithName:(NSString *)name phone:(NSString *)phone age:(int)age ID:(int)ID;

+(NSMutableArray *)findAll;

+(int)count;

+(Person *)findByID:(int)ID;

+(NSMutableArray *)findByname:(NSString *)name;

+(void)addName:(NSString *)name phone:(NSString *)phone age:(int)age;

+(void)deleteByID:(int)ID;

+(void)updataName:(NSString *)name phone:(NSString *)phone age:(int)age forID:(int)ID;

@end

Person类.m文件

#import "Person.h"

#import "DB.h"

@implementation Person

@synthesize name,ID,phone,age;

-(id)initWithName:(NSString *)aName phone:(NSString *)aPhone age:(int)aAge ID:(int)aID

{

[super init];

if (self)

{

self.name = aName;

self.phone = aPhone;

self.age = aAge;

self.ID = aID;

}

return self;

}

-(NSString *)description

{

return [NSString stringWithFormat:@"id = %d name = %@ phone = %@ age = %d",self.ID,self.name,self.phone,self.age ];

}

+(NSMutableArray *)findAll

{

sqlite3 *db = [DB openDB];

sqlite3_stmt *stmt = nil;//创建一个声明对象

int result = sqlite3_prepare_v2(db, "select * from classDB order by ID ", -1, &stmt, nil);

NSMutableArray *persons = nil;

if (result == SQLITE_OK)

{

persons = [[NSMutableArray alloc]init];

while (sqlite3_step(stmt) == SQLITE_ROW)

{

int ID = sqlite3_column_int(stmt, 0);

const unsigned char *name = sqlite3_column_text(stmt, 1);

const unsigned char *phone = sqlite3_column_text(stmt, 2);

int age = sqlite3_column_int(stmt, 3);

Person *p = [[Person alloc]initWithName:[NSString stringWithUTF8String:(const char *)name] phone:[NSString stringWithUTF8String:(const char *)phone] age:age ID:ID];

[persons addObject:p];

[p release];

}

}

else

{

persons = [[NSMutableArray alloc]init];

}

sqlite3_finalize(stmt);

return [persons autorelease];

}

+(int)count

{

sqlite3 *db = [DB openDB];

sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare_v2(db, "select count(ID) from classDB", -1, &stmt, nil);

if (result == SQLITE_OK)

{

int count = 0;

if (sqlite3_step(stmt))

{

count = sqlite3_column_int(stmt, 0);

}

sqlite3_finalize(stmt);

return count;

}

else

{

sqlite3_finalize(stmt);

return 0;

}

}

+(Person *)findByID:(int)ID

{

sqlite3 *db = [DB openDB];

sqlite3_stmt *stmt = nil;

Person *p = nil;

int result = sqlite3_prepare_v2(db, "select * from classDB where ID = ?", -1, &stmt, nil);

if (result == SQLITE_OK)

{

sqlite3_bind_int(stmt, 1, ID);

if (sqlite3_step(stmt))

{

int ID = sqlite3_column_int(stmt, 0);

const unsigned char *name = sqlite3_column_text(stmt, 1);

const unsigned char *phone = sqlite3_column_text(stmt, 2);

int age = sqlite3_column_int(stmt, 3);

p = [[Person alloc]initWithName:[NSString stringWithUTF8String:(const char *)name] phone:[NSString stringWithUTF8String:(const char *)phone] age:age ID:ID];

}

}

sqlite3_finalize(stmt);

return [p autorelease];

}

+(NSMutableArray *)findByname:(NSString *)name

{

sqlite3 *db = [DB openDB];

sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare(db, "select * from classDB where name = ?", -1, &stmt, nil);

NSMutableArray *persons = nil;

if (result == SQLITE_OK)

{

sqlite3_bind_text(stmt, 1, [name UTF8String], -1, nil);

persons = [[NSMutableArray alloc]init];

while (sqlite3_step(stmt) == SQLITE_ROW)

{

int ID = sqlite3_column_int(stmt, 0);

const unsigned char *name = sqlite3_column_text(stmt, 1);

const unsigned char *phone = sqlite3_column_text(stmt, 2);

int age = sqlite3_column_int(stmt, 3);

Person *p = [[Person alloc]initWithName:[NSString stringWithUTF8String:(const char *)name] phone:[NSString stringWithUTF8String:(const char *)phone] age:age ID:ID];

[persons addObject:p];

[p release];

}

}

else

{

persons = [[NSMutableArray alloc]init];

}

sqlite3_finalize(stmt);

return [persons autorelease];

}

//添加元素

+(void)addName:(NSString *)name phone:(NSString *)phone age:(int)age

{

NSString *str = [NSString stringWithFormat:@"insert into classDB(name,phone,age) values(];

sqlite3 *db = [DB openDB];

sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare_v2(db, [str UTF8String],-1 ,&stmt , nil);

if (result == SQLITE_OK)

{

sqlite3_step(stmt);

}

sqlite3_finalize(stmt);

}

//根据ID删除信息

+(void)deleteByID:(int)ID

{

NSString *str = [NSString stringWithFormat:@"delete from classDB where ID = %d",ID];

sqlite3 *db = [DB openDB];

sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, nil);

if (result == SQLITE_OK)

{

sqlite3_step(stmt);

}

sqlite3_finalize(stmt);

}

//更新

+(void)updataName:(NSString *)name phone:(NSString *)phone age:(int)age forID:(int)ID

{

NSString *str = [NSString stringWithFormat:@"update classDB set name = = %d where ID = %d",name,phone,age,ID];

sqlite3 *db = [DB openDB];

sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, nil);

if (result == SQLITE_OK)

{

sqlite3_step(stmt);

}

sqlite3_finalize(stmt);

}

@end

更多信息请查看IT技术专栏