SQLite-05-增删改查

//
//  DataBaseManager.m
//  02 SQLite in iOS
//
//  Created by ZhuJiaCong on 16/5/3.
//  Copyright © 2016年 ZhuJiaCong. All rights reserved.
//

#import "DataBaseManager.h"
//数据库相关头文件
#import <sqlite3.h>

#define kDataBaseFilePath [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/User.sqlite"]


@implementation DataBaseManager

#pragma mark - 单例类的实现
+ (instancetype)shareManager {
    
    static DataBaseManager *manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [super allocWithZone:NULL];
        
        [manager initDataBase];
    });
    
    return manager;
    
}

+ (instancetype)allocWithZone:(struct _NSZone *)zone {
    return [self shareManager];
}

- (id)copy {
    return self;
}



#pragma mark - 用户管理
//添加用户数据
- (BOOL)addUser:(User *)user {
    
    // INSERT INTO userTable(username, password, age) VALUES('zhangsan', '123456', 20);
    
    //打开数据库
    //创建一个指向数据库结构体的指针
    sqlite3 *sqlite3 = NULL;
    //打开数据库
    int result =  sqlite3_open([kDataBaseFilePath UTF8String], &sqlite3);
    if (result != SQLITE_OK) {
        //打开数据库出错
        NSLog(@"数据库打开失败");
        return NO;
    }
    
    //构建SQL语句
    //在需要填充数据的位置，使用？作为占位符
    NSString *sqlString = @"INSERT INTO userTable(username, password, age) VALUES(?,?,?);";
    
    //编译SQL语句
    //数据库句柄，可以通过句柄，对正在准备执行的SQL语句进行操作
    sqlite3_stmt *stmt = NULL;
    //对SQL语句执行编译处理
    /*
        SQLITE_API int SQLITE_STDCALL sqlite3_prepare_v2(
            sqlite3 *db,            数据库指针
            const char *zSql,       SQL语句 UTF8字符串
            int nByte,              字符串的长度，当设置为－1时表示自动计算长度
            sqlite3_stmt **ppStmt,  数据库句柄的二级指针
            const char **pzTail     编译结束后剩余的字符串
        );
    */
    result = sqlite3_prepare_v2(sqlite3, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"编译SQL语句失败");
        sqlite3_close(sqlite3);
        
        return NO;
    }
    
    //向格式占位符中填充需要插入的数据
    /*
     第一个int类型的参数 表示当前要绑定的是第几个占位符 从1开始
     第二个int类型的参数 表示字符串长度
     */
    sqlite3_bind_text(stmt, 1, [user.username UTF8String], -1, NULL);
    sqlite3_bind_text(stmt, 2, [user.password UTF8String], -1, NULL);
    sqlite3_bind_int(stmt, 3, (int)user.age);
    
    //执行SQL语句
    result = sqlite3_step(stmt);
    //在插入语句中  SQLITE_DONE 表示执行成功
    if (result != SQLITE_DONE) {
        NSLog(@"执行插入数据失败");
        //关闭执行句柄
        sqlite3_finalize(stmt);
        sqlite3_close(sqlite3);
        
        return NO;
    }
    
    //关闭数据库
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite3);
    
    
    return YES;
}
//修改用户数据
- (BOOL)updateUser:(User *)user {
    
    // UPDATE userTable SET password='654321' WHERE name='zhangsan';

    //打开数据库
    //创建一个指向数据库结构体的指针
    sqlite3 *sqlite3 = NULL;
    //打开数据库
    int result =  sqlite3_open([kDataBaseFilePath UTF8String], &sqlite3);
    if (result != SQLITE_OK) {
        //打开数据库出错
        NSLog(@"数据库打开失败");
        return NO;
    }

    //构建SQL语句
    NSString *sqlString = @"UPDATE userTable SET password=? WHERE username=?;";
    
    //数据库句柄
    sqlite3_stmt *stmt = NULL;
    //编译SQL语句
    result =  sqlite3_prepare_v2(sqlite3, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"编译SQL语句失败");
        sqlite3_close(sqlite3);
        
        return NO;
    }
    
    //绑定数据
    sqlite3_bind_text(stmt, 1, [user.password UTF8String], -1, NULL);
    sqlite3_bind_text(stmt, 2, [user.username UTF8String], -1, NULL);
    
    //运行SQL语句
    result = sqlite3_step(stmt);
    if (result != SQLITE_DONE) {
        NSLog(@"修改密码失败");
    } else {
        NSLog(@"修改密码成功");
    }
    //关闭数据库
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite3);
    
    
    
    return result == SQLITE_DONE;
}
//删除用户
- (BOOL)deleteUser:(User *)user {
    return YES;
}
//查找用户，使用用户名作为参数
- (User *)searchUser:(NSString *)username {
    
    // SELECT * FROM userTable WHERE username = 'zhangsan';
    
    //打开数据库
    sqlite3 *sqlDB = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlDB);
    if (result != SQLITE_OK) {
        NSLog(@"数据库打开失败");
        return nil;
    }
    
    //编译SQL语句
    sqlite3_stmt *stmt = NULL;
    
    NSString *sqlString = @"SELECT * FROM userTable WHERE username = ?;";
    result = sqlite3_prepare_v2(sqlDB, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"编译SQL语句失败");
        sqlite3_close(sqlDB);
        
        return nil;
    }
    //绑定数据
    sqlite3_bind_text(stmt, 1, [username UTF8String], -1, NULL);
    
    //执行语句
    result = sqlite3_step(stmt);
    //查询语句执行成功，并且查询到了数据的返回值为 SQLITE_ROW
    if (result == SQLITE_ROW) {
        User *user = [[User alloc] init];
        
        //从查询结果中，获取一个字段所对应的值
        // stmt,int   要获取的数据所在的索引值  从0开始
        user.username = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
        user.password = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
        user.age = sqlite3_column_int(stmt, 2);
        
        sqlite3_finalize(stmt);
        sqlite3_close(sqlDB);
        
        return user;
    }
    
    
    sqlite3_finalize(stmt);
    sqlite3_close(sqlDB);    
    
    return nil;
}
//查找某一年龄段段用户
- (NSArray *)searchuserFromAge:(NSUInteger)fAge toAge:(NSUInteger)tAge {
    
    // SELECT * FROM userTable WHERE age > ? AND age < ?;
    
    //打开数据库
    sqlite3 *sqlDB = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlDB);
    if (result != SQLITE_OK) {
        NSLog(@"数据库打开失败");
        return nil;
    }
    
    NSString *sqlString = @"SELECT * FROM userTable WHERE age > ? AND age < ?;";
    
    //编译SQL语句
    sqlite3_stmt *stmt = NULL;
    result = sqlite3_prepare_v2(sqlDB, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"编译失败");
        
        sqlite3_close(sqlDB);
        
        return nil;
    }
    //绑定数据
    sqlite3_bind_int(stmt, 1, (int)fAge);
    sqlite3_bind_int(stmt, 2, (int)tAge);
    
    //建立数组 用于储存结果
    NSMutableArray *mArray = [[NSMutableArray alloc] init];
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        User *user = [[User alloc] init];
        
        user.username = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
        user.password = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
        user.age = sqlite3_column_int(stmt, 2);
        
        //将查询到的结果，添加到数组中去
        [mArray addObject:user];
        
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(sqlDB);
    
    return [mArray copy];
}


- (NSUInteger)userCount {
    
    // SELECT count(*) FROM userTable;
    
    
    //打开数据库
    sqlite3 *sqlDB = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlDB);
    if (result != SQLITE_OK) {
        NSLog(@"数据库打开失败");
        return 0;
    }
    
    NSString *sqlString =@"SELECT count(*) FROM userTable;";
    
    //编译SQL语句
    sqlite3_stmt *stmt = NULL;
    result = sqlite3_prepare_v2(sqlDB, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"编译失败");
        
        sqlite3_close(sqlDB);
        
        return 0;
    }
    
    //执行查询
    result = sqlite3_step(stmt);
    NSUInteger count = 0;
    if (result == SQLITE_ROW) {
        
        count = sqlite3_column_int(stmt, 0);
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(sqlDB);
    
    return  count;
    
}


#pragma mark - 数据库操作
//初始化数据库
- (void)initDataBase {
    
    //创建数据库文件
    //判断是否已有此数据库文件
    NSFileManager *manager = [NSFileManager defaultManager];
    if ([manager fileExistsAtPath:kDataBaseFilePath]) {
        NSLog(@"数据库文件已存在");
        
        return;
    }
    
    //创建数据库文件
    [manager createFileAtPath:kDataBaseFilePath contents:nil attributes:nil];
    NSLog(@"%@", kDataBaseFilePath);
    
    //创建一个指向数据库结构体的指针
    sqlite3 *sqlite3 = NULL;
    //打开数据库
    int result =  sqlite3_open([kDataBaseFilePath UTF8String], &sqlite3);
    if (result != SQLITE_OK) {
        //打开数据库出错
        NSLog(@"数据库打开失败");
        return;
    }
    //构建 CREATE TABLE 语句
    NSString *sqlString = @"CREATE TABLE userTable(username text PRIMARY KEY, password text NOT NULL, age integer DEFAULT 18);";
    
    //执行创建表格
    result = sqlite3_exec(sqlite3, [sqlString UTF8String], NULL, NULL, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"创建数据表失败");
        //关闭数据库
        sqlite3_close(sqlite3);
        return;
    }
    
    //关闭数据库
    NSLog(@"用户表格创建完成");
    sqlite3_close(sqlite3);
    
    return;
    
}





@end