I have written this code to insert json data in to sqlite in android its working fine but i have 50000+ row to insert so its taking so much time to insert into sqlite database. So how can I insert this data in fastest way please kindly give me the code I am very new in android. thank in advance.
Below i have written my code to insert data
private void insertItemDetails() {
final ProgressDialog loading = ProgressDialog .show(this,"Updating Data From Tally","Please wait");
StringRequest stringRequest=new StringRequest(Request.Method.GET, url,
new Response.Listener<String>() {
@Override
public void onResponse(String response) {
try {
loading.show();
itemDatabaseCon.open();
itemDatabaseCon.delete();
itemDatabaseCon.close();
itemDatabaseCon.open();
itemDatabaseCon.createTable();
int a=response.length();
// boolean b=a.equalsIgnoreCase("no");
Log.d("value", String.valueOf(a));
if (a==2) {
Log.d("inside item if loop ",response);
}
else {
JSONObject jsonObject = new JSONObject(response);
JSONArray array = jsonObject.getJSONArray("posts");
for (int i = 0; i < array.length(); i++) {
JSONObject ob = array.getJSONObject(i);
String stockid = ob.getString("stockid");
String itemname = ob.getString("itemname");
String group = ob.getString("group");
String baseunit = ob.getString("baseunit");
String alternateunit = ob.getString("alternateunit");
String gst = ob.getString("gst");
String hsn = ob.getString("hsn");
String mrp = ob.getString("mrp");
String sdtsellrate = ob.getString("sdtsellrate");
String closingstock = ob.getString("closingstock");
ContentValues contentValues = new ContentValues();
contentValues.put(Constant2.key_itemstockid, stockid);
contentValues.put(Constant2.key_itemname, itemname);
contentValues.put(Constant2.key_itemgroup, group);
contentValues.put(Constant2.key_itembaseunit, baseunit);
contentValues.put(Constant2.key_itemalternateunit, alternateunit);
contentValues.put(Constant2.key_itemgst, gst);
contentValues.put(Constant2.key_itemhsn, hsn);
contentValues.put(Constant2.key_itemmrp, mrp);
contentValues.put(Constant2.key_itemsdtsellrate, sdtsellrate);
contentValues.put(Constant2.key_itemclosingstock, closingstock);
itemDatabaseCon.insert(Constant2.Table_name, contentValues);
}
}
loading.dismiss();
} catch (JSONException e) {
e.printStackTrace();
}
}
}, new Response.ErrorListener() {
@Override
public void onErrorResponse(VolleyError error) {
Log.d("got api error ffff" , error.getMessage());
}
});
RequestQueue requestQueue= Volley.newRequestQueue(this);
requestQueue.add(stringRequest);
}
Here is my database controller code.
public class ItemDatabaseCon {
String TAG = "DBAdapter";
private SQLiteDatabase db;
private ItemDatabaseCon.DBHelper dbHelper;
public ItemDatabaseCon (Context context) {
dbHelper = new ItemDatabaseCon.DBHelper(context);
}
public void open() {
if (null == db || !db.isOpen()) {
try {
db = dbHelper.getWritableDatabase();
} catch (SQLiteException sqLiteException) {
}
}
}
public void close() {
if (db != null) {
db.close();
}
}
public int insert(String table, ContentValues values) {
try {
db = dbHelper.getWritableDatabase();
int y = (int) db.insert(table, null, values);
db.close();
Log.e("Data Inserted", "Item Data Inserted");
Log.e("number of row", y + "");
return y;
} catch (Exception ex) {
Log.e("Error Insert", ex.getMessage().toString());
return 0;
}
}
public void delete() {
db.execSQL("DROP TABLE IF EXISTS " + Constant2.Table_name);
}
public int getCount()
{
db = dbHelper.getWritableDatabase();
String qry="SELECT * FROM "+Constant2.Table_name;
Cursor cursor=db.rawQuery(qry,null);
return cursor.getCount();
}
public void createTable()
{
String create_sql = "CREATE TABLE IF NOT EXISTS " + Constant2.Table_name + "("
+ Constant2.key_id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Constant2.key_itemstockid + " TEXT ," + Constant2.key_itemname + " TEXT ," + Constant2.key_itemgroup + " TEXT ,"
+ Constant2.key_itembaseunit + " TEXT ,"+ Constant2.key_itemalternateunit + " TEXT ,"+ Constant2.key_itemgst + " TEXT ,"
+ Constant2.key_itemhsn + " TEXT ,"+ Constant2.key_itemmrp + " TEXT ,"+ Constant2.key_itemsdtsellrate + " TEXT ,"
+ Constant2.key_itemclosingstock + " TEXT " + ")";
db.execSQL(create_sql);
}
public Cursor getAllRow(String table) {
return db.query(table, null, null, null, null, null, Constant2.key_id);
}
private class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context) {
super(context, Constant2.DB_Name, null, Constant2.Db_Version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String create_sql = "CREATE TABLE IF NOT EXISTS " + Constant2.Table_name + "("
+ Constant2.key_id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Constant2.key_itemstockid + " TEXT ," + Constant2.key_itemname + " TEXT ," + Constant2.key_itemgroup + " TEXT ,"
+ Constant2.key_itembaseunit + " TEXT ,"+ Constant2.key_itemalternateunit + " TEXT ,"+ Constant2.key_itemgst + " TEXT ,"
+ Constant2.key_itemhsn + " TEXT ,"+ Constant2.key_itemmrp + " TEXT ,"+ Constant2.key_itemsdtsellrate + " TEXT ,"
+ Constant2.key_itemclosingstock + " TEXT " + ")";
db.execSQL(create_sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + Constant2.Table_name);
}
}
}
You could do the inserts inside a single SQLite transaction. This would significantly reduce the disk writes from 50000+ to very few.
That is before the loops starts begin a transaction using the SQLiteDatabase's
beginTransaction()method. After the loop has completed (all rows have been inserted) successfully use thesetTransactionSuccessful()method followed by theendTransactionMethod()setTransactionSuccessfulthen the changes would be rolled back (so if you encounter an issue/error and want the changes (inserts) to not be applied use appropriate logic so that thesetTransactionSuccessfulis skipped but that theendTransactionis run)e.g. The following might be suitable:-
Edit
However, considering the insert method the above will have no affect as you are closing the database after an insert. Closing the database and then re-opening it is very costly resource wise.
As such to benefit from running all the inserts in a single transaction you could use :-