Database—Sqlite3.0

#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;

}