Fetching data from sqlite and storing in json

Question

I have a table from which I want to fetch all data on behalf of a specific column and then want to save that data in form of JSON so that I can send it over API to the database for saving.

I am unable to get all data from the database though I tried doing it through cursor I am getting single data in this. Please help me in fetching the data and converting it into JSON. This is what I have coded. This is the method from Dbsave class which extends DatabaseOpenHelper.

public Cursor getAllData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from "+"autosave",null);
    return res;
}

And then I am using this method in the Activity like this.

public void getalldata(){
  cursor=dbAutoSave.getAllData();
  if (cursor!=null) {
      if(cursor.moveToNext()) {
          for (int i = 0; i <= cursor.getCount(); i++) {
              aaa = cursor.getString(1);
              String bbb = cursor.getString(2);
              String ccc = cursor.getColumnName(3);
          }

          ArrayList<String> aaaa=new ArrayList<>();
          aaaa.add(aaa);
          Toast.makeText(getApplicationContext(),"bbbbb"+aaaa,Toast.LENGTH_LONG).show();
      }
      cursor.close();
  }
}

I am getting only one data in aaaa. Then I tried doing this with gettersetter but with no benefit.

private void showEmployeesFromDatabase() {
   Cursor cursorEmployees = mDatabase.rawQuery("SELECT * FROM autosave", null);
   if (cursorEmployees.moveToFirst()) {
       do {
           // Pushing each record in the employee list
           employeeList.add(new SetterGetter(
                   cursorEmployees.getString(0),
                   cursorEmployees.getString(1),
                   cursorEmployees.getString(2)
           ));
       } while (cursorEmployees.moveToNext());
   }

   // Closing the cursor
   System.out.println("aaaaaa" + employeeList.get(1));
   cursorEmployees.close();
}

I am unable to parse the data from the list in settergetter. If I will be able to fetch all data, I will use GSON to convert it into JSON.

Answer 1

You are initializing your array list every time inside the cursor Initialize it outside the cursor

public void getalldata(){
  cursor=dbAutoSave.getAllData();
 ArrayList<String> aaaa=new ArrayList<>();
  if (cursor!=null) {
      if(cursor.moveToNext()) {
          for (int i = 0; i <= cursor.getCount(); i++) {
              aaa = cursor.getString(1);
              String bbb = cursor.getString(2);
              String ccc = cursor.getColumnName(3);
          }


          aaaa.add(aaa);
          Toast.makeText(getApplicationContext(),"bbbbb"+aaaa,Toast.LENGTH_LONG).show();
      }
      cursor.close();
  }
}

Answer 2

The loop inside getalldata function is faulty. It's not iterating over the cursor and just looping over the same element again and again. I would like to suggest to change the function like the following.

public void getalldata() {

  // Cursor is loaded with data
  cursor = dbAutoSave.getAllData();
  ArrayList<String> aaaa = new ArrayList<>();

  if (cursor != null) {
      cursor.moveToFirst();

       do {
          aaa = cursor.getString(1);
          String bbb = cursor.getString(2);
          String ccc = cursor.getColumnName(3);

          // Add into the ArrayList here
          aaaa.add(aaa);

       } while (cursor.moveToNext());

       cursor.close();
   }
}

Hope that fixes your problem.

Update

To convert the data stored in the ArrayList to JSON using GSON, you need to add the library first in your build.gradle file. You can find a way of using it here.

Just add the following dependency in your build.gradle file.

dependencies {
  implementation 'com.google.code.gson:gson:2.8.5'
}

GSON takes an object for converting it to JSON. So I would like to suggest you create an object with the elements fetched from your cursor like the following.

public class Data {
    public String aaa;
    public String bbb;
    public String ccc;
}

public class ListOfData {
    public List<Data> dataList;
}

Now modify the function again like the following.

public void getalldata() {

  // Cursor is loaded with data
  cursor = dbAutoSave.getAllData();
  ArrayList<Data> dataList = new ArrayList<Data>();

  if (cursor != null) {
      cursor.moveToFirst();

       do {
          Data data = new Data();
          data.aaa = cursor.getString(1);
          data.bbb = cursor.getString(2);
          data.ccc = cursor.getColumnName(3);

          // Add into the ArrayList here
          dataList.add(data);

       } while (cursor.moveToNext());

       // Now create the object to be passed to GSON
       DataList listOfData = new DataList();
       listOfData.dataList = dataList;

       Gson gson = new Gson();
       String jsonInString = gson.toJson(listOfData); // Here you go! 

       cursor.close();
   }
}