How To Bulk Insert In Sqlite In Android

I am using SQLiteOpenHelper for data insertion. I need to insert 2500 id and 2500 names, So it takes too much time. Please any one help me how to reduce the insertion time. can

Solution 1:

Use a transaction to insert all the rows -- NOT one row per transaction.

SQLiteDatabasedb= ...
db.beginTransaction();
try {
    // do ALL your inserts here
    db.setTransactionSuccessful()
} finally {
    db.endTransaction();
}

EDIT

publicvoidadd_cities(List<Cities> list) {
    SQLiteDatabasedb=this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValuesvalues=newContentValues();
        for (Cities city : list) {
            values.put(CityId, city.getCityid());
            values.put(CityName, city.getCityName());
            db.insert(TABLE_CITY, null, values);
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

ALL inserts, ONE transaction.

Solution 2:

try SQLiteStatement in 2-3 times faster than just beginTransaction()

publicvoidadd_cities(ArrayList<Cities> list) {
    SQLiteDatabasedatabase=this.getWritableDatabase();
    Stringsql="INSERT INTO " + TABLE_NAME + " VALUES(?, ?)";
    SQLiteStatementstatement= database.compileStatement(sql);
    database.beginTransaction();
    try {
        for (Cities c : list) {
            statement.clearBindings();
            statement.bindLong(1, c.getCityId());
            statement.bindLong(2, c.getCityName());
            statement.execute();
        }
        database.setTransactionSuccessful();
    } finally {
        database.endTransaction();
    }
}

Solution 3:

Thank you @karakuri. i pass the list of city id and city name to add_city function and loop through that list and insert all the data. From this we can insert data to database in a small amount of time.

database class:

publicvoidadd_cities(ArrayList<Cities> list) {
        SQLiteDatabasedb=this.getWritableDatabase();
        db.beginTransaction();
        try {
            ContentValuesvalues=newContentValues();
            for (Cities city : list) {
                values.put(CityId, city.getCityid());
                values.put(CityName, city.getCityname());
                db.insert(TABLE_CITY, null, values);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

data class:

publicclassCities {
    publicStringgetCityid() {
        return cityid;
    }

    publicvoidsetCityid(String cityid) {
        this.cityid = cityid;
    }

    publicStringgetCityname() {
        return cityname;
    }

    publicvoidsetCityname(String cityname) {
        this.cityname = cityname;
    }

    @ExposeprivateString cityid="0";

    @ExposeprivateString cityname="";

    publicCities(){
    }

}

activity class:

 ArrayList<Cities> mCities;
     protectedvoidonCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_splash);
            db = newDatabaseHandler(getApplicationContext());
           executeCircleAndOperatorsList();

        }
      voidexecuteCircleAndOperatorsList() {
     db.ClearTables();
            ServiceClientserviceClient= ServiceUtil.getServiceClient();
            JsonParserjsonParser=newJsonParser();

           mCitiesCallback = newCancelableCallback(citiescallback);

            serviceClient.getCities("getCities_v1", mCitiesCallback);
    }
    Callback<JsonObject> citiescallback=newCallback<JsonObject>() {

            @Overridepublicvoidsuccess(final JsonObject jsonObject, Response response) {
                Thread t=newThread(newRunnable() {
                    @Overridepublicvoidrun() {
                        parsecities(jsonObject);
                        runOnUiThread(newRunnable() {
                            @Overridepublicvoidrun() {
                                Toast.makeText(getApplicationContext(), "msg msg", Toast.LENGTH_SHORT).show();

                                Intent intent=newIntent(SplashActivity.this,LoginAcivtiy.class);
                                startActivity(intent);
                            }
                        });
                    }
                });
                t.start();

            }

            @Overridepublicvoidfailure(RetrofitError error) {

            }
        };
        publicvoidparsecities(JsonObject result) {
            //Log.v("TAG_RESULT", "" +result.toString());try{
                StringStatus= result.get("Status").getAsString();
                if (TextUtils.equals(Status, "true")) {
                    Gsongson=newGson();

                    JsonArrayarray= result.get("data")
                            .getAsJsonArray();
                    Typetype=newTypeToken<ArrayList<Cities>>() {
                    }.getType();
                    setmCities((ArrayList<Cities>) gson.fromJson(array, type));
                    longstart= System.currentTimeMillis();
                    db.add_cities(mCities);

                    System.out.println(System.currentTimeMillis() - start);
                    circle_list=db.get_cities();
                    Log.v("TAG_CIRCLELIST",""+circle_list);



                }
                elseif (TextUtils.equals("Status", "false")) {

                    // showToast(operators.getMessage());

                } else {
                    thrownewJSONException("Something went wrong ");
                }        }catch(Exception e){
                e.printStackTrace();

            }

        }
      publicvoidsetmCities(ArrayList<Cities> mCities) {
            this.mCities = mCities;
        }
    }

Solution 4:

Put your queries in string array in string.xml, open them in code with resource handler and perform them in cycle. Before cycle use beginTransaction() and entTransaction() methods to rollback changes in case failure.

Solution 5:

It is late for this answer but i could help the others to insert many rows in a simple way there is a way to insert many rows lets create the values we want to insert (add a loop according to your values)

public StringgetMultiInsertValues(){
      StringBuilder valueToInsert = newStringBuilder()
//start loop
      valueToInsert.append("(")
      valueToInsert.append(id)
      valueToInsert.append("),")
      valueToInsert.append("(")
      valueToInsert.append(name)
      valueToInsert.append("),")
//end of the loop      
      valueToInsert.deleteCharAt(valueToInsert.lastIndexOf(","))
      valueToInsert.append(";")
      return valueToInsert.toString()
}

then we will add this value to sql

String sql = "INSERT INTO " + tableName + " ( id ) VALUES \n" + valueToInsert 
    //value to Insert from the last function

finally we should execute this sql

SQLiteDatabase db = getWritableDatabase();
db.execSQL(sql);

note there is max variables and terms conditions in sqLite you should devide your values by 500 and insert each 500 using the above solution, since there will be more than one sql query i suggest you to use beginTransaction like whats mentioned in the other answers