今天總結(jié)下數(shù)據(jù)庫(kù)的基本使用方法:
iOS使用的數(shù)據(jù)庫(kù)一般就是sqlite3,在使用該數(shù)據(jù)庫(kù)前一定要先導(dǎo)入數(shù)據(jù)庫(kù)框架,否則會(huì)出錯(cuò),接下來(lái)引入頭文件#import
在工程里創(chuàng)建一個(gè)Model類Student,一個(gè)數(shù)據(jù)庫(kù)工具類DataBaseTool
在Student.h中定義幾條屬性:
#import
@interface
Student : NSObject
@property(nonatomic,copy)NSString
*name;
@property(nonatomic,copy)NSString
*hobby;
@property(nonatomic,assign)NSInteger
age;
@end
DataBaseTool.h中對(duì)數(shù)據(jù)庫(kù)操作方法的聲明:
#import
#import
#import "Student.h"
@interface DataBaseTool : NSObject
{
//用來(lái)保存數(shù)據(jù)庫(kù)對(duì)象的地址
sqlite3 *dbPoint;
}
//為了保證當(dāng)前數(shù)據(jù)庫(kù)在工程里是唯一的,我們用單例的方式創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)工具對(duì)象
+ (dataBaseTool *)shareDataBaseTool;
//打開(kāi)數(shù)據(jù)庫(kù)
- (void)openDB;
//給數(shù)據(jù)庫(kù)創(chuàng)建張表格,table
- (void)createTable;
//插入一個(gè)學(xué)生信息
- (void)insertStu:(Student *)stu;
//更新一個(gè)學(xué)生信息
- (void)updateStu:(Student *)stu;
//刪除操作
- (void)deletedateStu:(Student
*)stu;
//查詢操作
- (NSMutableArray *)selectAllStu;
//關(guān)閉數(shù)據(jù)庫(kù)
- (void)closeDB;
@end
DataBaseTool.m中實(shí)現(xiàn)方法:
#import "dataBaseTool.h"
@implementation dataBaseTool
+ (dataBaseTool *)shareDataBaseTool{
static dataBaseTool *tool;
static dispatch_once_t oneToken;
dispatch_once(&oneToken, ^{
tool=[[dataBaseTool alloc]
init];
});
return tool;
}
- (void)openDB{
//數(shù)據(jù)庫(kù)文件也保存在沙盒的documents文件里,所以先找沙盒路徑
NSArray
*sandBox=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES);
NSString
*sandBoxPath=sandBox[0];
//拼接文件路徑,如果系統(tǒng)根據(jù)這個(gè)文件路徑查找的時(shí)候有對(duì)應(yīng)文件則直接打開(kāi)數(shù)據(jù)庫(kù),如果沒(méi)有則會(huì)創(chuàng)建一個(gè)相應(yīng)的數(shù)據(jù)庫(kù)
NSString *documentPath=[sandBoxPath
stringByAppendingPathComponent:@"Student.sqlite"];
int result=sqlite3_open([documentPath
UTF8String], &dbPoint);
if (result==SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)打開(kāi)成功");
NSLog(@"%@",documentPath);
}else{
NSLog(@"數(shù)據(jù)庫(kù)打開(kāi)失敗");
}
}
- (void)createTable{
//primary key
是主鍵的意思,主健在當(dāng)前表里數(shù)據(jù)是唯一的,不能重復(fù),可以唯一標(biāo)識(shí)一條數(shù)據(jù),一般是整數(shù)
//autoincrement自增,為了讓主鍵不重復(fù),會(huì)讓主鍵采用自增的方式
//if not exists
如果沒(méi)有表才會(huì)創(chuàng)建,防止重復(fù)創(chuàng)建覆蓋之前數(shù)據(jù)
//數(shù)據(jù)庫(kù)問(wèn)題90%是sql語(yǔ)句問(wèn)題,所以先保證語(yǔ)句沒(méi)問(wèn)題,再放到工程里使用
NSString *sqlStr=@" create table if not
exists stu(number integer primary key autoincrement,name text,age integer,hobby
text)";
//執(zhí)行這條sql語(yǔ)句
int result=sqlite3_exec(dbPoint, [sqlStr
UTF8String], nil, nil, nil);
if (result==SQLITE_OK) {
NSLog(@"表創(chuàng)建成功");
}else{
NSLog(@"表創(chuàng)建失敗");
}
}
- (void)insertStu:(Student *)stu{
NSString *sqlStr=[NSString
stringWithFormat:@"insert into stu (name,age,hobby) values
('%@','%ld','%@')",stu.name,stu.age,stu.hobby
];
//執(zhí)行sql語(yǔ)句
int result=sqlite3_exec(dbPoint, [sqlStr
UTF8String], nil, nil, nil);
if (result==SQLITE_OK) {
NSLog(@"添加學(xué)生成功");
}else {
NSLog(@"添加學(xué)生失敗");
}
}
- (void)updateStu:(Student *)stu{
NSString *sqlStr= [NSString
stringWithFormat:@"update stu set hobby='%@',age=%ld where
name='%@'",stu.hobby,stu.age,stu.name];
//執(zhí)行sql語(yǔ)句
int result=sqlite3_exec(dbPoint, [sqlStr
UTF8String], nil, nil, nil);
if (result==SQLITE_OK) {
NSLog(@"更新成功");
}else{
NSLog(@"更新失敗");
NSLog(@"%d",result);
}
}
- (void)deletedateStu:(Student
*)stu{
NSString *sqlStr=[NSString
stringWithFormat:@"delete from stu where name='%@'",stu.name];
//執(zhí)行sql語(yǔ)句
int result=sqlite3_exec(dbPoint, [sqlStr
UTF8String], nil, nil, nil);
if (result==SQLITE_OK) {
NSLog(@"刪除成功");
}else{
NSLog(@"刪除失敗");
}
}
- (NSMutableArray *)selectAllStu{
//查詢邏輯
//1.先從本地的數(shù)據(jù)庫(kù)中讀取某張表里的所有數(shù)據(jù)
//2.然后逐條進(jìn)行讀取,對(duì)model進(jìn)行賦值
//3.把已經(jīng)賦值好得model放到數(shù)組中,并且返回
NSString *sqlStr=@"select * from
stu";
//在語(yǔ)句里*是通配符的意思,通過(guò)一個(gè)*相當(dāng)于代替了表里的所有的字段名
//接下來(lái)需要定義一個(gè)跟隨指針,它用來(lái)遍歷數(shù)據(jù)庫(kù)表中的每行數(shù)據(jù)
//第三個(gè)參數(shù):查詢語(yǔ)句字?jǐn)?shù)限制,-1是沒(méi)有限制
sqlite3_stmt *stmt=nil;
int result=sqlite3_prepare_v2(dbPoint,
[sqlStr UTF8String], -1, &stmt, nil);
//這個(gè)方法相當(dāng)于把數(shù)據(jù)庫(kù)和跟隨指針關(guān)聯(lián),一同完成查詢功能
//初始化一個(gè)用來(lái)裝學(xué)生的數(shù)組
NSMutableArray *stuArr=[NSMutableArray
array];
if (result==SQLITE_OK) {
NSLog(@"查詢成功");
//開(kāi)始遍歷查詢數(shù)據(jù)庫(kù)的每一行數(shù)據(jù)
while (sqlite3_step(stmt)==SQLITE_ROW)
{
//讓跟隨指針進(jìn)行遍歷查詢,如果沒(méi)有行,才會(huì)停止循環(huán)
//滿足條件,則逐列的讀取內(nèi)容
//第二個(gè)參數(shù)表示當(dāng)前這列數(shù)據(jù)在表的第幾列
const unsigned char
*name=sqlite3_column_text(stmt, 1);
int age=sqlite3_column_int(stmt,
2);
const unsigned char
*hobby=sqlite3_column_text(stmt,3);
//把列里的數(shù)據(jù)再進(jìn)行類型的轉(zhuǎn)換
NSInteger stuAge=age;
NSString *stuName=[NSString
stringWithUTF8String:(const char *)name];
NSString *stuHobby=[NSString
stringWithUTF8String:(const char *)hobby];
//給對(duì)象賦值,然后把對(duì)象放到數(shù)組里
Student *stu=[[Student alloc]
init];
stu.name=stuName;
stu.hobby=stuHobby;
stu.age=stuAge;
[stuArr addObject:stu];
[stu release];
}
}else{
NSLog(@"查詢失敗");
NSLog(@"%d",result);
}
return stuArr;
}
- (void)closeDB{
int
result=sqlite3_close(dbPoint);
if (result==SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉成功");
// NSLog(@"%@",documentPath);
}else{
NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉失敗");
}
}
@end
