1
\$\begingroup\$

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. The sending/recieving of data will happen once the button is clicked.

This is my PHP file

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;

?>

This are my Java codes

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);
    }
}
\$\endgroup\$
0
\$\begingroup\$

just to optimize the PHP part

<?php
$con->set_charset("utf8");

$sql = "select ITEMCODE, DESCRIPTION, BRAND from items where SERIAL_NO = ?";
$stmt = $con->prepare($sql);
$stmt->bind_param("s", $_POST['mySerial']);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

echo json_encode(array("allItems"=>$data));
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.