很多時候我們在做App的時候，需要對一些數(shù)據(jù)做緩存,在沒網(wǎng)絡(luò)支持的情況下給用戶展示一些效果症昏，這樣的需求在iOS開發(fā)中是非常常見的,qpple給我們提供了一個非常好用的數(shù)據(jù)庫:SQLite,它是一個輕量級關(guān)系數(shù)據(jù)庫,最初的設(shè)計目標(biāo)是用于嵌入式系統(tǒng),它占用資源非常少.在iOS中,只需要加入li’blibsqlite3.0依賴以及引入sqlite3.h頭文件即可.
SQLite是無類型的數(shù)據(jù)庫,可以保存任何類型的數(shù)據(jù),對于SQLite來說對字段不指定類型是完全有效的，學(xué)過數(shù)據(jù)庫的人都知道赠橙，存儲在數(shù)據(jù)庫中的每一條數(shù)據(jù)都必須要有PrimaryKey,這里設(shè)計到一個非常重要的概念:主鍵
數(shù)據(jù)表中每一條記錄都有一個主鍵,這就像我們的身份證號碼一樣,反過來說每一個主鍵對應(yīng)著一條數(shù)據(jù)記錄,所以,主鍵必須是唯一的,一般情況主鍵同時也是一個索引,所以通過主鍵查找記錄速度比較快,在關(guān)系類型庫中,一個表的主鍵可以作為另外一個表的外鍵,這樣,這兩個表之間就通過這個鍵建立了關(guān)系,主鍵一般是整數(shù)或者字符串,只要保證唯一就行,在SQLite中,主鍵如果是整型類型,該列的值可以自動增長,
所有的操作都是通過sql語句來進行操作的婶希，包括建表、數(shù)據(jù)的增刪差改蛆封、表關(guān)聯(lián)等等，至于sql語句在這里就不做介紹了,這里我們簡單的實現(xiàn)一下存儲數(shù)據(jù)并進行增刪查改操作勾栗，廢話不多說惨篱，看代碼:
- (void)sqliteAction {
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
NSString *dbPath = [documentPath stringByAppendingPathComponent:@"student.sqlite"];
int result =sqlite3_open([dbPath UTF8String], &_dataBase);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫打開成功!");
//創(chuàng)建表
char *ERROR = NULL;
//建表
NSString *sqlStr = @"create table if not exists 'student' ('number' integer primary key autoincrement not null,'name' text,'sex' text,'age' integer)";
if (sqlite3_exec(_dataBase, [sqlStr UTF8String], NULL, NULL, &ERROR) == SQLITE_OK) {
NSLog(@"創(chuàng)建表成功！");
//插入
for (int i = 0; i< 100; i ++) {
NSString *sqlStr =[NSString stringWithFormat:@"INSERT OR REPLACE INTO student(name,sex,age) VALUES('%@','%@','%d')",[NSString stringWithFormat:@"Chan%d",i],
i %2 == 0 ? @"男":@"女",
100 +i];
if (sqlite3_exec(_dataBase, [sqlStr UTF8String], NULL, NULL, &ERROR) == SQLITE_OK) {
NSLog(@"插入成功!");
}
}
//查詢
NSString *selectStr = @"SELECT * FROM student";
sqlite3_stmt *stmt;
if (sqlite3_prepare(_dataBase, [selectStr UTF8String], -1, &stmt, nil) == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW ) {
NSString *name = [NSString stringWithUTF8String:(char*) sqlite3_column_text(stmt, 1)];
NSLog(@"name:%@",name);
NSString *sex = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 2)];
NSLog(@"sex:%@",sex);
NSInteger age = sqlite3_column_int(stmt, 3);
NSLog(@"%zd",age);
/* StudentModel *model = [StudentModel new];
model.name = name;
model.sex = sex;
model.age = age;
[_dataArray addObject: model];*/
}
if (sqlite3_step(stmt) == SQLITE_DONE) {
//釋放句柄
sqlite3_finalize(stmt);
}
}
} else {
NSLog(@"創(chuàng)建表失敗!");
}
} else {
NSLog(@"數(shù)據(jù)庫打開失敗!");
sqlite3_close(_dataBase);
}
}
- (void)openSqlite {
//判斷數(shù)據(jù)庫是否為空,如果不為空說明已經(jīng)打開
if(_dataBase != nil) {
NSLog(@"數(shù)據(jù)庫已經(jīng)打開");
return;
}
//獲取文件路徑
NSString *str = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
NSString *strPath = [str stringByAppendingPathComponent:@"my.sqlite"];
NSLog(@"%@",strPath);
//打開數(shù)據(jù)庫
//如果數(shù)據(jù)庫存在就打開,如果不存在就創(chuàng)建一個再打開
int result = sqlite3_open([strPath UTF8String], &_dataBase);
//判斷
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫打開成功");
} else {
NSLog(@"數(shù)據(jù)庫打開失敗");
}
}
- (void)createTable {
NSString *sqlite = [NSString stringWithFormat:@"create table if not exists 'student' ('number' integer primary key autoincrement not null,'name' text,'sex' text,'age'integer)"];
char *error = NULL;//執(zhí)行sqlite語句失敗的時候,會把失敗的原因存儲到里面
int result = sqlite3_exec(_dataBase, [sqlite UTF8String], nil, nil, &error);
if (result == SQLITE_OK) {
NSLog(@"創(chuàng)建表成功");
} else {
NSLog(@"創(chuàng)建表失敗");
}
}
//添加數(shù)據(jù)
- (void)addStudent:(Student *)stu {
NSString *sqlite = [NSString stringWithFormat:@"insert into student(number,name,age,sex) values ('%ld','%@','%@','%ld')",stu.number,stu.name,stu.sex,stu.age];
char *error = NULL;//執(zhí)行sqlite語句失敗的時候,會把失敗的原因存儲到里面
int result = sqlite3_exec(_dataBase, [sqlite UTF8String], nil, nil, &error);
if (result == SQLITE_OK) {
NSLog(@"添加數(shù)據(jù)成功");
} else {
NSLog(@"添加數(shù)據(jù)失敗");
}
}
//刪除數(shù)據(jù)
- (void)delete:(Student*)stu {
//1.準備sqlite語句
NSString *sqlite = [NSString stringWithFormat:@"delete from student where number = '%ld'",stu.number];
//2.執(zhí)行sqlite語句
char *error = NULL;//執(zhí)行sqlite語句失敗的時候,會把失敗的原因存儲到里面
int result = sqlite3_exec(_dataBase, [sqlite UTF8String], nil, nil, &error);
if (result == SQLITE_OK) {
NSLog(@"刪除數(shù)據(jù)成功");
} else {
NSLog(@"刪除數(shù)據(jù)失敗%s",error);
}
}
//修改數(shù)據(jù)
- (void)updataWithStu:(Student *)stu {
//1.sqlite語句
NSString *sqlite = [NSString stringWithFormat:@"update student set name = '%@',sex = '%@',age = '%ld' where number = '%ld'",stu.name,stu.sex,stu.age,stu.number];
//2.執(zhí)行sqlite語句
char *error = NULL;//執(zhí)行sqlite語句失敗的時候,會把失敗的原因存儲到里面
int result = sqlite3_exec(_dataBase, [sqlite UTF8String], nil, nil, &error);
if (result == SQLITE_OK) {
NSLog(@"修改數(shù)據(jù)成功");
} else {
NSLog(@"修改數(shù)據(jù)失敗");
}
}
//查詢所有數(shù)據(jù)
- (NSMutableArray*)selectWithStu {
NSMutableArray *array = [[NSMutableArray alloc] init];
NSString *sqlite = [NSString stringWithFormat:@"select * from student"];
sqlite3_stmt *stmt = NULL;
int result = sqlite3_prepare(_dataBase, sqlite.UTF8String, -1, &stmt, NULL);//第4個參數(shù)是一次性返回所有的參數(shù),就用-1
if (result == SQLITE_OK) {
NSLog(@"查詢成功");
//4.執(zhí)行n次
while (sqlite3_step(stmt) == SQLITE_ROW) {
Student *stu = [[Student alloc] init];
//從伴隨指針獲取數(shù)據(jù),第0列
stu.number = sqlite3_column_int(stmt, 0);
//從伴隨指針獲取數(shù)據(jù),第1列
stu.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)] ;
//從伴隨指針獲取數(shù)據(jù),第2列
stu.sex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)] ;
//從伴隨指針獲取數(shù)據(jù),第3列
stu.age = sqlite3_column_int(stmt, 3);
}
} else {
NSLog(@"查詢失敗");
}
sqlite3_finalize(stmt);
return array;
}
- (void)closeSqlite {
if (sqlite3_close(_dataBase) == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫關(guān)閉成功");
} else {
NSLog(@"數(shù)據(jù)庫關(guān)閉失敗");
}
}
運行完成之后围俘，我們可以在沙盒文件系統(tǒng)里面看到創(chuàng)建的數(shù)據(jù)庫文件:
sqlite.png
sqlite Query.png
我們可以使用sqlite工具打開這個student.sqlite數(shù)據(jù)庫文件查看表結(jié)構(gòu):
SQLite_table.png
表中的數(shù)據(jù):
SQLite_data.png
