How to get Sqlite data as Json using GSON

Question

I'm trying to format my sqlite data from all my columns into a JSONArray.

My sqlite data example is as follows:

id | name | product |
  
 1 | stev |[{"id":"1","title":"box"}] 

 1 | Lian |[{"id":"2","title":"bag"}] 
 
 1 | beny |[{"id":"3","title":"pen"}]

I am saving a JSONArray as a string in my database under the column product.

I am trying to create a jsonArray using gson library.

This is what i have tried:

public String composeJSONfromSQLite() {

        ArrayList<HashMap<String, String>> offlineList;
        offlineList = new ArrayList<HashMap<String, String>>();
        String selectQuery = "SELECT  * FROM manyofflineCheckouts ";
  
        SQLiteDatabase database = this.getWritableDatabase();
        Cursor cursor = database.rawQuery(selectQuery, null);
  
        if (cursor.moveToFirst()) {
            do {
                HashMap<String, String> map = new HashMap<String, String>();
              map.put( cursor.getString(0));
              map.put( cursor.getString(1));
              map.put( cursor.getString(2));
              offlineList.add(map);

            } while (cursor.moveToNext());
        }
        database.close();

        Gson gson = new GsonBuilder().create();

        //Use GSON to serialize Array List to JSON

        return gson.toJson(offlineList);
    }

This however gives me in the format of:

[
	{
    	"id":"1",
    	"name":"stev",
    	"product":"[{"id":"1","title":"box"}]"
	},
	{
    	"id":"2",
    	"name":"Lian",
    	"product":"[{"id":"2","title":"bag"}]"
	}
]

This is not the correct Json format because of the double quotes surrounding product array "product":"[{"id":"1","title":"box"}]"

Is there a way to do this?

Are you saying you want the product value to be a JSON array instead of the string you see? — Doug Stevenson
– Doug Stevenson, Commented Apr 12, 2016 at 5:31

varren · Accepted Answer · 2016-04-12 06:06:52Z

1

One way you can solve this is to parse your product json string first.

gson.fromJson(cursor.getString(2), Object[].class) instead of cursor.getString(2) will probably work in your case

Here is demo:

Gson gson = new GsonBuilder().create();

ArrayList<HashMap<String, Object>> offlineList = new ArrayList<>();

// here is what i'm taking about
String product = "[{\"id\":\"2\",\"title\":\"bag\"}]";
Object[] productObj = gson.fromJson(product, Object[].class)

HashMap<String, Object> map = new HashMap<>();
map.put("product", productObj );
map.put("id", 1);
map.put("name", "stev");
offlineList.add(map);

System.out.println(gson.toJson(offlineList));

Result:

[
  {
    "product": [
      {
        "id": "2",
        "title": "bag"
      }
    ],
    "id": 1,
    "name": "stev"
  }
]

edited Apr 12, 2016 at 6:06

answered Apr 12, 2016 at 6:01

varren

14.8k2 gold badges46 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get Sqlite data as Json using GSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related