Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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());                  }             } 
like image 212
kartheeki j Avatar asked Aug 19 '15 06:08

kartheeki j


1 Answers

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.

like image 63
Karakuri Avatar answered Sep 22 '22 02:09

Karakuri