Android SQlite to json

Question

I have some data stored inside SQlite at my android application and I want to export it into json file and send it to a server via post HTTP. You can actually see my code in this link - Old question containing my code

After searching the web, I'm only finding samples of doing it the other way around (from json to sqlite). I'll be happy to receive some help/push to the right direction.

Edit -

I get a small exeption while trying to initilise Gson - String json = gson.toJson(Record); . Can you take a look please -

public ArrayList<Record> getAllRecordsArray() {
        ArrayList<Record> localList = new ArrayList<Record>();

        String selectQuery = "SELECT * FROM " + TABLE_RECORD;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        //Loops through all rows and adds them to the local list
        if(cursor.moveToFirst())
        {
            do {
                //Get record information
                Record record = new Record();
                record.setpLat(cursor.getDouble(0));
                record.setpLong(cursor.getDouble(1));
                record.setpAcc(cursor.getFloat(2));
                record.setpTime(cursor.getLong(2));
                //Add record  to list
                localList.add(record);
            } while (cursor.moveToNext());
        }

        return localList;
    }

    Gson gson = new Gson();
    String json = gson.toJson(Record);

Check this out http://stackoverflow.com/questions/25722585/convert-sqlite-to-json — Rohit5k2, Dec 29 '14 at 21:55
Thanks. when handling myPath=DB_PATH, How can I know my database path ? — GeoRay, Dec 29 '14 at 22:07
The path where you created your database. By default its something like this `/data/data/com.me.myapp/databases/mydb.db` Or you can use `String dbname = "mydb.db"; Path dbpath = ctx.getDatabasePath(dbname);` — Rohit5k2, Dec 29 '14 at 22:12

score 4 · Answer 1 · answered Dec 29 '14 at 22:13

4

Using the GSON library is by far the easiest way of handling it.

1) Initilize an ArrayList
2) Retrieve all the data from your database and store it in the ArrayList
3) Use gson.toJson(NameOfArrayList);
4) DONE. You now have a string containing all the data from your database.

answered Dec 29 '14 at 22:13

Moonbloom

7,738
3
26
38

Thanks you. Sounds indeed easy, but I have no experience with that. Can you provide an example please ? How can I insert the data from the SQlite into the ArrayList ? – GeoRay Dec 29 '14 at 22:27
I quickly put together this pastebin, with code snippets taken from one of my own projects: http://pastebin.com/2WYDvyha With that, you can get an ArrayList with all the data, then you can simply create a gson variable and use that: Gson gson = new Gson(); String jsonString = gson.toJson(arrayList); – Moonbloom Dec 30 '14 at 01:29
Thank you. I get a little exception, I added it to the main post. can you take a look please ? – GeoRay Dec 30 '14 at 08:03
There is absolutely no way for me to identify your problem if you don't post the actual exception message. – Moonbloom Dec 30 '14 at 12:08

score 1 · Answer 2 · answered Dec 29 '14 at 21:57

1

Have you considered using a library such as Gson? It allows you to easily convert a POJO (Plain old java object) to JSON.

You can simply get your object form your db and then do the following.

MyObject obj = new MyObject();
Gson gson = new Gson();
String json = gson.toJson(obj);

Its very useful, here is a link to the user guide: https://sites.google.com/site/gson/gson-user-guide

Good Luck!

answered Dec 29 '14 at 21:57

DejanRistic

2,039
18
13

Thank you, but it looks a bit too complicated for me. – GeoRay Dec 29 '14 at 22:06

score 0 · Answer 3 · edited Sep 29 '18 at 18:20

The below procedure could help ,

public JSONArray QueryAsJSON(String Query)
            throws org.json.simple.parser.ParseException {
        String out = "[";

        try {
            Cursor c = null;

            c = database.rawQuery(Query, null);
            c.moveToFirst();
            String VergCol = "";

            for (int j = 0; j < c.getCount(); j++) {
                out += VergCol + "{";
                String vergRow = "";

                for (int i = 0; i < c.getColumnCount(); i++) {
                    out += vergRow + "\"" + c.getColumnName(i) + "\"" + ":"
                            + "\"" + c.getString(i) + "\"";
                    vergRow = ",";
                }

                out += "}";
                VergCol = ",";
                c.moveToNext();
            }
        } 
        catch (Exception e1) {
            e1.printStackTrace();
        }

        out += "]";

        JSONParser parser = new JSONParser();
        org.json.simple.JSONArray jsonArray = (org.json.simple.JSONArray) parser
                .parse(out);
        return jsonArray;

}

please don't ever do this. – Alberto M Mar 17 '20 at 15:44 — Alberto M, Mar 17 '20 at 15:44

Android SQlite to json

3 Answers3