sqlite 分页查询

网上例子：

select * from users order by id limit 10 offset 0;  //offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果


运用：
sqlitecmd.CommandText = string.Format("select * from GuestInfo order by GuestId limit {0} offset {0}*{1}",size,index-1);

//size:每页显示条数，index页码

我的例子：
#pragma mark- 获取所有的系统消息 - msgType & displayType 分页查询
- (NSMutableArray *)getAllSysMessageBymsgType:(long long)msgType displayType:(long long)displayType page:(NSInteger)page pageSize:(NSInteger)size error:(NSError **)error {
  {
    if (size < 0) {
      return [NSMutableArray array];
    }
   
    __block NSMutableArray * resultArray = [NSMutableArray array];
    __block SSDAOSysMessage *message = nil;
   
    __weak typeof(self) weakObj = self;
    [self.dbPocrchQueue inDatabase:^(FMDatabase *db) {
     
      __strong typeof (weakObj) strongObj = weakObj;
      if (!db) {
        return;
      }
      
      NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@ = %lld and DISPLAY_TYPE = %lld ORDER BY %@ DESC",@"SSSysMessage",@"MSG_TYPE",msgType,displayType,@"MSGSEQ_ID"];
      LOGI(@"************sql:  %@",sql);
     
      FMResultSet *result = [db executeQuery:sql];
     
      while (result.next){
        message = [strongObj sysMessageFromDatabaseResult:result];
        [resultArray addObject:message];
      }
      [result close];
     
      if ([db hadError] && error) {
        *error = [db lastError];
      }
     
    }];
   
    return resultArray;
  }
}
 
 
  
   
 
 

- (void)getAllSysMessageBymsgType:(long long)msgType displayType:(long long)displayType page:(NSInteger)page pageSize:(NSInteger)size error:(NSError **)error callBack:(SSDataResultCallback)callback{

  __weak typeof(self) weakObj = self;
 
  [SSExecutionHelper invokeQueue:self.managerQueue usingBlock:^{
   
    __strong typeof(weakObj) strongObj = weakObj;
   
    NSError * err ;
    NSMutableArray * result = [strongObj.databasePorch getAllSysMessageBymsgType:msgType displayType:displayType page:page pageSize:size  error:&err];
   
    NSMutableArray * array = [NSMutableArray array];
   
    for (SSDAOSysMessage * daoObj in result) {
      SSSysMessage * sysMsg = [[SSSysMessage alloc]init];
      [sysMsg updateWithDAOSysMessage:daoObj];
      [array addObject:sysMsg];
    }
    if(callback)
    {
      callback(array,err);
    }
   
  }];

}

-(void)test_getdata_db_page_callBack:(SSDataResultCallback)callback{
 
  LOGI(@" ********* test_getdata_db_page  ********* " );
 
  //分页查询测试
 
  int pageSize = 10;
  __block int page = 1;
  __weak typeof(self) weakObj = self;
  int msgType = 4;
  int submsgTYpe = 8;
 
  __block NSMutableArray * tmp = [NSMutableArray array];
  [SSExecutionHelper invokeQueue:self.managerQueue usingBlock:^{
    __strong typeof(weakObj) strongObj = weakObj;
  [strongObj getAllSysMessageBymsgType:msgType displayType:submsgTYpe page:page pageSize:pageSize error:nil callBack:
   ^(id data,NSError *err) {
    
      page = page +1;
     
      NSArray * arr = data;
      if (arr.count > 0) {
        [tmp addObjectsFromArray:data];
       
      }else{
        callback(tmp,nil);
      }
     
   [strongObj getAllSysMessageBymsgType:msgType displayType:submsgTYpe page:page pageSize:pageSize error:nil callBack:
   ^(id data,NSError *err) {
       
        NSArray * arr = data;
       
        if (arr.count > 0) {
          [tmp addObjectsFromArray:data];
        }else{
          callback(tmp,nil);
        }
       
      }];
     
    }];
   
  }];
 
 
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!