Parsing JSON and save them on SQLite Database

Question

I used to have this kind of simple JSON data and I could successfully parse the attributes and save them into an SQLite Database.

[
  {
  "project_title" : " ",
  "organization_title" : " ",
  "website": "",
  "address": "",
  "keyword" : "",
  "short_code" : "",
  "project_description" : "",
  "smallImageUrl" : "",
  "bigImageUrl" : "",
  "price" : "",
  "country" : "",
  "donationAmount" : "",
  "categories" : "",
  "campaign_id" : "",
  "currency_isoname": "",
  "paypal_email" : "",
  "elv_available" : ""
  }
]

but now I have this a little bit more complicated JSON file:

[
  {
    "location": {
      "deleted_at": "null",
      "documentary_video_length": "81",
      "id": "15",
      "latitude": "52.4134145988286",
      "longitude": "13.0904620846177",
      "position": "5",
      "updated_at": "2011-08-26T15:30:27+02:00",
      "name": "Glienicker Br\u00fccke",
      "text": "",
      "documentary_video_url": "",
      "documentary_thumbnail_url": "",
      "audio_text_url": "",
      "footages": [
        {
          "id": "31",
          "latitude": "52.4134145988286",
          "longitude": "13.0904620846177",
          "position": "12",
          "name": "Glienicker Br\u00fccke 1933",
          "text": "sdcs",
          "thumbnail_url": "",
          "video_url": "",
          "video_length": "2",
          "time_period": {
            "id": "24",
            "name": "1933"
          }
        },
        {
          "id": "32",
          "latitude": "52.4134145988286",
          "longitude": "13.0904620846177",
          "position": "12",
          "name": "Glienicker Br\u00fccke 1985",
          "text": "fvd",
          "thumbnail_url": "",
          "video_url": "",
          "video_length": 35,
          "time_period": {
            "id": 30,
            "name": "1985"
          }
        },
        {
          "id": "33",
          "latitude": "52.4134145988286",
          "longitude": "13.0904620846177",
          "position": "12",
          "name": "Glienicker Br\u00fccke 1989",
          "text": "fghg",
          "thumbnail_url": "",
          "video_url": "",
          "video_length": "41",
          "time_period": {
            "id": "12",
            "name": "1989"
          }
        }
      ]
    }
  }
]

These are the Classes I used to have to parse the JSON and save their attributes into an SQLite Database.

IntentService Class

public class Sync extends IntentService {

    public Sync() {
        super("Sync");
    }
    @Override
    protected void onHandleIntent(Intent intent) {
        Database.OpenHelper dbhelper = new Database.OpenHelper(getBaseContext());
        SQLiteDatabase db = dbhelper.getWritableDatabase();
        DefaultHttpClient httpClient = new DefaultHttpClient();
        db.beginTransaction();
        HttpGet request = new HttpGet(
                "https://....");
        try {
            HttpResponse response = httpClient.execute(request);
            if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
                InputStream instream = response.getEntity().getContent();
                BufferedReader r = new BufferedReader(new InputStreamReader(
                        instream, "UTF-8"), 8000);
                StringBuilder total = new StringBuilder();
                String line;
                while ((line = r.readLine()) != null) {
                    total.append(line);
                }
                instream.close();
                String bufstring = total.toString();
                JSONArray arr = new JSONArray(bufstring);
                Database.Tables tab = Database.Tables.AllTables
                        .get(Database.Project.NAME);
                tab.DeleteAll(db);
                for (int i = 0; i < arr.length(); i++) {
                    tab.InsertJSON(db, (JSONObject) arr.get(i));
                }
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        db.endTransaction();
        db.close();
        getContentResolver().notifyChange(
                Uri.withAppendedPath(Provider.CONTENT_URI,
                        Database.Project.NAME), null);

    }

}

SQLite Database Class

public class Database {

    final OpenHelper openHelper;
    static final String DATABASE_NAME = "mydb";
    static final int DATABASE_VERSION = 6;

    public Database(Context context) throws Exception {
        openHelper = new OpenHelper(context);
    }

    public void Destroy() {
        openHelper.close();
    }

    public static enum ColumnsTypes {
        integer, varchar, guid, datetime, numeric
    };

    static final String COLLATE = "COLLATE LOCALIZED";
    static final String EMPTY = "";

    public static interface Project {
        public static final String NAME = "Project";
        public static String C_PROJECTTITLE = "project_title";
        public static String C_ORGANIZATIONTITLE = "organization_title";
        public static String C_WEBSITE = "website";
        public static String C_ADDRESS = "address";
        public static String C_KEYWORD = "keyword";
        public static String C_SHORTCODE = "short_code";
        public static String C_PROJECTDESCRIPTION = "project_description";
        public static String C_SMALLIMAGE = "smallImageUrl";
        public static String C_BIGIMAGE = "bigImageUrl";
        public static String C_PRICE = "price";
        public static String C_COUNTRY = "country";
        public static String C_DONATIONAMOUNT = "donationAmount";
        public static String C_CATEGORIES = "categories";
        public static String C_CAMPAIGNID = "campaign_id";
        public static String C_PAYPALEMAIL = "paypal_email";
        public static String C_ELVAVAILABLE = "elv_available";
        public static String C_CURRENCY = "currency_isoname";



        public final static String[] C = new String[] { C_PROJECTTITLE,
                C_ORGANIZATIONTITLE, C_WEBSITE, C_ADDRESS, C_KEYWORD, C_SHORTCODE,
                C_PROJECTDESCRIPTION, C_SMALLIMAGE, C_BIGIMAGE, C_PRICE,
                C_COUNTRY, C_DONATIONAMOUNT, C_CATEGORIES, C_CAMPAIGNID, C_PAYPALEMAIL, C_ELVAVAILABLE, C_CURRENCY };
        public final static ColumnsTypes[] CT = new ColumnsTypes[] {
                ColumnsTypes.varchar, ColumnsTypes.varchar,
                ColumnsTypes.varchar, ColumnsTypes.varchar,
                ColumnsTypes.varchar, ColumnsTypes.varchar,
                ColumnsTypes.varchar, ColumnsTypes.varchar,
                ColumnsTypes.varchar,ColumnsTypes.varchar, ColumnsTypes.varchar,
                ColumnsTypes.varchar,ColumnsTypes.varchar,
                ColumnsTypes.varchar,ColumnsTypes.varchar, ColumnsTypes.varchar, ColumnsTypes.varchar};
        public final static boolean[] CN = new boolean[] { false, false, false,
                false, false, false, false, false, false, false, false, false, false, false, false, false,false };
        public final static String[] CS = new String[] { COLLATE, COLLATE,
                COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE, COLLATE };
    }

    public static class Tables {
        String[] columns = null;
        ColumnsTypes[] columnsTypes = null;
        String[] columnsSpecials = null;
        String name = null;
        boolean[] columnsNullable = null;

        Tables(String name, String[] columns, ColumnsTypes[] columnsTypes,
                String[] columnsSpecials, boolean[] columnsNullable) {
            this.name = name;
            this.columns = columns;
            this.columnsTypes = columnsTypes;
            this.columnsSpecials = columnsSpecials;
            this.columnsNullable = columnsNullable;
        }

        public String DropStatment() {
            return "DROP TABLE IF EXISTS " + name;
        }

        public void DeleteAll(SQLiteDatabase db) {
            db.delete(name, null,null);
        }

        public long InsertJSON(SQLiteDatabase db, JSONObject obj)
                throws JSONException {
            ContentValues vals = new ContentValues();
            for (String col : columns) {
                vals.put(col, obj.getString(col));
            }
            return db.insert(name, null, vals);
        }

        public String CreateStatment() {
            StringBuilder sb = new StringBuilder("CREATE TABLE ");
            sb.append(name);
            sb.append(" ([");
            for (int i = 0; i < columns.length; i++) {
                sb.append(columns[i]);
                sb.append("] ");
                sb.append(columnsTypes[i].name());
                sb.append(' ');
                sb.append(columnsSpecials[i]);
                if (!columnsNullable[i])
                    sb.append(" NOT NULL ");
                sb.append(", [");
            }
            sb.append("_id] INTEGER PRIMARY KEY AUTOINCREMENT);");
            return sb.toString();
        }

        public final static Map<String, Tables> AllTables;
        static {
            HashMap<String, Tables> aMap = new HashMap<String, Tables>();
            aMap.put(Project.NAME, new Tables(Project.NAME, Project.C,
                    Project.CT, Project.CS, Project.CN));
            AllTables = Collections.unmodifiableMap(aMap);
        }
    }

    public static class OpenHelper extends SQLiteOpenHelper {

        public OpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            try {
                for (Tables table : Tables.AllTables.values()) {
                    String create = table.CreateStatment();
                    db.execSQL(create);
                }
            } catch (Exception e) {
                Log.e("Exception", e.toString());
            }
        }

        public OpenHelper Recreate() {
            onUpgrade(getWritableDatabase(), 1, 2);
            return this;
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            for (Tables table : Tables.AllTables.values()) {
                db.execSQL(table.DropStatment());
            }
            onCreate(db);
        }

    }       

}

ContentProvider Class

public class Provider extends ContentProvider {

    @Override
    public int delete(Uri arg0, String arg1, String[] arg2) {
        return 0;
    }

    private static final int PROJECTS = 1;
    private static final int PROJECT = 2;
    public static final String PROJECTS_MIME_TYPE = ContentResolver.CURSOR_DIR_BASE_TYPE
            + "/Project";
    public static final String PROJECT_MIME_TYPE = ContentResolver.CURSOR_ITEM_BASE_TYPE
            + "/Project";
    public static final String AUTHORITY = "spendino.de";
    public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY);
    static final UriMatcher matcher = new UriMatcher(UriMatcher.NO_MATCH);
    static final HashMap<String, String> map = new HashMap<String, String>();
    static {
        matcher.addURI(AUTHORITY, "Project", PROJECTS);
        matcher.addURI(AUTHORITY, "Project/#", PROJECT);
        map.put(BaseColumns._ID, BaseColumns._ID);
        map.put(Database.Project.C_BIGIMAGE, Database.Project.C_BIGIMAGE);
        map.put(Database.Project.C_COUNTRY, Database.Project.C_COUNTRY);
        map.put(Database.Project.C_KEYWORD, Database.Project.C_KEYWORD);
        map.put(Database.Project.C_ORGANIZATIONTITLE,
                Database.Project.C_ORGANIZATIONTITLE);
        map.put(Database.Project.C_PRICE, Database.Project.C_PRICE);
        map.put(Database.Project.C_PROJECTDESCRIPTION,
                Database.Project.C_PROJECTDESCRIPTION);
        map.put(Database.Project.C_PROJECTTITLE,
                Database.Project.C_PROJECTTITLE);
        map.put(Database.Project.C_SHORTCODE, Database.Project.C_SHORTCODE);
        map.put(Database.Project.C_SMALLIMAGE, Database.Project.C_SMALLIMAGE);
        map.put(Database.Project.C_DONATIONAMOUNT, Database.Project.C_DONATIONAMOUNT);
        map.put(Database.Project.C_WEBSITE, Database.Project.C_WEBSITE);
        map.put(Database.Project.C_ADDRESS, Database.Project.C_ADDRESS);
        map.put(Database.Project.C_CATEGORIES, Database.Project.C_CATEGORIES);
        map.put(Database.Project.C_CAMPAIGNID, Database.Project.C_CAMPAIGNID);
        map.put(Database.Project.C_PAYPALEMAIL, Database.Project.C_PAYPALEMAIL);
        map.put(Database.Project.C_ELVAVAILABLE, Database.Project.C_ELVAVAILABLE);
        map.put(Database.Project.C_CURRENCY, Database.Project.C_CURRENCY);
    }

    @Override
    public String getType(Uri uri) {
        switch (matcher.match(uri)) {
        case PROJECTS:
            return PROJECTS_MIME_TYPE;
        case PROJECT:
            return PROJECT_MIME_TYPE;
        default:
            throw new IllegalArgumentException("Unknown URL " + uri);
        }
    }

    @Override
    public Uri insert(Uri arg0, ContentValues arg1) {
        // TODO Auto-generated method stub
        return null;
    }

    private Database.OpenHelper mDB;

    @Override
    public boolean onCreate() {
        try {
            mDB = new Database.OpenHelper(getContext());
        } catch (Exception e) {
            Log.e("Exception", e.getLocalizedMessage());
            return false;
        }
        return true;
    }

    @Override
    public Cursor query(Uri uri, String[] projection, String selection,
            String[] selectionArgs, String sortOrder) {
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        switch (matcher.match(uri)) {
        case PROJECTS:
            String table = uri.getPathSegments().get(0);
            builder.setTables(table);
            break;
        case PROJECT:
            table = uri.getPathSegments().get(0);
            builder.setTables(table);
            selection = "_id=?";
            selectionArgs = new String[] { uri.getPathSegments().get(1) };
            break;
        default:
            throw new IllegalArgumentException("Unknown URL " + uri);
        }
        builder.setProjectionMap(map);

        Cursor cursor = builder.query(mDB.getReadableDatabase(), projection, selection,
                selectionArgs, null, null, sortOrder);

        if (cursor == null) {
            return null;
        }
        cursor.setNotificationUri(getContext().getContentResolver(), uri);
        return cursor;
    }

     public int update(Uri uri, ContentValues cv, String selection, String[] selectionArgs) {
         String table = null;
         SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        switch (matcher.match(uri)) {
         case PROJECTS:
                 table = uri.getPathSegments().get(0);
                 builder.setTables(table);
                 break;
         case PROJECT:
                 table = uri.getPathSegments().get(0);
                 builder.setTables(table);
                 selection = "_id=?";
                 selectionArgs = new String[] { uri.getPathSegments().get(1) };
                 break;
         default:
                 throw new IllegalArgumentException("Unknown URL " + uri);
         }
         return mDB.getWritableDatabase().update(table, cv, selection, selectionArgs);
 }


}

What do I need to change on my above Classes in order to make the new JSON file parsed and saved into the SQLite Database? I'm speaking here since there's no Array on my old JSON file, but I wanna re-use the classes I already have. I know that I need to change the column names and customize the Strings according to my JSON attributes on my SQLite Database and Provider Class

Thank you

Answer 1

You can use Gson or Jackson. These libraries allows you to parse/produce JSON input/output easily building your class "beans".

For example in Gson, if you have got a class named Car built in this way:

class Car{
  int wheels;
  String plate;
}

... and you want to parse an array of cars, you can easily inflate your JSON in this way:

Gson gson = new Gson();
List<Car> cars = gson.fromJson(input, new TypeToken<List<Car>>(){}.getType());

The very cool thing is that it's able to understand that you have contained arrays an parse it without troubles (I'm referring to your input).

Cheers, Simone

Answer 2

If your question is, how to parse the new JSON string, it's pretty straight forward...

To parse a JSON array (anything enclosed in []) you would use...

JSONArray jsonArray = new JSONArray(String json);

and to parse a JSON object (anything enclosed in {}) you would use...

JSONObject jsonObject = new JSONObject(String json);

Now, to parse the JSON string you had specified above, it would be something like...

JSONArray jsonArray = new JSONArray(String input);
JSONObject location = jsonArray.getJSONObject(0).getJSONObject("location");

to get the footages element...

JSONArray footages = location.getJSONArray("footages");

Then you can loop through the footages array and do your processing.

As far as storing the data to the database goes, It may be a good idea to store the footages as a JSON string in the database since it's a JSON array.

Answer 3

Try GSON -

And try to change your code design, hope it will help you.