IOS数据库

一.环境配置

在Frameworks中添加系统数据库类

二.创建单例类

StudentManager.h

@property (nonatomic,retain) NSString * tableName;

+ (StudentManager *)shareManager;
+ (NSString *)documentWithName:(NSString *)name;

- (void)openWithTable:(NSString *)tableName;
- (void)close;
- (void)createTable:(NSString *)tableName;
- (void)insert:(Student *)stu;
- (void)deleteWithId:(NSInteger)stuid;
- (void)updateWithModel:(Student *)model;
- (NSArray *)selectStuWithScore:(CGFloat)sscore;

StudentManager.m

// StudentManager是单例类

//[UIDevice currentDevice];
//[UIApplication sharedApplication];
//[NSUserDefaults standardUserDefaults];
//[NSNotificationCenter defaultCenter];


// 单例全局变量,默认为空
static StudentManager * manager = nil;
// 定义数据库指针对象
static sqlite3 * dbpoint = nil;

@implementation StudentManager

// 创建一个单例,// 单例两大特性:1.内存永远唯一 2.不能释放

+ (StudentManager *)shareManager
{
// 声明一个GCD全局变量
static dispatch_once_t onceToken;
// (单例安全)块语法中的内容只执行一次,保证内存唯一
dispatch_once(&onceToken, ^{
if (manager == nil) {

manager = [[StudentManager alloc] init];
}
});
return manager;

}

#pragma mark_
#pragma mark 创建并打开数据库文件
// 1.创建并打开数据库文件

- (void)openWithTable:(NSString *)tableName
{
self.tableName = tableName;
NSString * path = [StudentManager documentWithName:@"Student.sqlite"];
// 打印路径,查看是否已经创建成功(沙盒里存在否)
NSLog(@"path == %@",path);

// 打开数据库并获得数据库对象(没有数据库的话就创建,有的话就打开)
int result = sqlite3_open([path UTF8String], &dbpoint);
// SQLITE_OK代表打开成功
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);
}

#pragma mark_
#pragma mark 创建表
// 创建表
- (void)createTable:(NSString *)tableName
{
NSString * sqlStr = [NSString stringWithFormat:@"create table %@ (stuid int,stuname text,score float)",tableName];
// 第三个参数是函数指针,用到函回调,返回值类型为int
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];
[alert show];
[alert release];
}
}

#pragma mark_
#pragma mark 插入
- (void)insert:(Student *)stu
{
NSString * sqlStr = [NSString stringWithFormat:@"insert into %@ values(%d,'%@',%f)",self.tableName,stu.stuid,stu.stuName,stu.score];

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];
[alert show];
[alert release];
}

}

#pragma mark_
#pragma mark 删除
- (void)deleteWithId:(NSInteger)stuid
{
NSString *sqlStr = [NSString stringWithFormat:@"delete from %@ where stuid = %d",self.tableName,stuid];
int result = sqlite3_exec(dbpoint, [sqlStr UTF8String], NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}

}

#pragma mark_
#pragma mark 更新
// 保证新的对象主键不为空
- (void)updateWithModel:(Student *)model
{
NSString *sqlStr = [NSString stringWithFormat:@"update %@ set score = %f where stuid = %d",self.tableName,model.score,model.stuid];
int result = sqlite3_exec(dbpoint, [sqlStr UTF8String], NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"更新成功");
}

}

#pragma mark_
#pragma mark 查找

- (NSArray *)selectStuWithScore:(CGFloat)sscore
{
NSString * sqlStr = [NSString stringWithFormat:@"select * from %@ where score >= %f",self.tableName,sscore];
// sqlite3_prepare_v2 第三个参数代表对sql语句长度的限制,-1代表无限制
sqlite3_stmt * stmt = nil; // 数据库对象的替身
int result = sqlite3_prepare_v2(dbpoint, [sqlStr UTF8String], -1, &stmt, NULL);
NSMutableArray * modelArray = [NSMutableArray array];
if (result == SQLITE_OK) {
// 遍历数据库替身,查找是否有下一行数据并判断,如果有就取出放到数组中
while (sqlite3_step(stmt) == SQLITE_ROW) {
int stuid = sqlite3_column_int(stmt, 0);
const unsigned char * stuname = sqlite3_column_text(stmt, 1);
float stuscore = sqlite3_column_double(stmt, 2);
Student * stu = [Student studentWithStuid:stuid stuName:[NSString stringWithUTF8String:(const char *)stuname] score:stuscore];
[modelArray addObject:stu];
[stu release];
}
}
// 将替身清空
sqlite3_finalize(stmt);
return modelArray;
}

// 获取Documents的路径
+ (NSString *)documentWithName:(NSString *)name
{
NSString * doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString * path = [NSString stringWithFormat:@"%@/%@",doc,name];
return path;

}