SQLite的使用
#import "ViewController.h"
#import <sqlite3.h>
#import "Model.h"
@interface ViewController ()
{
// 用于存储数据model
NSMutableArray *_arrayM;
// 数据库的句柄（在c语言中，通常把用于控制的类叫做句柄）
sqlite3 *_dataBase;
}
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
_arrayM = [NSMutableArray array];
// 1. 打开数据库
[self openDataBase];
// 2. 创建数据表
[self creatTable];
// 3. 给数据表添加数据
[self addModelToTable];
// 4. 从数据库中读取数据
[self getModel];
}
- (void)openDataBase
{
// 1. 设置数据库的路径
NSString *path = [NSString stringWithFormat:@"%@/Documents/model.db",NSHomeDirectory()];
NSLog(@"%@",path);
// 2. 打开数据库（系统会检测，如果这个路径不存在在这个数据库，就创建一个并打开，如果有的话，直接打开）
if (SQLITE_OK == sqlite3_open([path UTF8String], &_dataBase)) {
NSLog(@"数据库打开成功~");
}else{
NSLog(@"数据库打开失败~");
}
}
- (void)creatTable
{
// 创建数据表
NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS T_Model (ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,name TEXT, color TEXT, age INTEGER, length REAL)";
// 执行sql 语句
[self sqlExecWithSQL:sqlStr message:@""];
}
- (void)addModelToTable
{
// 插入数据（如果不设置主键，系统会帮助我们设置主键，按次序增长）
NSString *sqlStr1 = [NSString stringWithFormat:@"INSERT INTO T_Model(name,age,color,length) VALUES ('花花',10,'花色',100.4)"];
[self sqlExecWithSQL:sqlStr1 message:@""];
NSString *sqlStr2 = [NSString stringWithFormat:@"INSERT INTO T_Model(name,age,color,length) VALUES ('花花',10,'花色',100.4)"];
[self sqlExecWithSQL:sqlStr2 message:@""];
NSString *sqlStr3 = [NSString stringWithFormat:@"INSERT INTO T_Model(name,age,color,length) VALUES ('花花',10,'花色',100.4)"];
[self sqlExecWithSQL:sqlStr3 message:@""];
}
- (void)getModel
{
// 搜索数据
NSString *sqlStr = @"SELECT ID,name,color,age,length FROM T_Model";
// 检测sql语法
sqlite3_stmt *stmt = NULL;
if (SQLITE_OK == sqlite3_prepare_v2(_dataBase, [sqlStr UTF8String], -1, &stmt, NULL)) {
NSLog(@"搜索数据成功");
// 逐一查询符合条件的数据
while (SQLITE_ROW == sqlite3_step(stmt)) {
// 取出ID
int ID = sqlite3_column_int(stmt, 0);
// 取出名字
const unsigned char *name = sqlite3_column_text(stmt, 1);
// 取出age
int age = sqlite3_column_int(stmt, 3);
// 颜色
const unsigned char *color = sqlite3_column_text(stmt, 2);
// 长度
CGFloat length = sqlite3_column_double(stmt, 4);
// 获取数据模型
Model *model = [Model modelWith:ID name:[NSString stringWithUTF8String:(const char *)name] age:age color:[NSString stringWithUTF8String:(const char *)color] length:length];
[_arrayM addObject:model];
}
}else{
NSLog(@"搜索数据失败");
}
}
#pragma mark - 执行sql语句方法
- (void)sqlExecWithSQL:(NSString *)sqlStr message:(NSString *)message
{
char *error = NULL;
// 1. 句柄
// 2. 要执行的sql语句
// 3. 4. 传NULl
// 5. sql语句执行完成后返回的信息
if (SQLITE_OK == sqlite3_exec(_dataBase, [sqlStr UTF8String], NULL, NULL, &error)) {
NSLog(@"SQL 语句执行成功");
}else{
NSLog(@"%s",error);
}
}
@end
#import <Foundation/Foundation.h>
#import <UIKit/UIKit.h>
@interface Model : NSObject
+(instancetype)modelWith:(NSInteger )ID name:(NSString *)name age:(NSInteger)age color:(NSString *)color length:(CGFloat )length;
/**
* 主键（区分不同的数据model）
*/
@property(nonatomic, assign)NSInteger ID;
@property (nonatomic, copy)NSString *name;
@property (nonatomic, assign)NSInteger age;
@property (nonatomic, copy)NSString *color;
@property (nonatomic, assign)CGFloat length;
@end
#import "Model.h"
@implementation Model
+ (instancetype)modelWith:(NSInteger)ID name:(NSString *)name age:(NSInteger)age color:(NSString *)color length:(CGFloat)length
{
Model *model = [[Model alloc] init];
model.ID = ID;
model.name = name;
model.age = age;
model.color = color;
model.length = length;
return model;
}
@end
