数据库

//
//  AppDelegate.h
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. All rights reserved.
//

#import <UIKit/UIKit.h>

@interface AppDelegate : UIResponder <UIApplicationDelegate>

@property (strong, nonatomic) UIWindow *window;


@end

//
//  AppDelegate.m
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. All rights reserved.
//

#import "AppDelegate.h"

@interface AppDelegate ()

@end

@implementation AppDelegate


- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    // Override point for customization after application launch.
    return YES;
}

- (void)applicationWillResignActive:(UIApplication *)application {
    // Sent when the application is about to move from active to inactive state. This can occur for certain types of temporary interruptions (such as an incoming phone call or SMS message) or when the user quits the application and it begins the transition to the background state.
    // Use this method to pause ongoing tasks, disable timers, and throttle down OpenGL ES frame rates. Games should use this method to pause the game.
}

- (void)applicationDidEnterBackground:(UIApplication *)application {
    // Use this method to release shared resources, save user data, invalidate timers, and store enough application state information to restore your application to its current state in case it is terminated later.
    // If your application supports background execution, this method is called instead of applicationWillTerminate: when the user quits.
}

- (void)applicationWillEnterForeground:(UIApplication *)application {
    // Called as part of the transition from the background to the inactive state; here you can undo many of the changes made on entering the background.
}

- (void)applicationDidBecomeActive:(UIApplication *)application {
    // Restart any tasks that were paused (or not yet started) while the application was inactive. If the application was previously in the background, optionally refresh the user interface.
}

- (void)applicationWillTerminate:(UIApplication *)application {
    // Called when the application is about to terminate. Save data if appropriate. See also applicationDidEnterBackground:.
}

@end

//
//  ViewController.h
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. All rights reserved.
//

#import <UIKit/UIKit.h>

@interface ViewController : UIViewController


@end

//
//  ViewController.m
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. All rights reserved.
//

#import "ViewController.h"
#import "dataBaseTool.h"
#import "Student.h"
@interface ViewController ()
- (IBAction)OpenDateBase:(id)sender;
- (IBAction)createDateBase:(id)sender;
- (IBAction)insertData:(id)sender;
- (IBAction)upData:(id)sender;
- (IBAction)deleteData:(id)sender;
- (IBAction)searchData:(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)OpenDateBase:(id)sender {
    //用单例对象调用打开数据库的方法
    [[dataBaseTool shareDataBaseTool] openDB];
}
- (IBAction)createDateBase:(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)upData:(id)sender {
    ///更新8号内容
//    Student *stu=[[Student alloc] init];
//    stu.name=@"文世轩";
//    stu.age=23;
//    stu.sex=@"男";
//    stu.hobby=@"宁佩珊";
//    [[dataBaseTool shareDataBaseTool] updateStu:stu number:8];
    ///更新安逸臣的内容
    Student *stu=[[Student alloc] init];
    stu.name=@"宁致远";
    stu.sex=@"男";
    stu.hobby=@"小雅惠子";
    stu.age=246;
    [[dataBaseTool shareDataBaseTool] updateStu:stu];
}

- (IBAction)deleteData:(id)sender {
    Student *stu=[[Student alloc] init];
    stu.name=@"小雅惠子";
    stu.sex=@"男";
    [[dataBaseTool shareDataBaseTool] deleteStu:stu];
}

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

- (IBAction)closeDataBase:(id)sender {
}
@end

//
//  Student.h
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. 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

//
//  Student.m
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. All rights reserved.
//

#import "Student.h"

@implementation Student

@end

//
//  dataBaseTool.h
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. All rights reserved.
//

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Student.h"
@interface dataBaseTool : NSObject
{
    //用来保存数据库对象的地址
    sqlite3 *dbPoint;
}
//为了保证当前数据库在工程中是唯一的,,用单例的方式创建一个数据库对象..
+(dataBaseTool *)shareDataBaseTool;
//打开数据库
- (void)openDB;
//给数据库创建表格,table
- (void)createTable;
- (void)insertStu:(Student *)stu;
//更新表里学生数据
//- (void)updateStu:(Student *)stu number:(NSInteger)number;
- (void)updateStu:(Student *)stu;
- (void)deleteStu:(Student *)stu;
- (NSMutableArray *)selectAllStu;
@end

//
//  dataBaseTool.m
//  UI19_数据库1
//
//  Created by dllo on 15/8/24.
//  Copyright (c) 2015年 flg. All rights reserved.
//

#import "dataBaseTool.h"
//#import "Student.h"
@implementation dataBaseTool
+(dataBaseTool *)shareDataBaseTool{
    static dataBaseTool *tool;
    static dispatch_once_t oneToken;
    dispatch_once(&oneToken, ^{
        tool=[[dataBaseTool alloc] init];
    });
    return tool;
}


- (void)openDB{
    //数据库文件也保存在沙盒的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 not exists如果没有表才会创建,防止重复创建覆盖之前数据
    ///数据库问题90%是sql语句出问题,所以先保证语句没有问题,,再放到工程里面使用.
NSString *sqlStr=@"create table if not exists stu(number integer primary key autoincrement, name text, sex text, age integer, hobby text )";
    //执行这条sql语句
    int result=sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    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(@"添加学生失败");
    }
}
按number更新
//- (void)updateStu:(Student *)stu number:(NSInteger)number{
//    NSString *sqlStr=[NSString stringWithFormat:@"update stu set name ='%@',sex='%@',hobby='%@',age=%ld where number='%ld'",stu.name,stu.sex,stu.hobby,stu.age,number];
//    //执行sql语句
//    int result=sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
//    if (result==SQLITE_OK) {
//        NSLog(@"更新学生成功");
//    }else{
//        NSLog(@"更新学生失败");
//    }
//
//}
按名字更新
- (void)updateStu:(Student *)stu{
NSString *sqlStr=[NSString stringWithFormat:@"update stu set sex='%@',hobby='%@',age=%ld where name='%@'",stu.sex,stu.hobby,stu.age,stu.name];
    int result=sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK) {
        NSLog(@"更新学生成功");
    }else{
        NSLog(@"更新学生失败");
        NSLog(@"%d",result);
    }
}
- (void)deleteStu:(Student *)stu{
    NSString *sqlStr=[NSString stringWithFormat:@"delete from stu where name='%@'",stu.name];
    int result=sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK) {
        NSLog(@"学生删除成功");
    }else{
        NSLog(@"学生删除失败");
        NSLog(@"%d",result);
    }
}
- (NSMutableArray *)selectAllStu{
//查询逻辑
    //1.先从本地的数据库中读取某张表里的所有数据
    //2.然后逐条进行读取,对model进行赋值...
    //3.已经赋值好的model放到数组里,并返回
    NSString *sqlStr=@"select * from stu ";
    //在这条语句里*是通配符的意思,,通过一个*相当于代替了表里所有的字段名
    //接下来需要定义一个跟随指针.它用来遍历数据库表中每行数据
    sqlite3_stmt *stmt=nil;
    //第三个参数:查询语句字数限制,-1是没有限制,
    int result=sqlite3_prepare(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;
}

@end