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-数据库-数据库创建

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