//
// StudentTool.h
// SQLite的封装
// 学生数据的CRUD
#import <Foundation/Foundation.h>
@class Student;
@interface StudentTool : NSObject
+ (BOOL)addStudent:(Student *)student;
/**
* 获得所有学生
*
* @return 数组中装着都说Student模型
*/
+ (NSArray *)students;
@end
//
// StudentTool.m
// SQLite的封装
#import "StudentTool.h"
#import "Student.h"
#import <sqlite3.h>
@implementation StudentTool
/**
* 全局变量
static的作用:能保证 _db 这个变量只被StudentTool.m直接访问
*/
static sqlite3 *_db;
+ (void)initialize
{
//0.获取沙盒中的数据库文件名
NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
//1.创建(打开)数据库(如果数据库不存在,会自动创建)
int result = sqlite3_open(filename.UTF8String, &_db);
if (result == SQLITE_OK) {
NSLog(@"成功打开数据库");
//3.建表
const char *sql = "create table if not exists t_student (id integer primary key autoincrement,name text,age integer);";
char *errorMesg = NULL;
int result = sqlite3_exec(_db, sql, NULL, NULL, &errorMesg);
if (result == SQLITE_OK) {
NSLog(@"成功创建t_student表");
}else{
NSLog(@"创建t_student表失败:%s",errorMesg);
}
}else{
NSLog(@"打开数据库失败");
}
}
/**
* 添加学生
*
* @param student 需要添加的学生
*/
+ (BOOL)addStudent:(Student *)student
{
NSString *sql = [NSString stringWithFormat:@"insert into t_student (name,age) values ('%@',%d);",student.name,student.age];
char *errorMesg = NULL;
int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errorMesg);
return result == SQLITE_OK;
}
+ (NSArray *)students
{
//0.定义数组
NSMutableArray *students = nil;
//1.定义sql语句
const char *sql = "select id,name,age form t_student;";
//2.定义一个stmt存放结果集
sqlite3_stmt *stmt = NULL;
//3.检测SQL语句的合法性
int ressult = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);
if (ressult == SQLITE_OK) {
NSLog(@"查询语句合法");
students = [NSMutableArray array];
//4.执行SQL语句
int stepResult = sqlite3_step(stmt);
while (stepResult == SQLITE_ROW) {
Student *student = [[Student alloc] init];
//获得第0列的id
student.ID = sqlite3_column_int(stmt, 0);
//获得第1列的name
const unsigned char *sname = sqlite3_column_text(stmt, 1);
student.name = [NSString stringWithUTF8String:(const char *)sname];
//获得第2列的age
student.age = sqlite3_column_int(stmt, 2);
//添加到数组
[students addObject:student];
}
}else{
NSLog(@"查询语句非法");
}
return students;
}
@end
//
// Student.h
// SQLite的封装
// 学生模型
#import <Foundation/Foundation.h>
@interface Student : NSObject
/**
* 学生的id
*/
@property(nonatomic,assign) int ID;
/**
* 学生姓名
*/
@property(nonatomic,copy) NSString *name;
/**
* 学生的年龄
*/
@property(nonatomic,assign) int age;
@end
//
// ViewController.m
// SQLite的封装
#import "ViewController.h"
#import "StudentTool.h"
#import "Student.h"
@interface ViewController ()
- (IBAction)insert;
- (IBAction)query;
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
}
- (IBAction)insert {
//添加30条数据
for (int i=0; i<30; i++) {
//创建学生
Student *student = [[Student alloc] init];
student.name = [NSString stringWithFormat:@"jack-%d",arc4random()%100];
student.age = arc4random()%100;
//添加学生
BOOL result = [StudentTool addStudent:student];
if (result) {
NSLog(@"添加成功");
}
}
}
- (IBAction)query {
NSArray *students = [StudentTool students];
for (Student *stu in students) {
NSLog(@"%d %@ %d",stu.ID,stu.name,stu.age);
}
}
@end