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 openDB];
}
}
return dataBase;
}
//創(chuàng)建數據庫對象
static sqlite3 *db = nil;
//打開數據庫
-(void)openDB{
//如果數據庫已經打開,則不需要執(zhí)行后面的操作
if (db != nil) {
return;
}
//創(chuàng)建保存數據庫的路徑
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
documentPath = [documentPath stringByAppendingString:@"/藍歐.sqlite"];
//轉碼 打開數據庫(如果該數據庫存在 則直接打開 否則,自動創(chuàng)建一個再打開)
int result = sqlite3_open([documentPath UTF8String], &db);
NSLog(@"%@",documentPath);
if (result == SQLITE_OK) {
NSLog(@"數據庫成功打開");
//建表
//準備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);";
//執(zhí)行sql語句
sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
}else{
NSLog(@"數據庫打開失敗%d",result);
}
}
//關閉數據庫
-(void)closeDB{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"數據庫關閉成功");
//關閉數據庫的時候,將db置為空,是因為打開數據庫的時候,我們需要使用nil做判斷
db = nil;
}else{
NSLog(@"數據庫關閉失敗:%d",result);
}
}
//添加
-(void)insertStudent:(Student *)stu{
//1.打開數據庫
[self openDB];
//2.創(chuàng)建跟隨指針
sqlite3_stmt *stmt = nil;
//3.準備sql語句
NSString *sql = @"INSERT INTO Class43 (Stu_ID,name,gender,age)VALUES (?,?,?,?)";
//4.驗證sql語句的正確性
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
//5.
if (result == SQLITE_OK) {
NSLog(@"數據庫添加成功");
//一旦sql語句沒有問題就要開始綁定數據,替換 ?
//(1)跟隨指針 (2)問號的順序(從1開始) (3)要綁定的值
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.單步執(zhí)行
sqlite3_step(stmt);
}else{
NSLog(@"數據庫添加失敗:%d",result);
}
//7.釋放跟隨指針占用的內存
sqlite3_finalize(stmt);
}
//刪除
-(void)deleteStudent:(int)Stu_ID{
//1.
[self openDB];
//2
sqlite3_stmt *stmt = nil;
//3.
NSString *str = @"DELETE FROM Class43 WHERE Stu_ID = ?";
//4.
int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
//5.
if (result == SQLITE_OK){
NSLog(@"數據刪除成功");
//開始綁定
sqlite3_bind_int(stmt, 1, Stu_ID);
//6.執(zhí)行
sqlite3_step(stmt);
}else{
NSLog(@"數據刪除失敗:%d",result);
}
//7.
sqlite3_finalize(stmt);
}
//修改
-(void)upDataStudentWithGender:(NSString *)gender
Stu_ID:(int)Stu_ID{
[self openDB];
sqlite3_stmt *stmt = nil;
NSString *str = @"UPDATE Class43 SET name = ? WHERE Stu_ID = ?";
int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"修改成功");
//開始綁定
sqlite3_bind_text(stmt, 1, [gender UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 2, Stu_ID);
//執(zhí)行
sqlite3_step(stmt);
}else{
NSLog(@"修改失敗");
}
//
sqlite3_finalize(stmt);
}
//查詢所有
-(NSArray *)selectAllowStudents{
[self openDB];
sqlite3_stmt *stmt = nil;
NSString *str = @"SELECT *FROM Class43";
int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"查詢成功");
//創(chuàng)建可編數組,用來存放查詢到的學生
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, 2)];
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_step(stmt);
return array;
}else{
NSLog(@"查詢失敗%d",result);
}
//釋放空間
sqlite3_finalize(stmt);
return nil;
}
//查詢單個學生
-(Student *)selectStudentWithID:(int)Stu_ID{
//1.打開數據庫
[self openDB];
//2.跟隨指針
sqlite3_stmt *stmt = nil;
//3準備sql語句
NSString *str = @"SELECT *FROM Class43 WHERE Stu_ID = ?";
//4驗證
int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
if (result == SQLITE_OK) {
NSLog(@"查詢成功");
//5.綁定
sqlite3_bind_int(stmt, 1, Stu_ID);
//6.執(zhí)行
Student *stu = [Student new];
while (sqlite3_step(stmt) == SQLITE_ROW) {
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, 2)];
int age = sqlite3_column_int(stmt, 3);
stu.Stu_ID = stu_ID;
stu.name = name;
stu.gender = gender;
stu.age = age;
}
//釋放
sqlite3_step(stmt);
return stu;
}else{
NSLog(@"數據庫查詢單個學生失敗 %d",result);
}
//釋放內存
sqlite3_finalize(stmt);
return nil;
}
@end
#import "ViewController.h"
#import "DataBase.h"
#import "Student.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
DataBase *db = [DataBase shareDatabase];
// [db closeDB];
// Do any additional setup after loading the view, typically from a nib.
}
- (IBAction)addStudent:(UIButton *)sender {
NSLog(@"增加學生");
Student *stu = [Student new];
stu.Stu_ID = 1;
stu.name = @"昭君";
stu.gender = @"男";
stu.age = 18;
Student *stu1 = [[Student alloc]initWithName:@"大隊長" age:25 gender:@"女" Stu_ID:2];
Student *stu2 = [[Student alloc]initWithName:@"愛君" age:18 gender:@"女" Stu_ID:3];
Student *stu3 = [[Student alloc]initWithName:@"飽飽" age:24 gender:@"男" Stu_ID:21];
Student *stu4 = [[Student alloc]initWithName:@"金金" age:25 gender:@"女" Stu_ID:25];
DataBase *db = [DataBase shareDatabase];
[db insertStudent:stu];
[db insertStudent:stu1];
[db insertStudent:stu2];
[db insertStudent:stu3];
[db insertStudent:stu4];
}
- (IBAction)deletStudent:(UIButton *)sender {
NSLog(@"刪除學生");
// Student *stu = [Student new];
// stu.Stu_ID = 1;
DataBase *db = [DataBase shareDatabase];
[db deleteStudent:1];
}
- (IBAction)findStudent:(UIButton *)sender {
NSLog(@"查詢學生");
DataBase *db = [DataBase shareDatabase];
NSArray *array = [db selectAllowStudents];
NSLog(@"%@",array);
}
- (IBAction)findOneStudent:(UIButton *)sender {
NSLog(@"查詢單個學生");
DataBase *db = [DataBase shareDatabase];
Student *stu = [db selectStudentWithID:2];
NSLog(@"%@",stu);
}
- (IBAction)UpDataStudent:(UIButton *)sender {
NSLog(@"修改學生");
DataBase *db = [DataBase shareDatabase];
[db upDataStudentWithGender:@"齊天大圣" Stu_ID:2];
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
