Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Parsing JSON and save them on SQLite Database

Tags:

java

json

android

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

like image 758
hectichavana Avatar asked Apr 05 '12 10:04

hectichavana


People also ask

Can we store JSON data in SQLite?

SQLite stores JSON as ordinary text. Backwards compatibility constraints mean that SQLite is only able to store values that are NULL, integers, floating-point numbers, text, and BLOBs. It is not possible to add a sixth "JSON" type. SQLite does not (currently) support a binary encoding of JSON.

Can we save JSON in database?

You can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database.

Which database is best for storing JSON data?

The best database for JSON A JSON database like MongoDB stores the data in a JSON-like format (binary JSON), which is the binary encoded version of JSON, and is optimized for performance and space. This makes the MongoDB database the best natural fit for storing JSON data.


2 Answers

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

like image 160
Simone Casagranda Avatar answered Oct 29 '22 15:10

Simone Casagranda


Try GSON -

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

like image 41
Suvam Roy Avatar answered Oct 29 '22 15:10

Suvam Roy