UI数据库
.h
#import <Foundation/Foundation.h>
//引数据库的头文件
#import <sqlite3.h>
#import "Student.h"
@interface dataBaseHandle : NSObject
//成员变量
{
sqlite3 *dbPoint;
}
//单例
//因为工程所对应的数据库在工程里是唯一的,所以用单例来创建
+ (dataBaseHandle *)shareDataBaseHandle;
//第一个方法 打开数据库
- (void)openDB;
//给数据库新建一张表
- (void)createTable;
//给数据库中插入一个学生
- (void)insertStu:(Student *)stu;
- (void)updateStu:(Student *)stu;
//更新数据库的内容
- (void)deleteStu;
//查询所有学生的信息
- (NSMutableArray *)selectStu;
@end
#import "dataBaseHandle.h"
@implementation dataBaseHandle
+ (dataBaseHandle *)shareDataBaseHandle
{
// static dataBaseHandle *dataBase = nil;
// if (dataBase == nil) {
// dataBase = [[dataBaseHandle alloc] init];
//
// }
//另一种方法
static dataBaseHandle *dataBase = nil;
static dispatch_once_t onceTaken;
dispatch_once(&onceTaken, ^{
dataBase = [[dataBaseHandle alloc] init];
});
return dataBase;
}
//打开数据库
- (void)openDB
{
//找到沙盒里的document路径
NSArray *sandBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *sandBoxPath = sandBox[0];
//拼接一个文件路径
NSString *documentPath = [sandBoxPath stringByAppendingPathComponent:@"student.sqlite"];
//打开数据库
//如果在指定的路径下有对应的数据库,则是打开数据库,如果没有,则是创建一个数据库
//const char *filename c语言的字符串
int result = sqlite3_open([documentPath UTF8String], &dbPoint);
if (result == SQLITE_OK) {
NSLog(@"数据库成功开启");
}else{
NSLog(@"数据库开启失败");
}
NSLog(@"%@",documentPath);
}
- (void)createTable
{
//第一件事:写sql语句
NSString *sqlStr = @"create table if not exists stu(number integer primary key autoincrement,sex text,age integer,name text)";
//第二件事:执行SQL语句
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"创建表成功");
}else{
NSLog(@"创建表失败");
}
}
- (void)insertStu:(Student *)stu
{
NSString *sqlStr = [NSString stringWithFormat:@"insert into stu(name,age,sex) values ('%@','%ld','%@')",stu.name,stu.age,stu.sex];
//执行SQL语句
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"添加学生成功");
}else{
NSLog(@"失败");
}
}
- (void)updateStu:(Student *)stu
{
NSString *sqlStr = [NSString stringWithFormat:@"update stu set name = '%@',sex = '%@',age = '%ld' where number = 4",stu.name,stu.sex,stu.age];
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"更新成功");
}else{
NSLog(@"更新失败");
}
}
- (void)deleteStu
{
NSString *sqlStr = [NSString stringWithFormat:@"delete from stu where age = 20"];
//
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败");
}
}
- (NSMutableArray *)selectStu
{
NSString *sqlStr = [NSString stringWithFormat:@"select * from stu"];
//* 是通配符,表示查询的时候找到所有数据库表中所有的字符段
//查询的逻辑
//1.先从数据库中读取所有符合要求的数据
//2.然后从数据中逐条进行读取,然后把数据的内容赋值给类
//3.最后放到数组中,并且model随数组一同返回
//创建一个跟随指针,用来循环遍历数据库中的每行数据
sqlite3_stmt *stmt = nil;
//执行查询的sql
//第三个参数:查询语句的字数限制,-1是没有权限
int result = sqlite3_prepare(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
if(result == SQLITE_OK){
NSLog(@"查询成功");
//用来装学生的数组
NSMutableArray *stuArr = [NSMutableArray array];
//让跟随指针stmt开始以行为单位进行遍历,直到没有行,遍历停止
while (sqlite3_step(stmt) == SQLITE_ROW) {
int number = sqlite3_column_int(stmt, 0);
const unsigned char *sex = sqlite3_column_text(stmt, 1);
int age = sqlite3_column_int(stmt, 2);
const unsigned char *name = sqlite3_column_text(stmt, 3);
//把读出来的数据,进行类型转换
NSInteger stuAge = age;
NSString *stuName = [NSString stringWithUTF8String:(const char *)name];
NSString *stuSex = [NSString stringWithUTF8String:(const char *)sex];
//创建一个model,把数据放入model里中
Student *stu = [[Student alloc] init];
stu.name = stuName;
stu.sex = stuSex;
stu.age = stuAge;
[stuArr addObject:stu];
}
return stuArr;
}else{
NSLog(@"查询失败");
return nil;
}
}
@end