英文:
Optimize mysql DB execution using JDBC in Android
问题
public class MainActivity extends AppCompatActivity {
// ... (other imports and class variables)
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_login);
// ... (other code)
loginBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
CheckEditTextIsEmptyOrNot();
if (CheckEditText) {
// Perform the login operation asynchronously
new UserLoginTask().execute(username, password);
} else {
Toast.makeText(MainActivity.this, "Please fill all form fields.", Toast.LENGTH_LONG).show();
}
}
});
}
// ... (other methods)
private class UserLoginTask extends AsyncTask<String, Void, String> {
@Override
protected void onPreExecute() {
progressDialog = ProgressDialog.show(MainActivity.this, "Loading Data", null, true, true);
}
@Override
protected String doInBackground(String... params) {
String username = params[0];
String password = params[1];
String result = "It does not match"; // Default result
try {
// Move the database connection and query logic here
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='" + username + "'");
while (rs.next()) {
String queryPassword = rs.getString("password");
String hash_php = queryPassword.replaceFirst("2y", "2a");
if (BCrypt.checkpw(password, hash_php)) {
result = "It matches";
} else {
result = "It does not match";
}
}
con.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
protected void onPostExecute(String httpResponseMsg) {
progressDialog.dismiss();
if (httpResponseMsg.equalsIgnoreCase("It matches")) {
finish();
Intent intent = new Intent(MainActivity.this, StartActivity.class);
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();
}
}
}
}
public class StartActivity extends AppCompatActivity {
// ... (other imports and class variables)
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// ... (other code)
new StartDbTask().execute();
}
// ... (other methods)
private class StartDbTask extends AsyncTask<Void, Void, Void> {
@Override
protected void onPreExecute() {
progressDialog = ProgressDialog.show(StartActivity.this, "Loading Data", null, true, true);
}
@Override
protected Void doInBackground(Void... params) {
try {
// Move the database connection and query logic here
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()) {
// ... (process data and populate arrays)
}
con.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
protected void onPostExecute(Void aVoid) {
progressDialog.dismiss();
}
}
}
Please note that the provided code assumes that you have the necessary imports and variables declared elsewhere in your code. Also, this code provides a more organized structure by separating the database operations into separate AsyncTask classes, which can improve readability and maintainability. However, if you want to further improve the execution time, you might consider implementing more advanced techniques like connection pooling and optimizing the database queries.
英文:
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.
答案1
得分: 2
MySQL协议在长距离上性能表现不佳。这只是一个事实。
如果您需要远程访问MySQL，您应该在数据库前面放置一个REST/API，尽量在物理上靠近MySQL运行，然后让您的应用程序查询该API，例如在容器中运行多个API网关以实现故障转移和负载平衡。
“[MySQL]-[REST/API网关]-----------{互联网}------------[客户端]”
我从未使用过，但看起来像是这种类型的工具可以满足您的需求 - https://www.progress.com/odata/mysql
或者这个“从现有MySQL数据库自动生成REST API” - https://www.indiehackers.com/product/noco/auto-generate-a-rest-api-from-an-existing-mysql-db--Lt2CGDHrNrZVLZLMpaI
您使用的REST/API取决于您对哪种后端语言感到最舒适。
老实说，您真正应该做的是在数据库之前放置一个特定于应用程序的REST/API，这样，如果存储在您的应用程序中的数据库凭据被黑客入侵，人们就不能随意破坏数据库。
也就是说，您应该拥有一个REST/API，对客户端应用程序可以执行的操作施加特定于应用程序的安全限制。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论