-
導(dǎo)入libsqlite3.0.tbd庫
導(dǎo)入sql庫
-
創(chuàng)建類,將后綴改為.mm(兼容C/C++)
創(chuàng)建類(Model層)
ViewController.m
#import "ViewController.h"
#import <sqlite3.h>
#import "DataBaseHandle.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
DataBaseHandle *data = [DataBaseHandle shareDataBaseHandle];
//[data getPath];
[data openDB];
[data creatTable];
// [data inserName:@"alnh" gender:@"男" age:18];
// [data inserName:@"陳楊" gender:@"女" age:18];
// [data inserName:@"高鞠" gender:@"女" age:18];
// [data deleteWithUID:1];
[data updateWithUID:1];
[data searchAll];
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
DataBaseHandle.h
#import <Foundation/Foundation.h>
@interface DataBaseHandle : NSObject
+(DataBaseHandle *)shareDataBaseHandle;
-(void)getPath;
//打開數(shù)據(jù)庫
-(void)openDB;
//關(guān)閉數(shù)據(jù)庫
-(void)closeDB;
//創(chuàng)建表
-(void)creatTable;
//插入數(shù)據(jù)
-(void)inserName:(NSString *)name gender:(NSString *)gender age:(NSInteger)age;
//通過uid更新數(shù)據(jù)
-(void)updateWithUID:(NSInteger)uid;
//通過uid去刪除數(shù)據(jù)
-(void)deleteWithUID:(NSInteger)uid;
//查找所有數(shù)據(jù)
-(void)searchAll;
//根據(jù)名字查找相關(guān)數(shù)據(jù)信息
-(void)searchWithName:(NSString *)name;
@end
DataBaseHandle.mm
#import "DataBaseHandle.h"
#import <sqlite3.h>
@interface DataBaseHandle()
//數(shù)據(jù)庫存儲路徑
@property(nonatomic,strong)NSString *dbPath;
@end
static DataBaseHandle *dataBase = nil;
@implementation DataBaseHandle
//將DataBaseHandle該類寫為單例類，方便外部使用：
+(DataBaseHandle *)shareDataBaseHandle{
if (dataBase == nil) {
dataBase = [[DataBaseHandle alloc]init];
}
return dataBase;
}
-(void)getPath{
}
//懶加載(數(shù)據(jù)庫路徑)
-(NSString *)dbPath
{
if(_dbPath == nil){
//獲取document路徑:
NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
//在document路徑下創(chuàng)建數(shù)據(jù)庫文件夾
_dbPath = [document stringByAppendingPathComponent:@"person.sqlite"];
NSLog(@"_dbPath = %@",self.dbPath);
}
return _dbPath;
}
static sqlite3 *db = nil;
//打開數(shù)據(jù)庫
-(void)openDB{
//第一個參數(shù)：數(shù)據(jù)庫的路徑
//第二個參數(shù)：代表數(shù)據(jù)庫地址
int result = sqlite3_open(self.dbPath.UTF8String, &db);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫打開成功");
} else {
NSLog(@"數(shù)據(jù)庫打開失敗");
}
}
//關(guān)閉數(shù)據(jù)庫
-(void)closeDB{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫關(guān)閉成功");
} else {
NSLog(@"數(shù)據(jù)庫關(guān)閉失敗");
}
}
//創(chuàng)建表
-(void)creatTable{
//創(chuàng)建一個person表，字段 ID integer類型 主鍵 自增不能為空，name text類型，gender text類型 age integer類型
//if not exists 如果有表則不創(chuàng)建
NSString *creatStr = @"create table if not exists person(uid integer primary key autoincrement not null,name text,gender text,age integer)";
int result = sqlite3_exec(db, creatStr.UTF8String, NULL, NULL, NULL);
NSLog(@"%d",result);
if (result == SQLITE_OK) {
NSLog(@"建表成功");
} else {
NSLog(@"建表失敗");
}
NSLog(@"_dbPath = %@",_dbPath);
}
//插入數(shù)據(jù)
-(void)inserName:(NSString *)name gender:(NSString *)gender age:(NSInteger)age{
//當(dāng)values不確定的情況下用 ？ 代替
NSString *insertStr = @"insert into person(name,gender,age)values(?,?,?)";
//伴隨指針
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, insertStr.UTF8String, -1, &stmt, NULL);
//判斷執(zhí)行結(jié)果
if (result == SQLITE_OK) {
//在操作成功的方法內(nèi)進(jìn)行 ？值綁定
//第一個參數(shù)：伴隨指針
//第二個參數(shù)：？位置 從1開始
//第三個參數(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語句執(zhí)行完畢
//根據(jù)伴隨指針情況判斷是否插入成功
if(sqlite3_step(stmt) == SQLITE_DONE){
NSLog(@"插入成功");
}else{
NSLog(@"插入失敗");
}
}
//釋放伴隨指針
sqlite3_finalize(stmt);
}
//通過uid更新數(shù)據(jù)
-(void)updateWithUID:(NSInteger)uid{
//sql語句
NSString *update = @"update person set name = '666' where uid = ?";
//創(chuàng)建伴隨指針
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, update.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//伴隨指針綁定參數(shù)
sqlite3_bind_int64(stmt, 1, uid);
//執(zhí)行完畢
if (sqlite3_step(stmt) == SQLITE_DONE) {
NSLog(@"數(shù)據(jù)庫更新成功");
} else {
NSLog(@"數(shù)據(jù)庫更新失敗");
}
}
sqlite3_finalize(stmt);
}
//通過uid去刪除數(shù)據(jù)
-(void)deleteWithUID:(NSInteger)uid{
//準(zhǔn)備sql語句
NSString *deleteStr = [NSString stringWithFormat:@"delete from person where uid = %ld",uid];
int result = sqlite3_exec(db, deleteStr.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"刪除成功");
} else {
NSLog(@"刪除失敗");
}
}
//查找所有數(shù)據(jù)
-(void)searchAll{
//sql語句
NSString *searchAllStr = @"select * from person";
sqlite3_stmt *stmt = NULL;
int result = sqlite3_prepare(db, searchAllStr.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//取數(shù)據(jù)
//sqlite3_step每調(diào)用一次setp就從&stmt中取一次數(shù)據(jù)
while (sqlite3_step(stmt) == SQLITE_ROW) {//SQLITE_ROW 行數(shù)
//取出數(shù)據(jù)
//取出第一列字段的值（int 類型）
NSInteger ID = sqlite3_column_int64(stmt, 0);
//取出第二列字段的值（text 類型）
const unsigned char *name = sqlite3_column_text(stmt, 1);
//取出第三列字段的值（text 類型）
const unsigned char *gender = sqlite3_column_text(stmt, 2);
//取出第三列字段的值 （int 類型）
NSInteger age = sqlite3_column_int64(stmt, 3);
NSLog(@"%ld %s %s %ld",ID,name,gender,age);
}
}else{
NSLog(@"select error！");
}
}
//根據(jù)名字查找相關(guān)數(shù)據(jù)信息
-(void)searchWithName:(NSString *)name{
//sql語句
NSString *searchStr = @"select * from person where name = ?";
sqlite3_stmt *stmt = NULL;
int result = sqlite3_prepare(db, searchStr.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//取數(shù)據(jù)
//取出第一列字段的值（int 類型）
NSInteger ID = sqlite3_column_int64(stmt, 0);
//取出第二列字段的值（text 類型）
const unsigned char *name = sqlite3_column_text(stmt, 1);
//取出第三列字段的值（text 類型）
const unsigned char *gender = sqlite3_column_text(stmt, 2);
//取出第三列字段的值 （int 類型）
NSInteger age = sqlite3_column_int64(stmt, 3);
NSLog(@"%ld %s %s %ld",ID,name,gender,age);
}else{
NSLog(@"select error！");
}
}
@end
