I am creating an application in which i have a expandable list view which loads the data from sq lite. At first i have set the limit as 10 to the select query and when the list reaches the end i want to load another 10 data's from the sq lite to list view. I have used list-scrolling option to find the last item in the list view but it didn't works as expected can anyone tell me how i have to fetch the data's from sq lite and load those into the list view when the list reaches ends.
Query:
ArrayList<Daybook> daybookDetails = new ArrayList<Daybook>();
String selectquery = "SELECT date,IFNULL(SUM(amountin),0) as amountin,IFNULL(SUM(amountout),0) as amountout,daybookusertype FROM daybookdetails GROUP BY strftime('%Y-%m-%d',date) ORDER BY strftime('%Y-%m-%d',date) DESC LIMIT " + s + "";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectquery, null);
if (cursor.moveToFirst()) {
do {
Daybook daybookentries = new Daybook();
daybookentries.setDate(cursor.getString(0));
daybookentries.setCashin(cursor.getString(1));
daybookentries.setCashout(cursor.getString(2));
daybookDetails.add(daybookentries);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return daybookDetails;
Expandable Listview:
daybooks = new ArrayList<Daybook>();
daybooks = databaseHandler.getAlldaybookentriesdatewise(olimit);
daybooklists = new ArrayList<Daybooklist>();
listDataHeader = new ArrayList<String>();
listDataChild = new HashMap<String, List<Daybooklist>>();
for (int i = 0; i < daybooks.size(); i++) {
String date = daybooks.get(i).getDate();
if (date != null) {
String s = date;
String[] spiliter = s.split("-");
String year = spiliter[0];
String month = spiliter[1];
String dates = spiliter[2];
if (month.startsWith("01")) {
disorderedlist = dates + "Jan" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("02")) {
disorderedlist = dates + "Feb" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("03")) {
disorderedlist = dates + "Mar" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("04")) {
disorderedlist = dates + "Apr" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("05")) {
disorderedlist = dates + "May" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("06")) {
disorderedlist = dates + "Jun" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("07")) {
disorderedlist = dates + "Jul" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("08")) {
disorderedlist = dates + "Aug" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("09")) {
disorderedlist = dates + "Sep" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("10")) {
disorderedlist = dates + "Oct" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("11")) {
disorderedlist = dates + "Nov" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("12")) {
disorderedlist = dates + "Dec" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
}
listDataHeader.add(disorderedlist);
}
chid = new ArrayList<Daybooklist>();
daybooklists = databaseHandler.getAllDaywisedaybookdetails(date);
for (int j = 0; j < daybooklists.size(); j++) {
String name = daybooklists.get(j).getName();
String desc = daybooklists.get(j).getDescription();
String type = daybooklists.get(j).getType();
String usertype = daybooklists.get(j).getUsertype();
String amtin = daybooklists.get(j).getAmountin();
String amtout = daybooklists.get(j).getAmountout();
String extamt = daybooklists.get(j).getExtraamt();
String mobno = daybooklists.get(j).getMobileno();
String dates = daybooklists.get(j).getSdate();
String time = daybooklists.get(j).getCtime();
if (name != null) {
chid.add(new Daybooklist(name, desc, type, usertype, amtin, amtout, extamt, mobno, dates, time));
}
}
listDataChild.put(listDataHeader.get(i), chid);
}
ListView scrolling:
expListView.setOnScrollListener(new AbsListView.OnScrollListener() {
@Override
public void onScrollStateChanged(AbsListView view, int scrollState) {
int btn_initPosY = fabaddnew.getScrollY();
int li_initPosY = li_general.getScrollY();
if (scrollState == SCROLL_STATE_TOUCH_SCROLL) {
listAdapter.isScrolling(true);
fabaddnew.animate().cancel();
fabaddnew.animate().translationYBy(150);
} else {
listAdapter.isScrolling(false);
fabaddnew.animate().cancel();
fabaddnew.animate().translationY(btn_initPosY);
}
}
@Override
public void onScroll(AbsListView view, int firstVisibleItem, int visibleItemCount, int totalItemCount) {
listAdapter.isScrolling(true);
//what is the bottom item that is visible
int lastInScreen = firstVisibleItem + visibleItemCount;
//is the bottom item visible & not loading more already? Load more!
if ((lastInScreen == totalItemCount) && !(loadingMore)) {
if (olimit > totalcount) {
} else {
olimit = olimit + 10;
new LoadDataTask().execute(String.valueOf(olimit));
}
}
}
});
Background task to load more data from sqlite:
class LoadDataTask extends AsyncTask<String, Void, String> {
@Override
protected String doInBackground(String... olimits) {
String limits = null;
loadingMore = true;
try {
limits = olimits[0];
grouplistDataHeader = new ArrayList<String>();
grouplistDataChild = new HashMap<String, List<Daybooklist>>();
groupdaybooklists = new ArrayList<Daybooklist>();
childdaybook = new ArrayList<Daybooklist>();
databasehandler = new DatabaseHandler(getApplicationContext());
loadeddate = new ArrayList<String>();
String selectquery = "SELECT date,IFNULL(SUM(amountin),0) as amountin,IFNULL(SUM(amountout),0),daybookusertype as amountout FROM daybookdetails GROUP BY strftime('%Y-%m-%d',date) ORDER BY strftime('%Y-%m-%d',date) DESC LIMIT '" + limits + "'";
SQLiteDatabase db = databasehandler.getReadableDatabase();
Cursor cursor = db.rawQuery(selectquery, null);
if (cursor.moveToFirst()) {
do {
loadeddate.add(cursor.getString(0));
for (String s : loadeddate) {
newdate = s;
}
String[] spiliter = newdate.split("-");
String year = spiliter[0];
String month = spiliter[1];
String dates = spiliter[2];
if (month.startsWith("01")) {
disorderedlist = dates + "Jan" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("02")) {
disorderedlist = dates + "Feb" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("03")) {
disorderedlist = dates + "Mar" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("04")) {
disorderedlist = dates + "Apr" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("05")) {
disorderedlist = dates + "May" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("06")) {
disorderedlist = dates + "Jun" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("07")) {
disorderedlist = dates + "Jul" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("08")) {
disorderedlist = dates + "Aug" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("09")) {
disorderedlist = dates + "Sep" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("10")) {
disorderedlist = dates + "Oct" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("11")) {
disorderedlist = dates + "Nov" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
} else if (month.startsWith("12")) {
disorderedlist = dates + "Dec" + year;
disorderedlist = disorderedlist.replaceAll("\\s+", "");
}
grouplistDataHeader.add(disorderedlist);
groupdaybooklists = databaseHandler.getAllDaywisedaybookdetails(newdate);
} while (cursor.moveToNext());
/*for (int j = 0; j < groupdaybooklists.size(); j++) {
}
*/
}
cursor.close();
db.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
@Override
protected void onPostExecute(String s) {
// listAdapter.setVTransactionList(grouplistDataHeader);
for(int j = 0 ;j<daybooklists.size();j++){
String name = groupdaybooklists.get(j).getName();
String desc = groupdaybooklists.get(j).getDescription();
String type = groupdaybooklists.get(j).getType();
String usertype = groupdaybooklists.get(j).getUsertype();
String amtin = groupdaybooklists.get(j).getAmountin();
String amtout = groupdaybooklists.get(j).getAmountout();
String extamt = groupdaybooklists.get(j).getExtraamt();
String mobno = groupdaybooklists.get(j).getMobileno();
String datess = groupdaybooklists.get(j).getSdate();
String time = groupdaybooklists.get(j).getCtime();
childdaybook.add(new Daybooklist(name, desc, type, usertype, amtin, amtout, extamt, mobno, datess, time));
}
for(int i = 0;i<grouplistDataHeader.size();i++){
grouplistDataChild.put(grouplistDataHeader.get(i), childdaybook);
}
listAdapter.setTransactionList(grouplistDataHeader, grouplistDataChild);
for (int i = 0; i < listAdapter.getGroupCount(); i++) {
expListView.expandGroup(i);
}
loadingMore = false;
}
@Override
protected void onCancelled() {
// Notify the loading more operation has finished
loadingMore = false;
}
}
I am not getting the next set of datas properly can anyone tell me whether is it the correct way of loading data's from sqlite or is there any better approach to load the data's.
