当前位置：首页 > news >正文

iOS sqlite 使用事务操作数据库

news 来源：原创 2024/5/3 9:45:03

业务层代码：

//将解析的更新人员数据批量同步到数据库
+(void)operateCompUsers:(NSMutableArray*)operateCompUsers
{
    sqliteHelper *mysqlite = [[sqliteHelper alloc]init];
    if (operateCompUsers.count<=0) return;
    if([mysqlite openDatabase:@"ucab_db.db"])
    {
        NSMutableArray *transactionSql= [[NSMutableArray alloc]init];
        for (int i=0; i<operateCompUsers.count; i++)
        {
            CompUser *operateCompUser = [operateCompUsers objectAtIndex:i];
            
            if ([operateCompUser.operateType isEqualToString:@"0"])   //删除
            {
                NSString *nsstrSql = [[NSString alloc]initWithFormat:@"%@%@%@",@"delete from cloud_contacts where uid='",operateCompUser.uId,@"'"];
               
                    [mysqlite execSql:nsstrSql];
            }
            if ([operateCompUser.operateType isEqualToString:@"1"])   //可用，新增数据
            {
                //先将数据库中的数据删除
                NSString *nsstrSql = [[NSString alloc]initWithFormat:@"%@%@%@",@"delete from cloud_contacts where uid='",operateCompUser.uId,@"'"];
                [mysqlite execSql:nsstrSql];
                
                //再添加一次
                if (nil==operateCompUser.uId) operateCompUser.uId=@"";
                if (nil==operateCompUser.cn) operateCompUser.cn=@"";
                if (nil==operateCompUser.telephoneNumber) operateCompUser.telephoneNumber=@"";
                if (nil==operateCompUser.departmentNumber) operateCompUser.departmentNumber=@"";
                if (nil==operateCompUser.deptName) operateCompUser.deptName=@"";
                if (nil==operateCompUser.coNo) operateCompUser.coNo=@"";
                if (nil==operateCompUser.coName) operateCompUser.coName=@"";
                if (nil==operateCompUser.cuOrder) operateCompUser.cuOrder=@"";
                if (nil==operateCompUser.mobile) operateCompUser.mobile=@"";
                if (nil==operateCompUser.cuMail) operateCompUser.cuMail=@"";
                if (nil==operateCompUser.cuAllShow) operateCompUser.cuAllShow=@"";
                if (nil==operateCompUser.cuEntryStatus) operateCompUser.cuEntryStatus=@"";
                if (nil==operateCompUser.imagePath) operateCompUser.imagePath=@"";
                if (nil==operateCompUser.sort) operateCompUser.sort=@"";
                if (nil==operateCompUser.duty) operateCompUser.duty=@"";
                if (nil==operateCompUser.sex) operateCompUser.sex=@"0";  //性别默认为男
                
                //组sql语句
                NSString *strSql = [NSString stringWithFormat:@"insert into cloud_contacts (uid,cn,telephoneNumber,departmentNumber,deptName,coNo,coName,cuOrder,mobile,cuMail,cuAllShow,cuEntryStatus,imagePath,sort,duty,sex) values ('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@');",operateCompUser.uId,operateCompUser.cn,operateCompUser.telephoneNumber,operateCompUser.departmentNumber,operateCompUser.deptName,operateCompUser.coNo,operateCompUser.coName,operateCompUser.cuOrder,operateCompUser.mobile,operateCompUser.cuMail,operateCompUser.cuAllShow,operateCompUser.cuEntryStatus,operateCompUser.imagePath,operateCompUser.sort,operateCompUser.duty,operateCompUser.sex];
               
                [transactionSql addObject:strSql];
            }
        }
        [mysqlite execInsertTransactionSql:transactionSql];
        [mysqlite closeDatabase];
    }
}

数据操作层（使用事务）：

//执行插入事务语句
-(void)execInsertTransactionSql:(NSMutableArray *)transactionSql
{
    //使用事务，提交插入sql语句
    @try{
        char *errorMsg;
        if (sqlite3_exec(database, "BEGIN", NULL, NULL, &errorMsg)==SQLITE_OK)
        {
            NSLog(@"启动事务成功");
            sqlite3_free(errorMsg);
            sqlite3_stmt *statement;
            for (int i = 0; i<transactionSql.count; i++)
            {
                if (sqlite3_prepare_v2(database,[[transactionSql objectAtIndex:i] UTF8String], -1, &statement,NULL)==SQLITE_OK)
                {
                    if (sqlite3_step(statement)!=SQLITE_DONE) sqlite3_finalize(statement);
                }
            }
            if (sqlite3_exec(database, "COMMIT", NULL, NULL, &errorMsg)==SQLITE_OK)   NSLog(@"提交事务成功");
            sqlite3_free(errorMsg);
        }
        else sqlite3_free(errorMsg);
    }
    @catch(NSException *e)
    {
        char *errorMsg;
        if (sqlite3_exec(database, "ROLLBACK", NULL, NULL, &errorMsg)==SQLITE_OK)  NSLog(@"回滚事务成功");
        sqlite3_free(errorMsg);
    }
    @finally{}
}