How to bulk insert from Json to Sqlite in Android

Question

How is it possible to insert bulk json data coming from server into Sqlite database in Android very efficiently. The method I use now is very inefficient and it takes almost a minute to complete insertion of about 2000 records. The method I am following is :

   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);
}

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

score 0 · Answer 1 · answered Oct 16 '14 at 06:15

0

You could try bulkInsert as the following:

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

This only fits if you are going to use only 1 URI, if you are going to use multiple uris, you should use applyBatch method in your ContentResolver.

Hope it helps

answered Oct 16 '14 at 06:15

zozelfelfo

3,776
2
21
35

Can you give an example ? What is Uri ? – Geevarghese Oct 17 '14 at 06:10

score 0 · Answer 2 · answered Oct 16 '14 at 06:44

0

First create the model class of your json data. Using Gson you can get data inside an arraylist. then you can insert data into sqlite using that arraylist. GreenDao is your best option for fast performance.

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

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

answered Oct 16 '14 at 06:44

TheAndroidFreak

74
6

Once I create the arraylist, how will I insert it into the db? Should I use the method I have already used ? – Geevarghese Oct 17 '14 at 06:10
You need to use DB helper. ORMlite is very easy to use. GreenDAO is another option. Using greendao its easy. but u have read about it.once u get it. its much faster. – TheAndroidFreak Oct 17 '14 at 08:03

score 0 · Answer 3 · answered Oct 16 '14 at 06:52

0

There is an excellent library called JSQL available in Github.

https://github.com/wenchaojiang/JSQL

Its makes it so easy to save Persist JSON string and JSON Objects to your SQLite on Database.

answered Oct 16 '14 at 06:52

Pravin Raj

3,439
1
13
13

score -1 · Answer 4 · answered Oct 16 '14 at 06:37

Create a list of your json data and then used this custom query for bulk data insertion:

/**
 * 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;
}

The transaction is not handled correctly; when an error occurs, you must not call `setTransactionSuccessful()`. — CL., Oct 16 '14 at 06:55

How to bulk insert from Json to Sqlite in Android

4 Answers4