sqLite的打开、关闭、增删查改

3.一般把对数据库的操作单独作为一个类，并且该类是一个单例类

.h文件中的代码:

#import <Foundation/Foundation.h>
#import "Student.h"

@interface DBManager : NSObject

//创建一个单例对象
+(instancetype)shareDBManager;




#pragma mark-------------数据库的基本方法
//打开数据库
-(void)openDB;

//关闭数据库
-(void)closeDB;

//创建表
-(void)createTable;

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


//删：一般不要根据主键删除，应为rowid要根据主键改变
-(void)deleteStudentWithName:(NSString *)name;


//删除17岁以下的学生
-(void)deleteStudentWithAge;


//改：根据姓名修改年龄
-(void)updateStudentAge:(NSInteger)age withName:(NSString *)name;




//查询全部
-(NSArray *)seleteAllStudent;

//根据条件查询
-(NSArray *)selectWithName:(NSString *)name;

.m中的主要代码:

#import "DBManager.h"

#pragma mark-----------导入包
#import <sqlite3.h>


@interface DBManager ()

@property(nonatomic,strong) NSMutableArray *array;


@end


@implementation DBManager


#pragma mark-------------伪单例的静态方法
+(instancetype)shareDBManager
{
    static DBManager *dbManager = nil;
    
#warning 判断条件一般放在前面
    if (nil == dbManager) {
        
        dbManager = [[DBManager alloc] init];
        
    }
    
    return dbManager;
}

#pragma mark-------------数据库的基本方法

//创建一个静态指针，表示唯一的
 static sqlite3 *db = nil;

//打开数据库
-(void)openDB
{
    
    if (nil != db ) {
        return;
    }
    
    //创建路径
    NSString *path = NSHomeDirectory();
    path = [path stringByAppendingPathComponent:@"student.sqlite"];
    
  //打开数据库
-(void)openDB
{
    
    if (nil != db ) {
        return;
    }
    
    //创建路径
    NSString *path = NSHomeDirectory();
    path = [path stringByAppendingPathComponent:@"student.sqlite"];
    
    //打开数据库
  int result = sqlite3_open([path UTF8String], &db);
   
    // SQLITE_OK:表示的是代码是否执行
    
    if (result == SQLITE_OK) {
        if (result == SQLITE_OK) {
        NSLog(@"哈哈,打开成功了");
    }
    else
    {
        NSLog(@"打开失败了,错误的操作数为%d",result);
    }
}

//关闭数据库
-(void)closeDB
{
    //调用关闭方法
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        
        //将数据库置空
        db = nil;
        NSLog(@"关闭成功");
    }
    else
    {
        NSLog(@"关闭失败,错误的操作数为%d",result);
    }
    
}

//创建表
-(void)createTable
{
    
    //准备sql语句
    NSString *createString = @"CREATE TABLE if not exists 'student' ('number' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, 'name' TEXT NOT NULL, 'age' INTEGER NOT NULL, 'gender' TEXT DEFAULT male)";

    //创建表
    int result = sqlite3_exec(db,createString.UTF8String,NULL,NULL,NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功了");
    }
    else
    {
        NSLog(@"创建表失败了，错误操作数为%d",result);
    }
}




//增
-(void)insertStudent:(Student *)student
{
    //准备
    NSString *insertString = [NSString stringWithFormat:@"insert into 'student'(name,age,gender)values('%@','%ld','%@')",student.name,student.age,student.gender];
    
    //执行
    int result  = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入数据成功");
    }
    else
    {
        NSLog(@"插入数据失败,错误操作数为%d",result);
    }
    
    
}

//删
-(void)deleteStudentWithName:(NSString *)name
{
    //准备
    NSString *deleteString = [NSString stringWithFormat:@"delete from 'student' where name = '%@' ",name ];
    
    int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    else
    {
        NSLog(@"删除失败");
    }
}

//删除17岁以下的学生
-(void)deleteStudentWithAge
{
    //准备
    NSString *deleteStr = [NSString stringWithFormat:@"delete from 'student' where age < 17"];
    
    int result  = sqlite3_exec(db, [deleteStr UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"小于17岁的删除成功");
    }
    else
    {
        NSLog(@"小于17岁的删除失败");
    }
}


//改：根据姓名修改年龄
-(void)updateStudentAge:(NSInteger)age withName:(NSString *)name
{
    //准备
    NSString *updateString = [NSString stringWithFormat:@"update 'student' set age = %ld where name = '%@'",age,name];
    //执行
    int result = sqlite3_exec(db, [updateString UTF8String], NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }
    else
    {
        NSLog(@"修改失败,错误为%d",result);
    }
}


//查

//查找全部的信息
/*-(NSArray *)seleteAllStudent
{
    //准备数组
    NSMutableArray *array = nil;
    
    //准备伴随指针
    sqlite3_stmt *stmt = nil;
    
    //准备sql语句
    NSString *selectString = @"select * from student";
    
    //准备执行：-1:读取的长度为最大值
    int result  = sqlite3_prepare_v2(db, selectString.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        
        //给数组开辟空间
        array = [NSMutableArray arrayWithCapacity:20];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //字段位置:number、name、age、gender
            Student *student = [[Student alloc] init];
            
            student.number = sqlite3_column_int(stmt, 0);
            
            //根据字段查询的字符串需要强转为const char *类型后，再转化成OC字符串
            student.name = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];
            
            student.age = sqlite3_column_int(stmt, 2);
            
            
            student.gender = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 3)];
            
            //添加到数组中
            [array addObject:student];
            
        }
        
        
            }
    else
    {
        NSLog(@"查询失败,失败操作是%d",result);
    }
    
    //释放伴随指针
    sqlite3_finalize(stmt);

    return array;
}
*/
-(NSArray *)seleteAllStudent
{
    //准备存放数据的数组
    NSMutableArray *array = nil;
    //准备查询语句
    NSString *selectString = @"select * from student";
    //准备伴随指针
    sqlite3_stmt *stmt = nil;
   
    //执行
    //判断sql语句是否正确
    int result = sqlite3_prepare_v2(db,selectString.UTF8String , -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        
       //为数组开辟空间
        array = [NSMutableArray arrayWithCapacity:4];
        
        //循环每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //创建一个学生对象
            Student *student = [[Student alloc] init];
            
            //根据每个字段给对象赋值
            student.number = sqlite3_column_int(stmt, 0);
            student.name = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];
            student.age = sqlite3_column_int(stmt, 2);
            student.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
            
            //将学生对象加入数组中
            [array addObject:student];
            
        }
        
        
        
        
    }
    else
    {
        NSLog(@"查找失败，失败的原因是%d",result);
    }
    
    //释放伴随指针
    sqlite3_finalize(stmt);
    
    return array;
    
}


//根据条件查询
-(NSArray *)selectWithName:(NSString *)name
{
//    方法一：
//    NSString *selectString = [NSString stringWithFormat:@"select * from 'student' where name = '%@'",name];
    
    //方法二:绑定
    //查询字符串
    NSString *sqlWord = @"select * from student where name = ?";
    
    NSMutableArray *array = nil;
    //伴随指针
    sqlite3_stmt *stmt = nil;
    
  int result =  sqlite3_prepare(db, [sqlWord UTF8String], -1, &stmt, NULL);
    if(result == SQLITE_OK)
    {
        array = [NSMutableArray arrayWithCapacity:4];
        //1：是绑定的第几个问号，从1开始
       
        //绑定指针
        sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            Student *student = [Student new];
            student.number = sqlite3_column_int64(stmt, 0);
            student.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            student.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            student.age = sqlite3_column_int(stmt, 3);
            
            
            [array addObject:student];
            NSLog(@"查询成功");
        }
    }
    else
    {
        NSLog(@"查询失败");
    }
    
    sqlite3_finalize(stmt);
    
    return array;
}


//懒方法
-(NSMutableArray *)array
{
    if (_array == nil) {
        _array = [NSMutableArray array];
    }
    return _array;
}