-
导入libsqlite3.0.tbd库
导入sql库
-
创建类,将后缀改为.mm(兼容C/C++)
创建类(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;
//打开数据库
-(void)openDB;
//关闭数据库
-(void)closeDB;
//创建表
-(void)creatTable;
//插入数据
-(void)inserName:(NSString *)name gender:(NSString *)gender age:(NSInteger)age;
//通过uid更新数据
-(void)updateWithUID:(NSInteger)uid;
//通过uid去删除数据
-(void)deleteWithUID:(NSInteger)uid;
//查找所有数据
-(void)searchAll;
//根据名字查找相关数据信息
-(void)searchWithName:(NSString *)name;
@end
DataBaseHandle.mm
#import "DataBaseHandle.h"
#import <sqlite3.h>
@interface DataBaseHandle()
//数据库存储路径
@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{
}
//懒加载(数据库路径)
-(NSString *)dbPath
{
if(_dbPath == nil){
//获取document路径:
NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
//在document路径下创建数据库文件夹
_dbPath = [document stringByAppendingPathComponent:@"person.sqlite"];
NSLog(@"_dbPath = %@",self.dbPath);
}
return _dbPath;
}
static sqlite3 *db = nil;
//打开数据库
-(void)openDB{
//第一个参数：数据库的路径
//第二个参数：代表数据库地址
int result = sqlite3_open(self.dbPath.UTF8String, &db);
if (result == SQLITE_OK) {
NSLog(@"数据库打开成功");
} else {
NSLog(@"数据库打开失败");
}
}
//关闭数据库
-(void)closeDB{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"数据库关闭成功");
} else {
NSLog(@"数据库关闭失败");
}
}
//创建表
-(void)creatTable{
//创建一个person表，字段 ID integer类型 主键 自增不能为空，name text类型，gender text类型 age integer类型
//if not exists 如果有表则不创建
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);
}
//插入数据
-(void)inserName:(NSString *)name gender:(NSString *)gender age:(NSInteger)age{
//当values不确定的情况下用 ？ 代替
NSString *insertStr = @"insert into person(name,gender,age)values(?,?,?)";
//伴随指针
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, insertStr.UTF8String, -1, &stmt, NULL);
//判断执行结果
if (result == SQLITE_OK) {
//在操作成功的方法内进行 ？值绑定
//第一个参数：伴随指针
//第二个参数：？位置 从1开始
//第三个参数：要插入的值
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语句执行完毕
//根据伴随指针情况判断是否插入成功
if(sqlite3_step(stmt) == SQLITE_DONE){
NSLog(@"插入成功");
}else{
NSLog(@"插入失败");
}
}
//释放伴随指针
sqlite3_finalize(stmt);
}
//通过uid更新数据
-(void)updateWithUID:(NSInteger)uid{
//sql语句
NSString *update = @"update person set name = '666' where uid = ?";
//创建伴随指针
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, update.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);
}
//通过uid去删除数据
-(void)deleteWithUID:(NSInteger)uid{
//准备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(@"删除失败");
}
}
//查找所有数据
-(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) {
//取数据
//sqlite3_step每调用一次setp就从&stmt中取一次数据
while (sqlite3_step(stmt) == SQLITE_ROW) {//SQLITE_ROW 行数
//取出数据
//取出第一列字段的值（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！");
}
}
//根据名字查找相关数据信息
-(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) {
//取数据
//取出第一列字段的值（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
