db類之.h文件
#import <foundation/foundation.h>
#import <sqlite3.h>
@interface db : nsobject
+(sqlite3 *)opendb;//打開數(shù)據(jù)庫
-(void)closedb;//關(guān)閉數(shù)據(jù)庫
@end
db類之.m文件
#import db.h
#import <sqlite3.h>
static sqlite3 *db = nil;
@implementation db
+(sqlite3 *)opendb
{
if(db)
{
return db;
}
//目標(biāo)路徑
nsstring *docpath = [nssearchpathfordirectoriesindomains(nsdocumentdirectory, nsuserdirectory, yes)objectatindex:0];
//原始路徑
nsstring *filepath = [docpath stringbyappendingpathcomponent:@db.sqlite];
nsfilemanager *fm = [nsfilemanager defaultmanager];
if ([fm fileexistsatpath:filepath] == no)//如果doc下沒有數(shù)據(jù)庫，從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;//創(chuàng)建一個(gè)聲明對象
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);
}
//根據(jù)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