UI数据库

建立完工程后往里面拖入一个文件.

2 targets, iOS SDK 8.1 -> Build Phases -> Link Binary With Libraries (1 item),展开箭头,选择’+’,输入sql,选择libsqlite3.0.dylib.

1.创建一个Single View Application,在ViewController.m中拖拽进去七个按钮.

//
//  ViewController.m
//  UI19_数据库
//
//  Created by dlios on 15-8-24.
//  Copyright (c) 2015年 sOldiers. All rights reserved.
//

#import "ViewController.h"
#import "dataBaseTool.h"

@interface ViewController ()

- (IBAction)creatDataBase:(id)sender;
- (IBAction)creatTable:(id)sender;
- (IBAction)insertData:(id)sender;
- (IBAction)updataData:(id)sender;
- (IBAction)deleteData:(id)sender;
- (IBAction)selectData:(id)sender;
- (IBAction)closeDataBase:(id)sender;

@end

@implementation ViewController

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

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}


- (IBAction)creatDataBase:(id)sender {    
    // 用单例对象调用打开数据库的方法.
    [[dataBaseTool shareDataBaseTool] openDB];
}

- (IBAction)creatTable:(id)sender {
    [[dataBaseTool shareDataBaseTool] createTable]; 
}

- (IBAction)insertData:(id)sender {
    Student *stu = [[Student alloc] init];
    stu.name = @"伙计";
    stu.age = 20;
    stu.sex = @"男";
    stu.hobby = @"鹰王";
    [[dataBaseTool shareDataBaseTool] insertStu:stu]; 
}

- (IBAction)updataData:(id)sender {
    Student *stu = [[Student alloc] init];
    stu.name = @"杜甫";
    stu.sex = @"女";
    stu.hobby = @"跳舞";
    stu.age = 45;
    [[dataBaseTool shareDataBaseTool] updateStu:stu];
}

- (IBAction)deleteData:(id)sender {
    Student *stu = [[Student alloc] init];
    stu.name = @"杜甫";
    [[dataBaseTool shareDataBaseTool] deleteStu:stu];
}

- (IBAction)selectData:(id)sender {
    NSMutableArray *arr = [[dataBaseTool shareDataBaseTool] selectAllStu];
    for (Student *stu in arr) {
        NSLog(@"%@", stu.name);
    }
}

- (IBAction)closeDataBase:(id)sender {
    [[dataBaseTool shareDataBaseTool] closeDB];
}
@end

2.创建一个Student的类,在Student.h中写入四个属性

//
//  Student.h
//  UI19_数据库
//
//  Created by dlios on 15-8-24.
//  Copyright (c) 2015年 sOldiers. All rights reserved.
//

#import <Foundation/Foundation.h>

@interface Student : NSObject

@property(nonatomic, copy)NSString *name;
@property(nonatomic, copy)NSString *sex;
@property(nonatomic, copy)NSString *hobby;
@property(nonatomic, assign)NSInteger age;

@end

3.创建一个dataBaseTool工具类.

//
//  dataBaseTool.h
//  UI19_数据库
//
//  Created by dlios on 15-8-24.
//  Copyright (c) 2015年 sOldiers. All rights reserved.
//

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Student.h"

@interface dataBaseTool : NSObject
{
    // 用来保存数据库对象的地址.
    sqlite3 *dbPoint;
}

// 为了保证当前数据库在工程里唯一的,我们用单例的方式创建一个数据库工具对象.
+ (dataBaseTool *)shareDataBaseTool;

// 打开数据库.
- (void)openDataBase;

/ 数据库中创建表,table
- (void)createTable;

// 插入数据.
- (void)insertStu:(Student *)stu;

// 更新数据.
- (void)updateStu:(Student *)stu;

// 删除数据.
- (void)deleteStu:(Student *)stu;

// 查询数据.
- (NSMutableArray *)selectAllStu;

// 关闭数据库.
- (void)closeDB;

4.在dataBaseTool.m中实现上述方法.

//
//  dataBaseTool.m
//  UI19_数据库
//
//  Created by dlios on 15-8-24.
//  Copyright (c) 2015年 sOldiers. All rights reserved.
//

#import "dataBaseTool.h"

@implementation dataBaseTool

// 实现单例的方法.
+ (dataBaseTool *)shareDataBaseTool
{
    static dataBaseTool *tool;
    static dispatch_once_t oneToken;
    dispatch_once(&oneToken, &{
        tool = [[dataBaseTool alloc] init];
    });
    return tool;`
}

// 实现打开数据库的方法.
- (void)openDataBase
{
    // 数据库文件也保存在沙盒的document文件里,所以先找沙盒路径.
    NSArray *sandBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *sandBoxPath = sandBox[0];

    // 拼接文件路径,如果系统根据这个文件路径查找的时候有对应文件则直接打开数据库,如果没有则会创建一个新的数据库.
    NSString *documentPath = [sandBoxPath stringByAppendingPathComponent:@"Student.sqlite"];

    int result = sqlite3_open([documentPath UTF8String], &dbPoint);

    if (result == SQLITE_OK) {
        NSLog(@"创建数据库成功!~");
        NSLog(@"%@",documentPath);
    } else {
        NSLog(@"创建数据库失败!~");

    }
}

// 在数据库中创建表的方法.
- (void)createTable
{
    // primary key 主键,主键在当前表里数据是唯一的,不能重复,,可以唯一标示一条数据,一般是整数.
    // autoincrement自增,为了让主键不重复,会让主键采用自增的方式.
    // if notexists,如果没有表才会创建,防止重复创建覆盖之前数据.
    // 数据库问题90%是SQL语句出问题,所以先保证语句没问题,再放到工程中使用.
    NSString *sqlStr = @"create table if not exists stu(number integer primary key autoincrement, name text, sex text, age integer, hobby text)";

    // 执行sql语句
    if (result == SQLITE_OK) {
        NSLog(@"表创建成功!~");
    } else {
        NSLog(@"表创建失败!~");
    }
}

// 插入数据方法.
- (void)insertStu:(Student *)stu
{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into stu (name, age, sex, hobby) values ('%@', '%ld', '%@', '%@')", stu.name, stu.age, stu.sex, stu.hobby];

    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入数据成功!~");
    } else {
        NSLog(@"插入数据失败!~");
    }
}

// 更新数据方法
- (void)updataStu:(Student *)stu
{
    NSString *sqlStr = [NSString stringWithFormat:@"update stu set sex = '%@', hobby = '%@', age = '%ld'  where name = '%@'", stu.sex, stu.hobby, stu.age, stu.name];

    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新数据成功!~");
    } else {
        NSLog(@"更新数据失败!~");
    }
}

// 删除数据方法
- (void)deleteStu:(Student *)stu
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from stu where name = '%@'", stu.name];

    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除数据成功!~");
    } else {
        NSLog(@"删除数据失败!~");
    }
}

// 查询数据方法
- (NSMutableArray *)selectAllStu
{
    // 查询的逻辑
    // 1.先从本地的数据库中读取某张表里的所有数据.
    // 2.然后逐条进行读取,对model进行赋值.
    // 3.把已经赋值好的model放到数组中,并且返回.
    NSString *sqlStr = @"select * form stu";

    // 在语句里*是通配符的意思,通过一个*相当于代替了表里的所有的字段名.
    // 接下来需要定义一个跟随指针,它用来遍历数据库表中得每行数据.
    sqlite3_stmt *stmt = nil;

    // 第三个参数:查询语句字数限制,-1是没有限制.
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);

    // 这个相当于把数据库和跟随指针相关联,一同完成查询功能.
    // 初始化一个用来装学生的数组.
    NSMutableArray *stuArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        // 开始遍历查询数据库的每一行数据.
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 让跟随指针进行遍历查询,如果没有行,才会停止循环
            // 满足条件,则逐列读取内容.
            // 第二个参数表示当前这列数据在表的第几列
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            const unsigned char *hobby = sqlite3_column_text(stmt, 4);

            // 把列里的数据再进行转换.
            NSInteger stuAge = age;
            NSString *stuName = [NSString stringWithUTF8String:(const char *)name];
            NSString *stuSex = [NSString stringWithUTF8String:(const char *)sex];
            NSString *stuHobby = [NSString stringWithUTF8String:(const char *)hobby];

            // 然后对象赋值,然后把对象放到数组里.
            Student *stu = [[Student alloc] init];
            stu.name = stuName;
            stu.sex = stuSex;
            stu.age = stuAge;
            stu.hobby = stuHobby;
            [stuArr addObject:stu];
            [stu release];
        }
    } else {
        NSLog(@"查询失败");
        NSLog(@"%d", result);
    }
    return stuArr;
}

// 关闭数据库方法.
- (void)closeDB
{
    int result = sqlite3_close(dbPoint);

    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功!~");

    } else {
        NSLog(@"数据库关闭失败!~");
    }
}