UI day 18 数据库SQL语句

1. 在”ViewController.m.中写

#import"ViewController.h"
#import "DataBaseHandle.h"
#import "Student.h"
@interface ViewController ()
@property (retain,nonatomic)IBOutlet UITextField *numberField;//学号
@property (retain,nonatomic)IBOutlet UITextField *nameField;//姓名
@property (retain,nonatomic)IBOutlet UITextField *genderField;//性别
@property (retain,nonatomic)IBOutlet UITextField *ageField;//年龄
@end
@implementation ViewController
//添加学生
- (IBAction)insertStudent:(UIButton*)sender {
    if (0 == self.nameField.text.length|| 0== self.genderField.text.length||0==self.ageField.text.length) {
        return;

    }

//创建student对象存储控件中输入的能容
    Student *stu = [[Student alloc]init];
    stu.name = self.nameField.text;
    stu.gender = self.genderField.text;
    stu.age = [self.ageField.textintegerValue];
    [[DataBaseHandleshareDataBaseHandle]insertStudent:stu];

    [stu release];

  

}
//更新学生
- (IBAction)updateStudent:(id)sender

{

    NSString *gender = self.genderField.text;//获取输入框的内容
    NSInteger number = [self.numberField.textintegerValue];
    [[DataBaseHandleshareDataBaseHandle]updataStudentGender:genderbyNumber:number];
   
}
//删除学生
- (IBAction)deleteStudent:(id)sender
{
    NSInteger number= [self.numberField.textintegerValue];
    [[DataBaseHandleshareDataBaseHandle]deleteOneStudentBuNumber:number];
}
//查找某个学生
- (IBAction)selectOneStudent:(id)sender
{
    NSInteger number = [self.numberField.textintegerValue];//获取学号输入框内容
//    调用根据学号查询学生的方法
  Student *stu =[[DataBaseHandleshareDataBaseHandle]selectOneStudentByNumber:number];
    NSLog(@"======%@",stu);
}
//查找所有学生
- (IBAction)selectAllStudent:(id)sender

{

    NSMutableArray *contentArray = [[DataBaseHandleshareDataBaseHandle]selectAllStudent];
    for (Student *stu in contentArray) {
        NSLog(@"%@",stu);
    }
}

- (void)viewDidLoad {
    [super viewDidLoad];
//    [DataBaseHandle shareDataBaseHandle];//内部打开数据库
//    [[DataBaseHandle shareDataBaseHandle]closeDatabase];//关闭数据库
  

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

}

- (void)dealloc {
    [_ageField release];
    [_genderField release];
    [_nameField release];
    [_numberField release];
    [super dealloc];
}

@end

2.在DataBaseHandle.h中写

#import<Foundation/Foundation.h>
@class Student;
@interface DataBaseHandle : NSObject
//创建单例的方法 第一步
+ (DataBaseHandle *)shareDataBaseHandle;

- (void)openDataBase;//打开数据库的方法
- (void)closeDatabase;//关闭数据库的方法
//插入学生对象的接口
- (void)insertStudent:(Student*)student;
- (NSMutableArray *)selectAllStudent;//返回表格中所有学生的接口
//根据唯一标示学号删除学生
- (void)deleteOneStudentBuNumber:(NSInteger)number;
//根据唯一标示学号修改学生的性别
- (void)updataStudentGender:(NSString*)gender byNumber:(NSInteger)number;
//根据学号查找学生

- (Student*)selectOneStudentByNumber:(NSInteger)number;

@end

3.在DataBaseHandle.m中写

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "Student.h"
@implementation DataBaseHandle
 static DataBaseHandle *handle = nil;
//单例第二步
+ (DataBaseHandle *)shareDataBaseHandle
{
    @synchronized(self){
        if (handle == nil) {
            handle = [[DataBaseHandle alloc]init];
            [handle openDataBase];//让单例对象以创建出来就可以访问数据库
        }
    }
   
    return handle;
}

//返回数据库文件的路径 数据库第一步
- (NSString *)dataBasePath{
//    将数据库文件放到Documents文件夹下，student.sqllite
   
    return [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)lastObject]stringByAppendingPathComponent:@"student.sqlite"];
   
}

static sqlite3 *db = nil;//定义一个全局且在静态区的数据库
- (void)openDataBase
{
//1.现货区数据文件的路径
    NSString *dbPath = [self dataBasePath];
//    使用数据库语句（sql）之前一定要导入libsqlite3.0动态链接类库，其中libsqlite3.0是快捷方式libsqlite3.是实体类库，导入快捷方式的好处，当版本更新的时候，不用在导入新的实体类库，因为快捷方式永远指向的都是最新的实体类库
//    2.使用sql语句打开数据库  UTF8String将OC字符串转换为C语言字符串  sqlite3是数据库指针
//    创建数据库指针db
//    sqlite3 *db = nil;
//    方法执行完内部会对数据库指针db初始化方法执行完如果正确无误就会存在数据库文件 此方法会先检查文件路径中有没有对应的数据库文件，没有的话创建，有的话直接打开
   int result =  sqlite3_open([dbPathUTF8String], &db);
//  SQLITE_OK说明sql语句成功
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
//        创建表格
//        准备sql语句
        NSString *sqlString = @"create table if not exists Student(stu_number integer primary key autoincrement,stu_name text,stu_gender text,stu_age integer)";
//        执行sql语句
        sqlite3_exec(db, [sqlStringUTF8String],
                     NULL,NULL,NULL);
       
    }else{
       
        NSLog(@"数据库打开失败");
    }
}


//关闭数据库
- (void)closeDatabase
{
  int result = sqlite3_close(db);
    NSLog(@"%@",(result ==SQLITE_OK) ?@"关闭成功":@"关闭失败");
 
}
///插入学生的接口
- (void)insertStudent:(Student*)student
{
//    1.打开数据库
    [self openDataBase];
//    2.准备插入SQL语句
    NSString *sqlString = @"insert into Student(stu_name,stu_gender,stu_age)values(?,?,?)";
//    3.创建数据库管理指针(数据库管理指令集)
    sqlite3_stmt *stmt = nil;
//    4.验证SQL语句是否正确
//    参数1：数据库指针 参数2：SQL语句参数3：SQL语句的长度-1 自动计算SQL语句的最大长度，否则自己计算长度参数4：SQL语句的管理指针 参数5：预留参数未来使用
  int result = sqlite3_prepare_v2(db, [sqlStringUTF8String], -1, &stmt,NULL);
//    5.那验证的结果，判断是否执行参数绑定的操作
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
//        绑定字段stu_name的数据
//        参数1：SQL语句管理指针参数2：上面SQL语句中问号的位置，下标为1 参数3：要绑定的数据参数4：数据的长度
        sqlite3_bind_text(stmt,1, [student.nameUTF8String], -1,NULL);//绑定name数据
        sqlite3_bind_text(stmt,2, [student.genderUTF8String], -1,NULL);//绑定gender数据
//        绑定字段age的数据
        sqlite3_bind_int(stmt,3, (int)student.age);
//        6.让SQL语句执行
        sqlite3_step(stmt);
//
    }
//    7.释放掉管理指针
    sqlite3_finalize(stmt);
    //        8.关闭数据库
    [self closeDatabase];

}

- (NSMutableArray *)selectAllStudent
{
//  打开数据库
    [self openDataBase];
//    准备SQL语句
    NSString *sqlString= @"select *from Student";
//    3.创建管家指针
    sqlite3_stmt  *stmt =nil;
//    4.验证SQL语句是否正确
 int resule = sqlite3_prepare_v2(db, [sqlStringUTF8String], -1, &stmt,NULL);
//    创建数组存放找到的所有学生对象
    NSMutableArray *array = [NSMutableArrayarrayWithCapacity:0];
   
   
    if (resule == SQLITE_OK) {
        NSLog(@"查找全部语句成功");
//        如果SQLITE_ROW说名下一行是有数据的，循环继续，如果不等于SQLITE_ROW。说名下一行没有数据了，循环结束了
        while (sqlite3_step(stmt)==SQLITE_ROW) {
//            、依次读出字段中的数据 列的编号是从0开始的
            int number = sqlite3_column_int(stmt,0);
//            第1列
            NSString*name =  [NSStringstringWithUTF8String:(constchar *)sqlite3_column_text(stmt,1)];
//            第2列
            NSString*gender = [NSStringstringWithUTF8String:(constchar *)sqlite3_column_text(stmt,2)];
//            第三列
            int age = sqlite3_column_int(stmt,3);
//            创建学生对象并赋值
            Student *stu = [[Student alloc]init];
            stu.number= number;
            stu.name= name;
            stu.gender= gender;
            stu.age = age;
            [array addObject:stu];
            [stu release];
           
        }
//        6.释放管理指针
        sqlite3_finalize(stmt);
        [self closeDatabase];
        return array;
       
    }else{
        sqlite3_finalize(stmt);
        [self closeDatabase];
        return nil;
    }
}

//根据唯一标示学号删除学生
- (void)deleteOneStudentBuNumber:(NSInteger)number
{
//    1.打开数据库
    [self openDataBase];
//    2.准备SQL语句
    NSString *sqlString = @"delete from Student  where stu_number = ?";
//    3.创建管家指针
    sqlite3_stmt *stmt = nil;
//    4.验证SQL语句是否正确
    int resule = sqlite3_prepare_v2(db,[sqlStringUTF8String], -1, &stmt,NULL);
    if (resule == SQLITE_OK) {
//    5.绑定传过来的参数
        sqlite3_bind_int(stmt,1, (int)number);
        sqlite3_step(stmt);
    }
//    6.释放管家指针
    sqlite3_finalize(stmt);
//    7.关闭数据库
    [self closeDatabase];
   
}




//根据唯一标示学号修改学生的性别
- (void)updataStudentGender:(NSString*)gender byNumber:(NSInteger)number
{
    [self openDataBase];
    NSString *sqlString = @"update Student set stu_gender = ? where stu_number = ? ";
    sqlite3_stmt *stmt = nil;
    int resule = sqlite3_prepare_v2(db, [genderUTF8String], -1, &stmt,NULL);
    if (resule == SQLITE_OK) {
        sqlite3_bind_text(stmt,1, [sqlStringUTF8String], -1,NULL);
        sqlite3_bind_int(stmt,2, (int)number);//第二个参数是？在SQL语句中的位置，位置从一开始
        sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    [self closeDatabase];
}



//根据学号查找学生
- (Student *)selectOneStudentByNumber:(NSInteger)number
{
    [self openDataBase];
    NSString *sqlString = @"select * from Student where stu_number = ?";
    sqlite3_stmt *stmt = nil;
    int resule = sqlite3_prepare_v2(db, [sqlStringUTF8String], -1, &stmt,NULL);
    if (resule == SQLITE_OK) {
        sqlite3_bind_int(stmt,1, (int)number);
//        创建Model对象
        Student *stu = [[Student alloc]init];
//        遍历表格中的数据
        while (sqlite3_step(stmt)==SQLITE_ROW) {
//            根据找的数据给学生的属性赋值
            stu.number= number;
            stu.name=[NSStringstringWithUTF8String:(constchar *) sqlite3_column_text(stmt,1)];
            stu.gender= [NSStringstringWithUTF8String:(constchar *) sqlite3_column_text(stmt,2)];

            stu.age= sqlite3_column_int(stmt, 3);    

        }
        sqlite3_finalize(stmt);
        [self closeDatabase];
        return [stu autorelease];
       
    }else
    sqlite3_finalize(stmt);
    [self closeDatabase];
    return nil;
   
}

4. #import <Foundation/Foundation.h>

@interface Student : NSObject
@property (nonatomic,assign)NSIntegernumber;
@property (nonatomic,copy)NSString*name;
@property (nonatomic,copy)NSString*gender;
@property (nonatomic,assign)NSIntegerage;

@end

#import"Student.h"

@implementation Student
- (void)dealloc
{
    self.name= nil;
    self.gender= nil;
    [super dealloc];
   
   
}

- (NSString*)description
{
    return [NSString stringWithFormat:@"%ld %@ %@ %ld",self.number,self.name,self.gender,self.age];
}
@end