Android—SQlite数据库提高篇之数据库分页查询

 根据此方法来确定数据库总数据可以最多分几页进行查询

参数：page_size：是一页查询数据的数量

type，vid：是查询数据库的条件，根据这些条件查询来确定数据库中查询数据的总数。

private int getMaxPageSize(int page_size,String type,String vid){

opendb(); //打开数据库
StringBuffer sql_page=new StringBuffer("select count(*) from wonder_classphoto_tb where user_id=? and type=? and ");

if(TextUtils.equals(type, HUODONG)){
sql_page.append("class_id=?");
}else{
sql_page.append("child_id=?");
}

Cursor cursor_page=db.rawQuery(sql_page.toString(), new String[]{user_id,type,vid});

cursor_page.moveToLast(); //将查询数据库的游标移动到最后一行
int all_bean=cursor_page.getInt(0); //获取总数据有多少行数据
cursor_page.close(); //关闭游标
closedb(); //关闭数据库
int max_page_size=all_bean/page_size+1; //获取分页 页面总数
return max_page_size;
}



/**
* type :huodong
* :chengzhang
* @param type
* @param id 
*/
public void getList(List list,String type,String vid,int page_index,int page_size){
int max_page_size=getMaxPageSize(page_size,type,vid);
if(page_index>max_page_size)page_index=max_page_size; //如果当前请求数据的页数大于数据库分页总数则page_index=max_page_size
opendb();
StringBuffer sql=new StringBuffer("select * from wonder_classphoto_tb where type=? and user_id=? and ");
if(TextUtils.equals(type, HUODONG)){
sql.append("class_id=?");
}else{
sql.append("child_id=?");
}
sql.append(" order by create_time desc limit "+page_size+" offset "+(page_index-1)*page_size);//查询的数据 page_size,(page_index-1)*page_size",查询结果中以第(page_index-1)*page_size)条记录为基准（包括第(page_index-1)*page_size)条）,返回page_size行数据
Cursor cursor=db.rawQuery(sql.toString(), new String[]{type,user_id,vid});
while(cursor.moveToNext()){
String id=cursor.getString(WonderShowDBHelper.server_id_cp);
String create_time=cursor.getString(WonderShowDBHelper.create_time_cp);
String title=cursor.getString(WonderShowDBHelper.title_cp);
String logo_url=cursor.getString(WonderShowDBHelper.logo_url_cp);
String remark=cursor.getString(WonderShowDBHelper.remark_cp);
String child_count=cursor.getString(WonderShowDBHelper.child_count_cp);
PhotoAlbumBean bean=new PhotoAlbumBean(id, create_time, title, logo_url, remark, child_count);
if(list.contains(bean)){
list.remove(bean);
}
list.add(bean);
}
cursor.close();
closedb();

}

2.00000000000000000000000000000000000

 //查询数据
    private void query(int pageNo){
     mSqLiteDatabase=mMySQLiteOpenHelper.getReadableDatabase();
     Cursor cursor=null;
     if(isfirst){
      cursor= mSqLiteDatabase.query(TableName, null, null,
       null,null, null, null, null);
      pageCount=cursor.getCount()/limit+1; //pageCount获取分页页面总数
      isfirst=false;
     }
      cursor= mSqLiteDatabase.query(TableName, null, null,
            null,null, null, null, limit*(pageNo-1)+","+limit);//"5,9",标志从第下标为5的行开始,返回9行数据
       System.out.println(limit*(pageNo-1)+"--=--"+limit*pageNo);
     s
     setpageNo(pageNo);
     setAdapter(cursor);
  
    }