//
// 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