SQLite 嵌入式数据库
1. ⽀持事件,不需要配置,不需要安装,不需要管理员;
2. ⽀持⼤部分SQL92;
3. 完整的数据库保存在磁盘上⾯一个⽂件,同一个数据库⽂件可以在不同机器上⾯使用,最大
⽀持数据库到2T
4. 整个系统少于3万行代码,少于250KB的内存占⽤;
5. 源代码开发,代码 5%有较好的注释,简单易用的API;
引入libsqlite3.dylib 引入方法:Build Phases ---- Link Binary With Libraries ---- ' + '
ViewController.m
#import "ViewController.h"
#import "DataBaseHandle.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
//创建数据库对象
DataBaseHandle * dbh = [DataBaseHandle shareDataBase];
//**********************
//打开数据库
[dbh openDB];
//创建表
// [dbh createTableWithName:@"student"];
//插入数据
// [dbh insertTableWithTableName:@"student" sName:@"王老五" sAge:25];
// [dbh insertTableWithTableName:@"student" sName:@"324" sAge:20];
// [dbh insertTableWithTableName:@"student" sName:@"345" sAge:28];
// [dbh insertTableWithTableName:@"student" sName:@"234" sAge:21];
// [dbh insertTableWithTableName:@"student" sName:@"ert" sAge:29];
// [dbh insertTableWithTableName:@"student" sName:@"dg" sAge:35];
// [dbh insertTableWithTableName:@"student" sName:@"fgh" sAge:55];
// //修改数据
// [dbh updateTableWithTableName:@"student"];
//
// //删除表中的数据
// [dbh deleteTableWithTableName:@"student"];
//查找数据
[dbh selectData:30];
//关闭数据库
[dbh closeDB];
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
DataBaseHandle.h
#import <Foundation/Foundation.h>
@interface DataBaseHandle : NSObject
+(instancetype)shareDataBase;
//打开数据库
-(void)openDB;
//关闭数据库
-(void)closeDB;
//创建表
-(void)createTableWithName:(NSString *)name;
//插入数据
-(void)insertTableWithTableName:(NSString *)tableName sName:(NSString *)sname sAge:(NSInteger)sage;
//更新数据
-(void)updateTableWithTableName:(NSString *)tableName;
//删除数据
-(void)deleteTableWithTableName:(NSString *)tableName;
//查找数据
-(NSArray *)selectData:(NSInteger)sage;
@end
DataBaseHandle.m
#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Model.h"
static DataBaseHandle *d = nil;
@implementation DataBaseHandle
+(instancetype)shareDataBase{
if (d == nil) {
d=[[DataBaseHandle alloc]init];
}
return d;
}
// 首先导入框架sqlite3
// 创建数据库指针
static sqlite3 *db = NULL;
//打开数据库
-(void)openDB{
//拼接路径
NSString * filePath = [[self p_doucumnetsPath]stringByAppendingPathComponent:@"DataBase.sqlite"];
NSLog(@"%@",filePath);
//打开数据库(根据路径打开,如果没有创建再打开)
//int类型返回,用于判断语句是否执行成功
int result = sqlite3_open(filePath.UTF8String, &db);
//SQLITE_OK 宏定义 表示语句执行成功
if (result == SQLITE_OK) {
NSLog(@"数据库打开成功");
}else{
NSLog(@"数据库打开失败");
}
}
//获取documents文件路径
-(NSString *)p_doucumnetsPath{
NSString * s = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
return s;
}
//关闭数据库
-(void)closeDB{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"数据库关闭成功");
}else{
NSLog(@"数据库关闭失败");
}
}
//创建表
-(void)createTableWithName:(NSString *)name{
//准备sql语句
NSString * sqlStr = [NSString stringWithFormat:@"create table if not exists %@(s_id integer primary key autoincrement not null,s_name text not null,s_age integer) ",name];
// 接收错误
char *err = NULL;
// 执行语句
int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, &err);
if(result == SQLITE_OK){
NSLog(@"建表成功");
}else{
NSLog(@"建表失败,%s",err);
}
}
//插入数据
-(void)insertTableWithTableName:(NSString *)tableName sName:(NSString *)sname sAge:(NSInteger)sage{
//准备sql语句
NSString *sqlStr = [NSString stringWithFormat:@"insert into %@(s_name,s_age) values('%@',%ld)",tableName,sname,sage];
//接收错误
char *err = NULL;
//执行语句
int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, &err);
if (result == SQLITE_OK) {
NSLog(@"插入成功");
}else{
NSLog(@"插入失败 %s",err);
}
}
//更新数据
-(void)updateTableWithTableName:(NSString *)tableName{
NSString * sqlStr = [NSString stringWithFormat:@"update %@ set s_name = '王老六' where s_name = '王老五'",tableName];
int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"更改成功");
}else{
NSLog(@"更改失败");
}
}
//删除数据
-(void)deleteTableWithTableName:(NSString *)tableName{
NSString * sqlStr = [NSString stringWithFormat:@"delete from %@ where s_age > 35 ",tableName];
int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败");
}
}
//查找数据
-(NSArray *)selectData:(NSInteger)sage{
//数组
NSMutableArray * dataArray = [NSMutableArray array];
//1 准备语句
NSString *sqlStr = @"select * from student where s_age > ?";
//2 伴随指针
sqlite3_stmt * stmt = nil;
//3 预执行 无符号当中-1是最大位的正整数
int result = sqlite3_prepare_v2(db, sqlStr.UTF8String, -1, &stmt, NULL);
//4
if (result == SQLITE_OK) {
//4 确定参数
// 第一个参数是返回值
// 第二个参数是确定要和第几个问号绑定
// 第三个参数是值
sqlite3_bind_int64(stmt, 1, sage);
// sqlite3_bind_text(<#sqlite3_stmt *#>, <#int#>, <#const char *#>, <#int n#>, <#void (*)(void *)#>)
//5 遍历
// int result = sqlite3_step(stmt);
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSInteger sid = sqlite3_column_int64(stmt, 0);
NSString * sname =[NSString stringWithUTF8String:sqlite3_column_text(stmt, 1)] ;
NSInteger sage = sqlite3_column_int64(stmt, 2);
//创建Model对象
Model *m = [[Model alloc]init];
m.sid = sid;
m.sage = sage;
m.sname = sname;
//将model对象存入数组
[dataArray addObject:m];
NSLog(@"sid = %ld sname = %@ sage = %ld",sid,sname,sage);
}
//关闭伴随指针(如果不关 数据库也关不上)
sqlite3_finalize(stmt);
}else{
NSLog(@"查找失败");
}
return dataArray;
}
@end
Model.h
#import <Foundation/Foundation.h>
@interface Model : NSObject
@property(nonatomic,assign)NSInteger sid;
@property(nonatomic,copy)NSString * sname;
@property(nonatomic,assign)NSInteger sage;
@end
Model.m
#import "Model.h"
@implementation Model
-(void)setValue:(id)value forUndefinedKey:(NSString *)key{
}
@end