SQLite数据库基础

#import "StudentManager.h"

#import "Student.h"

#import //4.创建数据库并引入数据库的头文件

#define kSqlFileName @"Student.sqlite"

// 这个类之一个单例类, 类所创建的对象是唯一的

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

static sqlite3 *db = nil; //5.定义数据库指针对象(这样才能获得)

@implementation StudentManager

#pragma mark - 获取document文件夹路径

//1.获得Document的路径

+ (NSString *)documentsWithName:(NSString *)name

{   //1.获取路径

    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) lastObject];

    //2.拼接

    path = [path stringByAppendingPathComponent:name];

    //3.返回

    return path;

}

#pragma mark - 单例传值

//2.单例传值 //单例的特性:1-内存永远唯一 2-不能被释放

+ (StudentManager *)shareManager//不以类名开头方法是单例方法

{

    //----------以下都是单例

    [UIDevice currentDevice];

    [UIApplication sharedApplication];

    [NSUserDefaults standardUserDefaults];

    [NSNotificationCenter defaultCenter];

    //----------以上都是单例

    

    

    //复杂写法(练习推荐)

    static dispatch_once_t onceToken; // 声明一个GCD全局变量

    dispatch_once(&onceToken, ^{

        //单例安全

        if (manager == nil) {

            NSLog(@"数据库存在");

            //保证他的内存是唯一的

            manager = [[StudentManager alloc] init];

        }

    }); // block(块语法)中的内容只执行一次, 保证manager的内存唯一

    return manager;

    

//    //简单写法

//    if (!manager){

//        manager = [[StudentManager alloc]init];

//    

//    }

//    return manager;

}

#pragma mark - 创建并打开数据库文件

//3.创建方法

    //-----打开数据库文件

- (void)open:(NSString *)tableName

{

    //1-创建一个数据库并命名

    NSString *path = [StudentManager documentsWithName:kSqlFileName];

    //2-打开数据库并获得数据库对象 他的返回类型是int

    int result = sqlite3_open([path UTF8String], &db); // (第一个参数)数据库打开这个路径的文件, [path UTF8String]是将string转换为char, (第二个参数)获得数据库的对象

    if (result != SQLITE_OK) { // SQLITE_OK 表示0 , 打开数据库成功

        //设置提示框

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示"message:@"打开失败" delegate:self cancelButtonTitle:@"关闭" otherButtonTitles:nil,nil];

        [alert show];

    } else {

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示"message:@"打开成功" delegate:self cancelButtonTitle:@"关闭" otherButtonTitles:nil,nil];

        [alert show];

    }

    NSLog(@"%@",path);

    [self creatTable:tableName];

}

#pragma mark - 关闭数据库

    // 关闭数据库

- (void)close

{

    sqlite3_close(db); // 关闭数据库

}

#pragma mark - 创建表

    //-----创建表(因为需要不同数据所以要创建一个字符串进行命名)

- (void)creatTable:(NSString *)tableName

{

    self.tableName = [[NSString alloc] init];

    self.tableName = tableName;

    NSString *sqlStr = [NSString stringWithFormat:@"create table %@ (stuID int, stuName TEXT, stuScore float)", _tableName]; // 创建表的sql语句

    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);  // 执行上一条的sql语句创建表

    if (result == SQLITE_OK) {

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示"message:@"创建表成功" delegate:self cancelButtonTitle:@"关闭" otherButtonTitles:nil,nil];

        [alert show];

        NSLog(@"创建成功");

    }

}

#pragma mark - 增删改查

#pragma mark  增加

- (void)insertWithModel:(Student *)stu

{

    NSString *sqlStr = [NSString stringWithFormat:@"insert into %@ values (%d, '%@', %f)", self.tableName, stu.stuID, stu.stuName, stu.stuScore]; // 添加条目的sql语句

    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

    if (result == SQLITE_OK) {

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示"message:@"插入数据成功" delegate:self cancelButtonTitle:@"关闭"otherButtonTitles:nil, nil];

        [alert show];

        NSLog(@"插入成功");

    }

}

#pragma mark  删除

- (void)delete:(Student *)stu

{

    NSString * sqlStr = [NSString stringWithFormat:@"delete from %@ where stuId = %d",self.tableName,stu.stuID];

//    @"select * from tableName"

    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

    if (result == SQLITE_OK) {

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示"message:@"删除数据成功" delegate:self cancelButtonTitle:@"关闭"otherButtonTitles:nil, nil];

        [alert show];

    }

}

#pragma mark  修改

- (void)xiugai:(Student *)stu{

//@"updata from %@ set stuScore = %f where stuId = %d "

    NSString * sqlStr = [NSString stringWithFormat:@"update %@ set stuScore = %f where stuId = %d ",self.tableName,stu.stuScore,stu.stuID];

    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

    if (result == SQLITE_OK) {

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示"message:@"修改成功" delegate:self cancelButtonTitle:@"关闭" otherButtonTitles:nil,nil];

        [alert show];

    }

    

}

#pragma mark  查询

- (void)chaxun:(Student *)stu

{

    NSMutableArray * array = [NSMutableArray arrayWithCapacity:10];

    NSString * sqlStr = [NSString stringWithFormat:@"select * from %@ where stuId = %d",self.tableName,stu.stuID];

    sqlite3_stmt *statement = nil;//数据库的替身

    const char *sql = [sqlStr UTF8String];//转换类型

    int result = sqlite3_prepare_v2(db, sql, -1, &statement, NULL);//(第三个参数-1代表数组长度是无限的) (第五个参数是sq语句中那句话是不用的)

    if (result == SQLITE_OK) {//判断语句是否正确

        while (sqlite3_step(statement) == SQLITE_ROW) {//找到对应的行

            //遍历对应的列

            Student * sqList = [[Student alloc] init];

            sqList.stuID = sqlite3_column_int(statement, 0);

            sqList.stuName = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];

            sqList.stuScore = sqlite3_column_double(statement, 2);

            //添加到数组中

            [array addObject:sqList];

        }

        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示"message:@"查询成功" delegate:self cancelButtonTitle:@"关闭" otherButtonTitles:nil,nil];

        [alert show];

    }

    sqlite3_finalize(statement);//最后清空替身

}

//查询学生分数

- (NSArray *)selectStuWithScore:(CGFloat)score{

    NSString * sqlStr = [NSString stringWithFormat:@"select * from %@ where stuscore >= %f",self.tableName,score];

    sqlite3_stmt *statement = nil;

    const char *sql = [sqlStr UTF8String];

    int result = sqlite3_prepare_v2(db, sql, -1, &statement, NULL);

    NSMutableArray * arr = [NSMutableArray array];

    if (result == SQLITE_OK) {

        while (sqlite3_step(statement) == SQLITE_ROW) {

            int stuid = sqlite3_column_int(statement, 0);//取出第一列

            const unsigned char * stuname = sqlite3_column_text(statement, 1);//2

            float stuscore = sqlite3_column_double(statement, 2);//3

            Student *model = [[Student alloc] init];

            model.stuID = stuid;

            model.stuName = [NSString stringWithUTF8String:(char *)stuname];

            model.stuScore = stuscore;

            [arr addObject:model];

        }

    }

    sqlite3_finalize(statement);

    

    return arr;

    

}

 

@end