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