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技术专栏