#warning第一步引入sqlite数据库头文件
#import<sqlite3.h>
#warning第二步加入libsqlite3.0.dylib库文件
#warning 第三步 创建数据库对象
static sqlite3 *db = nil;
#warning 第四步 打开数据库
- (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 = %@", 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 INTEGER)";
int result = sqlite3_exec(db, createSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"创建表成功");
}else{
NSLog(@"创建表失败");
}
}else{
NSLog(@"数据库打开失败");
}
}
#warning 第五步 插入数据
- (void)insertStudent:(Person*)person
{
//创建插入语句
NSString *insertSql = [NSStringstringWithFormat:@"INSERT INTO lanou23(name, gender, age) values('%@', '%@', '%ld')", person.name, person.gender, person.age];
int result = sqlite3_exec(db, insertSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"插入成功");
}else{
NSLog(@"插入失败");
}
}
#warning 第六步 修改数据
- (void)updateStudent:(Person*)person number:(NSInteger)number
{
NSString *updataSql = [NSStringstringWithFormat:@"UPDATE lanou23 SET name = '%@', gender = '%@', age = '%ld' WHERE number = '%ld'", person.name, person.gender, person.age, number];
int result = sqlite3_exec(db, updataSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"修改成功");
}else{
NSLog(@"修改失败");
}
}
#warning 第七步 删除
- (void)deleteStudent:(NSInteger)number
{
NSString *deleteSql = [NSStringstringWithFormat:@"DELETE FROM lanou23 WHERE number = '%ld'", number];
int result = sqlite3_exec(db, deleteSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}else
{
NSLog(@"删除失败");
}
}
#warning 第八步 查询所有数据
- (NSMutableArray*)selectAllStudent
{
NSMutableArray*array = [NSMutableArrayarray];
NSString *selectSql = @"SELECT * FROM lanou23";
//查询一个或几个数据
NSString *sql = [NSStringstringWithFormat:@"SELECT * FROM lanou23 WHERE name = %@",@"大水杯"];
//声明数据库跟随指针,作用是跟踪表里的每一行
sqlite3_stmt*stmt =nil;
//查询之前准备工作,将数据库对象db,sql语句,stml关联起来
//1.参数1.数据库对象
//2.参数2.sql语句
//3.参数3.sql语句长度, -1为不限长度
//4.参数4.数据库跟随指针
int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt,nil);
if (result == SQLITE_OK) {
NSLog(@"查询成功");
//循环遍历每条数据
while(sqlite3_step(stmt) ==SQLITE_ROW) {
//取值
intnumber =sqlite3_column_int(stmt,0);
constunsignedchar * name = sqlite3_column_text(stmt,1);
constunsignedchar * gender = sqlite3_column_text(stmt,2);
intage =sqlite3_column_int(stmt,3);
NSString*nameStr = [NSStringstringWithUTF8String:(constchar*)name];
NSString*genderStr = [NSStringstringWithUTF8String:(constchar*)gender];
NSIntegernumberInteger = number;
NSIntegerageInteger = age;
Person*person = [[Personalloc]init];
person.name= nameStr;
person.gender= genderStr;
person.age= ageInteger;
person.number= numberInteger;
[array addObject:person];
}
}else{
NSLog(@"查询失败");
}
return array;
#warning 第三步 创建数据库对象
static sqlite3 *db = nil;
#warning 第四步 打开数据库
- (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 = %@", 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 INTEGER)";
int result = sqlite3_exec(db, createSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"创建表成功");
}else{
NSLog(@"创建表失败");
}
}else{
NSLog(@"数据库打开失败");
}
}
#warning 第五步 插入数据
- (void)insertStudent:(Person*)person
{
//创建插入语句
NSString *insertSql = [NSStringstringWithFormat:@"INSERT INTO lanou23(name, gender, age) values('%@', '%@', '%ld')", person.name, person.gender, person.age];
int result = sqlite3_exec(db, insertSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"插入成功");
}else{
NSLog(@"插入失败");
}
}
#warning 第六步 修改数据
- (void)updateStudent:(Person*)person number:(NSInteger)number
{
NSString *updataSql = [NSStringstringWithFormat:@"UPDATE lanou23 SET name = '%@', gender = '%@', age = '%ld' WHERE number = '%ld'", person.name, person.gender, person.age, number];
int result = sqlite3_exec(db, updataSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"修改成功");
}else{
NSLog(@"修改失败");
}
}
#warning 第七步 删除
- (void)deleteStudent:(NSInteger)number
{
NSString *deleteSql = [NSStringstringWithFormat:@"DELETE FROM lanou23 WHERE number = '%ld'", number];
int result = sqlite3_exec(db, deleteSql.UTF8String,NULL,NULL,nil);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}else
{
NSLog(@"删除失败");
}
}
#warning 第八步 查询所有数据
- (NSMutableArray*)selectAllStudent
{
NSMutableArray*array = [NSMutableArrayarray];
NSString *selectSql = @"SELECT * FROM lanou23";
//查询一个或几个数据
NSString *sql = [NSStringstringWithFormat:@"SELECT * FROM lanou23 WHERE name = %@",@"大水杯"];
//声明数据库跟随指针,作用是跟踪表里的每一行
sqlite3_stmt*stmt =nil;
//查询之前准备工作,将数据库对象db,sql语句,stml关联起来
//1.参数1.数据库对象
//2.参数2.sql语句
//3.参数3.sql语句长度, -1为不限长度
//4.参数4.数据库跟随指针
int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt,nil);
if (result == SQLITE_OK) {
NSLog(@"查询成功");
//循环遍历每条数据
while(sqlite3_step(stmt) ==SQLITE_ROW) {
//取值
intnumber =sqlite3_column_int(stmt,0);
constunsignedchar * name = sqlite3_column_text(stmt,1);
constunsignedchar * gender = sqlite3_column_text(stmt,2);
intage =sqlite3_column_int(stmt,3);
NSString*nameStr = [NSStringstringWithUTF8String:(constchar*)name];
NSString*genderStr = [NSStringstringWithUTF8String:(constchar*)gender];
NSIntegernumberInteger = number;
NSIntegerageInteger = age;
Person*person = [[Personalloc]init];
person.name= nameStr;
person.gender= genderStr;
person.age= ageInteger;
person.number= numberInteger;
[array addObject:person];
}
}else{
NSLog(@"查询失败");
}
return array;
}