SQLite数据库类

封装好的SQLite数据库类
首先你先导入静态库文件 libsqlite3.dylib

在.h声明方法
#pragma mark - 单例



//创建单例方法  注意：是类方法

+(instancetype)shareDataBaseHandle;



#pragma mark - 数据库相关操作

//打开数据库

-(void)open;



//创建表单

-(void)createTable;



//插入数据（学生信息）

-(void)insertStudent:(Student *)student;



//更新学生信息

-(void)updataStudent:(Student *)student number:(NSInteger)number;



//删除数据

-(void)deleteStudent:(NSInteger)number;



//删除表格

-(void)dropTable;



//关闭数据库

-(void)close;



//查询

-(NSArray *)selectStudentWithSex:(NSString *)sex;


在.m写入方法
//创建一个数据库对象

static sqlite3 *db;



+(instancetype)shareDataBaseHandle

{

    // static 修饰词作用：1 在静态区开空间，程序退出时，内存才释放  2 只初始化一次

    static DataBaseHandle *databaseHandle = nil;



    if (databaseHandle == nil) {

        databaseHandle = [[DataBaseHandle alloc] init];

    }



    return databaseHandle;

}



//打开数据库

-(void)open

{

    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];

    NSString *sqlPath = [docPath stringByAppendingPathComponent:@"students.sqlite"];



    //如果数据库已经打开，直接返回，不运行下面代码

    if (db != nil) {

        NSLog(@"数据库已经打开");

        return;

    }



    //核心API ： sqlite3_open 函数

    int result = sqlite3_open(sqlPath.UTF8String, &db);



    //根据函数返回值，判断操作是否正确

    if (result == SQLITE_OK) {

        NSLog(@"数据库打开成功");

    }else{

        NSLog(@"数据库打开失败");

    }

}



//创建表单

-(void)createTable

{

    //创建SQL语句

    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS lanou0613(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, sex TEXT, age integer)";  //lanou0613是表单名字可以更改



    //核心API

    int result = sqlite3_exec(db, createSQL.UTF8String, NULL, NULL, nil);



    if (SQLITE_OK == result) {

        NSLog(@"创建表格成功");

    }else{

        NSLog(@"创建表格失败");

    }

}



//插入数据（学生信息）

-(void)insertStudent:(Student *)student

{

    //创建SQL语句

    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO lanou0613(name,sex,age) VALUES('%@','%@','%ld')",student.name, student.sex, student.age];



    //核心API

    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);

    if (SQLITE_OK == result) {

        NSLog(@"插入数据成功");

    }else{

        NSLog(@"插入数据失败");

    }

}



//更新学生信息

-(void)updataStudent:(Student *)student number:(NSInteger)number

{

    NSString *updataSQL = [NSString stringWithFormat:@"UPDATE lanou0613 SET name = '%@', sex = '%@', age = '%ld' WHERE number = '%ld'",student.name, student.sex, student.age, number];



    int result = sqlite3_exec(db, updataSQL.UTF8String, NULL, NULL, nil);



    if (SQLITE_OK == result) {



        NSLog(@"更新数据成功");

    }else{

        NSLog(@"更新数据失败");

    }



}



//删除数据

-(void)deleteStudent:(NSInteger)number

{

    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou0613 WHERE number = '%ld'",number];

    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);

    if (SQLITE_OK == result) {

        NSLog(@"删除成功");

    }else{

        NSLog(@"删除失败");

    }



}



//删除表格

-(void)dropTable

{

    NSString *dropTable = [NSString stringWithFormat:@"DROP TABLE lanou0613"];

    int result = sqlite3_exec(db, dropTable.UTF8String, NULL, NULL, nil);

    if (SQLITE_OK == result) {

        NSLog(@"删除表格成功");

    }else{

        NSLog(@"删除表格失败");

    }

}



//关闭数据库

-(void)close

{

    int result = sqlite3_close(db);

    if (SQLITE_OK == result) {

        db = nil;

        NSLog(@"关闭数据库成功");

    }else{

        NSLog(@"关闭数据库失败");

    }

}



//查询

-(NSArray *)selectStudentWithSex:(NSString *)sex

{

    //创建数组

    NSMutableArray *arr = [NSMutableArray array];



    //创建SQL查询语句

    NSString *selectSQL = [NSString stringWithFormat:@"SELECT *FROM lanou0613 WHERE sex = '%@'",sex];



    //准备好的SQL语句对象

    sqlite3_stmt *stmt = nil;



    //

    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);

    if (SQLITE_OK == result) {

        NSLog(@"查询成功");



//        //执行准备好的SQL语句

//        sqlite3_step(stmt);

        //sqlite3_step(stmt); 这个函数的返回值为 SQLITE_ROW



        //循环 判断到什么时候结束

        while (SQLITE_ROW == sqlite3_step(stmt)) {



            //但需要对象查询之后的结果进行处理操作时，使用sqlite3_column_ 函数

            const unsigned char *name = sqlite3_column_text(stmt, 1);//取出name

            const unsigned char *sex = sqlite3_column_text(stmt, 2);//取出sex

            int age = sqlite3_column_int(stmt, 3); //取出age



            //创建Model对象，赋值

            Student *stu = [[Student alloc] init];

            stu.name = [NSString stringWithUTF8String:(const char *)name];

            stu.sex = [NSString stringWithUTF8String:(const char *)sex];

            stu.age = age;



            //model 对象添加到数组中

            [arr addObject:stu];

            [stu release];

        }





        //销毁 准备好的SQL语句对象

        sqlite3_finalize(stmt);

    }else{



        NSLog(@"查询失败");



        sqlite3_finalize(stmt);

    }

    return arr;



}