title: Optimize mysql DB execution using JDBC in Android
body: <p>I have some code in android which is running a query to the database which i have hosted online.
  I have used AsyncTask to perform DB operation in this activity, similarly I have other activities too which are having similar code and connection procedure. I wanted to know if this is the optimal way to connect to mysql db using JDBC connection in android or can this be improved</p>
  <p>The code is taking around 3 sec for login as in MainActivity class.</p>
  <pre><code>public class MainActivity extends AppCompatActivity {
  
  private ProgressDialog mProgress;
  final int REQUEST_PERMISSION_CODE = 1000;
  
  private static final String url = &quot;jdbc:mysql://192.168.0.103/pos&quot;;
  private static final String user = &quot;root&quot;;
  private static final String pass = &quot;&quot;;
  private EditText mPassword, mUsername;
  private Button loginBtn;
  private ProgressBar mLoginProgress;
  private TextView mLoginFeedbackText;
  String password, username;
  ProgressDialog progressDialog;
  Boolean CheckEditText;
  
  
  @Override
  protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_login);
  
      if (!checkPermissionFromDevice())
          requestPermission();
  
      mPassword = findViewById(R.id.password);
      mUsername = findViewById(R.id.username);
      loginBtn = findViewById(R.id.generate_btn);
      mLoginProgress = findViewById(R.id.login_progress_bar);
      mLoginFeedbackText = findViewById(R.id.login_form_feedback);
      mProgress = new ProgressDialog(this);
      
      loginBtn.setOnClickListener(new View.OnClickListener() {
          @Override
          public void onClick(View view) {
              CheckEditTextIsEmptyOrNot();
              if (CheckEditText) {
                  UserLoginFunction(username, password);
              } else {
                  Toast.makeText(MainActivity.this, &quot;Please fill all form fields.&quot;, Toast.LENGTH_LONG).show();
              }
          }
      });
  }
  
  public void CheckEditTextIsEmptyOrNot() {
      username = mUsername.getText().toString();
      password = mPassword.getText().toString();
      if (TextUtils.isEmpty(username) || TextUtils.isEmpty(password)) {
          CheckEditText = false;
      } else {
          CheckEditText = true;
      }
  }
  
  private void requestPermission() {
      ActivityCompat.requestPermissions(this, new String[]{
              Manifest.permission.WRITE_EXTERNAL_STORAGE,
              Manifest.permission.RECORD_AUDIO
      }, REQUEST_PERMISSION_CODE);
  }
  
  private boolean checkPermissionFromDevice() {
      int write_external_storage_result = ContextCompat.checkSelfPermission(this, Manifest.permission.WRITE_EXTERNAL_STORAGE);
      int record_audio_result = ContextCompat.checkSelfPermission(this, Manifest.permission.RECORD_AUDIO);
      return write_external_storage_result == PackageManager.PERMISSION_GRANTED &amp;&amp;
              record_audio_result == PackageManager.PERMISSION_GRANTED;
  }
  public void UserLoginFunction(final String username, final String password) {
      class UserLoginClass extends AsyncTask&lt;String, Void, String&gt; {
          @Override
          protected void onPreExecute() {
              System.out.println(&quot;In onPreExecute&quot;);
              super.onPreExecute();
  
              progressDialog = ProgressDialog.show(MainActivity.this, &quot;Loading Data&quot;, null, true, true);
          }
  
          @Override
          protected void onPostExecute(String httpResponseMsg) {
              System.out.println(&quot;In onPostExecute&quot;);
              super.onPostExecute(httpResponseMsg);
  
              progressDialog.dismiss();
  
              if (httpResponseMsg.equalsIgnoreCase(&quot;It matches&quot;)) {
  
                  finish();
  
                  Intent intent = new Intent(MainActivity.this, StartActivity.class);
                  System.out.println(&quot;USERNAME&quot; + username);
                  intent.putExtra(&quot;USERNAME&quot;, username);
                  startActivity(intent);
  
              } else {
                  mLoginFeedbackText.setText(&quot;Verification Failed, please try again.&quot;);
                  mLoginFeedbackText.setVisibility(View.VISIBLE);
                  mLoginProgress.setVisibility(View.INVISIBLE);
                  loginBtn.setEnabled(true);
                  Toast.makeText(MainActivity.this, httpResponseMsg, Toast.LENGTH_LONG).show();
              }
  
          }
  
          @Override
          protected String doInBackground(String... params) {
              System.out.println(&quot;In doInBackground&quot;);
              try {
                  StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
                  StrictMode.setThreadPolicy(policy);
  
                  Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
                  Connection con = DriverManager.getConnection(url, user, pass);
  
                  Statement st = con.createStatement();
                  ResultSet rs = st.executeQuery(&quot;SELECT * FROM `users` WHERE username='BobMartin'&quot;);
  
  
                  while (rs.next()) {
                      String queryPassword = rs.getString(&quot;password&quot;);
                      String hash_php = queryPassword.replaceFirst(&quot;2y&quot;, &quot;2a&quot;);
                      if (BCrypt.checkpw(password, hash_php)) {
                          con.close();
                          System.out.println(&quot;It matches&quot;);
                          return &quot;It matches&quot;;
                      } else {
                          System.out.println(&quot;It does not match&quot;);
                          return &quot;It does not match&quot;;
                      }
                  }
              } catch (ClassNotFoundException | SQLException e) {
                  e.printStackTrace();
                  System.out.println(&quot;result in catch&quot;);
              }
              return &quot;It does not match&quot;;
          }
      }
      UserLoginClass userLoginClass = new UserLoginClass();
      userLoginClass.execute(username, password);
  }
  
  
  }
  </code></pre>
  <p>Similarly for other activity also im again creating connection and closing them in the similar manner as shown.</p>
  <pre><code>public class StartActivity extends AppCompatActivity {
  Button startButton;
  String userName;
  private static final String url = &quot;jdbc:mysql://192.168.0.103/pos&quot;;
  private static final String user = &quot;root&quot;;
  private static final String pass = &quot;&quot;;
  
  ArrayList&lt;String&gt; dbQuestions = new ArrayList&lt;String&gt;();
  ArrayList&lt;String&gt; dbAnswers = new ArrayList&lt;String&gt;();
  
  ProgressDialog progressDialog;
  
  @Override
  protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_main);
      userName = getIntent().getStringExtra(&quot;USERNAME&quot;);
      new StartDb().execute();
  
      startButton = findViewById(R.id.startButton);
      startButton.setOnClickListener(new View.OnClickListener() {
          @Override
          public void onClick(View v) {
              System.out.println(&quot;USERNAMEstart&quot; + userName);
              Intent intent = new Intent(StartActivity.this, BillActivity.class);
              Bundle args = new Bundle();
              args.putSerializable(&quot;ANSWERS&quot;, (Serializable) dbAnswers);
              args.putSerializable(&quot;QUESTIONS&quot;, (Serializable) dbQuestions);
              intent.putExtra(&quot;USERNAME&quot;, userName);
              intent.putExtra(&quot;BUNDLE&quot;, args);
              startActivity(intent);
          }
      });
  }
  
  @Override
  public void onBackPressed() {
      // super.onBackPressed();
      Toast.makeText(StartActivity.this, &quot;There is no back action&quot;, Toast.LENGTH_LONG).show();
      return;
  }
  
  class StartDb extends AsyncTask&lt;String, Void, String&gt; {
  
      @Override
      protected void onPreExecute() {
          System.out.println(&quot;In onPreExecute&quot;);
          super.onPreExecute();
  
          progressDialog = ProgressDialog.show(StartActivity.this, &quot;Loading Data&quot;, null, true, true);
      }
  
      @Override
      protected void onPostExecute(String httpResponseMsg) {
          System.out.println(&quot;In onPostExecute&quot;);
          super.onPostExecute(httpResponseMsg);
          progressDialog.dismiss();
      }
  
      @Override
      protected String doInBackground(String... params) {
          System.out.println(&quot;In doInBackground&quot;);
          try {
              StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
              StrictMode.setThreadPolicy(policy);
  
              Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
              Connection con = DriverManager.getConnection(url, user, pass);
  
              Statement st = con.createStatement();
              ResultSet rs = st.executeQuery(&quot;SELECT * FROM `company_details`&quot;);
  
              while (rs.next()) {
  
                  String que = rs.getString(&quot;questions&quot;);
                  JSONObject obj1 = new JSONObject(que);
                  for (Iterator&lt;String&gt; it = obj1.keys(); it.hasNext(); ) {
                      String key = it.next();
                      dbQuestions.add(obj1.getString(key));
                  }
  
                  String ans = rs.getString(&quot;answers&quot;);
                  JSONObject obj2 = new JSONObject(ans);
                  for (Iterator&lt;String&gt; it = obj2.keys(); it.hasNext(); ) {
                      String key = it.next();
                      dbAnswers.add(obj2.getString(key));
                  }
  
                  con.close();
              }
          } catch (ClassNotFoundException | SQLException | JSONException e) {
              e.printStackTrace();
              System.out.println(&quot;resilt in catch&quot;);
          }
          return &quot;It does not match&quot;;
      }
  }
  
  }
  </code></pre>
  <p>Please suggest how can i increase execution time.</p>