Sending and receiving of JSON data to and from Android Studio

Question

I'm currently developing an app that requires sending and receiving of data from android studio going to MySQL and then data coming from MySQL will be saved from the SQLite. I need advice on how to make the process faster. Right now I'm inserting 80,000 + rows of data coming from MySQL and then saving it to SQLite and that process lasts around 25-30 minutes.

rowItem.php

<?php
require "init.php";
$serial = $_POST['mySerial'];
$sql = "select ITEMCODE, DESCRIPTION, BRAND from items where SERIAL_NO = '" .$serial. "'";
$result = mysqli_query($con, $sql);
$data =array();
while($row = mysqli_fetch_array($result)) {
 $row['ITEMCODE'] = mb_convert_encoding($row['ITEMCODE'], 'UTF-8', 'UTF-8');
 $row['DESCRIPTION'] = mb_convert_encoding($row['DESCRIPTION'], 'UTF-8', 'UTF-8');
 $row['BRAND'] = mb_convert_encoding($row['BRAND'], 'UTF-8', 'UTF-8');
 array_push($data, array('ITEMCODE' => $row['ITEMCODE'], 'DESCRIPTION' => $row['DESCRIPTION'], 'BRAND' => $row['BRAND']));
}
$json = json_encode(array("allItems"=>$data));
echo $json;
?>

DatabaseOperations.java

 public class DatabaseOperations extends SQLiteOpenHelper {
 public static final int dbVersion = 1;
 public String CREATE_ITEMS_TABLE = "CREATE TABLE " + TableData.TableInfo.TB_ITEMS +
 " (" + TableData.TableInfo.COL_ITEMS_ITEMCODE + " VARCHAR(20) PRIMARY KEY NOT NULL, " +
 TableData.TableInfo.COL_ITEMS_DESCRIPTION + " VARCHAR(50), " +
 TableData.TableInfo.COL_ITEMS_BRAND + " VARCHAR(10), " +
 TableData.TableInfo.COL_ITEMS_BARCODE + " VARCHAR(20));";
 public DatabaseOperations(Context context) {
 super(context, TableData.TableInfo.DB_NAME, null, dbVersion);
 } 
 @Override
 public void onCreate(SQLiteDatabase sdb) {
 sdb.execSQL(CREATE_ITEMS_TABLE);
 }
 public void insertItems (DatabaseOperations dop,
 String itemCode, String brand, String desc) {
 SQLiteDatabase sq = dop.getWritableDatabase();
 sq.beginTransaction();
 try {
 ContentValues cv = new ContentValues();
 cv.put(TableData.TableInfo.COL_ITEMS_ITEMCODE, itemCode);
 cv.put(TableData.TableInfo.COL_ITEMS_BRAND, brand);
 cv.put(TableData.TableInfo.COL_ITEMS_DESCRIPTION, desc);
 sq.insert(TableData.TableInfo.TB_ITEMS, null, cv);
 sq.setTransactionSuccessful();
 }
 catch (Exception e) {
 }
 finally {
 sq.endTransaction();
 sq.close();
 }
 }
 }

MainScreen.java

public class MainScreen extends AppCompatActivity {
 Context ctx = this;
 Button btnSync;
 String rowItemURL = "http://192.168.100.118:81/rowItem.php";
 @Override
 protected void onCreate(Bundle savedInstanceState) {
 super.onCreate(savedInstanceState);
 setContentView(R.layout.activity_main_screen);
 btnSync = findViewById(R.id.btnSync);
 btnSync.setOnClickListener(new View.OnClickListener() {
 @Override
 public void onClick(View v) {
 StringRequest itemImeiRequest = new StringRequest(Request.Method.POST, rowItemURL,
 new Response.Listener<String>() {
 @Override
 public void onResponse(String response) {
 try {
 JSONObject jsonObject;
 jsonObject = new JSONObject(response);
 JSONArray itemArray = jsonObject.getJSONArray("allItems");
 for (int i = 0; i < itemArray.length(); i++) {
 itemCode = itemArray.getJSONObject(i).getString("ITEMCODE");
 itemBrand = itemArray.getJSONObject(i).getString("BRAND");
 itemDesc = itemArray.getJSONObject(i).getString("DESCRIPTION");
 DatabaseOperations dop = new DatabaseOperations(ctx);
 dop.insertItems(dop, itemCode, itemBrand, itemDesc);
 }
 Toast.makeText(ctx, "SYNC ITEMS COMPLETED!", Toast.LENGTH_LONG).show();
 lblDebug.setText("SUCCESS!");
 }
 catch (JSONException e) {
 Toast.makeText(ctx, e.getMessage(), Toast.LENGTH_LONG).show();
 lblDebug.setText(e.getMessage() + "500");
 }
 }
 }, new Response.ErrorListener() {
 @Override
 public void onErrorResponse(VolleyError error) {
 Toast.makeText(ctx, error.getMessage(), Toast.LENGTH_LONG).show();
 lblDebug.setText(error.getMessage() + "511");
 }
 }) {
 @Override
 protected Map<String, String> getParams() throws AuthFailureError {
 SharedPreferences sharedPreferencesIMEI = getSharedPreferences(IMEI_PREF, MODE_PRIVATE);
 myIMEI = sharedPreferencesIMEI.getString(TEXT5, "");
 Map<String,String> params = new HashMap<>();
 params.put("mySerial", myIMEI);
 return params;
 }
 };
 MySingleton.getInstance(MainScreen.this).addToRequestQue(itemImeiRequest);
 }
 }
 }
}

MySingleton.java

public class MySingleton {
 private static MySingleton mInstance;
 private RequestQueue requestQueue;
 private static Context mCtx;
 private MySingleton(Context context) {
 mCtx = context;
 requestQueue = getRequestQueue();
 }
 public RequestQueue getRequestQueue(){
 if(requestQueue==null) {
 requestQueue = Volley.newRequestQueue(mCtx.getApplicationContext());
 }
 return requestQueue;
 }
 public static synchronized MySingleton getInstance(Context context) {
 if(mInstance==null) {
 mInstance = new MySingleton(context);
 }
 return mInstance;
 }
 public<T> void addToRequestQue(Request<T> request) {
 requestQueue.add(request);
 }
}

Answer 1

You are using a transaction per insert statement, that will basically do nothing as a single statement on it's own is effectively a transaction.

You want to perform the loop within a single transaction so that the disk is written just the once, rather than for every insert.

I'd suggest changing :-

 for (int i = 0; i < itemArray.length(); i++) {
 itemCode = itemArray.getJSONObject(i).getString("ITEMCODE");
 itemBrand = itemArray.getJSONObject(i).getString("BRAND");
 itemDesc = itemArray.getJSONObject(i).getString("DESCRIPTION");
 DatabaseOperations dop = new DatabaseOperations(ctx);
 dop.insertItems(dop, itemCode, itemBrand, itemDesc);
 } 

to :-

 DatabaseOperations dop = new DatabaseOperations(ctx);
 dop.getWritableDatabase().beginTransaction();
 for (int i = 0; i < itemArray.length(); i++) {
 itemCode = itemArray.getJSONObject(i).getString("ITEMCODE");
 itemBrand = itemArray.getJSONObject(i).getString("BRAND");
 itemDesc = itemArray.getJSONObject(i).getString("DESCRIPTION");
 dop.insertItems(dop, itemCode, itemBrand, itemDesc);
 }
 dop.setTransactionSuccessful();
 dop.endTransaction(); 

Along with changing the insertItems method to :-

 public void insertItems (DatabaseOperations dop,
 String itemCode, String brand, String desc) {
 SQLiteDatabase sq = dop.getWritableDatabase();
 ContentValues cv = new ContentValues();
 cv.put(TableData.TableInfo.COL_ITEMS_ITEMCODE, itemCode);
 cv.put(TableData.TableInfo.COL_ITEMS_BRAND, brand);
 cv.put(TableData.TableInfo.COL_ITEMS_DESCRIPTION, desc);
 sq.insert(TableData.TableInfo.TB_ITEMS, null, cv);
 }

• Note this is in-principle code, it has not been run or tested and may therefore contain some errors.

• Note in regard to SQL Injection, the Android convenience methods, such as insert do provide protection against SQL injection as the values extracted from the ContentValues are appropriately enclosed when the underlying SQL is built by the methods.