UI第十九讲:数据库
一、数据库管理系统
二.SQL语句
1.数据库的增删改查
2.数据的插入
3.数据的修改
4.数据的删除
5.数据的查找
示例代码分析:
DatabaseHandle.h文件
#import <Foundation/Foundation.h>
#import "AddresssBook.h"
@interface DatabaseHandle : NSObject
+(DatabaseHandle *)shareDataBaseHanndle;
//打开数据库
-(void)openDB;
//关闭数据库
-(void)closeDB;
//创建表
-(void)creataTable;
//增加
-(void)insertIntoTable:(AddresssBook *)AB;
//删除
-(void)deleteFromeTable:(NSString *)ADID;
//修改
-(void)updateFromTableWhereName:(NSString *)name
toName:(NSString *)toName;
//查询
-(NSArray *)selectAll;
//条件查询
-(NSArray *)selectFromeTableWherName:(NSString *)name;
@end
数据库中增删改查的使用:
DatabaseHandle.m文件
#import "DatabaseHandle.h"
#import <sqlite3.h>
@implementation DatabaseHandle
//单例类不会再被初始化也不会被释放
//单例声明的对象在第一次声明后 就会在静态区存在 程序结束之后才会被释放 不会第二次初始化
+(DatabaseHandle *)shareDataBaseHanndle
{
static dispatch_once_t onceToken;
static DatabaseHandle *dataBH = nil;
dispatch_once(&onceToken, ^{
if (dataBH == nil) {
dataBH = [[DatabaseHandle alloc] init];
}
});
return dataBH;
}
//在静态区声明一个数据库指针
static sqlite3 *bd = nil;
//打开数据库
-(void)openDB
{
if (bd != nil) {
return;
}
//document沙盒路径
NSString *documentStr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
//数据库文件在沙盒中的路径
NSString *bdpath = [documentStr stringByAppendingString:@"/addressBook.sqlite"];
NSLog(@"%@",documentStr);
//在bdpath 里面创建一个文件 并作为数据库 而且在这一步做完之后
//打开数据库语句
int result = sqlite3_open(bdpath.UTF8String , &bd);
if (result == SQLITE_OK) {
NSLog(@"打开成功");
} else{
NSLog(@"打开失败");
}
}
//关闭数据库
-(void)closeDB
{
//关闭数据库语句
int result = sqlite3_close(bd);
if (result == SQLITE_OK) {
NSLog(@"关闭成功");
} else{
NSLog(@"关闭失败");
}
}
//创建数据库
-(void)creataTable
{
//创建表的SQlite语句 格式CREATE TABLE + 表名(字段 类型.....字段 类型)
NSString *createStr = @"CREATE TABLE AddressBook(ADID text PRIMARY KEY, name text , phone text, age integer)";
//第一个参数 要在那个数据库里进行操作
//第二个参数 代表要去执行哪一条SQlite语句
//第三个参数 回调的函数
//第四个参数 回调函数的一些参数
//第五个参数 错误信息
int result = sqlite3_exec(bd, createStr.UTF8String, NULL , NULL , NULL );
if (result == SQLITE_OK) {
NSLog(@"ok");
} else{
NSLog(@"no");
}
}
//在数据库中插入数据
-(void)insertIntoTable:(AddresssBook *)AB
{
//插入数据的SQlite语句
NSString *insertStr = [NSString stringWithFormat:@"INSERT INTO AddressBook(ADID , name , phone , age) values ('%@','%@','%@','%ld')",AB.ADID, AB.name, AB.phone, AB.age];
int result = sqlite3_exec(bd, insertStr.UTF8String, NULL , NULL , NULL );
if (result == SQLITE_OK) {
NSLog(@"插入成功");
} else{
NSLog(@"插入失败");
}
}
//在数据库中删除数据
-(void)deleteFromeTable:(NSString *)ADID
{
//删除的SQL语句
//DELETE FROM + 表名 WHERE 删除索引语句
NSString *deleteStr = [NSString stringWithFormat:@"DELETE FROM AddressBook WHERE ADID = '%@' ",ADID];
int resurt = sqlite3_exec(bd, deleteStr.UTF8String, NULL, NULL, NULL);
if (resurt == SQLITE_OK) {
NSLog(@"删除成功");
} else{
NSLog(@"删除失败");
}
}
//在数据库中修改数据
//修改的过程中name为修改的值 toName为原值
-(void)updateFromTableWhereName:(NSString *)name toName:(NSString *)toName
{
NSString *updateStr = [NSString stringWithFormat:@"UPDATE AddressBook SET name = '%@' WHERE name = '%@' ", name ,toName ];
int resurt = sqlite3_exec(bd, updateStr.UTF8String, NULL, NULL, NULL);
if (resurt == SQLITE_OK) {
NSLog(@"修改成功");
} else{
NSLog(@"修改失败");
}
}
//在数据库中查询数据
-(NSArray *)selectAll
{
//查询的SQL语句
//SELECT * FROM + 表名
NSMutableArray *dataArray = [NSMutableArray array];
NSString *selectStr = @"SELECT * FROM AddressBook";
//伴随指针(保存查询出来的数据 , 保存SQL语句)
sqlite3_stmt *stmt = nil;
//-1为断句方式
int result = sqlite3_prepare(bd, selectStr.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//判断stmt里面是否有合适的数据(一整条的)
while (sqlite3_step(stmt) == SQLITE_ROW) {
//
NSString *ADID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *phone = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
NSInteger age = sqlite3_column_int(stmt, 3);
AddresssBook *add = [[AddresssBook alloc] init];
add.ADID = ADID;
add.name = name;
add.phone = phone;
add.age = age;
//存进数组
[dataArray addObject:add];
}
//释放伴随指针
sqlite3_finalize(stmt);
for (AddresssBook *add in dataArray) {
NSLog(@"%@ ",add.name);
}
}
return dataArray;
}
//在数据库中按照条件查询数据
-(NSArray *)selectFromeTableWherName:(NSString *)name
{
//条件查询的SQL语句
//SELECT * FROM + 表名 WHERE 条件 + ?
NSMutableArray *dataArray = [NSMutableArray array];
NSString *selectStr = @"SELECT * FROM AddressBook WHERE name = ? or ADID = ?";
//伴随指针(保存查询出来的数据 , 保存SQL语句)
sqlite3_stmt *stmt = nil;
//-1为断句方式
int result = sqlite3_prepare(bd, selectStr.UTF8String, -1, &stmt, NULL);
if (result == SQLITE_OK) {
//填充到查询语句里面
sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
sqlite3_bind_text(stmt, 2, name.UTF8String, -1, NULL);
//判断stmt里面是否有合适的数据(一整条的)
while (sqlite3_step(stmt) == SQLITE_ROW) {
//
NSString *ADID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
NSString *phone = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
NSInteger age = sqlite3_column_int(stmt, 3);
AddresssBook *add = [[AddresssBook alloc] init];
add.ADID = ADID;
add.name = name;
add.phone = phone;
add.age = age;
[dataArray addObject:add];
}
//释放伴随指针
sqlite3_finalize(stmt);
for (AddresssBook *add in dataArray) {
NSLog(@"%@ %@",add.name,add.ADID);
}
}
return dataArray;
}
@end
在ViewController..m中的调用
#import "ViewController.h"
#import "DatabaseHandle.h"
#import "AddresssBook.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
DatabaseHandle *data = [DatabaseHandle shareDataBaseHanndle];
//打开数据库
[data openDB];
//创建数据库
[data creataTable];
AddresssBook *add = [[AddresssBook alloc] init];
add.ADID = [NSString stringWithFormat:@"%d%d",arc4random()%100 , arc4random() %2000];
NSInteger index= arc4random() %200;
if (index %2) {
add.name = @"张三";
add.phone = @"1111";
add.age = 12;
} else{
add.name = @"李四";
add.phone = @"4444";
add.age = 2;
}
//插入数据库
[data insertIntoTable:add];
//删除数据库
[data deleteFromeTable:@"17891"];
//修改数据库
[data updateFromTableWhereName:@"小明" toName:@"张三"];
//查询所有
[data selectAll];
//条件查询
[data selectFromeTableWherName:@"哈哈"];
//关闭数据库
[data closeDB];
}
@end
AddresssBook.h文件
#import <Foundation/Foundation.h>
@interface AddresssBook : NSObject
@property(nonatomic,copy)NSString *ADID;
@property(nonatomic,copy)NSString *name;
@property(nonatomic,assign)NSInteger age;
@property(nonatomic,copy)NSString *phone;
@end
三.iOS的数据库技术的实现
