数据库

导入libsqlite3.0.dylib
#import <sqlite3.h>


//创建数据库
+(void) creatDataBase
{
    //    if (dbPoint) {
    //        return dbPoint;
    //    }
    //目标路径
    NSString * docPath=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString * filePath=[docPath stringByAppendingPathComponent:@"PersonDB.rdb"];
    NSLog(@"%@",filePath);
    NSFileManager * manager=[[NSFileManageralloc] init];
    if ([manager fileExistsAtPath:filePath]) {
        [manager removeItemAtPath:filePath error:nil];
        NSLog(@"文件存在");
    }
    [manager release];
    //创建数据库
    if (sqlite3_open([filePath UTF8String], &dbPoint)!=SQLITE_OK) {
        sqlite3_close(dbPoint);
        NSLog(@"打开失败");
    }
    //创建表
    sqlite3_stmt * stmt=nil;
    int result=sqlite3_exec(dbPoint, "create table if not exists class(ID integer primary key,name text,sex text,del text,image BLOB)", nil, nil, nil);
    if (result==SQLITE_OK) {
        //sqlite3_finalize(stmt);
        NSLog(@"创建成功");
    }
    sqlite3_finalize(stmt);
    sqlite3_close(dbPoint);
}


--------------------------------------------------------------------------------------------------------------------

static sqlite3 * dbPoint=nil;


//打开数据库
+(sqlite3 *) openDB
{
    if (dbPoint) {
        return dbPoint;
    }
    //目标文件路径
    NSString * docPath=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString * filePath=[docPath stringByAppendingPathComponent:@"Studentdb.rdb"];
    //原始路径
    NSString * orignFilePath=[[NSBundle mainBundle] pathForResource:@"Studentdb" ofType:@"rdb"];
    //文件管理
    NSFileManager * manager=[NSFileManagerdefaultManager];
    //如果不存在数据库则从bundle拷贝
    if ([manager fileExistsAtPath:filePath]==NO) {
        //如果拷贝失败
        if ([manager copyItemAtPath:orignFilePath toPath:filePath error:nil]==NO) {
            return nil;
        }
    }
    sqlite3_open([filePath UTF8String], &dbPoint);
    return dbPoint;
}
//关闭数据库
+(void) clodeDB
{
    if (dbPoint) {
        sqlite3_close(dbPoint);
    }
    
}

----------------------------------------------------------------------------------------------


+(NSMutableArray *)findAll;//所有人
//按ID查找人
+(Person *)findByID:(int)ID;
//总人数
+(int) count;
//更改人物信息
+(BOOL) updateName:(NSString *)name del:(NSString *)del age:(int)age whereID:(int)ID;
//增加人物
+(BOOL) addName:(NSString *)name del:(NSString *)del age:(int)age;
//删除人物
+(BOOL) deletewhereID:(int)ID;


//查询所有人
+(NSMutableArray *)findAll
{
    //打开数据库
    sqlite3 * db=[Link openDB];
    //陈述
    sqlite3_stmt * stmt=nil;
    //准备SQL语句
    int result=sqlite3_prepare_v2(db, "select * from student", -1, &stmt, nil);
    if (result==SQLITE_OK) {
        NSMutableArray * allObject=[[NSMutableArray alloc] init];
        //单步执行语句
        while (SQLITE_ROW==sqlite3_step(stmt)) {
            int ID=sqlite3_column_int(stmt, 0);//第一字段
            const unsigned char * name=sqlite3_column_text(stmt, 1);//第二字段
            const unsigned char * del=sqlite3_column_text(stmt, 2);//第三字段
            int age=sqlite3_column_int(stmt, 3);//第四字段
            Person * person=[[Person alloc] initWithName:[NSString stringWithUTF8String:(char *)name] andDel:[NSString stringWithUTF8String:(char *)del] andAge:age andID:ID];
            [allObject addObject:person];
            [person release];
        }
        //释放stmt对象
        sqlite3_finalize(stmt);
        return [allObject autorelease];
    }
    //释放stmt对象
    sqlite3_finalize(stmt);
    returnnil;
}
//按ID查找
+(Person *) findByID:(int)ID
{
    //打开数据库
    sqlite3 * db=[Link openDB];
    //陈述
    sqlite3_stmt * stmt=nil;
    //准备SQL语句
    int result=sqlite3_prepare_v2(db, "select * from student where ID=?", -1, &stmt, nil);
    if (result==SQLITE_OK) {
        //将第一个？替换为ID
        sqlite3_bind_int(stmt, 1, ID);
        while (SQLITE_ROW==sqlite3_step(stmt)) {
            const unsigned char * name=sqlite3_column_text(stmt, 1);//第二字段
            const unsigned char * del=sqlite3_column_text(stmt, 2);//第三字段
            int age=sqlite3_column_int(stmt, 3);//第四字段
            Person * person=[[Person alloc] initWithName:[NSString stringWithUTF8String:(char *)name] andDel:[NSString stringWithUTF8String:(char *)del] andAge:age andID:ID];
            sqlite3_finalize(stmt);
            return [person autorelease];
        }
    }
    sqlite3_finalize(stmt);
    returnnil;
}
//总人数
+(int) count
{
    //打开数据库
    sqlite3 * db=[Link openDB];
    //陈述
    sqlite3_stmt * stmt=nil;
    //准备SQL语句
    int result=sqlite3_prepare_v2(db, "select count (*) from student", -1, &stmt, nil);
    if (result==SQLITE_OK) {
        if (SQLITE_ROW==sqlite3_step(stmt)) {
            int count=sqlite3_column_int(stmt, 0);
            sqlite3_finalize(stmt);
            return count;
        }
    }
    sqlite3_finalize(stmt);
    return 0;
    
}
//更改人物信息
+(BOOL) updateName:(NSString *)name del:(NSString *)del age:(int)age whereID:(int)ID
{
    //打开数据库
    sqlite3 *db=[Link openDB];
    //陈述
    sqlite3_stmt * stmt=nil;
    //准备SQl语句
    int result=sqlite3_prepare_v2(db, "update student set name=?,age=?,del=? where ID=?", -1, &stmt, nil);
    if (result==SQLITE_OK) {        
        sqlite3_bind_text(stmt, 1, [name UTF8String], -1, nil);//将第一个？替换成name
        sqlite3_bind_int(stmt, 2, age);//将第二个？替换成age
        sqlite3_bind_text(stmt, 3, [del UTF8String], -1, nil);//将第三个？替换成del
        sqlite3_bind_int(stmt, 4, ID);//将第四个？替换成ID
        if(sqlite3_step(stmt)==SQLITE_DONE){
            NSLog(@"更改成功");
            sqlite3_finalize(stmt);
            return YES;
        }        
    }
    sqlite3_finalize(stmt);
    NSLog(@"更改失败");
    returnNO;
}
//增加人物
+(BOOL) addName:(NSString *)name del:(NSString *)del age:(int)age
{
    //打开数据库
    sqlite3 * db=[Link openDB];
    //陈述
    sqlite3_stmt * stmt=nil;
    //准备SQL语句
    int result=sqlite3_prepare_v2(db, "insert into student(name,del,age) values (?,?,?)", -1, &stmt, nil);
    if (result==SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, [name UTF8String], -1, nil);//将第一个？替换成name
        sqlite3_bind_int(stmt, 2, age);//将第二个？替换成age
        sqlite3_bind_text(stmt, 3, [del UTF8String], -1, nil);//将第三个？替换成del
        if (sqlite3_step(stmt)==SQLITE_DONE) {
            NSLog(@"添加成功");
            sqlite3_finalize(stmt);
            return YES;
        }
    }
    NSLog(@"添加失败");
    sqlite3_finalize(stmt);
    returnNO;
}
//删除人物
+(BOOL) deletewhereID:(int)ID
{
    //打开数据库
    sqlite3 * db=[Link openDB];
    //陈述
    sqlite3_stmt * stmt=nil;
    //准备SQL语句
    int result=sqlite3_prepare_v2(db, "delete from student where ID=?", -1, &stmt, nil);
    if (result==SQLITE_OK) {
        //将第一个？替换为ID
        sqlite3_bind_int(stmt, 1, ID);
        if (sqlite3_step(stmt)==SQLITE_DONE) {
            NSLog(@"删除成功");
            sqlite3_finalize(stmt);
            return YES;
        }
    }
    NSLog(@"删除失败");
    sqlite3_finalize(stmt);
    returnNO;
}


-----------------------------------------------------------------------------------------------------------------------