IOS数据库操作(Sqlite)

一、概述

SQLite  是一个轻量级的关系数据库。
SQLite最初的设计目标是用于嵌入式系统，它占用资源非常少，在嵌入式设备中，只需要几百K的内存就够了，
目前应用于浏览器、Android、iOS以及一些便携需求的小型web应用系统。在iOS中使用时SQLite，只需要加入 libsqlite3.dylib 依赖以及引入 sqlite3.h 头文件即可。

二、数据库操作

数据库的操作包括表的创建，添加数据、查询数据、修改数据、删除数据、事务

数据库的创建或者打开

-(BOOL)OPenDB{
    NSArray *array =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentpath = [array objectAtIndex:0];
    NSString *destpath =[documentpath stringByAppendingPathComponent:DBName];
    //如果数据库存在，则直接打开，如果不存在，则创建
	//打开数据库，这里的[path UTF8String]是将NSString转换为C字符串，因为SQLite3是采用可移植的C编写的
    if(sqlite3_open([destpath UTF8String], &db) == SQLITE_OK){
        NSLog(@"创建数据数成功");
        return YES;
    }else{
        NSLog(@"创建数据库失败");
        sqlite3_close(db);
        return NO;
    }
}

IOS中没有提供创建数据库的命令，当使用sqlite3_open打开数据库时，会检测指定路径下面是否有该数据库，如果有则直接打开，如果没有，则创建
打开数据库以后我们可以创建表、对表中的数据进行增、删、改、查等操作，IOS中，sqlite3使用sqlite3_exec来进行表的创建、插入、修改、删除等操作
首先我们对IOS的Sqlite3_exec进行简单的封装，代码如下:

//可以执行添加、修改、删除操作(不需要返回值得)
-(void)ExecuteSql:(NSString *)sql{
    if([self OPenDB]){
        char *errMsg = nil;
        if(sqlite3_exec(db, [sql UTF8String],nil, nil, &errMsg)==SQLITE_OK){
            NSLog(@"数据库操作成功");
        }else{
            NSLog(@"数据库操作失败:%s",errMsg);
        }
    }
}

创建一张表，代码如下：

-(void)ExecuteSql:(NSString *)sql{
    if([self OPenDB]){
        char *errMsg = nil;
        if(sqlite3_exec(db, [sql UTF8String],nil, nil, &errMsg)==SQLITE_OK){
            NSLog(@"数据库操作成功");
        }else{
            NSLog(@"数据库操作失败:%s",errMsg);
        }
    }
}

对表中插入数据，代码如下：
拼接SQL语句

-(void)BtnInsert:(id)sender{
    
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO Member('Name','Age','Info','Weight') values('%@','%@','%@','%@')",@"Sandy",@"22",@"Good Student",@"65.6"];
    [self ExecuteSql:insertSql];
    
    NSString *insertSql1 = [NSString stringWithFormat:@"INSERT INTO Member('Name','Age','Info','Weight') values('%@','%@','%@','%@')",@"Robbin",@"23",@"Sale Manager",@"60.62"];
    [self ExecuteSql:insertSql1];
    
    NSString *insertSql2 = [NSString stringWithFormat:@"INSERT INTO Member('Name','Age','Info','Weight') values('%@','%@','%@','%@')",@"DoubleIcon",@"21",@"Company Manager",@"62.6"];
    [self ExecuteSql:insertSql2];
    
    sqlite3_close(db);
}

参数化的形式添加数据

-(void)BtnAddData:(id)sender{
    [self OPenDB];
    NSString *paraSql =@"INSERT INTO Member(Name,Age,Info,Weight) values(?,?,?,?)";
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, [paraSql UTF8String], -1, &statement, NULL) != SQLITE_OK){
        return;
    }
    
    const char *name = [@"Geoge" cStringUsingEncoding:NSUTF8StringEncoding];
    sqlite3_bind_text(statement, 1, name, strlen(name), SQLITE_STATIC);//index是从一开始
    
    sqlite3_bind_int(statement, 2, 24);
    
    const char *info =[@"new info" cStringUsingEncoding:NSUTF8StringEncoding];
    sqlite3_bind_text(statement, 3, info, strlen(info),SQLITE_STATIC);
    
    sqlite3_bind_double(statement, 4, 66.1);
   
    if(sqlite3_step(statement)!=SQLITE_DONE){
        sqlite3_finalize(statement);
        return;
    }
    NSLog(@"参数化添加数据");
    
    sqlite3_finalize(statement);
    
}

查询数据：

-(void)BtnSelect:(id)sender{
    [self OPenDB];
    NSString *querySql = @"select * from Member";
    sqlite3_stmt *statement;
   
    if(sqlite3_prepare_v2(db, [querySql UTF8String], -1, &statement, nil) == SQLITE_OK){
        while (sqlite3_step(statement) == SQLITE_ROW) {
            int ID = sqlite3_column_int(statement, 0);
            char *Name = (char *)sqlite3_column_text(statement, 1);
            NSString *NameStr =[[NSString alloc] initWithUTF8String:Name];
            
            float weight = sqlite3_column_double(statement, 4);
            
            char *Info = (char *)sqlite3_column_text(statement, 3);
            NSString *InfoStr = [[NSString alloc] initWithUTF8String:Info];
            
            char *createDate = (char *)sqlite3_column_text(statement, 5);
            NSString *createDateStr =[[NSString alloc] initWithUTF8String:createDate];
            
            NSLog(@"ID:%d,Name:%@,Info:%@,Weight:%f,CreateDate:%@",ID,NameStr,InfoStr,weight,createDateStr);
        }
    }else{
        NSLog(@"查询数据库失败");
    }
    sqlite3_close(db);
    
}

修改数据：

-(void)BtnUpdate:(id)sender{
    [self OPenDB];
    NSString *updateSql =[NSString stringWithFormat:@"update '%@' set '%@' = '%@' where %@ = %@",@"Member",@"Info",@"New Info",@"ID",@"2"];
    [self ExecuteSql:updateSql];
    sqlite3_close(db);
}

删除数据：

-(void)BtnDelete:(id)sender{
    [self OPenDB];
    NSString *deleteSql = [NSString stringWithFormat:@"delete  from '%@' where %@ = %@",@"Member",@"ID",@"1"];
    [self ExecuteSql:deleteSql];
    sqlite3_close(db);
}

以事务的方式对数据进行操作

-(void)BtnTransaction:(id)sender{
    [self OPenDB];
    
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO Member('Name','Age','Info','Weight') values('%@','%@','%@','%@')",@"Jack",@"20",@"Jack is",@"62.6"];
    
    NSString *insertSql1 = [NSString stringWithFormat:@"INSERT INTO Member('Name','Age','Info','Weight') values('%@','%@','%@','%@')",@"Bill",@"21",@"New Student",@"61.62"];
    
    NSString *insertSql2 = [NSString stringWithFormat:@"INSERT INTO Member('1Name','Age','Info','Weight') values('%@','%@1','%@a','%@')",@"Dion",@"23",@"Manager",@"68.6"];
   
    @try {
        char *errorMsg = nil;
        if(sqlite3_exec(db, "begin transaction", NULL, NULL, &errorMsg) == SQLITE_OK){//begin transaction可以写为BEGIN
            sqlite3_free(errorMsg);
            NSLog(@"启动事务");
            
            if(sqlite3_exec(db,[insertSql UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK){
                if(sqlite3_exec(db, "rollback transaction", NULL, NULL, &errorMsg) == SQLITE_OK){//rollback transaction可以写为ROLLBACK
                    NSLog(@"事务回滚成功");
                }
            }
            if(sqlite3_exec(db,[insertSql1 UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK){
                if(sqlite3_exec(db, "rollback transaction", NULL, NULL, &errorMsg) == SQLITE_OK){
                    NSLog(@"事务回滚成功");
                }
            }
            if(sqlite3_exec(db,[insertSql2 UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK){
                if(sqlite3_exec(db, "rollback transaction", NULL, NULL, &errorMsg) == SQLITE_OK){
                    NSLog(@"事务回滚成功");
                }
            }
        }else{
            NSLog(@"开始事务失败");
            sqlite3_free(errorMsg);
        }
        if(sqlite3_exec(db,"commit transaction", NULL, NULL, &errorMsg)==SQLITE_OK){//commit transaction可以写为COMMIT
            sqlite3_free(errorMsg);
            NSLog(@"提交事务成功");
        }
    }
    @catch (NSException *exception) {
        char *errorMsg = nil;
        if(sqlite3_exec(db, "rollback transaction", NULL, NULL, &errorMsg) == SQLITE_OK){
            NSLog(@"事务回滚成功");
        }
        sqlite3_free(errorMsg);
    }
    @finally {
        sqlite3_close(db);
    }
}

上面就是IOS中对Sqlite数据库的操作，IOS中对数据库操作的类封装的不太好用，我们在使用的时候可以自己封装一下或者使用网上已经封装好的

示例下载