SQLite的使用——OC代码

一、添加”libsqlite3.dylib”库文件

二、代码结构

三、代码实现

1.AppDelegate.m

#import "AppDelegate.h"#import "SQLiteManager.h"@interface AppDelegate ()@end@implementation AppDelegate- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {    BOOL flag = [[SQLiteManager shareInstance] openDB];    if (flag) {        NSLog(@"打开数据库成功");    } else {        NSLog(@"打开数据库失败");    }    return YES;}@end

2.ViewController.m

#import "ViewController.h"#import "SQLiteManager.h"#import "Student.h"@interface ViewController () <UISearchBarDelegate>/* 关键字搜索出来的数据 */@property (nonatomic, strong) NSArray *listData;@end@implementation ViewController/* 0.封装一个工具类,管理SQLite的数据库(单例) 1.创建数据库(文件) 2.创建表,用于存储数据 3.基本操作(增删改) 4.查询数据 */- (void)viewDidLoad {    [super viewDidLoad];    // 添加搜索框    UISearchBar *searchBar = [[UISearchBar alloc] init];    searchBar.frame = CGRectMake(0, 0, self.view.bounds.size.width, 44);    self.tableView.tableHeaderView = searchBar;    searchBar.delegate = self;}- (void)touchesBegan:(NSSet<UITouch *> *)touches withEvent:(UIEvent *)event{    NSArray *students = [Student loadData];    NSLog(@"%@", students);}- (void)deleteData{    // 1.删除的SQL    NSString *deleteSQL = @"DELETE FROM t_student WHERE age = 24;";    // 2.执行SQL    if ([[SQLiteManager shareInstance] execSQL:deleteSQL]) {        NSLog(@"删除数据成功");    }}- (void)updateData{    // 1.更新的SQL    NSString *updateSQL = @"UPDATE t_student SET name = 'zs2' WHERE id = 1;";    // 2.执行sql    if ([[SQLiteManager shareInstance] execSQL:updateSQL]) {        NSLog(@"更新数据成功");    }}#pragma mark - 实现UISearchBar的代理方法- (void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText{    // 1.拿到关键字,查询学生数据    self.listData = [Student loadDataWithKeyword:searchText];    // 2.刷新表格    [self.tableView reloadData];}#pragma mark - 实现tableView的数据源方法- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section{    return self.listData.count;}- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{    static NSString *ID = @"MenuCell";    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:ID];    if (cell == nil) {        cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:ID];    }    Student *stu = self.listData[indexPath.row];    cell.textLabel.text = stu.name;    return cell;}@end

3.SQLiteManager.h

#import <Foundation/Foundation.h>@interface SQLiteManager : NSObject+ (instancetype)shareInstance;- (BOOL)openDB;- (BOOL)execSQL:(NSString *)sql;- (NSArray *)querySQL:(NSString *)querySQL;@end

4.SQLiteManager.m

#import "SQLiteManager.h"#import <sqlite3.h>@interface SQLiteManager ()@property (nonatomic, assign) sqlite3 *db;@end@implementation SQLiteManagerstatic id _instance;+ (instancetype)shareInstance{    static dispatch_once_t onceToken;    dispatch_once(&onceToken, ^{        _instance = [[self alloc] init];    });    return _instance;}#pragma mark - 创建或者打开数据库- (BOOL)openDB{    // 获取沙盒路径,将数据库放入沙盒中    NSString *filePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];    filePath = [filePath stringByAppendingPathComponent:@"my.sqlite"];    // 如果有对应的数据库,则打开对应的数据库.如果没有对应的数据库,则创建数据库    // 1> 参数一:文件路径(数据库的存放路径)    // 2> 参数二:操作数据库的对象    if (sqlite3_open(filePath.UTF8String, &_db) != SQLITE_OK) {        NSLog(@"打开数据库失败");        return NO;    }    // 如果打开数据成功,则创建一张表,用于之前存放数据    return [self createTable];}- (BOOL)createTable {    // 1.定义创建表的SQL语句    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS 't_student' ('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'name' TEXT,'age' INTEGER);";    // 2.执行SQL语句    return [self execSQL:createTableSQL];}- (BOOL)execSQL:(NSString *)sql{    // 执行sql语句    // 1> 参数一:数据库sqlite3对象    // 2> 参数二:执行的sql语句    return sqlite3_exec(self.db, sql.UTF8String, nil, nil, nil) == SQLITE_OK;}#pragma mark - 查询数据- (NSArray *)querySQL:(NSString *)querySQL{    // 定义游标对象    sqlite3_stmt *stmt = nil;    // 准备工作(获取查询的游标对象)    // 1> 参数三:查询语句的长度, -1自动计算    // 2> 参数四:查询的游标对象地址    if (sqlite3_prepare_v2(self.db, querySQL.UTF8String, -1, &stmt, nil) != SQLITE_OK) {        NSLog(@"没有准备成功");        return nil;    }    // 取出某一个行数的数据    NSMutableArray *tempArray = [NSMutableArray array];    // 获取字段的个数    int count = sqlite3_column_count(stmt);    while (sqlite3_step(stmt) == SQLITE_ROW) {        NSMutableDictionary *dict = [NSMutableDictionary dictionary];        for (int i = 0; i < count; i++) {            // 1.取出当前字段的名称(key)            NSString *key = [NSString stringWithUTF8String:sqlite3_column_name(stmt, i)];            // 2.取出当前字段对应的值(value)            const char *cValue = (const char *)sqlite3_column_text(stmt, i);            NSString *value = [NSString stringWithUTF8String:cValue];            // 3.将键值对放入字典中            [dict setObject:value forKey:key];        }        [tempArray addObject:dict];    }    // 不再使用游标时,需要释放对象    sqlite3_finalize(stmt);    return tempArray;}@end

5.Student.h

#import <Foundation/Foundation.h>@interface Student : NSObject@property (nonatomic, copy) NSString *name;@property (nonatomic, assign) NSInteger age;- (instancetype)initWithDict:(NSDictionary *)dict;- (void)insertStudent;+ (NSArray *)loadData;+ (NSArray *)loadDataWithKeyword:(NSString *)keyword;@end

6.Student.m

#import "Student.h"#import "SQLiteManager.h"@implementation Student- (instancetype)initWithDict:(NSDictionary *)dict{    if (self = [super init]) {        [self setValuesForKeysWithDictionary:dict];    }    return self;}- (void)insertStudent{    // 1.通过属性拼接出来,插入语句    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO t_student (name, age) VALUES ('%@', %ld);", self.name, self.age];    // 2.执行该sql语句    if ([[SQLiteManager shareInstance] execSQL:insertSQL]) {        NSLog(@"插入数据成功");    }}+ (NSArray *)loadData{    // 1.封装查询语句    NSString *querySQL = @"SELECT name, age FROM t_student;";    return [self loadDataWithQuerySQL:querySQL];}+ (NSArray *)loadDataWithKeyword:(NSString *)keyword{    // 1.封装查询语句    NSString *querySQL = [NSString stringWithFormat:@"SELECT name, age FROM t_student WHERE name like '%%%@%%';", keyword];    return [self loadDataWithQuerySQL:querySQL];}+ (NSArray *)loadDataWithQuerySQL:(NSString *)querySQL{    // 2.执行查询语句    NSArray *dictArray = [[SQLiteManager shareInstance] querySQL:querySQL];    // 3.将数组中的字典转成模型对象    NSMutableArray *stus = [NSMutableArray array];    for (NSDictionary *dict in dictArray) {        [stus addObject:[[Student alloc] initWithDict:dict]];    }    return stus;}@end