数据库的连接

**在这个项目中实现了从数据库中获取数据，往数据库添加数据、修改数据、删除数据等功能。
实现的过程中要注意的是sql语句是正确表达，模型类和数据库中表的属性的正确连接。学会用block传值等。**

在创建项目之后先导入libsqlite3.0.tbd这个库

首先创建一个ViewController
在storyBoard中创建如图

#import "ViewController.h"
#import "UserModel.h"
#import "UserDataBase.h"
#import "AddViewController.h"
#import "UpdateViewController.h"
#import "TBCell.h"
@interface ViewController () <UITableViewDelegate,UITableViewDataSource>

@property (strong, nonatomic) IBOutlet UITableView *tbView;

@property (strong,nonatomic) NSMutableArray *array;

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    NSLog(@"主界面%@",self);

    [self loadData];

    //设置表视图的背景颜色
    _tbView.backgroundColor = [UIColor whiteColor];

    //注册一个单元格
    [_tbView registerNib:[UINib nibWithNibName:@"TBCell" bundle:nil] forCellReuseIdentifier:@"TBCell"];

}
- (void) loadData {

    UserDataBase *manage = [UserDataBase shareManage];

    _array = [NSMutableArray arrayWithArray:[manage selectDataBase]];


}
- (IBAction)editData:(UIButton *)sender {
    //获取当前tbView是编辑状态
    BOOL isEdit = self.tbView.editing;

    //判断
    if (isEdit) {

        [self.tbView setEditing:NO animated:YES];

        [sender setTitle:@"Edit" forState:UIControlStateNormal];

    } else {
        [self.tbView setEditing:YES animated:YES];

        [sender setTitle:@"Done" forState:UIControlStateSelected];


    }
    sender.selected = !sender.selected;

}

- (IBAction)AddModel:(id)sender {
    UIStoryboard *storyBoard = [UIStoryboard storyboardWithName:@"Main" bundle:nil];

    AddViewController *addVC = [storyBoard instantiateViewControllerWithIdentifier:@"AddViewController"];
    [addVC pressModel:^(UserModel *model) {

        UserDataBase *manage = [UserDataBase shareManage];
        [manage addDataWith:model];


    }];

    [self.navigationController pushViewController:addVC animated:YES];

}

- (IBAction)refresh:(id)sender {

    [self loadData];

    [_tbView reloadData];
}
#pragma mark dataSource

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
    return _array.count;
}
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

    TBCell *cell = [tableView dequeueReusableCellWithIdentifier:@"TBCell"];

    cell.backgroundColor = [UIColor clearColor];
    UserModel *model = _array[indexPath.row];

    cell.model = model;

    return cell;
}
- (void) tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
    //修改数据
    UIStoryboard *story = [UIStoryboard storyboardWithName:@"Main" bundle:nil];
    AddViewController *updateVC = [story instantiateViewControllerWithIdentifier:@"UpdateViewController"];


    updateVC.model = _array[indexPath.row];


    [updateVC pressModel:^(UserModel *model) {

        UserDataBase *manage = [UserDataBase shareManage];
        [manage updateData:model];

    }];
    [self.navigationController pushViewController:updateVC animated:YES];

}

- (void) tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath {
    if (editingStyle == UITableViewCellEditingStyleDelete) {

        //获取这一行的model
        UserModel *model = _array[indexPath.row];

        UserDataBase *manage = [UserDataBase shareManage];
        [manage deleteData:model];


        //删除这一行的数据
        [_array removeObjectAtIndex: indexPath.row];
        [tableView deleteRowsAtIndexPaths:[NSArray arrayWithObject:indexPath]
                         withRowAnimation:UITableViewRowAnimationFade];

    }
}
@end

创建一个TableCell命名为TBCell
用xib文件创建如图：

#import "TBCell.h"
#import "UserModel.h"
@implementation TBCell

- (void) setModel:(UserModel *)model {
    _model = model;

    name.text = model.name;
    age.text = [NSString stringWithFormat:@"%ld",model.age];    
}
@end

增加数据页面,命名为AddViewController
在storyboard中创建如图：

//.h文件
#import <UIKit/UIKit.h>
#import "UserModel.h"
//定义一个block
typedef void(^modelValue)(UserModel *model);

@interface AddViewController : UIViewController

@property (nonatomic,copy) modelValue block;

@property (nonatomic,weak) UserModel *model;

- (void) pressModel:(modelValue )block;

@end
//.m文件
#import "AddViewController.h"
#import "UserModel.h"

@interface AddViewController () {


    __weak IBOutlet UITextField *age;
    __weak IBOutlet UITextField *name;
    __weak IBOutlet UITextField *pwd;
}


@end

@implementation AddViewController

- (void)viewDidLoad {
    [super viewDidLoad];


    self.navigationItem.leftBarButtonItem = [[UIBarButtonItem alloc] initWithTitle:@"返回" style:UIBarButtonItemStylePlain target:self action:@selector(backAction:)];
}

- (void) pressModel:(modelValue )block {
     self.block = block;
}
- (void)backAction:(UIBarButtonItem *)bar {

    UserModel *model = [[UserModel alloc] init];

    model.id = model.id++;
    model.name = name.text;
    model.pwd = pwd.text;
    model.age = [age.text integerValue];

    self.model = model;
    //  NSLog(@"名字%@",_model.name);

    //block回调
    self.block(self.model);

    [self.navigationController popViewControllerAnimated:YES];
}
@end

修改数据页面,命名为UpdateViewController
在storyBoard中创建如图：

//.h文件
#import <UIKit/UIKit.h>
#import "UserModel.h"
//定义一个block
typedef void(^modelValue)(UserModel *model);

@interface UpdateViewController : UIViewController

@property (nonatomic,copy) modelValue block;

@property (nonatomic,weak) UserModel *model;

- (void) pressModel:(modelValue )block;
@end
//.m文件
#import "UpdateViewController.h"

@interface UpdateViewController () {
    __weak IBOutlet UITextField *age;
    __weak IBOutlet UITextField *name;
    __weak IBOutlet UITextField *pwd;
}

@end

@implementation UpdateViewController

- (void)viewDidLoad {
    [super viewDidLoad];

    self.title = @"修改界面";

    self.navigationItem.leftBarButtonItem = [[UIBarButtonItem alloc] initWithTitle:@"返回" style:UIBarButtonItemStylePlain target:self action:@selector(backAction:)];

    name.text = [NSString stringWithFormat:@"%@",self.model.name];
    pwd.text =  [NSString stringWithFormat:@"%@",self.model.pwd];
    age.text = [NSString stringWithFormat:@"%ld",self.model.age];
    }

- (void) pressModel:(modelValue )block {
    self.block = block;
}
- (void)backAction:(UIBarButtonItem *)bar {

    UserModel *model = [[UserModel alloc] init];

    model.id = self.model.id;
    model.name = name.text;
    model.pwd = pwd.text;
    model.age = [age.text integerValue];

    self.model = model;
    //  NSLog(@"名字%@",_model.name);

    //block回调
    self.block(self.model);

    [self.navigationController popViewControllerAnimated:YES];
}
@end

模型类:UserModel

.h文件
#import <Foundation/Foundation.h>

@interface UserModel : NSObject

//创建属性
@property (nonatomic,assign)NSInteger id;
@property (nonatomic,copy)NSString *name;
@property (nonatomic,copy)NSString *pwd;
@property (nonatomic,assign) NSInteger age;

@end

从数据库中获取数据的类:UserDataBase

.h文件
#import <Foundation/Foundation.h>
#import "UserModel.h"
@interface UserDataBase : NSObject

//利用类方法来创建单例
+(UserDataBase *)shareManage;

//创建方法来获取数据库中的数据
- (NSArray *) selectDataBase;

//增加数据
- (BOOL)addDataWith:(UserModel *)model;

//更新数据
- (BOOL) updateData:(UserModel *)model;

//删除数据
- (BOOL) deleteData:(UserModel *) model;
@end
.m文件
#import "UserDataBase.h"
#import <sqlite3.h>
#import "UserModel.h"

static UserDataBase *instance = nil;
//创建一个打开的数据库
sqlite3 *sqlite = nil;

@implementation UserDataBase

//利用类方法来创建单例
+(UserDataBase *)shareManage {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[UserDataBase alloc] init];

        [instance addFileWithShadBox];
    });

    return instance;
}

//创建方法将数据库添加到沙盒中
- (void) addFileWithShadBox {
    NSLog(@"%@",NSHomeDirectory());

    //创建一个文件管理着
    NSFileManager *manager = [NSFileManager defaultManager];

    //创建沙盒中的路径
    NSString *topath = [self filePath];

    //获取数据库
    NSString *path = [[NSBundle mainBundle] pathForResource:@"dbData" ofType:@"sqlite"];

    //先判断文件是否存在
    if ([manager fileExistsAtPath:topath]) {
        return;
    }
    //复制文件
    [manager copyItemAtPath:path toPath:topath error:NULL];
}
//创建方法来获取数据库的路径
- (NSString *) filePath {
    return [NSHomeDirectory() stringByAppendingString:@"/Documents/User.sqlite"];

}

//删除数据
- (BOOL) deleteData:(UserModel *) model {

    //1)打开数据库
    //获取数据库的位置
    NSString *path = [self filePath];
    int openResult = sqlite3_open([path UTF8String], &sqlite);
    if (openResult != SQLITE_OK) {
        NSLog(@"打开失败");
        return NO;
    }
    //2、准备数据库
    //查询语句
    NSString *statement = @"delete from User where id = ?";

    //创建参数
    sqlite3_stmt *stmt = nil;

    sqlite3_prepare_v2(sqlite, [statement UTF8String], -1, &stmt, NULL);


    //条件语句
    sqlite3_bind_int(stmt, 1,(int)model.id);



    //3、执行语句

    int  result =  sqlite3_step(stmt);
    if (result != SQLITE_OK && result!= SQLITE_DONE) {
        NSLog(@"执行语句失败");
        return NO;
    }

    //结束执行语句
    sqlite3_finalize(stmt);

    //关闭数据库
    sqlite3_close(sqlite);

    return YES;


}


//更新数据
- (BOOL) updateData:(UserModel *)model{
    //1)打开数据库
    //获取数据库的位置
    NSString *path = [self filePath];
    int openResult = sqlite3_open([path UTF8String], &sqlite);
    if (openResult != SQLITE_OK) {
        NSLog(@"打开失败");
        return NO;
    }
    //2、准备数据库
    //查询语句
    NSString *statement = @"update User set name=?,pwd=?,age=? where id=?";

    //创建参数
    sqlite3_stmt *stmt = nil;

    sqlite3_prepare_v2(sqlite, [statement UTF8String], -1, &stmt, NULL);


    //条件语句
    sqlite3_bind_int(stmt, 4,(int)model.id);

    //参数绑定
    sqlite3_bind_text(stmt, 1, [model.name UTF8String], -1,NULL);
    sqlite3_bind_text(stmt, 2, [model.pwd UTF8String], -1, NULL);
    sqlite3_bind_int(stmt, 3, (int)model.age);

    //3、执行语句

        int  result =  sqlite3_step(stmt);
        if (result != SQLITE_OK && result!= SQLITE_DONE) {
            NSLog(@"执行语句失败");
            return NO;
    }

    //结束执行语句
    sqlite3_finalize(stmt);

    //关闭数据库
    sqlite3_close(sqlite);

    return YES;
}

//增加数据
- (BOOL)addDataWith:(UserModel *)model {
    //1)打开数据库
    //获取数据库的位置
    NSString *path = [self filePath];
    int openResult = sqlite3_open([path UTF8String], &sqlite);

    if (openResult != SQLITE_OK) {
        NSLog(@"打开失败");
        return NO;
    }
    //2、准备数据库
    //增加语句
    NSString *statement = @"insert into User(name,pwd,age)values (?,?,?)";

    //创建参数
    sqlite3_stmt *stmt = nil;

    sqlite3_prepare_v2(sqlite, [statement UTF8String], -1, &stmt, NULL);

    //参数绑定
//    int  ID = 1000;
//    sqlite3_bind_int(stmt, 1, ID++);
    sqlite3_bind_text(stmt, 1, [model.name UTF8String], -1,NULL);
    sqlite3_bind_text(stmt, 2, [model.pwd UTF8String], -1, NULL);
    sqlite3_bind_int(stmt, 3, (int)model.age);

    //3、执行语句
    int  result =  sqlite3_step(stmt);
    if (result != SQLITE_OK && result!= SQLITE_DONE) {
        NSLog(@"执行语句失败");
        return NO;
    }
    //结束执行语句
    sqlite3_finalize(stmt);

    //关闭数据库
    sqlite3_close(sqlite);

    return YES;
}

//创建方法来获取数据库中的数据
- (NSArray *) selectDataBase {

    //创建一个可变数组
    NSMutableArray *array = [NSMutableArray array];

    /*
     1、打开数据库
     2、准备数据库
     3、执行语句
     4、结束语句
     5、关闭数据库
     */
    //1)打开数据库
    //获取数据库的位置
    NSLog(@"%@",NSHomeDirectory());


    NSString *path = [self filePath];
    int openResult = sqlite3_open([path UTF8String], &sqlite);
    if (openResult != SQLITE_OK) {
        NSLog(@"打开失败");
        return nil;
    }
    //2、准备数据库
    //查询语句
    NSString *statement = @"select * from  User";

    //创建参数
    sqlite3_stmt *stmt = nil;



    sqlite3_prepare_v2(sqlite, [statement UTF8String], -1, &stmt, NULL);

    //3、执行语句
   int  result =  sqlite3_step(stmt);

    //判断执行后的数据
    while (result == SQLITE_ROW) {

        int id = sqlite3_column_int(stmt, 0);
        char *name = (char *) sqlite3_column_text(stmt, 1);
        char *pwd = (char *)sqlite3_column_text(stmt, 2);
        int age = sqlite3_column_int(stmt, 3);

        UserModel *model = [[UserModel alloc] init];
        model.id = id;
        model.name = [NSString stringWithUTF8String:name];
        model.pwd = [NSString stringWithUTF8String:pwd];
        model.age = age;

        [array addObject:model];

        result = sqlite3_step(stmt);
    }
    //结束执行语句
    sqlite3_finalize(stmt);

    //关闭数据库
    sqlite3_close(sqlite);

    return array;
}
@end

完整代码连接：
http://note.youdao.com/yws/public/redirect/share?id=3b0b52ab6e36f7ae00f345830bad2348&type=false
运行结果图如下：