ios数据持久化之数据库(SQLite)

1,准备工作:引入libsqlite3.0.dylib框架

2,写一个单例,并实现下面的方法

//打开数据库
-(void)openDB;

<span style="font-size:12px;">-(void)openDB
{
    if (nil == db) {
        //拼接数据库存放路径
        NSString *dbPath = [[self documentsPath] stringByAppendingString:@"/Student.sqlite"];
        //根据路径打开数据库
        //如果该路径下没用该数据库,就自动创建一个
        
        //打开数据库
        int result =  sqlite3_open(dbPath.UTF8String, &db);
        //判断数据库是否打开成功
        if (result == SQLITE_OK) {
            NSLog(@"数据库打开成功");
        }else{
            NSLog(@"数据库打开失败");
        }
    }
}</span>

//创建表
-(void)createTable;

<span style="font-size:12px;">-(void)createTable
{
    //要创建表,就要是使用SQL语句
    //SQL语句在这是以字符串的形式存在的
    NSString *createTable = @"CREATE  TABLE  IF NOT EXISTS Student (Sid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , Sname TEXT, Sage TEXT, Ssex TEXT)";
    //执行SQL语句(c语言语法格式)
    int result = sqlite3_exec(db, createTable.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    }else{
        NSLog(@"建表失败");
    }
}</span>

//插入信息
-(void)insertStudent:(Student *)stu;

-(void)insertStudent:(Student *)stu
{
    //准备插入语句
    NSString *insertString = [NSString stringWithFormat:@"INSERT INTO Student (Sname,Sage,Ssex) VALUES ('%@','%@','%@')",stu.name,stu.age,stu.sex];
    //执行语句
    int result = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

//修改信息
-(void)alterMessage;

     SQL语句:"INSERT INTO Student (Sname,Sage,Ssex) VALUES ('%@','%@','%@')",stu.name,stu.age,stu.sex"

//删除信息
-(void)deleteMessage;

    SQL语句:"delete from Student where sid = 5"

//查找全部
-(void)selectAllStudent;

    SQL语句:"select * from Student"

//条件查找
-(void)selectWithSex:(NSString *)sex;

-(void)selectWithSex:(NSString *)sex
{
    //准备SQL语句
    NSString *selectString = @"select * from Student where Ssex = ?";
    //伴随指针
    sqlite3_stmt *stmt = nil;
    //预备执行
    int result = sqlite3_prepare(db, selectString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //绑定?的值
        //1代表第一个?和参数绑定
        sqlite3_bind_text(stmt, 1, sex.UTF8String, -1, NULL);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger Sid = sqlite3_column_int(stmt, 0);
            NSString *Sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString *Sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            NSString *Ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
            NSLog(@"Sid:%d,Sname:%@,Sage:%@,Ssex:%@",Sid,Sname,Sage,Ssex);
        }
        sqlite3_finalize(stmt);
        
    }else{
        sqlite3_finalize(stmt);
        NSLog(@"查询失败");
    }
}

//关闭数据库
-(void)closeDB;

<span style="font-size:12px;">-(void)closeDB
{
    
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        //如果关闭成功,置为nil
        db = nil;
        NSLog(@"数据库关闭成功");
    }else{
        NSLog(@"数据库关闭失败");
    }
}</span>