SQLite的简单封装

//
//  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