#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
0 0
