总结今天所学习的数据库知识:
首先在AddressBook 工程里建4个属性
#import <Foundation/Foundation.h>
@interface AddressBook : NSObject
@property (nonatomic, copy) NSString *ADID;
@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *phone;
@property (nonatomic, assign) NSUInteger age;
@end
在DataBaseHandle.h 工程里写方法
单列
+ (DataBaseHandle *)shareDataBaseHandle;
然后在写数据库的方法
//打开数据库
- (void) openDB;
关闭数据库
- (void) closeDB;
创建表
- (void) cesateTable;
增加
- (void) insertIntoTable:(AddressBook *)AB;
删除
- (void) deleteFromTable:(NSString *)ADID;
修改
- (void) UpdateToTable:(NSString *)name toName:(NSString *)toName;
查询
- (AddressBook *)selectAlll;
条件查询
- (NSArray *)selectFromTableWhereName;(NSString *)name;
在DataBaseHandle.m 里面实现
<span style="font-size:18px;">#import "DataBaseHandle.h"
#import <sqlite3.h>
@implementation DataBaseHandle
+ (DataBaseHandle *)shareDataBaseHandle
{
//单列类声明的对象,在第一次初始化之后 就会在静态去存在。 在程序结束之后才会被释放,不会第二次初始化;
static dispatch_once_t onceToken;
static DataBaseHandle *dataBH = nil;
dispatch_once(&onceToken, ^{
if (dataBH == nil) {
dataBH = [[DataBaseHandle alloc]init];
}
});
return dataBH;
}
//在静态去声明一个数据库的指针
static sqlite3 *db =nil;
//
- (void)openDB
{
if (db != nil) {
return;
}
NSString *documentStr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
NSString *dbPath = [documentStr stringByAppendingString:@"/addressBook.sqlite"];
NSLog(@"%@",dbPath);
//在dbPath里面创建一个文件,并作为数据库,而且这一步做完之后 就已经初始化了数据库了 静态区的数据库已经不为nil了
int result = sqlite3_open(dbPath.UTF8String, &db);
if (result == SQLITE_OK) {
NSLog(@"打开成功");
}else{
NSLog(@"打开失败");
}
}
-(void)closeDB
{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"成功");
}else{
NSLog(@"失败");
}
}
- (void)cesateTable
{
//创建表的SQL语句 格式:CREATE TABLE +表明(字段 类型。。。字段 类型)
NSString *cresteStr = @"CREATE TABLE AddressBook(ADID text PRIMARY KEY,name text,phone text, age integer)";
//第一个参数:代表的是要在哪个数据库里执行或操作
//第二个参数:代表的是要执行哪一条SQL语句
//第三个参数:回调的函数
//第四个参数:回调函数的一些函数
//第五个参数; 错误信息
int result = sqlite3_exec(db, cresteStr.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"成功");
}else{
NSLog(@"失败");
}
}
- (void)insertIntoTable:(AddressBook *)AB
{
//插入数据sql语句
NSString *insertSTR = [NSString stringWithFormat:@"INSERT INTO AddressBook(ADID,name,phone,age)VALUES('%@','%@','%@','%ld')",AB.ADID,AB.name,AB.phone,(unsigned long)AB.age];
int result = sqlite3_exec(db, insertSTR.UTF8String, NULL,NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"插入成功");
}else
{
NSLog(@"插入失败");
}
}
- (void)deleteFromTable:(NSString *)ADID
{
//删除的SQL语句
//DELETE FROM +表名 WHERE 删除的数据引参数 = 参数
NSString *deleteStr = [NSString stringWithFormat:@"DELETE FROM AddressBook WHERE ADID = '%@'", ADID];
int result = sqlite3_exec(db, deleteStr.UTF8String, NULL,NULL,NULL);
if (result == SQLITE_OK) {
NSLog(@"SC成功");
}else
{
NSLog(@"SC失败");
}
}
- (void)UpdateToTable:(NSString *)name toName:(NSString *)toName
{
NSString *updateStr = [NSString stringWithFormat:@"UPDATE AddressBook SET name = '%@' WHERE name = '%@'",name ,toName];
int resylt = sqlite3_exec(db, updateStr.UTF8String, NULL, NULL, NULL) ;
if (resylt == SQLITE_OK) {
NSLog(@"SC成功");
}else
{
NSLog(@"SC失败");
}
}
- (NSArray *)selectAll
{
//查询SQL语句
//SELECT * FROM + 表名
NSMutableArray *dataArray = [[NSMutableArray alloc]init];
NSString *selectStr = @"SELECT * FROM AddressBook";
//伴随指针(保存sql语句,保存查询出来的数据)
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, 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);
AddressBook *add = [[AddressBook alloc]init];
add.ADID = ADID;
add.name = name;
add.phone = phone;
add.age = age;
[dataArray addObject:add];
}
}
//释放掉 伴随指针
sqlite3_finalize(stmt);
// for (AddressBook * arr in dataArray) {
// NSLog(@"%@,%@,%@,%ld",arr.ADID,arr.name,arr.phone,arr.age);
// }
return dataArray;
}
-(NSArray *)selectFromTableWhereName:(NSString *)name
{
//查询SQL语句
//SELECT * FROM + 表名 WHERE + 条件 + ?
NSMutableArray *dataArray = nil;
NSString *selectStr = @"SELECT * FROM AddressBook WHERE name = ?";
//伴随指针(保存sql语句,保存查询出来的数据)
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare(db, selectStr.UTF8String, -1 , &stmt, NULL);
if (result == SQLITE_OK) {
//判断stmt里面是否有适合的数据(一整条的)
//填充查询语句里面的?
sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
dataArray = [NSMutableArray array];
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);
AddressBook *add = [[AddressBook alloc]init];
add.ADID = ADID;
add.name = name;
add.phone = phone;
add.age = age;
[dataArray addObject:add];
}
}
//释放掉 伴随指针
sqlite3_finalize(stmt);
for (AddressBook * arr in dataArray) {
NSLog(@"%@",arr.name);
}
return dataArray;
}
@end
在ViewController.m里面打印出来
</span><pre name="code" class="objc"><span style="font-size:18px;">#import "ViewController.h"
#import "DataBaseHandle.h"
#import "AddressBook.h"
@interface ViewController ()
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
DataBaseHandle *data = [DataBaseHandle shareDataBaseHandle];
[data openDB];
// [data closeDB];(做其他操作时,先注销 关掉数据库方法 )
[data cesateTable];
AddressBook *add = [[AddressBook alloc]init];
add.ADID = [NSString stringWithFormat:@"%d%d",arc4random()% 1000,arc4random() % 2000];
NSInteger index = arc4random() %200;
if (index % 2) {
add.name = @"小强";
add.phone = @"10086";
add.age = 18;
}else
{
add.name = @"光头强";
add.phone = @"100000";
add.age = 28;
}
[data insertIntoTable:add];
[data selectAll];
[data selectFromTableWhereName:@"zhang2 san"];
}
</span>
今天学习的总结。有什么地方错的希望大神指导下,谢谢