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
The code is taking around 3 sec for login as in MainActivity class.
public class MainActivity extends AppCompatActivity {
private ProgressDialog mProgress;
final int REQUEST_PERMISSION_CODE = 1000;
private static final String url = "jdbc:mysql://192.168.0.103/pos";
private static final String user = "root";
private static final String pass = "";
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, "Please fill all form fields.", 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 &&
record_audio_result == PackageManager.PERMISSION_GRANTED;
}
public void UserLoginFunction(final String username, final String password) {
class UserLoginClass extends AsyncTask<String, Void, String> {
@Override
protected void onPreExecute() {
System.out.println("In onPreExecute");
super.onPreExecute();
progressDialog = ProgressDialog.show(MainActivity.this, "Loading Data", null, true, true);
}
@Override
protected void onPostExecute(String httpResponseMsg) {
System.out.println("In onPostExecute");
super.onPostExecute(httpResponseMsg);
progressDialog.dismiss();
if (httpResponseMsg.equalsIgnoreCase("It matches")) {
finish();
Intent intent = new Intent(MainActivity.this, StartActivity.class);
System.out.println("USERNAME" + username);
intent.putExtra("USERNAME", username);
startActivity(intent);
} else {
mLoginFeedbackText.setText("Verification Failed, please try again.");
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("In doInBackground");
try {
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(url, user, pass);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM `users` WHERE username='BobMartin'");
while (rs.next()) {
String queryPassword = rs.getString("password");
String hash_php = queryPassword.replaceFirst("2y", "2a");
if (BCrypt.checkpw(password, hash_php)) {
con.close();
System.out.println("It matches");
return "It matches";
} else {
System.out.println("It does not match");
return "It does not match";
}
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
System.out.println("result in catch");
}
return "It does not match";
}
}
UserLoginClass userLoginClass = new UserLoginClass();
userLoginClass.execute(username, password);
}
}
Similarly for other activity also im again creating connection and closing them in the similar manner as shown.
public class StartActivity extends AppCompatActivity {
Button startButton;
String userName;
private static final String url = "jdbc:mysql://192.168.0.103/pos";
private static final String user = "root";
private static final String pass = "";
ArrayList<String> dbQuestions = new ArrayList<String>();
ArrayList<String> dbAnswers = new ArrayList<String>();
ProgressDialog progressDialog;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
userName = getIntent().getStringExtra("USERNAME");
new StartDb().execute();
startButton = findViewById(R.id.startButton);
startButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
System.out.println("USERNAMEstart" + userName);
Intent intent = new Intent(StartActivity.this, BillActivity.class);
Bundle args = new Bundle();
args.putSerializable("ANSWERS", (Serializable) dbAnswers);
args.putSerializable("QUESTIONS", (Serializable) dbQuestions);
intent.putExtra("USERNAME", userName);
intent.putExtra("BUNDLE", args);
startActivity(intent);
}
});
}
@Override
public void onBackPressed() {
// super.onBackPressed();
Toast.makeText(StartActivity.this, "There is no back action", Toast.LENGTH_LONG).show();
return;
}
class StartDb extends AsyncTask<String, Void, String> {
@Override
protected void onPreExecute() {
System.out.println("In onPreExecute");
super.onPreExecute();
progressDialog = ProgressDialog.show(StartActivity.this, "Loading Data", null, true, true);
}
@Override
protected void onPostExecute(String httpResponseMsg) {
System.out.println("In onPostExecute");
super.onPostExecute(httpResponseMsg);
progressDialog.dismiss();
}
@Override
protected String doInBackground(String... params) {
System.out.println("In doInBackground");
try {
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(url, user, pass);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM `company_details`");
while (rs.next()) {
String que = rs.getString("questions");
JSONObject obj1 = new JSONObject(que);
for (Iterator<String> it = obj1.keys(); it.hasNext(); ) {
String key = it.next();
dbQuestions.add(obj1.getString(key));
}
String ans = rs.getString("answers");
JSONObject obj2 = new JSONObject(ans);
for (Iterator<String> 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("resilt in catch");
}
return "It does not match";
}
}
}
Please suggest how can i increase execution time.
The MySQL protocol does not perform at all well over long distances. Its just a fact.
If you need to access MySQL over a long distance, you should put a rest/api in front of the database, running as physically close to the MySQL as you can, then get your app to query that, e.g. running multiple API-Gateways in containers for fail-over & load-balancing.
[MySQL]-[REST/API Gateway]-----------{Internet}------------[Client]
I've never used it, but looks like this sort of thing will do what you want - https://www.progress.com/odata/mysql
Or this "Auto-generate a REST API from an existing MySQL DB" - https://www.indiehackers.com/product/noco/auto-generate-a-rest-api-from-an-existing-mysql-db--Lt2CGDHrNrZVLZLMpaI
What rest/api you use depends on what back-end languages you feel the most comfortable with.
To be honest, what you really should be doing is putting a application specific rest/api in-front of your database so that, if the database credentials stored in your app get hacked, people can't just trash the database.
i.e. you should have a rest/api that imposes application specific security restrictions on what the client app can do.