数据库的基本操作

<span style="font-size:24px;">#import "StudentManager.h"
#import <sqlite3.h>
#import "StudentModel.h"


//单例 全局变量，默认为空
static StudentManager * manager = nil;

//定义数据库指针对象
static sqlite3 *dbPoint =nil;

@implementation StudentManager


//创建一个单例(内存永远唯一，不能释放)，   不以类名开头          判断是否唯一
+ (StudentManager *)shareManager
{
    //声明一个GCD全局变量
    static dispatch_once_t onceToken;
    
    
    //块语法中得内容只执行一次
    dispatch_once(&onceToken, ^{
        //保证内存唯一
        if (manager == nil) {
            manager = [[StudentManager alloc]init];
            
        }
    });
    return manager;
    
    //全是单例
//    [UIApplication sharedApplication];
//    [NSUserDefaults standardUserDefaults];
//    [NSNotificationCenter defaultCenter];
    
}




                                //（数据库名字）
+ (NSString *)documentsWithName:(NSString *)name
{
    //获得documentsWithName路径
    NSString * doc=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    //拼接
    NSString * path=[NSString stringWithFormat:@"%@/%@",doc,name];
    return path;
}



//  1 创建数据库并打开文件
-(void)openWithTable:(NSString *)tableName
{
    self.tablename =tableName;
    
    //获得的路径
    NSString * path=[StudentManager documentsWithName:@"Student.sqlite"];
    //打开数据库并获得数据库的对象
    
    
    //int类型接收  创建数据库过的指针对象              //(数据库对象)
    int result = sqlite3_open([path UTF8String], &dbPoint);
    //判断能不能打开
    
    NSLog(@"%@",path);
     //如果没有创建
    if (result != SQLITE_OK ) {
        UIAlertView * alert =[[UIAlertView alloc] initWithTitle:@"提示" message:@"数据库打失败" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        [alert show];
        [alert release];
        
        
    }
    [self createTable:tableName];
}

//关闭
-(void)close
{
    sqlite3_close(dbPoint);
}

// 2 创建表 增加
- (void)createTable:(NSString *)tableName
{
                                                     //创建表
    NSString * sqlStr = [NSString stringWithFormat:@"create table %@ (stuid int,stuname text,stuscore float)",tableName];
             //执行当前的sql语句            (sql语句转换成UTf8)
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        UIAlertView * alert = [[UIAlertView alloc]initWithTitle:@"提示" message:@"表创建成功" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        [alert show];
        [alert release];
    }
}
//插入
- (void)inserWithModel:(StudentModel *)model
{
    NSString * sqlStr = [NSString stringWithFormat:@"insert into %@ values(%d,'%@',%f)",self.tablename,model.stuId,model.stuName,model.stuscore];
    
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        UIAlertView * alert=[[UIAlertView alloc]initWithTitle:@"提示" message:@"添加成功" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        [alert show];
        [alert release];
    }
    
}
//删除
- (void)deleteWithModel:(StudentModel *)model
{
    NSString * sqlStr=[NSString stringWithFormat:@"delete from '%@' where stuId=%d",self.tablename,model.stuId];
    
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        UIAlertView * alert=[[UIAlertView alloc]initWithTitle:@"提示" message:@"删除成功" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        [alert show];
        [alert release];
    }
}
//修改(更新)
-(void)updateWithModel:(StudentModel *)model
{
    NSString *sqlStr=[NSString stringWithFormat:@"update '%@' set stuscore = %g , stuname = '%@' where stuid = %d",self.tablename , model.stuscore , model.stuName , model.stuId ];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        UIAlertView * alert=[[UIAlertView alloc]initWithTitle:@"提示" message:@"修改成功" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];
        [alert show];
        [alert release];
    }
}

                    //分数查询
-(NSArray *)selectStuWithScore:(CGFloat)score
{
    NSString * sqlStr = [NSString stringWithFormat:@"select * from %@ where stuscore >= %f",self.tablename,score];
    //数据库对象的替身
    sqlite3_stmt * stmt = nil;
                                                                //(-1 SQL语句长度)
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String],-1 ,&stmt, NULL); //根据sql语句查询的结果放在stmt里
    NSMutableArray * modelArray=[NSMutableArray array];
    
    
    if (result ==SQLITE_OK) {
        //查找是否有下一行数据，并且判断，如果有了，取出来放到数组中
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //让一个变量指向它的结果
            int stuid = sqlite3_column_int(stmt, 0);//0  id位子
            //取name
            const unsigned char * stuname=sqlite3_column_text(stmt, 1); // 名字位子
            float stucore = sqlite3_column_double(stmt, 2);//   分数
            //存储
            StudentModel * model=[[StudentModel alloc] init];
            model.stuId= stuid;
            model.stuName= [NSString stringWithUTF8String:(const char *)stuname];
            model.stuscore= stucore;
            
            
            [modelArray addObject:model];
            [model release];
            
        }
    }
    //将替身清空
    sqlite3_finalize(stmt);
    return modelArray;
}
@end
</span>