无论在学习哪一门编程语言数据库都是必不可少的,当然了在IOS学习过程中,也不例外,在前我再博客写过plist和归档都是数据储存的不错选择,相比较它们,数据库的特点就是在设计大容量,内容复杂数据关联性强的程序中,发挥的作用更大。本章博客是我针对IOS初学者写的一些数据库内信息增删改除的简单实现,以及每一步数据库使用的注意事项,希望可以帮助到你们。
创建一个继承于NSObject的类 : DataBaseHandler
DataBaseHandler.h中
{
sqlite3 * dbPoint;//指针指向的是本地数据库文件
}
//作为一个数据处理的类,其对象最好为一个单例
+ (DataBaseHandler *)shareInstance;
//开启数据库
- (void)openDB;
//关闭数据库
- (void)closeDB;
//创建表
- (BOOL)creatTable;
数据库的增删改查
//插入数据
- (BOOL)insertTableWithName:(NSString *)name age:(int)age number:(int)number;
//删除数据
- (BOOL)deleteTableWithName:(NSString *)name;
//更新数据
- (BOOL)updateName:(NSString *)scorceName toName:(NSString *)targetName;
//查询数据
- (NSArray *)selectTable;
DataBaseHandler.m
+ (DataBaseHandler *)shareInstance
{
static DataBaseHandler * handler= nil;
//保证线程安全的情况下进行一次
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
handler = [[DataBaseHandler alloc] init];
//第一次执行的时候打开数据库 创建单例
[handler openDB];
});
return handler;
}
#pragma mark -
#pragma mark -拼接
- (void)openDB
{
//拼接一个数据库文件的地址
NSArray * arrPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//沙盒中的documents文件夹
NSString * documentPath = [arrPath lastObject];
NSLog(@"%@",documentPath);
//拼接处地址
NSString * dbPointPath = [documentPath stringByAppendingPathComponent:@"lanou.db"];
NSLog(@"%@",dbPointPath);
//参数1: 数据库的文件路径
//参数2: sqlite3 指针的地址&dbPoint 属性
//[dbPointPath UTF8String] 将NSString转成constchar * 类型
int result = sqlite3_open([dbPointPath UTF8String], &dbPoint);
//对数据库结果进行判断
if (result == SQLITE_OK) {
NSLog(@"打开成功");
}
else
{
NSLog(@"打开失败");
}
}
- (void)closeDB
{
int result = sqlite3_close(dbPoint);
if (result == SQLITE_OK) {
NSLog(@"关闭数据库成功");
}else
{
NSLog(@"关闭数据库失败");
}
}
#pragma mark -
#pragma mark - 创建表
- (BOOL)createTable
{
//注意两个单引号
NSString * sqlStr = [NSString stringWithFormat:@"create tableStudent (name text,age int,number int primary key)"];
char * error = nil;
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);
if (result == SQLITE_DONE) {
return YES;
}
return NO;
/* **************注意事项********************/
//数据库的替身缓冲地带 确保更新完成之后会自动的更新数据库
//作用 :存储所有对数据库的修改
sqlite3_stmt * stmt = nil;
//检查SQL语句,为执行sql语句做的准备
//参数1:数据库指针
//参数2:SQL语句 不需要@ 直接双引号里面填写
//参数3:限制sql语句的长度不想限制长度写-1
//参数4:数据库替身stmt的地址
int result =sqlite3_prepare(dbPoint, "create table Student (name text,ageint,number int primary key)", -1, &stmt, nil);
//3.判断检查的结果
if (result == SQLITE_OK) {
//4.执行sql语句
int sqlResult = sqlite3_step(stmt);
//判断sqlResult
if (sqlResult == SQLITE_DONE ) {
//stmt 数据保存到数据库指针释放掉
sqlite3_finalize(stmt);
return YES;
}
}
//如果检查sql语句失败,将stmt指针释放掉
sqlite3_finalize(stmt);
return NO;}
#pragma mark -
#pragma mark - 插入
- (BOOL)insertTableWithName:(NSString *)name age:(int)age number:(int)number
{
//注意两个单引号
NSString * sqlStr = [NSString stringWithFormat:@"insert into Studentvalues ('%@',%d,%d)",name,age,number]; char * error = nil;
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);
if (result == SQLITE_DONE) {
return YES;
}
return NO;
sqlite3_stmt * stmt = nil;
//注意NULL
int result =sqlite3_prepare(dbPoint, "insert into Student values(?,?,?)", -1, &stmt, NULL);
//给问号 ? 绑定参数和数据
//参数1 : 替身
//参数2 : 给哪个位置的 ?绑定 第一个元素为1
//参数3: 提供的数据
sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 2, age);
sqlite3_bind_int(stmt, 3, number);
//sql检查无误
if (result == SQLITE_OK) {
//执行
if (sqlite3_step(stmt) == SQLITE_DONE) {
//释放
sqlite3_finalize(stmt);
return YES;
}
}
sqlite3_finalize(stmt);
return NO;
}
#pragma mark-更新
- (BOOL)updateName:(NSString *)scorceName toName:(NSString *)targetName
{
//注意两个单引号
NSString * sqlStr = [NSString stringWithFormat:@"update Student setname = '%@' where name = '%@'",scorceName,targetName];
char * error = nil;
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);
if (result == SQLITE_DONE) {
return YES;
}
return NO;
//替身
sqlite3_stmt * stmt = nil;
//注意NULL
int result =sqlite3_prepare(dbPoint, "update Student set name = ? wherename = ?", -1, &stmt, NULL);
//给问号 ? 绑定参数和数据
//参数1 : 替身
//参数2 : 给哪个位置的 ?绑定 第一个元素为1
//参数3: 提供的数据
sqlite3_bind_text(stmt, 1, [scorceName UTF8String], -1, NULL)
sqlite3_bind_text(stmt, 2, [targetName UTF8String], -1, NULL);
//sql检查无误
if (result == SQLITE_OK) {
//执行
if (sqlite3_step(stmt) == SQLITE_DONE) {
//释放
sqlite3_finalize(stmt);
return YES;
}
}
sqlite3_finalize(stmt);
return NO;
}
#pragma mark- 删除 改进 除了查询之外的方法都可以简化
- (BOOL)deleteTableWithName:(NSString *)name
{
//注意两个单引号
NSString * sqlStr = [NSString stringWithFormat:@"delete from Studentwhere name = '%@'",name];
char * error = nil;
int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);
if (result == SQLITE_DONE) {
return YES;
}
return NO;
#pragma mark-原来的复杂算法
sqlite3_stmt * stmt = nil;
int result =sqlite3_prepare(dbPoint, "delete from Student where name =?", -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, [name UTF8String ], -1, NULL);
//Sql检查无误
if (result == SQLITE_OK) {
//执行
if (sqlite3_step(stmt) == SQLITE_DONE) {
//保存/释放
sqlite3_finalize(stmt);
return YES;
}
}
sqlite3_finalize(stmt);
return NO;
}
#pragma mark-查询 显示所有的
- (NSArray *)selectTable
{
sqlite3_stmt * stmt = nil;
int result = sqlite3_prepare(dbPoint, "select * fromStudent", -1, &stmt, NULL);
if (result == SQLITE_OK) {
//数组用来存储结果
NSMutableArray * arr = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW)
{
//获取每行的数据
const unsigned char * nameChar = sqlite3_column_text(stmt, 0);
NSString * name = [NSString stringWithUTF8String:(const char *)nameChar];//产生第一条数据
int age = sqlite3_column_int(stmt, 1);
int number = sqlite3_column_int(stmt, 2);
NSString * str = [NSString stringWithFormat:@"姓名:%@,年龄:%d,学号: %d",name,age,number];
NSLog(@"%@",str);
[arr addObject:str];
}
sqlite3_finalize(stmt);
return arr;
}
sqlite3_finalize(stmt);
return [NSMutableArray array];
}