//
// DataBaseManager.m
// 数据库系统
//
// Created by yangtingting on 14-7-29.
// Copyright (c) 2014年 灭神科技. All rights reserved.
//
#import "DataBaseManager.h"
#import <sqlite3.h>
@implementation DataBaseManager
// 单例方法的实现
+ (DataBaseManager *)shareInstance
{
static DataBaseManager *dbManage = nil;// static在程序运行时,只初始化一次
if (dbManage == nil) {
dbManage = [[DataBaseManager alloc] init];
}
return dbManage;
}
// 打开数据库
- (void)openDB
{
NSArray *pathArr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
// 获取documents文件夾的路径
NSString *docupath = [pathArr lastObject];
// 拼接一个数据库文件的地址
NSString *path = [docupath stringByAppendingPathComponent:@"ghw.db"];
NSLog(@"%@", path);
// 打开数据库的函数
// 参数1:数据库文件的路径
// 参数2:数据库的指针地址
int result = sqlite3_open([path UTF8String]/*转换为c的字符串*/,
&dbPoint);
NSLog(@"打开数据库的结果:%d", result);//0sucess 1, sql语句有问题 SQLITE_OK
}
//关闭数据库
- (void)closeDB
{
//
int result = sqlite3_close(dbPoint);
NSLog(@"%d", result);
}
// 创建一个表
- (void)creatTable
{
NSString *sql = @"create table teacher (name text, id integer primary key, age integer)";
// 参数1:数据库指针
// 参数2:要执行的sql语句
// 参数3:回调函数
// 参数4:
int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
NSLog(@"创建结果:%d", result);
}
- (void)insertData
{
NSString *sql = @"insert into teacher values ('guo',2, '2')";
int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
NSLog(@"插入结果%d", result);
}
- (void)updata
{
NSString *sql = @"update teacher set name = '李', age = '20' where id = 2";
int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
NSLog(@"更新的结果%d", result);
}
- (void)deleteData
{
NSString *sql = @"delete from teacher where id = 2";
int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
NSLog(@"删除的结果:%d", result);
}
- (void)insertTeacher:(Teacher *)teacher
{
// 插入一条数据
NSString *sql = [NSString stringWithFormat:@"insert into teacher values ('%@', %d, %d)", teacher.name, teacher.ID, teacher.age];
int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
NSLog(@"插入结果:%d", result);
}
- (NSMutableArray *) selectAllTeacher
{
// 返回所有的teacher对象组成的数组
// 1sql 语句
NSString *sql = @"select * from teacher";
// 2创建一个数据库的替身 作用,作为一个临时的数据库,存储对底层数据库所有数据操作 和改变
sqlite3_stmt *stmt = nil;
// 3对sql语句进行语法检查
// 参数1:数据库的指针
// 参数2:sql语句
// 参数3:限制sql语句的长度(-1:不限制)
// 参数4:数据库的替身的地址
int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
NSLog(@"结果%d", result);
// 4对检查结果进行判断
if (SQLITE_OK == result) {
// 5如果sql语句没有错误, 执行sql语句
NSMutableArray *arr = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
// 6每当获取到一条数据,执行循环体
Teacher *teacher = [[Teacher alloc] init];
// 获取数据
// 参数1:替身
// 参数2:第几列
const unsigned char *namechar = sqlite3_column_text(stmt, 0);
NSString *name = [NSString stringWithUTF8String:(const char *)namechar];
teacher.name = name;
// NSLog(@"%@", name);
int ID = sqlite3_column_int(stmt, 1);
teacher.ID = ID;
teacher.age = sqlite3_column_int(stmt, 2);
[arr addObject:teacher];
[teacher release];
}
// 对数据库的操作和改变 提交到底层数据库中
// 同时销毁stmt替身
sqlite3_finalize(stmt);
return arr;
}
sqlite3_finalize(stmt);
return [NSMutableArray array];
}
@end