iOS-数据库-ViewController.m 调用- 数据库附件(1)
iOS-数据库-Student类-附件(2)

iOS-数据库-数据库创建


DataBaseHandle.h

//

// DataBaseHandle.h
// UI17_数据库
//
// Created by YIem on 16/3/3.
// Copyright © 2016年 YIem. All rights reserved.
//

import <Foundation/Foundation.h>

@class Student;
@interface DataBaseHandle : NSObject

  • (instancetype)shareDataBase;
  • (void)openDB;
  • (void)closeDB;
  • (void)createTable;
  • (void)deleteTabl;
  • (void)insertDataWithStu:(Student *)stu;
  • (void)updateWithStu:(Student *)stu num:(NSInteger)num;
  • (void)deleteDataWithNum:(NSInteger)num;
  • (NSMutableArray *)selectAllStudent;
  • (NSMutableArray )selectStuWithSex:(NSString )sex;
    @end

iOS-数据库-数据库创建


iOS-数据库-数据库创建
iOS-数据库-数据库创建
iOS-数据库-数据库创建
iOS-数据库-数据库创建
iOS-数据库-数据库创建
iOS-数据库-数据库创建
iOS-数据库-数据库创建
iOS-数据库-数据库创建
DataBaseHandle.m

//

// DataBaseHandle.m
// UI17_数据库
//
// Created by YIem on 16/3/3.
// Copyright © 2016年 YIem. All rights reserved.
//

import "DataBaseHandle.h"

import "Student.h"

// 包含库文件libsq

import <sqlite3.h>

@implementation DataBaseHandle

  • (instancetype)shareDataBase
    {
    // 声明 static(编译的时候执行,) (声明局部, 声明全局(本文件可以访问/文件外面不可以访问), 声明函数/方法)
    static DataBaseHandle *dataBase = nil;
    if (nil == dataBase) {

    dataBase = [[DataBaseHandle alloc] init];

    }
    return dataBase;
    }

// (相当于创建函数变量)
static sqlite3 *DB = nil;

  • (void)openDB
    {
    / 打开数据库 (没有则创建) /

    // 获取文件路径
    NSString *filePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSLog(@"路径: %@", filePath);
    // 创建一个文件名
    NSString *file = [filePath stringByAppendingPathComponent:@"dataBase.db"];
    // 打开数据库 (C语言不写: 函数)
    // 指针本身是变量(8 字节)
    int ret = sqlite3_open(file.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)deleteTabl
    {
    // 注意删除表单的名字
    NSString *sqlStr = @"DROP TABLE student";
    int ret = sqlite3_exec(DB, sqlStr.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == ret) {

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

    } else {

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

    }
    }

  • (void)insertDataWithStu:(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)updateWithStu:(Student *)stu num:(NSInteger)num
    {
    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(@"更新数据失败");

    }

    }

  • (void)deleteDataWithNum:(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(@"删除数据失败");

    }
    }

// 查询数据
// 查询所有数据

  • (NSMutableArray *)selectAllStudent
    {
    // -1 是NSIntege类型最大值

    NSString sqlStr = @"SELECT FROM student";
    sqlite3_stmt *stmt = nil;// ( 二级指针取地址 &stmt)
    int ret = sqlite3_prepare(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    if (SQLITE_OK == ret) {

    // 创建数组
    NSMutableArray *stuArr = [NSMutableArray array];
    
    // 逐行解析
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        // 参数2: 列数
      const unsigned char *name =  sqlite3_column_text(stmt, 1);
        const unsigned char *sex =  sqlite3_column_text(stmt, 2);
        sqlite3_int64 age = sqlite3_column_int64(stmt, 3);
        
        Student *stu = [[Student alloc] init];
        //  转码(强转)
        stu.name = [NSString stringWithUTF8String:(const char *) name];
        stu.sex = [NSString stringWithUTF8String:(const char *) sex];
        stu.age = (NSInteger)age;
        [stuArr addObject:stu];
        [stu release];
        
        
    }
     //
    return stuArr;

    } else {

    NSLog(@"查询数据失败");
    return nil;

    }

}
// 查询分类数据

  • (NSMutableArray )selectStuWithSex:(NSString )sex
    {
    // -1 是NSIntege类型最大值

    NSString sqlStr = [NSString stringWithFormat:@"SELECT FROM student WHERE sex LIKE '%%%@%%'", sex];

    sqlite3_stmt *stmt = nil;// ( 二级指针取地址 &stmt)
    int ret = sqlite3_prepare(DB, sqlStr.UTF8String, -1, &stmt, NULL);
    if (SQLITE_OK == ret) {

    // 创建数组
    NSMutableArray *stuArr = [NSMutableArray array];
    
    // 逐行解析
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        // 参数2: 列数
        const unsigned char *name =  sqlite3_column_text(stmt, 1);
        const unsigned char *sex =  sqlite3_column_text(stmt, 2);
        sqlite3_int64 age = sqlite3_column_int64(stmt, 3);
        
        Student *stu = [[Student alloc] init];
        //  转码(强转)
        stu.name = [NSString stringWithUTF8String:(const char *) name];
        stu.sex = [NSString stringWithUTF8String:(const char *) sex];
        stu.age = (NSInteger)age;
        [stuArr addObject:stu];
        [stu release];
        
        
    }
    //
    return stuArr;
    

    } else {

    NSLog(@"查询数据失败");
    return nil;

    }

}

@end