iOS 数据库详解 sqlite实现增删改查操作
来源:互联网 发布:日本演员关口知宏 编辑:程序博客网 时间:2024/06/02 08:50
//// CLViewController.m// LessonDatabase//// Created by vaercly on 14-9-19.// Copyright (c) 2014年 vaercly@163.com 陈聪雷. All rights reserved.//#import "CLViewController.h"#import "DatabaseHelper.h"#import "Student.h"@interface CLViewController ()@end@implementation CLViewController- (void)viewDidLoad{ [super viewDidLoad];// Do any additional setup after loading the view, typically from a nib. //数据库(Database): 存放数据的仓库, 存放的是一张的表, 特别像Excel, Numbers, 都以表格的形式存放数据, 可以创建多张表 //常见的数据库: sqlite, MySQL, SQLServer, Oracle, Access //为什么要用数据库 1 文件读写和归档读取数据需要一次把数据全部读出来, 占用内存开销大 2 数据库数据效率高, 体现在增删改查 //SQL Structured Query Language 用于对数据库的操作语句 (增删改查) //SQL 语句不区分大小写, 字符串需要加""或'' //主键: 是一条数据的唯一标示符, 一张表只能有一个主键, 主键不能够重复, 一般把主键名设为"id", 不需要赋值, 会自增 //*代表所有的字段 //where是条件 //创建表: creat table 表名 (字段名 字段数据类型 是否为主键, 字段名 字段数据类型, 字段名 字段数据类型...) //查: select 字段名 (或者*) from 表名 where 字段名 = 值 //增: insert into 表名 (字段1, 字段2...) values (值1, 值2...) //改: update 表名 set 字段 = 值 where 字段 = 值 //删: delete from 表名 where 字段 = 值 }- (void)didReceiveMemoryWarning{ [super didReceiveMemoryWarning]; // Dispose of any resources that can be recreated.}- (IBAction)selectAll:(id)sender { NSMutableArray *array = [DatabaseHelper getAllStudents]; for (Student *stu in array) { NSLog(@"%@", stu); }}- (IBAction)selectOne:(id)sender { Student *stu = [DatabaseHelper getStudentWithID:2]; NSLog(@"%@", stu);}- (IBAction)insetOne:(id)sender { Student *stu = [[Student alloc] init]; stu.name = @"vaercly"; stu.sex = @"man"; stu.age = 22; BOOL result = [DatabaseHelper insertStudent:stu]; NSLog(@"%d", result);}- (IBAction)updateName:(id)sender { [DatabaseHelper updateStudentName:@"陈聪雷" byID:5];}- (IBAction)deleteOne:(id)sender { [DatabaseHelper deleteStudentWithID:5];}@end//// Datebase.m// LessonDatabase//// Created by lanouhn on 14-9-19.// Copyright (c) 2014年 vaercly@163.com 陈聪雷. All rights reserved.//#define FILE_NAME @"Database.sqlite"#import "Database.h"static sqlite3 *db = nil;@implementation Database//打开数据库+ (sqlite3 *)openDB{ if (!db) { //1 获取document文件夹的路径 //参数1: 文件夹的名字 参数2: 查找域 参数3: 是否使用绝对路径 NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject]; //获取数据库文件的路径 NSString *dbPath = [docPath stringByAppendingPathComponent:FILE_NAME]; //iOS 中管理文件的类, 负责复制文件, 删除文件, 移动文件 NSFileManager *fm = [NSFileManager defaultManager]; //判断document中是否有sqlite文件 if (![fm fileExistsAtPath:dbPath]) { //获取在*.app中sqlite文件的路径 NSString *boundlePath = [[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"]; NSError *error = nil; //将*.app中sqlite文件复制一份到dbPath BOOL result = [fm copyItemAtPath:boundlePath toPath:dbPath error:&error]; //若复制文件失败, 打印错误信息 if (!result) { NSLog(@"%@", error); } } //打开数据库 参数1: 文件路径(UTF8String可以将OC的NSString转为C中的char) 参数2: 接受数据库的指针 sqlite3_open([dbPath UTF8String], &db); } return db;}//关闭数据库+ (void)closeDB{ sqlite3_close(db); db = nil;}@end//// DatabaseHelper.m// LessonDatabase//// Created by lanouhn on 14-9-19.// Copyright (c) 2014年 vaercly@163.com 陈聪雷. All rights reserved.//#import "DatabaseHelper.h"#import "Student.h"#import "Database.h"@implementation DatabaseHelper//查询所有学生+ (NSMutableArray *)getAllStudents{ //打开数据库 sqlite3 *db = [Database openDB]; //数据库操作指针 stmt:statement sqlite3_stmt *stmt = nil; //验证SQL的正确性 参数1: 数据库指针, 参数2: SQL语句, 参数3: SQL语句的长度 -1代表无限长(会自动匹配长度), 参数4: 返回数据库操作指针, 参数5: 为未来做准备的, 预留参数, 一般写成NULL int result = sqlite3_prepare_v2(db, "select * from Student", -1, &stmt, NULL); NSMutableArray *studentArr = [NSMutableArray array]; //判断SQL执行的结果 if (result == SQLITE_OK) { while (sqlite3_step(stmt) == SQLITE_ROW) {//存在一行数据 //列数从0开始 int ID = sqlite3_column_int(stmt, 0); 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); //blob类型的获取 //1 获取长度 int length = sqlite3_column_bytes(stmt, 4); //2 获取数据 const void *photo = sqlite3_column_blob(stmt, 4); //3 转成NSData NSData *photoData = [NSData dataWithBytes:photo length:length]; //4 转成UIImage UIImage *image = [UIImage imageWithData:photoData]; //封装Student模型 Student *student = [[Student alloc] init]; student.ID = ID; student.name = [NSString stringWithUTF8String:(const char *)name]; student.sex = [NSString stringWithUTF8String:(const char *)sex]; student.age = age; student.photo = image; //添加到数组 [studentArr addObject:student]; } } //释放stmt指针 sqlite3_finalize(stmt); //关闭数据库 [Database closeDB]; return studentArr;}//查询单个学生+ (Student *)getStudentWithID:(NSInteger)aID{ sqlite3 *db = [Database openDB]; sqlite3_stmt *stmt = nil; NSString *sqlStr = [NSString stringWithFormat:@"select * from Student where id = %d", aID]; int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL); Student *student = nil; if (result == SQLITE_OK) { if (sqlite3_step(stmt) == SQLITE_ROW) { int ID = sqlite3_column_int(stmt, 0); 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); int length = sqlite3_column_bytes(stmt, 4); const unsigned char *photo = sqlite3_column_blob(stmt, 4); NSData *photoData = [NSData dataWithBytes:photo length:length]; UIImage *image = [UIImage imageWithData:photoData]; student = [[Student alloc] init]; student.ID = ID; student.name = [NSString stringWithUTF8String:(const char *)name]; student.sex = [NSString stringWithUTF8String:(const char *)sex]; student.age = age; student.photo = image; } } sqlite3_finalize(stmt); [Database closeDB]; return student;}//添加一个新学生+ (BOOL)insertStudent:(Student *)aStudent{ sqlite3 *db = [Database openDB]; sqlite3_stmt *stmt = nil; NSString *sqlStr = [NSString stringWithFormat:@"insert into Student (name, sex, age) values ('%@', '%@', '%d')", aStudent.name, aStudent.sex, aStudent.age]; int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL); if (result == SQLITE_OK) { //判断语句执行完成没有 if (sqlite3_step(stmt) == SQLITE_DONE) { sqlite3_finalize(stmt); [Database closeDB]; return YES; } } sqlite3_finalize(stmt); [Database closeDB]; return NO;}//修改学生的姓名+ (BOOL)updateStudentName:(NSString *)aName byID:(NSInteger)aID{ sqlite3 *db = [Database openDB]; sqlite3_stmt *stmt = nil; NSString *sqlStr = [NSString stringWithFormat:@"update Student set name = '%@' where id = %d", aName, aID]; int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL); if (result == SQLITE_OK) { if (sqlite3_step(stmt) == SQLITE_ROW) {//觉的应加一个判断, 若有这一行则修改 if (sqlite3_step(stmt) == SQLITE_DONE) { sqlite3_finalize(stmt); [Database closeDB]; return YES; } } } sqlite3_finalize(stmt); [Database closeDB]; return NO;}//删除一个学生+ (BOOL)deleteStudentWithID:(NSInteger)aID{ sqlite3 *db = [Database openDB]; sqlite3_stmt *stmt = nil; NSString *sqlStr = [NSString stringWithFormat:@"delete from Student where id = %d", aID]; int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL); if (result == SQLITE_OK) { if (sqlite3_step(stmt) == SQLITE_ROW) {//觉的应加一个判断, 若有这一行则删除 if (sqlite3_step(stmt) == SQLITE_DONE) { sqlite3_finalize(stmt); [Database closeDB]; return YES; } } } sqlite3_finalize(stmt); [Database closeDB]; return NO;}@end//// Student.h// LessonDatabase//// Created by lanouhn on 14-9-19.// Copyright (c) 2014年 vaercly@163.com 陈聪雷. All rights reserved.//#import <Foundation/Foundation.h>@interface Student : NSObject@property (nonatomic, assign) NSInteger ID;@property (nonatomic, retain) NSString *name;@property (nonatomic, retain) NSString *sex;@property (nonatomic, assign) NSInteger age;@property (nonatomic, retain) UIImage *photo;@end
0 0
- iOS 数据库详解 sqlite实现增删改查操作
- iOS开发9-iOS操作SQLite数据库增删改查
- SQlite---SQLite数据库增删改查操作
- ios Sqlite数据库增删改查基本操作
- iOS Sqlite数据库增删改查基本操作
- ios Sqlite数据库增删改查基本操作
- ios Sqlite数据库增删改查基本操作
- iOS Sqlite数据库增删改查基本操作
- ios Sqlite数据库增删改查基本操作
- ios Sqlite数据库增删改查基本操作
- IOS--Sqlite数据库增删改查基本操作
- iOS Sqlite数据库增删改查基本操作
- ios Sqlite数据库增删改查基本操作
- Android SQLite数据库增删改查操作的使用详解
- Android SQLite数据库增删改查操作的使用详解
- Android SQLite数据库增删改查操作的使用详解
- Android SQLite数据库增删改查操作的使用详解
- Android SQLite数据库增删改查操作的使用详解
- Eclipse 安装 Maven 的 m2eclipse 插件
- Redis运行流程解析
- coursera课程批量下载
- 着重介绍了非递归算法,特别是非递归后续遍历
- cocos2dx 常见的22种动画特效
- iOS 数据库详解 sqlite实现增删改查操作
- LAMP环境搭建
- hdu 3639(强连通分量+dfs)
- lua 类, 继承, 面向对象
- 我在南邮的三年—研二生活
- js ajax 兼容多浏览器 get post 解决乱码 响应过程全齐了
- OC 字典
- 一个小码农这半年的经验和教训
- kernel---x86中计算struct thread——info偏移地址