how to bulk insert in sqlite in android

Question

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 we insert multiple records at a time ? any one help me. thank you in advance. code:

public class DatabaseHandler extends SQLiteOpenHelper {
    SQLiteDatabase db;
    private static final int DATABASE_VERSION = 8;
    private static final String TABLE_CITY = "CITYDETAILS";
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.mContext = context;
}

public void onCreate(SQLiteDatabase db) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CITY );
    String CREATE_CITY_TABLE = "CREATE TABLE " + TABLE_CITY + "("
                + CityId + " INTEGER," + CityName + " TEXT " + ")";
    db.execSQL(CREATE_CITY_TABLE);
    db.execSQL(CREATE_RechargeTypes_TABLE);
    this.db=db;
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
     // Drop older table if existed
     // Create tables again
     onCreate(db);
 }

 public  void add_city(String cityid,String cityname){
     SQLiteDatabase db = this.getWritableDatabase();
     db.beginTransaction();
     ContentValues values = new ContentValues();
     values.put(CityId, cityid);
     values.put(CityName, cityname);
     db.insert(TABLE_CITY, null, values);
     db.insertWithOnConflict(TABLE_CITY, null, values, SQLiteDatabase.CONFLICT_IGNORE);
     db.setTransactionSuccessful();
     db.endTransaction();
     }
}

activity calss:

try{
            String Status = result.get("Status").getAsString();
            if (TextUtils.equals(Status, "true")) {
                Gson gson = new Gson();

                JsonArray array = result.get("data")
                        .getAsJsonArray();
                Type type = new TypeToken<ArrayList<Cities>>() {
                }.getType();
                setmCities((ArrayList<Cities>) gson.fromJson(array, type));
                for(int i=0;i< array.size();i++) {

                   db.add_city(mCities.get(i).getCityid(),mCities.get(i).getCityname());

                }
            }

Kornher

Answer 1

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

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

EDIT

public void add_cities(List<Cities> list) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues values = new ContentValues();
        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.

Midway

@kartheekij you are inserting only one row per transaction there. I am saying insert ALL the rows in the same transaction. – Midway

that is db class, in my activity class i am sending json data like db.add_city(cityid,cityname); like this...so i will get all the records...but it is taking too much time for insertion. – Kornher

@kartheekij I assume you are doing that in some kind of loop. Put the entire loop in a single transaction. Nothing you've told me so far prevents you from doing that. – Midway

what @Midway suggested that, you should pass the list of cityid and cityname to your add_city function and loop through that list and insert all data. so that you have to beginTransaction and endTransaction only once. right?? – Seneschal

@kartheekij You have many ways to achieve what I am telling you. Make a method that takes the entire ArrayList<Cities> and does one transaction. – Midway

thq,...i will try it now. – Kornher

Answer 2

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

public void add_cities(ArrayList<Cities> list) {
    SQLiteDatabase database = this.getWritableDatabase();
    String sql = "INSERT INTO " + TABLE_NAME + " VALUES(?, ?)";
    SQLiteStatement statement = 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();
    }
}

Blithering

It helped me. I just adapted it to match with my situation. Great! – Melanymelaphyre

Answer 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:

public void add_cities(ArrayList<Cities> list) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            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:

public class Cities {
    public String getCityid() {
        return cityid;
    }

    public void setCityid(String cityid) {
        this.cityid = cityid;
    }

    public String getCityname() {
        return cityname;
    }

    public void setCityname(String cityname) {
        this.cityname = cityname;
    }

    @Expose

    private String cityid="0";

    @Expose
    private String cityname="";

    public Cities(){
    }

}

activity class:

 ArrayList<Cities> mCities;
     protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_splash);
            db = new DatabaseHandler(getApplicationContext());
           executeCircleAndOperatorsList();

        }
      void executeCircleAndOperatorsList() {
     db.ClearTables();
            ServiceClient serviceClient = ServiceUtil.getServiceClient();
            JsonParser jsonParser = new JsonParser();

           mCitiesCallback = new CancelableCallback(citiescallback);

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

            @Override
            public void success(final JsonObject jsonObject, Response response) {
                Thread t=new Thread(new Runnable() {
                    @Override
                    public void run() {
                        parsecities(jsonObject);
                        runOnUiThread(new Runnable() {
                            @Override
                            public void run() {
                                Toast.makeText(getApplicationContext(), "msg msg", Toast.LENGTH_SHORT).show();

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

            }

            @Override
            public void failure(RetrofitError error) {

            }
        };
        public void parsecities(JsonObject result) {
            //Log.v("TAG_RESULT", "" +result.toString());
            try{
                String Status = result.get("Status").getAsString();
                if (TextUtils.equals(Status, "true")) {
                    Gson gson = new Gson();

                    JsonArray array = result.get("data")
                            .getAsJsonArray();
                    Type type = new TypeToken<ArrayList<Cities>>() {
                    }.getType();
                    setmCities((ArrayList<Cities>) gson.fromJson(array, type));
                    long start = System.currentTimeMillis();
                    db.add_cities(mCities);

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



                }
                else if (TextUtils.equals("Status", "false")) {

                    // showToast(operators.getMessage());

                } else {
                    throw new JSONException("Something went wrong ");
                }        }catch(Exception e){
                e.printStackTrace();

            }

        }
      public void setmCities(ArrayList<Cities> mCities) {
            this.mCities = mCities;
        }
    }

Kornher

Database get's locked. How do you address database locking problem? – Knowing

Answer 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.

Lust

Answer 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 String getMultiInsertValues(){
      StringBuilder valueToInsert = new StringBuilder()
//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

Flashlight