創(chuàng)建DataBaseHandle繼承NSObject

在DataBaseHandle.h中編寫(xiě)方法

+ (DataBaseHandle *)sharedDataBaswHandle;

//打開(kāi)數(shù)據(jù)庫(kù)

- (void)openDB;

//關(guān)閉數(shù)據(jù)庫(kù);

- (void)closeDB;

//創(chuàng)建表

- (void)createTable;

//插入一條數(shù)據(jù)

- (void)insertName:(NSString *)name

gender:(NSString *)gender

age:(NSInteger)age;

//通過(guò)UID更新一個(gè)數(shù)據(jù)

-(void)updataWithUID:(NSInteger )uid;

//通過(guò)UID刪除一個(gè)數(shù)據(jù)

-(void)deleteWithUID:(NSInteger)uid;

//搜索全部

-(void)searchAll;

//根據(jù)name查詢(xún)一條數(shù)據(jù)

-(void)seaechWithName:(NSString *)name;

在DataBaseHandle.m中

@interface DataBaseHandle()

//sqlite的頭文件#import@interface DataBaseHandle()

//documents文件夾下的一個(gè)叫做person.sqlite;

@property (nonatomic,copy) NSString *dbPath;

@end

static DataBaseHandle *dataBase = nil;

+ (DataBaseHandle *)sharedDataBaswHandle{

if (dataBase == nil) {

dataBase = [[DataBaseHandle alloc]init];

}

return dataBase;

}

//懶加載:重寫(xiě)了屬性的getter方法

- (NSString *) dbPath{

if (_dbPath == nil) {

NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];

_dbPath = [document stringByAppendingPathComponent:@"person.sqlite"];

}

return _dbPath;

}

//初始化一個(gè)數(shù)據(jù)庫(kù)

static sqlite3 *db = nil;

//打開(kāi)數(shù)據(jù)庫(kù)

- (void)openDB{

//打開(kāi)數(shù)據(jù)庫(kù)函數(shù)

//在數(shù)據(jù)庫(kù)里面 所有的字符串都要變成utf-8的編碼格式

int result = sqlite3_open(self.dbPath.UTF8String, &db);

if (result == SQLITE_OK) {

NSLog(@"打開(kāi)成功");

}else{

NSLog(@"打開(kāi)失敗");

}

}

//關(guān)閉數(shù)據(jù)庫(kù);

- (void)closeDB{

int result = sqlite3_close(db);

if (result == SQLITE_OK) {

NSLog(@"關(guān)閉成功");

}else{

NSLog(@"關(guān)閉失敗");

}

}

//創(chuàng)建表

- (void)createTable{

//創(chuàng)建一個(gè)person表 要求字段 UID integer? 主鍵 自增 name text,? gender text, age integer

NSString *createString = @"create table if not exists person (uid integer primary key autoincrement not null, name text, gender text, age integer)";

//第一個(gè)參數(shù): 數(shù)據(jù)庫(kù)

//第二個(gè)參數(shù): sql語(yǔ)句.要使用utf-8的格式

//第三個(gè)參數(shù): 結(jié)果的回調(diào)函數(shù)

//第四個(gè)參數(shù): 回調(diào)函數(shù)的參數(shù)

//第五個(gè)參數(shù): 錯(cuò)誤信息

int result = sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL);

//判斷是否創(chuàng)建成功

if (result == SQLITE_OK ) {

NSLog(@"創(chuàng)建成功");

}else{

NSLog(@"創(chuàng)建不成功");

NSLog(@"%d",result);

}

//打印數(shù)據(jù)庫(kù)地址

NSLog(@"_dbpath == %@",_dbPath);

}

//插入一條數(shù)據(jù)

- (void) insertName:(NSString *)name gender:(NSString *)gender age:(NSInteger)age{

//插入數(shù)據(jù)的sql語(yǔ)句,數(shù)據(jù)不確定,所以在value里面使用 ? 代替,之后向里面綁定

NSString *insertString = @"insert into person (name, gender, age) values (?, ?, ?)";

//sqlite的伴隨指針

sqlite3_stmt *stmt = nil;

//預(yù)執(zhí)行sql語(yǔ)句

//第一個(gè)參數(shù): 數(shù)據(jù)庫(kù)

//第二個(gè)參數(shù): sql語(yǔ)句

//第三個(gè)參數(shù): 如果為正, 例如 :1 表示再去參數(shù)的時(shí)候,只取一個(gè)字節(jié);使用負(fù)數(shù)表示取值取到碰到結(jié)束符號(hào)('\000','u000'),

//第四個(gè)參數(shù): 伴隨指針.會(huì)伴隨著數(shù)據(jù)庫(kù)的操作,獲取值或者綁定值

//第五個(gè)參數(shù): 取值的時(shí)候如果取得不全,那么剩下的都存在這里面

int result = sqlite3_prepare(db, insertString.UTF8String, -1, &stmt, NULL);

//如果預(yù)執(zhí)行成功的話(huà),那么就要往里面放數(shù)據(jù)了

if (result == SQLITE_OK) {

//向預(yù)執(zhí)行的sql語(yǔ)句里面插入?yún)?shù) (取代 '?'的位置)

//第一個(gè)參數(shù) :伴隨著指針

//第二個(gè)參數(shù): '?'的位置,從1開(kāi)始

//第三個(gè)參數(shù): 插入的數(shù)據(jù)

//第四個(gè)參數(shù): 和上面的-1是一樣的

//第五個(gè)參數(shù): 回調(diào)函數(shù)

sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);

sqlite3_bind_text(stmt, 2, gender.UTF8String, -1, NULL);

sqlite3_bind_int64(stmt, 3, age);

//sql語(yǔ)句已經(jīng)全了

//執(zhí)行伴隨指針,如果為SQLITE_DONE代表執(zhí)行成功,并且成功的插入數(shù)據(jù)

if (sqlite3_step(stmt) == SQLITE_DONE) {

NSLog(@"插入成功");

}

}

//一定要記得釋放掉伴隨指針

sqlite3_finalize(stmt);

}

//更新數(shù)據(jù)

- (void)updataWithUID:(NSInteger)uid{

NSString *updateString = @"update person set name = '掃軍' where uid = ?";

//伴隨指針

sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare(db, updateString.UTF8String, -1, &stmt, NULL);

if (result == SQLITE_OK) {

sqlite3_bind_int64(stmt, 1, uid);

if (sqlite3_step(stmt) == SQLITE_DONE) {

NSLog(@"修改成功");

}else{

NSLog(@"修改失敗");

}

}

sqlite3_finalize(stmt);

}

//刪除簡(jiǎn)單的操作方式

- (void)deleteWithUID:(NSInteger)uid{

NSString *deleyaString = [NSString stringWithFormat:@"delete from person where uid = %ld",uid];

int result = sqlite3_exec(db, deleyaString.UTF8String, NULL, NULL, NULL);

if (result == SQLITE_OK) {

NSLog(@"刪除成功");

}else{

NSLog(@"刪除失敗");

}

}

//查找

- (void)searchAll{

NSString *searchString = @"select * from person";

sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare(db, searchString.UTF8String, -1, &stmt, NULL);

if (result == SQLITE_OK) {

//sqlite3_step(stmt) == SQLITE_ROW? 的時(shí)候,代表還有下一條數(shù)據(jù)

while (sqlite3_step(stmt) == SQLITE_ROW) {

//第二個(gè)參數(shù): 表示參數(shù)的位置 , 從0 開(kāi)始

int uid = sqlite3_column_int(stmt, 0);

NSLog(@"%d",uid);

NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];

NSLog( @"%@",name);

NSString *gender = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)];

NSLog(@"%@",gender);

int age = sqlite3_column_int(stmt, 3);

NSLog(@"%d",age);

}

}

sqlite3_finalize(stmt);

}