iOS UI 18 数据库

//

//  RootViewController.m

//  Ui - 18 数据库

//

//  Created by dllo on 15/12/2.

//  Copyright (c) 2015年 dllo. All rights reserved.

//

#import "RootViewController.h"

#import "DataBasehandle.h"

#import "Student.h"

@interface RootViewController ()

@property (retain, nonatomic) IBOutlet UIButton *button;

@property (retain, nonatomic) IBOutlet UIButton *button1;

@property (retain, nonatomic) IBOutlet UIButton *button2;

@property (retain, nonatomic) IBOutlet UIButton *button3;

@property (retain, nonatomic) IBOutlet UIButton *insert;

@property (retain, nonatomic) IBOutlet UIButton *updat;

@property (retain, nonatomic) IBOutlet UIButton *deleteda;

@property (retain, nonatomic) IBOutlet UIButton *button4;

@property (retain, nonatomic) IBOutlet UIButton *button5;

@end

@implementation RootViewController

- (void)viewDidLoad {

    [super viewDidLoad];

    NSLog(@"%@",NSHomeDirectory());

    

    

    

    // Do any additional setup after loading the view from its nib.

}

//打开数据库

- (IBAction)button:(id)sender {

    

    [[DataBasehandle sharedDatabase] openDB];

    

    

    

}

//关闭数据库

- (IBAction)button1:(id)sender {

    

    [[DataBasehandle sharedDatabase] closeDB];

}

//创建表单

- (IBAction)button2:(id)sender {

    

     [[DataBasehandle sharedDatabase] createTable];

    

    

}

//删除表单

- (IBAction)button3:(id)sender {

    

     [[DataBasehandle sharedDatabase] deleteTable];

    

}

//插入数据

- (IBAction)insert:(id)sender {

    

    Student *stu = [[Student alloc]init];

    stu.name = @"dfsa";

    stu.sex = @"fdsf";

    stu.age = 1;

      [[DataBasehandle sharedDatabase] insertDataWithStudent:stu];

    

}

//修改数据

- (IBAction)updat:(id)sender {

    

    Student *stu = [[Student alloc]init];

    stu.name = @"小平好帅";

     stu.sex = @"男";

    stu.age = 100;

     [[DataBasehandle sharedDatabase] updataWithNumber:4 Student:stu];

    

}

//删除数据

- (IBAction)deleteda:(id)sender {

    

    

    [[DataBasehandle sharedDatabase] deleteDataWithNumber:2];

    

    

    

}

//查询所有数据

- (IBAction)button4:(id)sender {

    NSMutableArray *stuArr = [[DataBasehandle sharedDatabase] selectAllStudent];

    for (Student *stu in stuArr) {

        NSLog(@"%@ %@ %ld", stu.name, stu.sex, stu.age);

    }

    

    

}

- (IBAction)button5:(id)sender {

    NSMutableArray *stuArr = [[DataBasehandle sharedDatabase]selectBySexOfStudent:@"男" name:@"小平好帅"];

    for (Student *stu in stuArr) {

        NSLog(@"%@ %@ %ld", stu.name, stu.sex, stu.age);

    }

}

- (void)didReceiveMemoryWarning {

    [super didReceiveMemoryWarning];

    // Dispose of any resources that can be recreated.

}

/*

#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.

}

*/

- (void)dealloc {

    [_button release];

    [_button1 release];

    [_button2 release];

    [_button3 release];

    [_insert release];

    [_updat release];

    [_deleteda release];

    [_button4 release];

    [_button5 release];

    [super dealloc];

}

@end

//

//  DataBasehandle.h

//  Ui - 18 数据库

//

//  Created by dllo on 15/12/2.

//  Copyright (c) 2015年 dllo. All rights reserved.

//

#import <Foundation/Foundation.h>

@class Student;

@interface DataBasehandle : NSObject

+(instancetype)sharedDatabase;

- (void)openDB;

- (void)closeDB;

- (void)createTable;

- (void)deleteTable;

- (void)insertDataWithStudent:(Student *)stu;

- (void)deleteDataWithNumber:(NSInteger)num;

- (void)updataWithNumber:(NSInteger)num Student:(Student *)stu;

- (NSMutableArray *)selectAllStudent;

- (NSMutableArray *)selectBySexOfStudent:(NSString *)sex name :(NSString *)name;

@end

//

//  DataBasehandle.m

//  Ui - 18 数据库

//

//  Created by dllo on 15/12/2.

//  Copyright (c) 2015年 dllo. All rights reserved.

//

#import "DataBasehandle.h"

#import "Student.h"

#import <sqlite3.h>

@implementation DataBasehandle

+(instancetype)sharedDatabase

{

    static DataBasehandle *dataBase = nil;

    if (nil == dataBase) {

        dataBase = [[DataBasehandle alloc]init];

    }

    return dataBase;

}

      static sqlite3  *DB = nil;

//sql指令(以Student举例):

//

//创建表单 : @"CREATE TABLE IF NOT EXISTS student(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, sex TEXT, age integer)"

//

//删除表单 : @"DROP TABLE student"

//

//插入数据 : @"INSERT INTO student(name, sex, age) VALUES ('%@', '%@', '%ld')", stu.name, stu.sex, stu.age

//

//删除数据 : @"DELETE FROM student WHERE number = '%ld'", num

//

//修改数据(按num) : @"UPDATE student SET name = '%@', sex = '%@', age = '%ld' WHERE number = '%ld'", stu.name, stu.sex, stu.age, num

//

//查询所有数据 : SELECT * FROM student

//

//按分类查找(按sex) : @"SELECT * FROM student WHERE  sex LIKE '%%%@%%'", sex

- (void)openDB

{

    NSString *path = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES)lastObject];

    NSString *filepath = [path stringByAppendingPathComponent:@"dataBase.db"];

    

    //打开数据库

    //注意将文件路径转为c语言识别的字符串类型

    int ret = sqlite3_open(filepath.UTF8String, &DB);

    if (SQLITE_OK == ret) {

        NSLog(@"打开数据库成功");

        

    } else {

        NSLog(@"打开数据库失败");

    }

    

    

}

- (void)closeDB

{

    int ret = sqlite3_close(DB);

    if (SQLITE_OK == ret) {

        NSLog(@"关闭数据库成功");

    } else {

        NSLog(@"关闭数据库失败");

    }

}

- (void)createTable

{

    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS student(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, sex TEXT, age integer)";

    

    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);

    

    if (SQLITE_OK == ret) {

        NSLog(@"创建表单成功");

    } else {

        NSLog(@"创建表单失败");

    }

}

- (void)deleteTable

{

    NSString *sqlStr =  @"DROP TABLE student";

    

    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);

    

    if (SQLITE_OK == ret) {

        NSLog(@"删除表单成功");

    } else {

        NSLog(@"删除表单失败");

    }

}

- (void)insertDataWithStudent:(Student *)stu

{

    NSString *sqlStr = [NSString stringWithFormat:@"INSERT INTO student(name, sex, age) VALUES ('%@', '%@', '%ld')", stu.name, stu.sex, stu.age];

    

    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);

    

    if (SQLITE_OK == ret) {

        NSLog(@"插入数据成功");

    } else {

        NSLog(@"插入数据失败");

    }

}

- (void)deleteDataWithNumber:(NSInteger)num

{

    NSString *sqlstr = [NSString stringWithFormat: @"DELETE FROM student WHERE number = '%ld'", num];

    int ret = sqlite3_exec(DB, sqlstr.UTF8String, NULL, NULL, NULL);

    

    if (SQLITE_OK == ret) {

        NSLog(@"删除数据成功");

    } else {

        NSLog(@"删除数据失败");

    }

    

}

- (void)updataWithNumber:(NSInteger)num Student:(Student *)stu

{

    NSString *sqlstr = [NSString stringWithFormat: @"UPDATE student SET name = '%@', sex = '%@', age = '%ld' WHERE number = '%ld'", stu.name, stu.sex, stu.age, num];

    int ret = sqlite3_exec(DB, sqlstr.UTF8String, NULL, NULL, NULL);

    

    if (SQLITE_OK == ret) {

        NSLog(@"修改数据成功");

    } else {

        NSLog(@"修改数据失败");

    }

}

- (NSMutableArray *)selectAllStudent

{

    NSString *sqlstr = @"SELECT * FROM student";

    sqlite3_stmt *stmt = nil;

    int ret = sqlite3_prepare_v2(DB, sqlstr.UTF8String, -1, &stmt, NULL);

    if (SQLITE_OK == ret) {

        

        

        NSMutableArray *arr = [NSMutableArray array];

        //判断是否还有有效数据

        while (SQLITE_ROW == sqlite3_step(stmt)) {

            

            //参数:列数

           const unsigned char *name =  sqlite3_column_text(stmt, 1);

            const unsigned char *sex =  sqlite3_column_text(stmt, 2);

            sqlite3_int64 age = sqlite3_column_int(stmt, 3);

            Student *stu = [[Student alloc]init];

//            stu.name = name;

            stu.name = [NSString stringWithUTF8String:(const char *)name];

            stu.sex = [NSString stringWithUTF8String:(const char *)sex];

            //整型可以直接强转

            stu.age = (NSInteger)age;

            

            [arr addObject:stu];

            [stu release];

            

            

        }

        return arr;

    } else {

        NSLog(@"获取数据失败");

        return nil;

    }

}

- (NSMutableArray *)selectBySexOfStudent:(NSString *)sex name:(NSString *)name

{

     NSString *sqlstr = [NSString stringWithFormat:  @"SELECT * FROM student WHERE  sex LIKE '%%%@%%'    and name = '%@'", sex, name ];

    sqlite3_stmt *stmt = nil;

    int ret = sqlite3_prepare_v2(DB, sqlstr.UTF8String, -1, &stmt, NULL);

    if (SQLITE_OK == ret) {

        

        

        NSMutableArray *arr = [NSMutableArray array];

        //判断是否还有有效数据

        while (SQLITE_ROW == sqlite3_step(stmt)) {

            

            //参数:列数

          

            const unsigned char *name =  sqlite3_column_text(stmt, 1);

            const unsigned char *sex =  sqlite3_column_text(stmt, 2);

            sqlite3_int64 age = sqlite3_column_int(stmt, 3);

        

            Student *stu = [[Student alloc]init];

            //            stu.name = name;

            stu.name = [NSString stringWithUTF8String:(const char *)name];

            stu.sex = [NSString stringWithUTF8String:(const char *)sex];

            //整型可以直接强转

            stu.age = (NSInteger)age;

       

            

            [arr addObject:stu];

            [stu release];

            

            

        }

        return arr;

    } else {

        NSLog(@"获取数据失败");

        return nil;

    }

    

}

@end

//

//  Student.h

//  Ui - 18 数据库

//

//  Created by dllo on 15/12/2.

//  Copyright (c) 2015年 dllo. All rights reserved.

//

#import <Foundation/Foundation.h>

@interface Student : NSObject

@property (nonatomic, copy)NSString *name;

@property (nonatomic, copy)NSString *sex;

@property (nonatomic, assign)NSInteger age;

@end

//

//  Student.m

//  Ui - 18 数据库

//

//  Created by dllo on 15/12/2.

//  Copyright (c) 2015年 dllo. All rights reserved.

//

#import "Student.h"

@implementation Student

- (void)dealloc

{

    [_name release];

    [_sex release];

    [super dealloc];

    

    

}

@end