ViewController.m#
//
// ViewController.m
// SQLite数据库
//
#import "ViewController.h"
#import "DataBase.h"
#import "Student.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// DataBase *db = [DataBase shareDatabase];
// [db closeDB];//关闭数据库
}
- (IBAction)insert:(UIButton *)sender {
NSLog(@"添加学生");
Student *stu = [Student new];
stu.Stu_ID = 1;
stu.name = @"zhaojun";
stu.gender = @"nan";
stu.age = 19;
Student *stu1 = [[Student alloc] initWithName:@"大队长" age:18 gender:@"男" Stu_ID:2];
Student *stu2 = [[Student alloc] initWithName:@"aijun" age:25 gender:@"女" Stu_ID:3];
Student *stu3 = [[Student alloc] initWithName:@"dabao" age:23 gender:@"nan" Stu_ID:4];
DataBase *db = [DataBase shareDatabase];
[db insertStudent:stu];
[db insertStudent:stu1];
[db insertStudent:stu2];
[db insertStudent:stu3];
}
- (IBAction)delete:(UIButton *)sender {
NSLog(@"删除学生");
DataBase *db = [DataBase shareDatabase];
[db deleteStudent:4];
}
- (IBAction)selectAll:(UIButton *)sender {
NSLog(@"查询所有学生");
DataBase *db = [DataBase shareDatabase];
NSArray *array = [db selectAllStudents];
NSLog( @"%@",array);
}
- (IBAction)selectOne:(UIButton *)sender {
NSLog(@"查询某个学生");
DataBase *db = [DataBase shareDatabase];
Student *stu = [db selectStudentWithID:1];
NSLog(@"%@",stu);
}
- (IBAction)updateStudent:(UIButton *)sender {
NSLog(@"修改学生");
DataBase *db = [DataBase shareDatabase];
[db updateStudentWithGender:@"女" andStu_ID:2];
[db updateStudentWithGender:@"女" andStu_ID:4];
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
DataBase.h#
//
// DataBase.h
// SQLite数据库
//
#import <Foundation/Foundation.h>
@class Student;
@interface DataBase : NSObject
//创建单例
+(instancetype)shareDatabase;
//打开数据库
-(void)opendDB;
//关闭数据库
-(void)closeDB;
//添加学生
-(void)insertStudent:(Student *)stu;
//删除学生
-(void)deleteStudent:(int )stu_ID;
//修改学生
-(void)updateStudentWithGender:(NSString *)gender andStu_ID:(int)stu_ID;
//查询所有学生
-(NSArray *)selectAllStudents;
//查询某个学生
-(Student *)selectStudentWithID:(int)stu_ID;
@end
DataBase.m#
//
// DataBase.m
// SQLite数据库
//
//
#import "DataBase.h"
#import <sqlite3.h>//�
#import "Student.h"
@implementation DataBase
static DataBase *dataBase = nil;
+(instancetype)shareDatabase{
//加锁
@synchronized(self) {
if (nil == dataBase) {
dataBase = [[DataBase alloc] init];
//打开数据库
[dataBase opendDB];
}
}
return dataBase;
}
//创建数据库对象
static sqlite3 *db = nil;
//打开数据库
-(void)opendDB{
//如果数据库已经打开,则不需要执行后面的操作
if (db != nil) {
return;
}
//创建保存数据库的路径
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
documentPath = [documentPath stringByAppendingString:@"/LOClass43.sqlite"];
//打开数据库 (如果该数据库存在则直接打开,否则,自动创建一个再打开)
int result = sqlite3_open([documentPath UTF8String], &db);
if (result == SQLITE_OK) {
NSLog(@"数据库成功打开");
//建表
//1.准备sql语句
NSString *sql = @"CREATE TABLE Class43 (Stu_ID INTEGER PRIMARY KEY NOT NULL UNIQUE, name TEXT NOT NULL, gender TEXT NOT NULL DEFAULT M, age INTEGER NOT NULL);";
//2.执行sql语句
sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
} else {
NSLog(@"打开数据库失败 :%d",result);
}
NSLog(@"数据保存路径 :%@",documentPath);
}
//关闭数据库
-(void)closeDB{
int result = sqlite3_close(db);
if (result ==SQLITE_OK) {
NSLog(@"数据库关闭成功");
//关闭数据库的时候将db置为空,是因为打开数据库的时候,我们需要使用nil做判断
db = nil;
} else {
NSLog(@"数据库关闭失败");
}
}
//添加学生
-(void)insertStudent:(Student *)stu{
//1.打开数据库
[self opendDB];
//2.创建一个跟随指针
sqlite3_stmt *stmt = nil;
//3.准备sql语句
NSString *sql = @"INSERT INTO Class43 (Stu_ID,name,gender,age) VALUES (?,?,?,?)";
//4.验证aql语句的正确性
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);//-1代表无限大
//5.绑定
if (result ==SQLITE_OK) {
NSLog(@"数据库添加成功");
//一旦sql语句没有问题,就逃开始绑定语句,替换 ?
//第一个参数 :跟随指针
//第二个参数 :问号的顺序(从1开始)
//第三个参数 :要绑定的值
sqlite3_bind_int(stmt, 1, stu.Stu_ID);
sqlite3_bind_text(stmt, 2, [stu.name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 3, [stu.gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 4, stu.age);
//6.单步执行
sqlite3_step(stmt);
} else{
NSLog(@"数据库添加失败 :%d",result);
}
//7.释放跟随指针占用的内存
sqlite3_finalize(stmt);
}
//删除学生
-(void)deleteStudent:(int )stu_ID{
//1.打开数据库
[self opendDB];
//2.创建一个跟随指针
sqlite3_stmt *stmt = nil;
//3.准备sql语句
NSString *sql = @"DELETE FROM Class43 WHERE Stu_ID= ?";
//4.验证aql语句的正确性
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
//5.绑定
if (result ==SQLITE_OK) {
NSLog(@"数据库添加成功");
sqlite3_bind_int(stmt, 1, stu_ID);
//6.单步执行
sqlite3_step(stmt);
} else{
NSLog(@"数据库添加失败 :%d",result);
}
//7.释放跟随指针占用的内存
sqlite3_finalize(stmt);
}
//修改学生
-(void)updateStudentWithGender:(NSString *)gender andStu_ID:(int)stu_ID{
//1.打开数据库
[self opendDB];
//2.创建一个跟随指针
sqlite3_stmt *stmt = nil;
//3.准备sql语句
NSString *sql = @"UPDATE Class43 SET gender = ? WHERE Stu_ID = ? ";
//4.验证aql语句的正确性
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
//5.绑定
if (result ==SQLITE_OK) {
NSLog( @"修改成功");
sqlite3_bind_text(stmt, 1, [gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 2, stu_ID);
//6.单步执行
sqlite3_step(stmt);
} else {
NSLog(@"数据库添加失败 :%d",result);
}
//7.释放跟随指针占用的内存
sqlite3_finalize(stmt);
}
//查询所有学生
-(NSArray *)selectAllStudents{
//1.打开数据库
[self opendDB];
//2.创建一个跟随指针
sqlite3_stmt *stmt = nil;
//
NSString *sql = @"SELECT * FROM Class43";
//
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
//
if (result ==SQLITE_OK) {
NSLog(@"查询成功");
//创建可变数组,用来存储查询到的学生
NSMutableArray *array = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
//根据sql语句将搜索到的符合条件的值取出来
//0代表数据库表的第一列
int stu_ID = sqlite3_column_int(stmt, 0);
NSString *name = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];
NSString *gender = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 3)];
int age = sqlite3_column_int(stmt, 3);
//将取出来的信息赋值给学生模型
Student *stu = [[Student alloc] initWithName:name age:age gender:gender Stu_ID:stu_ID];
//将学生添加到可变数组里
[array addObject:stu];
}
//释放
sqlite3_finalize(stmt);
return array;
} else {
NSLog(@"查询失败 :%d",result);
}
//释放
sqlite3_finalize(stmt);
return nil;
}
//查询某个学生
-(Student *)selectStudentWithID:(int)stu_ID{
[self opendDB];
//2.创建一个跟随指针
sqlite3_stmt *stmt = nil;
NSString *sql = @"SELECT * FROM Class43 WHERE Stu_ID = ?";
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"查询成功");
sqlite3_bind_int(stmt, 1, stu_ID);
Student *stu = [Student new];
while (sqlite3_step(stmt) == SQLITE_ROW) {
int stu_IID = sqlite3_column_int(stmt, 0);
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
int age = sqlite3_column_int(stmt, 3);
stu.Stu_ID = stu_IID;
stu.name = name;
stu.gender = gender;
stu.age = age;
}
sqlite3_finalize(stmt);
return stu;
} else {
NSLog(@"失败:%d",result);
}
//释放
sqlite3_finalize(stmt);
return nil;
}
@end
Student.h#
//
// Student.h
// SQLite数据库
//
//
#import <Foundation/Foundation.h>
@interface Student : NSObject
@property(nonatomic, strong)NSString *name;
@property(nonatomic, strong)NSString *gender;
@property(nonatomic, assign)int age;
@property(nonatomic, assign)int Stu_ID;
//自定义初始化方法
-(instancetype)initWithName:(NSString *)name
age:(int)age
gender:(NSString *)gender
Stu_ID:(int)stu_ID;
@end
Student.m#
//
// Student.m
// SQLite数据库
//
//
#import "Student.h"
@implementation Student
//容错处理
-(void)setValue:(id)value forUndefinedKey:(NSString *)key{
}
//重写父类方法
- (NSString *)description
{
return [NSString stringWithFormat:@"%@", _name];
}
//自定义初始化方法
-(instancetype)initWithName:(NSString *)name
age:(int)age
gender:(NSString *)gender
Stu_ID:(int)stu_ID{
if (self = [super init]) {
_name = name;
_age = age;
_gender = gender;
_Stu_ID = stu_ID;
}
return self;
}
@end
