Sqlite3—IOS成长之路

记录自己学习IOS经历 

欢迎和大家一起讨论学习

首先

在项目配置General最下面导入sqlite3.0的包

Command + N 在IOS 里创建Cocos Touch Class 创建DBManager  ，创建完生成 一个.h和.m文件

在.h文件导入sqlite3.h的包

#import <Foundation/Foundation.h>
#import "sqlite3.h" 
#import "Student.h"

@interface DBManager : NSObject{
    
    //数据库路径
    NSString *_databasePath;

}
//获取实例类方法（java中叫静态方法 static修饰 oc叫类方法 用+修饰）
+(DBManager *)getInstance;
//创建数据库
-(BOOL)createDB;
//插入数据
-(BOOL)insertData:(Student *)student;
//查询数据
-(NSMutableArray *)queryData;
//删除数据
-(BOOL)deleteData:(NSInteger)index;
@end

DBManager.m文件实现方法

#import "DBManager.h"

static 	DBManager * dbManager;
static  sqlite3 *database=nil;
static sqlite3_stmt *statement=nil;


@implementation DBManager

+(DBManager *)getInstance{
    if(dbManager==nil){
        dbManager=[[super allocWithZone:nil] init];
        [dbManager createDB];
    }
    return dbManager;
}

-(BOOL)createDB{
    NSString *docsDir;
    NSArray * dirPaths;
    //获取sqlite目录
    //参数1: 文件夹的名字 参数2: 查找域 参数3: 是否使用绝对路径
    dirPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir=dirPaths[0];
    //在这个目录创建一个student的sqlite文件
    _databasePath=[[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:@"student.db"]];
    NSLog(@"%@",_databasePath);
    BOOL isSuccess=YES;
    
    NSFileManager *fileMg=[NSFileManager defaultManager];
    //判断数据库是否存在
    if([fileMg fileExistsAtPath:_databasePath]==NO){
        //将路径转为C字符串
        const char *dbpath=[_databasePath UTF8String];
        
        if(sqlite3_open(dbpath, &database)==SQLITE_OK){
            
            //创建表
            const char *sql_stmt="CREATE TABLE IF NOT EXISTS student(_id INTEGER PRIMARY KEY AUTOINCREMENT,name text,age int);";
            if(sqlite3_exec(database, sql_stmt, nil, nil, nil)!=SQLITE_OK){
                isSuccess=NO;
                NSLog(@"Failed to create table");
            }
            sqlite3_close(database);
            return isSuccess;
        }else{
            
            isSuccess=NO;
            NSLog(@"failed to open/create database");
        }
        
    }
    return isSuccess;
}


//打开数据库
-(BOOL)openDatabase{
    if(sqlite3_open([_databasePath UTF8String], &database)==SQLITE_OK){
        return YES;
    }
     NSLog(@"open database to fail");
    return NO;
}
//关闭数据库
-(void)closeDatabase{
    sqlite3_close(database);
}


//插入数据
-(BOOL)insertData :(Student *)student{
    if(![self openDatabase]){
        return NO;
    }
    NSString *sql=[NSString stringWithFormat:@"insert into student(name,age) values('%@','%d')",student.name,(int)student.age];

    int result=sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil);
    if(result==SQLITE_OK){
        if(sqlite3_step(statement)==SQLITE_DONE){
            sqlite3_finalize(statement);
            [self closeDatabase];
            NSLog(@"插入成功");
            return YES;
        }
    }else{
        NSLog(@"插入失败");
    }
    sqlite3_finalize(statement);
    [self closeDatabase];
    return NO;
}


//查询数据
-(NSMutableArray *)queryData{
    if(![self openDatabase]){
        return nil;
    }
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *sql=@"select * from student";
    if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil)==SQLITE_OK){
        while(sqlite3_step(statement)==SQLITE_ROW){
            Student *s=[[Student alloc]init];
            [s setName:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)]];
            [s setAge: sqlite3_column_int(statement, 2)];
            [s setID:sqlite3_column_int(statement, 0)];
            NSLog(@"%d",s.ID);
            [array addObject:s];
        }
        sqlite3_finalize(statement);
        [self closeDatabase];
        return array;
    }
    return nil;
}
//删除数据
-(BOOL)deleteData:(NSInteger )index{
    if(![self openDatabase]){
        return NO;
    }
    NSString *sql=[NSString stringWithFormat:@"delete from student where _id=%zd",index];
    if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil)==SQLITE_OK){
        //更新数据库
        sqlite3_step(statement);
        //释放statement
        sqlite3_finalize(statement);
        [self closeDatabase];
        NSLog(@"删除成功");
    }else{
        NSLog(@"delete data to fail");
        [self closeDatabase];
        return NO;
    }

    return  YES;
}

Command +N 创建Student对象

student.h

#import <Foundation/Foundation.h>

@interface Student : NSObject{
  
    //用属性定义则需要自写set和get方法
    @private
    NSString *_name;
    NSInteger _age;
 
}
//用@property开头修饰的oc会自带set和get方法
@property(nonatomic,assign) NSInteger ID;

-(void)setName :(NSString *)name;
-(void)setAge :(NSInteger)age;
-(NSString *)name;
-(NSInteger)age;


@end

student.m实现方法

#import "Student.h"

@implementation Student

-(void)setName:(NSString *)name{
    _name=name;
}
-(void)setAge:(NSInteger)age{
    _age=age;
}
-(NSString *)name{
    return _name;
}
-(NSInteger)age{
    return _age;
}

@end