sqlite 数据库

#import <Foundation/Foundation.h>

#import "Person.h"

#warning Step.1 引入sqlite 数据库头文件

#import <sqlite3.h>

@interface DataHandle : NSObject

+ (instancetype)sharePersonDataHandle;

-(void)openDB;

- (void)insertStudent:(Person *)person;

- (void)updateStudent:(Person *)person number:(NSInteger)number;

- (void)deleteStudent:(NSInteger)number;

- (NSMutableArray *)selectAllStudent;

@end

#import "DataHandle.h"

@implementation DataHandle

+ (instancetype)sharePersonDataHandle

{

static DataHandle *personData = nil;

if (personData == nil) {

personData = [[DataHandle alloc] init];

}

return personData;

}

#warning Step.2 加入libsqlite3.0.dylib 库

#warning Step.3 创建数据库对象

static sqlite3 *db = nil;

#warning Step.4 打开数据库

-(void)openDB

{

if (db != nil) {

NSLog(@"数据库已经打开");

return;

}

NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);

NSString *path = [array lastObject];

NSString *sqlitePath = [path stringByAppendingPathComponent:@"student.sqlite"];

NSLog(@"== %@",sqlitePath);

// 打开数据库

int result = sqlite3_open(sqlitePath.UTF8String,&db);

if (result == SQLITE_OK) {

NSLog(@"数据库打开成功");

// 建数据库表

NSString *createSql = @"CREATE TABLE IF NOT EXISTS lanou23(number INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,gender TEXT,age INSTEGER)";

int result = sqlite3_exec(db,createSql.UTF8String,NULL,nil);

if (result == SQLITE_OK) {

NSLog(@"创建表成功");

} else {

NSLog(@"创建表失败");

}

} else {

NSLog(@"数据库打开失败");

}

}

- (void)insertStudent:(Person *)person

{

NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO lanou23(name,gender,age) values('%@','%@','%ld')",person.name,person.gender,person.age];

int result = sqlite3_exec(db,insertSql.UTF8String,nil);

if (result == SQLITE_OK) {

NSLog(@"插入成功");

} else {

NSLog(@"插入失败");

}

}

#warning 第六步 修改

- (void)updateStudent:(Person *)person number:(NSInteger)number

{

NSString *updateSql = [NSString stringWithFormat:@"UPDATE lanou23 SET name = '%@',gender = '%@',age = '%ld' WHERE number = '%ld'",person.age,number];

int result = sqlite3_exec(db,updateSql.UTF8String,19)">修改成功");

} else {

NSLog(@"修改失败");

}

}

#warning 第七步 删除

- (void)deleteStudent:(NSInteger)number

{

NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM lanou23 WHERE number = '%ld'",deleteSql.UTF8String,19)">删除成功");

} else {

NSLog(@"删除失败");

}

}

#warning 第八步 查询所有学生

- (NSMutableArray *)selectAllStudent

{

NSMutableArray *array = [NSMutableArray array];

NSString *selectSql = @"SELECT * FROM lanou23";

NSString *select = [NSString stringWithFormat:@"SELECT * FROM lanou23 WHERE name = '%@'",@"大水杯"];

// 声明数据库指针,作用是跟踪表里的每一行

sqlite3_stmt *stmt = nil;

// 查询之前准备工作: 将数据库对象db,sql语句,stmt关联起来

// 参数1.数据库对象,参数2.sql语句,参数3.sql语句长度,-1为不限制长度,参数4.数据库跟随指针

int result = sqlite3_prepare_v2(db,selectSql.UTF8String,-1,&stmt,19)">查询成功");

// 循环遍历每条数据

while (sqlite3_step(stmt) == SQLITE_ROW) {

// 取值

int number = sqlite3_column_int(stmt,0);

const unsigned char *name = sqlite3_column_text(stmt,1);

const unsigned char *gender = sqlite3_column_text(stmt,2);

int age = sqlite3_column_int(stmt,3);

NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];

NSString *genderStr = [NSString stringWithUTF8String:(const char *)gender];

NSInteger numberInteger = number;

NSInteger ageInteger = age;

Person *pp = [[Person alloc] init];

pp.name = nameStr;

pp.gender = genderStr;

pp.age = ageInteger;

pp.number = numberInteger;

[array addObject:pp];

}

} else {

NSLog(@"查询失败");

}

return array;

}

@end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!