How to bulk paste from Json to Sqlite in Android

Question

How can I insert massive json data coming from the server into the Sqlite database on Android, very efficiently. The method I'm using now is very inefficient, and it takes almost a minute to complete the input of about 2000 entries. Next method:

for (int i = 0; i < jsonObj.length(); i++) { JSONObject itemObj = (JSONObject) jsonObj.get(i); ContentValues value1 = new ContentValues(); ContentValues value2 = new ContentValues(); value2.put(DbHelper.BusinessID,itemObj.getString("BusinessID")); value2.put(DbHelper.LocationID,itemObj.getString("LocationID")); JSONArray list = itemObj.getJSONArray("OfficeDetails"); if (list != null) { for (int k = 0; k < list.length(); k++) { JSONObject elem = list.getJSONObject(k); if (elem != null) { try { value1.put(DbHelper.Office_Code,elem.getInt("Office_Code")); value1.put(DbHelper.Office_District,elem.getInt("Office_District")); db.insert(DbHelper.MessageDetail,null, value1); } catch (Exception e) { e.printStackTrace(); } } db.insert(DbHelper.Message,null, value2); } 

Incoming input is a nested Json array, which itself is nested. Is there a better way to quickly insert a huge amount of data in a very short time?

Answer 1

You can try bulkInsert as follows:

 ContentResolver.bulkInsert (Uri url, ContentValues[] values); //Array of rows to be inserted 

This only works if you intend to use only 1 URI, if you intend to use multiple uris, you should use the applyBatch method in your ContentResolver .

Hope this helps

Answer 2

First create a model class for your json data. Using Gson, you can get the data inside the arraylist. then you can embed data in sqlite using this arraylist. GreenDao is your best option for quick work.

when you get json data in a stream, use the following code:

 Type collectionType = new TypeToken<ArrayList<your_model>>() {}.getType(); Gson gson = new Gson(); ArrayList<your_model> yourModelList = gson.fromJson(stream, collectionType); 

Answer 3

Github has an excellent library called JSQL.

https://github.com/wenchaojiang/JSQL

This makes it easy to save the Persist JSON string and JSON objects to your SQLite in the database.

Answer 4

Create a list of your json data and then use this custom query to insert bulk data:

 /** * insert the bulk data into database * * @param query to insert data into table * @param parameter data to be inserted into table * @return number of rows got inserted */ protected int insertBulk(String query, String[][] parameter) throws SQLiteConstraintException, SQLiteException { int rowCount = -1; SQLiteStatement statement = mSqldb.compileStatement(query); mSqldb.beginTransaction(); try { for (int index = 0; index < parameter.length; index++) { statement.bindAllArgsAsStrings(parameter[index]); statement.execute(); statement.clearBindings(); } rowCount = parameter.length; } finally { mSqldb.setTransactionSuccessful(); mSqldb.endTransaction(); } return rowCount; }