Android sql 语句 分页加载 一次取10 条

String sql10= "select  * from "+Constants.TABLE_MAILS+ " where "+ Constants.TABLE_MAILS_FOLDER +" =? and "+Constants.TABLE_MAILS_COUNT+" =? "+" order by "+Constants.CONTACTSID+" desc "+" limit 4,8 ";//4是起始index（0为原点），desc方向取8条数据。所以是【4，8+4-1】=【4,8+4）//String sql10="select  * from ( "//+ "select  * from "+Constants.TABLE_MAILS//+ " where "+ Constants.TABLE_MAILS_FOLDER +" =? and "+Constants.TABLE_MAILS_COUNT+" =? "+" order by "+Constants.CONTACTSID+" desc "+" limit 0,8 "//+ " ) order by "+Constants.CONTACTSID+" asc"+" limit 0,4 ";

/** * 获取 从脚标M_N之间的数据; *  * 未读和星标邮件待会 处理  * @param folderName * @param countStr * @param startIndex * @return */public List<MailBean> queryIndex10sMailByFName(String folderName,String countStr,int startIndex ) {Log.i("queryIndexM_NMailByFName", "--------------------------start");List<MailBean> lists = new ArrayList<MailBean>();if (folderName.equals("所有未读")) {return queryNewMailByMsgCount(true, countStr);}else if (folderName.equals("星标邮件")) {return queryStarMailByMsgCount(true, countStr);}MailBean bean;Cursor cursor = null;SQLiteDatabase db = helper.getReadableDatabase();String sql ="select * from " + Constants.TABLE_MAILS+ " where "+ Constants.TABLE_MAILS_FOLDER +"=? and "+Constants.TABLE_MAILS_COUNT+"=?";//+ " ORDER BY " + Constants.KEY_MESSAGE_DATE + " desc";String selection[] = {folderName,countStr};cursor = db.rawQuery(sql, selection);int mailSum=cursor.getCount();if (mailSum < 1) {Log.i("queryIndexM_NMailByFName", folderName+"Group is null");db.close();cursor.close();return lists;} else {//String sqlPer10="SELECT TOP 10 * FROM (SELECT TOP 20 * FROM TableName ORDER BY _id ASC) ORDER BY _id DESC";String sql10= "select  * from "+Constants.TABLE_MAILS+ " where "+ Constants.TABLE_MAILS_FOLDER +" =? and "+Constants.TABLE_MAILS_COUNT+" =? "+" order by "+Constants.CONTACTSID+" desc "+" limit 4,8 ";//4是起始index（0为原点），desc方向取8条数据。所以是【4，8+4-1】=【4,8+4）//String sql10="select  * from ( "//+ "select  * from "+Constants.TABLE_MAILS//+ " where "+ Constants.TABLE_MAILS_FOLDER +" =? and "+Constants.TABLE_MAILS_COUNT+" =? "+" order by "+Constants.CONTACTSID+" desc "+" limit 0,8 "//+ " ) order by "+Constants.CONTACTSID+" asc"+" limit 0,4 ";String selPer10[] = {folderName,countStr};cursor=null;cursor = db.rawQuery(sql10, selPer10);int num2=cursor.getCount();System.out.println("num2  = "+num2);if (cursor.getCount()<1) {db.close();cursor.close();return lists;}else {while (cursor.moveToNext()) {String count = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_COUNT));String mailFolder = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_FOLDER));String msgID = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_MSGUID));String date = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_DATE));String from = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_FROM));String tos = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_TOS));String ccs = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_CCS));String subject = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_SUBJECT));String contentType = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_CONTENTTYPE));String summary = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_SUMMARY));String content = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_CONTENT));boolean isNew = cursor.getInt(cursor.getColumnIndex(Constants.TABLE_MAILS_ISNEW))==1?true:false;boolean isContainAttach = cursor.getInt(cursor.getColumnIndex(Constants.TABLE_MAILS_ISCONTAINATTACH))==1?true:false;String attachNames = cursor.getString(cursor.getColumnIndex(Constants.TABLE_MAILS_ATTACHNAMES));boolean isStar = cursor.getInt(cursor.getColumnIndex(Constants.TABLE_MAILS_ISSTAR))==1?true:false;boolean isReplySign = cursor.getInt(cursor.getColumnIndex(Constants.TABLE_MAILS_ISREPLAYSIGN))==1?true:false;bean = new MailBean(count, mailFolder, msgID, date, from, tos, ccs, subject, contentType, summary ,content, isNew, isContainAttach, attachNames, isStar, isReplySign);lists.add(bean);  }   }    }db.close();cursor.close();return lists;}

注意：在使用的降序和升序的时候， order    by ？？？ 这个地方这一列要是integer 类型，不然数据库按照这一列排序。（不能是   varchar（50） 和text）

运行过程：数据库先按照 ？？？ 这一列排序，再取出 【startIndex，endIndex） 之间的数据；备注：【 ， ） 数学里面的开闭区间你懂得

/** * 创建 邮件 数据列表 * @param db */private void createMailsTable(SQLiteDatabase db){String sql="create table if not exists "+Constants.TABLE_MAILS+"("+ Constants.CONTACTSID +" integer primary key autoincrement,"+ Constants.TABLE_MAILS_COUNT+" text,"+ Constants.TABLE_MAILS_FOLDER+" text,"+ Constants.TABLE_MAILS_MSGUID+" integer,"+ Constants.TABLE_MAILS_DATE+" text,"+ Constants.TABLE_MAILS_FROM+" text,"+ Constants.TABLE_MAILS_TOS+" text,"+ Constants.TABLE_MAILS_CCS+" text,"+ Constants.TABLE_MAILS_SUBJECT+" text,"+ Constants.TABLE_MAILS_CONTENTTYPE+" text,"+ Constants.TABLE_MAILS_SUMMARY+" text,"+ Constants.TABLE_MAILS_CONTENT+" text,"+ Constants.TABLE_MAILS_ISNEW+" integer,"+ Constants.TABLE_MAILS_ISCONTAINATTACH+" integer,"+ Constants.TABLE_MAILS_ATTACHNAMES+" text,"+ Constants.TABLE_MAILS_ATTACHNAMES_SDPATH+" text,"+ Constants.TABLE_MAILS_ISSTAR+" integer,"+ Constants.TABLE_MAILS_ISREPLAYSIGN+" integer)";Log.d("Mine 数据库", sql);db.execSQL(sql);}