ViewController.m#
//
// ViewController.m
// SQLite數(shù)據(jù)庫(kù)
//
#import "ViewController.h"
#import "DataBase.h"
#import "Student.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// DataBase *db = [DataBase shareDatabase];
// [db closeDB];//關(guān)閉數(shù)據(jù)庫(kù)
}
- (IBAction)insert:(UIButton *)sender {
NSLog(@"添加學(xué)生");
Student *stu = [Student new];
stu.Stu_ID = 1;
stu.name = @"zhaojun";
stu.gender = @"nan";
stu.age = 19;
Student *stu1 = [[Student alloc] initWithName:@"大隊(duì)長(zhǎng)" 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(@"刪除學(xué)生");
DataBase *db = [DataBase shareDatabase];
[db deleteStudent:4];
}
- (IBAction)selectAll:(UIButton *)sender {
NSLog(@"查詢所有學(xué)生");
DataBase *db = [DataBase shareDatabase];
NSArray *array = [db selectAllStudents];
NSLog( @"%@",array);
}
- (IBAction)selectOne:(UIButton *)sender {
NSLog(@"查詢某個(gè)學(xué)生");
DataBase *db = [DataBase shareDatabase];
Student *stu = [db selectStudentWithID:1];
NSLog(@"%@",stu);
}
- (IBAction)updateStudent:(UIButton *)sender {
NSLog(@"修改學(xué)生");
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數(shù)據(jù)庫(kù)
//
#import <Foundation/Foundation.h>
@class Student;
@interface DataBase : NSObject
//創(chuàng)建單例
+(instancetype)shareDatabase;
//打開數(shù)據(jù)庫(kù)
-(void)opendDB;
//關(guān)閉數(shù)據(jù)庫(kù)
-(void)closeDB;
//添加學(xué)生
-(void)insertStudent:(Student *)stu;
//刪除學(xué)生
-(void)deleteStudent:(int )stu_ID;
//修改學(xué)生
-(void)updateStudentWithGender:(NSString *)gender andStu_ID:(int)stu_ID;
//查詢所有學(xué)生
-(NSArray *)selectAllStudents;
//查詢某個(gè)學(xué)生
-(Student *)selectStudentWithID:(int)stu_ID;
@end
DataBase.m#
//
// DataBase.m
// SQLite數(shù)據(jù)庫(kù)
//
//
#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];
//打開數(shù)據(jù)庫(kù)
[dataBase opendDB];
}
}
return dataBase;
}
//創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象
static sqlite3 *db = nil;
//打開數(shù)據(jù)庫(kù)
-(void)opendDB{
//如果數(shù)據(jù)庫(kù)已經(jīng)打開,則不需要執(zhí)行后面的操作
if (db != nil) {
return;
}
//創(chuàng)建保存數(shù)據(jù)庫(kù)的路徑
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
documentPath = [documentPath stringByAppendingString:@"/LOClass43.sqlite"];
//打開數(shù)據(jù)庫(kù) (如果該數(shù)據(jù)庫(kù)存在則直接打開,否則,自動(dòng)創(chuàng)建一個(gè)再打開)
int result = sqlite3_open([documentPath UTF8String], &db);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)成功打開");
//建表
//1.準(zhǔn)備sql語(yǔ)句
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.執(zhí)行sql語(yǔ)句
sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
} else {
NSLog(@"打開數(shù)據(jù)庫(kù)失敗 :%d",result);
}
NSLog(@"數(shù)據(jù)保存路徑 :%@",documentPath);
}
//關(guān)閉數(shù)據(jù)庫(kù)
-(void)closeDB{
int result = sqlite3_close(db);
if (result ==SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉成功");
//關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候?qū)b置為空,是因?yàn)榇蜷_數(shù)據(jù)庫(kù)的時(shí)候,我們需要使用nil做判斷
db = nil;
} else {
NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉失敗");
}
}
//添加學(xué)生
-(void)insertStudent:(Student *)stu{
//1.打開數(shù)據(jù)庫(kù)
[self opendDB];
//2.創(chuàng)建一個(gè)跟隨指針
sqlite3_stmt *stmt = nil;
//3.準(zhǔn)備sql語(yǔ)句
NSString *sql = @"INSERT INTO Class43 (Stu_ID,name,gender,age) VALUES (?,?,?,?)";
//4.驗(yàn)證aql語(yǔ)句的正確性
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);//-1代表無(wú)限大
//5.綁定
if (result ==SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)添加成功");
//一旦sql語(yǔ)句沒(méi)有問(wèn)題,就逃開始綁定語(yǔ)句,替換 ?
//第一個(gè)參數(shù) :跟隨指針
//第二個(gè)參數(shù) :問(wèn)號(hào)的順序(從1開始)
//第三個(gè)參數(shù) :要綁定的值
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(@"數(shù)據(jù)庫(kù)添加失敗 :%d",result);
}
//7.釋放跟隨指針占用的內(nèi)存
sqlite3_finalize(stmt);
}
//刪除學(xué)生
-(void)deleteStudent:(int )stu_ID{
//1.打開數(shù)據(jù)庫(kù)
[self opendDB];
//2.創(chuàng)建一個(gè)跟隨指針
sqlite3_stmt *stmt = nil;
//3.準(zhǔn)備sql語(yǔ)句
NSString *sql = @"DELETE FROM Class43 WHERE Stu_ID= ?";
//4.驗(yàn)證aql語(yǔ)句的正確性
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
//5.綁定
if (result ==SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)添加成功");
sqlite3_bind_int(stmt, 1, stu_ID);
//6.單步執(zhí)行
sqlite3_step(stmt);
} else{
NSLog(@"數(shù)據(jù)庫(kù)添加失敗 :%d",result);
}
//7.釋放跟隨指針占用的內(nèi)存
sqlite3_finalize(stmt);
}
//修改學(xué)生
-(void)updateStudentWithGender:(NSString *)gender andStu_ID:(int)stu_ID{
//1.打開數(shù)據(jù)庫(kù)
[self opendDB];
//2.創(chuàng)建一個(gè)跟隨指針
sqlite3_stmt *stmt = nil;
//3.準(zhǔn)備sql語(yǔ)句
NSString *sql = @"UPDATE Class43 SET gender = ? WHERE Stu_ID = ? ";
//4.驗(yàn)證aql語(yǔ)句的正確性
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.單步執(zhí)行
sqlite3_step(stmt);
} else {
NSLog(@"數(shù)據(jù)庫(kù)添加失敗 :%d",result);
}
//7.釋放跟隨指針占用的內(nèi)存
sqlite3_finalize(stmt);
}
//查詢所有學(xué)生
-(NSArray *)selectAllStudents{
//1.打開數(shù)據(jù)庫(kù)
[self opendDB];
//2.創(chuàng)建一個(gè)跟隨指針
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(@"查詢成功");
//創(chuàng)建可變數(shù)組,用來(lái)存儲(chǔ)查詢到的學(xué)生
NSMutableArray *array = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
//根據(jù)sql語(yǔ)句將搜索到的符合條件的值取出來(lái)
//0代表數(shù)據(jù)庫(kù)表的第一列
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);
//將取出來(lái)的信息賦值給學(xué)生模型
Student *stu = [[Student alloc] initWithName:name age:age gender:gender Stu_ID:stu_ID];
//將學(xué)生添加到可變數(shù)組里
[array addObject:stu];
}
//釋放
sqlite3_finalize(stmt);
return array;
} else {
NSLog(@"查詢失敗 :%d",result);
}
//釋放
sqlite3_finalize(stmt);
return nil;
}
//查詢某個(gè)學(xué)生
-(Student *)selectStudentWithID:(int)stu_ID{
[self opendDB];
//2.創(chuàng)建一個(gè)跟隨指針
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數(shù)據(jù)庫(kù)
//
//
#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數(shù)據(jù)庫(kù)
//
//
#import "Student.h"
@implementation Student
//容錯(cuò)處理
-(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
