IOS-数据存储之SQLITE简单的使用

数据存储之SQLITE简单的使用

ios中sqlite是一种轻量级的存储数据库，它的使用和mysql或者sqlserver等关系型数据库的使用方式相似。下面介绍sqlite使用结构流程。

先看下总体的效果图

第一个主界面 主要用来显示数据库中的数据使用tableview来展示

- 为第二个界面 用来向数据库中添加数据，数据名称为学生学号和学生姓名

- 第三个 为显示数据的主界面

————————————–华丽的分割线————————————-

代码如下

模型代码

#import <Foundation/Foundation.h>

@interface StudentModel : NSObject
@property(nonatomic,copy) NSString *stuNum;//学号
@property(nonatomic,copy) NSString *stuName;//姓名
@end

数据库管理代码

#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "StudentModel.h"
@interface SQLManager : NSObject
{
    sqlite3 *db;
}
+(SQLManager*)shareManager;
-(StudentModel*)searchWithID:(StudentModel*)model;
-(int)insert:(StudentModel*)model;
@end



//
//  SQLManager.m
//  Sqlite
//
//  Created by mac on 2018/4/20.
//  Copyright © 2018年 Gooou. All rights reserved.
//

#import "SQLManager.h"
#define kFileName (@"Student.sqlite")
@implementation SQLManager

static SQLManager *manager=nil;
+(SQLManager*)shareManager
{
    //只执行一次，线程安全类型
    static dispatch_once_t once;
    dispatch_once(&once, ^{
        manager=[[self alloc]init];
        //如果需要的时候创建数据库
        [manager createDataBaseTableIfNeeded];
    });
    return manager;
}
//获取数据库路径并返回
-(NSString *)applicationDocumentsDirectoryFile
{
    NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDirectory=[paths firstObject];
    //路径进行拼接
    NSString *filePath=[documentDirectory stringByAppendingPathComponent:kFileName];
    return filePath;
}
-(void)createDataBaseTableIfNeeded
{
    NSString *writeablePath=[self applicationDocumentsDirectoryFile];
    NSLog(@"数据库的地址是 :%@",writeablePath);
    if (sqlite3_open([writeablePath UTF8String], &db)!=SQLITE_OK) {
        //打开失败
        sqlite3_close(db);
        NSAssert(NO, @"打开数据库失败");
    }
    else
    {
        char *err;
        NSString *cretaeSQL=[NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS StudentName(idNum TEXT PRIMARY KEY,name TEXT);"];
        //[cretaseSQL UTF8String]为将NSString转换为c语言的char*
        if (sqlite3_exec(db, [cretaeSQL UTF8String], NULL, NULL, &err)!=SQLITE_OK) {
            //失败
            sqlite3_close(db);
            NSAssert1(NO, @"建表失败!    %s",err);
        }
        sqlite3_close(db);
    }
}
//根据模型查询数据库
-(StudentModel*)searchWithID:(StudentModel*)model
{
    NSString *path=[self applicationDocumentsDirectoryFile];
    if (sqlite3_open([path UTF8String], &db)!=SQLITE_OK) {
        sqlite3_close(db);
        NSAssert(NO, @"打开失败!");
    }else
    {
        NSString *qsql=@"SELECT idNum,name FROM StudentName where idNum=?";
        sqlite3_stmt *statement;//语句对象,-1代表全部执行
        if (sqlite3_prepare(db, [qsql UTF8String], -1, &statement, NULL)==SQLITE_OK) {//预处理
            NSString *idNum=model.stuNum;
            sqlite3_bind_text(statement,1, [idNum UTF8String], -1, NULL);
            if (sqlite3_step(statement)==SQLITE_ROW) {
                char *idNum=(char*)sqlite3_column_text(statement, 0);
                //将char转换为NSString
                NSString *idNumStr=[[NSString alloc]initWithUTF8String:idNum];
                char *name=(char*)sqlite3_column_text(statement, 1);
                //将char转换为NSString
                NSString *nameStr=[[NSString alloc]initWithUTF8String:name];
                StudentModel *studentModel=[[StudentModel alloc]init];
                studentModel.stuNum=idNumStr;
                studentModel.stuName=nameStr;
                sqlite3_finalize(statement);
                sqlite3_close(db);
                return studentModel;
            }
        }
        sqlite3_finalize(statement);
        sqlite3_close(db);
    }
    return nil;
}
//修改数据库
-(int)insert:(StudentModel*)model
{
    NSString *path=[self applicationDocumentsDirectoryFile];
    if (sqlite3_open([path UTF8String], &db)!=SQLITE_OK) {
        sqlite3_close(db);
         NSAssert(NO, @"数据库打开失败！");
    }else
    {
        NSString *sql=@"INSERT OR REPLACE INTO StudentName(idNum,name) VALUES(?,?)";
        sqlite3_stmt *statement;//语句对象,-1代表全部执行
        if (sqlite3_prepare(db, [sql UTF8String], -1, &statement, NULL)==SQLITE_OK) {//预处理
            NSString *idNum=model.stuNum;
            NSString *name=model.stuName;
            sqlite3_bind_text(statement,1, [idNum UTF8String], -1, NULL);
            sqlite3_bind_text(statement,2, [name UTF8String], -1, NULL);
            if (sqlite3_step(statement)!=SQLITE_DONE) {
                NSAssert(NO, @"插入数据失败！");
            }
            sqlite3_finalize(statement);
            sqlite3_close(db);
        }
    }
    return 0;
}
@end

显示数据控制器

//
//  HomeTableViewController.m
//  Sqlite
//
//  Created by mac on 2018/4/20.
//  Copyright © 2018年 Gooou. All rights reserved.
//

#import "HomeTableViewController.h"
#import "StudentModel.h"
#import "SQLManager.h"

#define StudentCellIdentifier (@"studentCellIdentifier")

@interface HomeTableViewController ()
@property(nonatomic,strong) NSMutableArray *studentMutableArray;
@end

@implementation HomeTableViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    //数据源进行初始化
    self.studentMutableArray=[[NSMutableArray alloc]init];

}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
}

#pragma mark - Table view data source

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
    return 1;
}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
    return self.studentMutableArray.count;
}

//使用的是系统的cell
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:StudentCellIdentifier forIndexPath:indexPath];
    //对cell进行赋值
    if (_studentMutableArray.count>0) {
        StudentModel *model=[self.studentMutableArray objectAtIndex:indexPath.row];//取出模型
        cell.textLabel.text=model.stuNum;
        cell.detailTextLabel.text=model.stuName;
    }

    return cell;
}

//表格支持编辑功能
- (BOOL)tableView:(UITableView *)tableView canEditRowAtIndexPath:(NSIndexPath *)indexPath {

    return YES;
}

//返回cell高度
-(CGFloat)tableView:(UITableView *)tableView heightForRowAtIndexPath:(NSIndexPath *)indexPath
{
    return 50.f;
}
-(IBAction)addUserDone:(UIStoryboardSegue*)sender
{
    StudentModel *model=[[StudentModel alloc]init];
    model.stuNum=@"100";
    StudentModel *result= [[SQLManager shareManager]searchWithID:model];
    [_studentMutableArray addObject:result];
    [self.tableView reloadData];
}
/*
// Override to support editing the table view.
- (void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath {
    if (editingStyle == UITableViewCellEditingStyleDelete) {
        // Delete the row from the data source
        [tableView deleteRowsAtIndexPaths:@[indexPath] withRowAnimation:UITableViewRowAnimationFade];
    } else if (editingStyle == UITableViewCellEditingStyleInsert) {
        // Create a new instance of the appropriate class, insert it into the array, and add a new row to the table view
    }   
}
*/

/*
// Override to support rearranging the table view.
- (void)tableView:(UITableView *)tableView moveRowAtIndexPath:(NSIndexPath *)fromIndexPath toIndexPath:(NSIndexPath *)toIndexPath {
}
*/

/*
// Override to support conditional rearranging of the table view.
- (BOOL)tableView:(UITableView *)tableView canMoveRowAtIndexPath:(NSIndexPath *)indexPath {
    // Return NO if you do not want the item to be re-orderable.
    return YES;
}
*/

/*
#pragma mark - Navigation

// In a storyboard-based application, you will often want to do a little preparation before navigation
- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender {
    // Get the new view controller using [segue destinationViewController].
    // Pass the selected object to the new view controller.
}
*/

@end

添加数据控制器

//
//  AddViewController.m
//  Sqlite
//
//  Created by mac on 2018/4/20.
//  Copyright © 2018年 Gooou. All rights reserved.
//

#import "AddViewController.h"
#import "StudentModel.h"
#import "SQLManager.h"

@interface AddViewController ()
@property (strong, nonatomic) IBOutlet UITextField *stuNum;
@property (strong, nonatomic) IBOutlet UITextField *stuName;

@end

@implementation AddViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view.
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}
-(void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender
{
    if ([segue.identifier isEqualToString:@"AddUser"]) {
        //确认进行跳转之前向数据库写数据
        StudentModel *model=[[StudentModel alloc]init];
        model.stuName=self.stuName.text;
        model.stuNum=self.stuNum.text;
        [[SQLManager shareManager]insert:model];
    }
}
/*
#pragma mark - Navigation

// In a storyboard-based application, you will often want to do a little preparation before navigation
- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender {
    // Get the new view controller using [segue destinationViewController].
    // Pass the selected object to the new view controller.
}
*/

@end