SQLite用于用户管理

User.h文件

#import <Foundation/Foundation.h>

@interface User : NSObject

@property (nonatomic, copy) NSString *username;
@property (nonatomic, copy) NSString *password;
@property (nonatomic, assign) NSUInteger age;

@end

DataBaseManager.h文件

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

@interface DataBaseManager : NSObject

//单例方法
+ (instancetype)shareManager;

//添加用户数据
- (BOOL)addUser:(User *)user;
//修改用户数据
- (BOOL)updateUser:(User *)user;
//删除用户
- (BOOL)deleteUser:(User *)user;
//查找用户，使用用户名作为参数
- (User *)searchUser:(NSString *)username;
//查找某一年龄段段用户
- (NSArray *)searchuserFromAge:(NSUInteger)fAge toAge:(NSUInteger)tAge;
//查找所有用户
- (NSArray *)allUsers;
//用户登录
- (BOOL)logInWithUsername:(NSString *)username Password:(NSString *)password;
//用户数量
- (NSUInteger)userCount;

@end

DataBaseManager.h.m文件

#import "DataBaseManager.h"
//数据库相关头文件
#import <sqlite3.h>
//宏定义数据库文件路径
#define kDataBaseFilePath [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/User.sqlite"]

单例实现

+ (instancetype)shareManager {

    static DataBaseManager *manager = nil;
    if (manager == nil) {

        static dispatch_once_t onceToken;
        dispatch_once(&onceToken, ^{

            manager = [[super allocWithZone:nil] init];
        });
    }
    return manager;
}
+ (instancetype)allocWithZone:(struct _NSZone *)zone {

    return [self shareManager];
}
- (instancetype)copy {

    return self;
}

DDL语句应用 初始化时创建数据库文件,创建表格

- (instancetype)init {

    NSLog(@"%@", kDataBaseFilePath);
    //创建数据库文件
    NSFileManager *manager = [NSFileManager defaultManager];
    //判断是否存在数据库文件
    if ([manager fileExistsAtPath:kDataBaseFilePath]) {
        return self;
    }
    //若不存在创建数据库文件
    [manager createFileAtPath:kDataBaseFilePath contents:nil attributes:nil];

    //创建表格
    //打开数据库文件
    sqlite3 *sqlite = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlite);
    if (result != SQLITE_OK) {

        NSLog(@"数据库打开失败");
        [manager removeItemAtPath:kDataBaseFilePath error:nil];
        return nil;
    }
    //构造sql语句
    NSString *sqlString = @"CREATE TABLE user(username text PRIMARY KEY, password text NOT NULL, age integer DEFAULT 18)";
    //执行sql语句
    sqlite3_exec(sqlite, [sqlString UTF8String], NULL, NULL, NULL);
    //关闭数据库文件
    sqlite3_close(sqlite);
    return self;
}

DML语句应用 添加用户

- (BOOL)addUser:(User *)user {

    //打开数据库文件
    sqlite3 *sqlite = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlite);
    if (result != SQLITE_OK) {

        NSLog(@"数据库打开失败");
        return NO;
    }
    //构建sql语句
    NSString *sqlString = @"INSERT INTO user (username,password,age) VALUES (?,?,?)";
    //编译sql语句
    sqlite3_stmt *stmt = NULL;
    result = sqlite3_prepare_v2(sqlite, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"语句编译失败");
        sqlite3_close(sqlite);
        return NO;
    }
    //绑定数据
    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);
    //执行语句
    result = sqlite3_step(stmt);
    if (result != SQLITE_DONE) {

        NSLog(@"数据插入失败");
        sqlite3_finalize(stmt);
        sqlite3_close(sqlite);
        return NO;
    }
    //关闭句柄 数据库
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite);

    return YES;
}

DQL语句应用 所有用户

- (NSArray *)allUsers {

    //打开数据库文件
    sqlite3 *sqlite = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlite);

    if (result != SQLITE_OK) {
        NSLog(@"数据库打开失败");
        return nil;
    }
    //构造sql语句
    NSString *sqlString = @"SELECT username,age FROM user";
    //编译语句
    sqlite3_stmt *stmt = NULL;
    result = sqlite3_prepare_v2(sqlite, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"编译失败");
        sqlite3_close(sqlite);
        return nil;
    }
    //执行语句
    NSMutableArray *array = [[NSMutableArray alloc] init];
    while(sqlite3_step(stmt) == SQLITE_ROW) {

        User *user = [[User alloc] init];
        user.username = [NSString stringWithFormat:@"%s", sqlite3_column_text(stmt, 0)];
        user.age = sqlite3_column_int(stmt, 1);
        [array addObject:user];
    }
    //关闭句柄 数据库
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite);
    return [array copy];
}

DQL语句应用 用户登录

- (BOOL)logInWithUsername:(NSString *)username Password:(NSString *)password {

    //打开数据库
    sqlite3 *sqlite = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlite);

    if (result != SQLITE_OK) {

        NSLog(@"数据库打开失败");
        return NO;
    }
    //构建sql语句
    NSString *sqlString = @"SELECT count(*) FROM user WHERE username=? AND password=?";
    //编译语句
    sqlite3_stmt *stmt = NULL;
    result = sqlite3_prepare_v2(sqlite, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {

        NSLog(@"编译失败");
        return NO;
    }
    //绑定数据
    sqlite3_bind_text(stmt, 1, [username UTF8String], -1, NULL);
    sqlite3_bind_text(stmt, 2, [password UTF8String], -1, NULL);
    //执行语句
    if (sqlite3_step(stmt) == SQLITE_ROW) {

        if (sqlite3_column_int(stmt, 0) > 0) {

            sqlite3_finalize(stmt);
            sqlite3_close(sqlite);
            return YES;
        }
    }
    //关闭句柄 数据库
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite);

    return NO;
}

DQL语句应用 用户个数

- (NSUInteger)userCount {

    //打开数据库
    sqlite3 *sqlite = NULL;
    int result = sqlite3_open([kDataBaseFilePath UTF8String], &sqlite);

    if (result != SQLITE_OK) {

        NSLog(@"数据库打开失败");
        return NO;
    }
    //构造sql语句
    NSString *sqlString = @"SELECT count(*) FROM user";
    //编译语句
    sqlite3_stmt *stmt = NULL;
    result = sqlite3_prepare_v2(sqlite, [sqlString UTF8String], -1, &stmt, NULL);
    if (result != SQLITE_OK) {

        NSLog(@"编译失败");
        return NO;
    }
    //执行语句
    if (sqlite3_step(stmt) == SQLITE_ROW) {

        NSInteger count = sqlite3_column_int(stmt, 0);
        return count;
    }
    //关闭句柄 服务器
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite);

    return 0;
}