java - 在 Android 中使用 JDBC 优化 mysql DB 执行
问题描述
我在 android 中有一些代码正在运行对我在线托管的数据库的查询。我在这个活动中使用了 AsyncTask 来执行数据库操作,同样我还有其他活动也有类似的代码和连接过程。我想知道这是否是在android中使用JDBC连接连接到mysql db的最佳方式,或者可以改进
代码在 MainActivity 类中登录大约需要 3 秒。
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);
}
}
类似地,对于其他活动,我也再次以如图所示的类似方式创建连接并关闭它们。
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";
}
}
}
请建议我如何增加执行时间。
解决方案
MySQL 协议在长距离上表现不佳。它只是一个事实。
如果您需要长距离访问 MySQL,您应该在数据库前面放置一个 rest/api,尽可能靠近 MySQL 运行,然后让您的应用程序查询它,例如在其中运行多个 API-Gateways用于故障转移和负载平衡的容器。
[MySQL]-[REST/API Gateway]-----------{Internet}------------[Client]
我从未使用过它,但看起来这种事情会做你想做的事 - 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,它对客户端应用程序可以做什么施加特定于应用程序的安全限制。
推荐阅读
- c++ - 带有 pthread 的 C++ 未定义引用
- c# - 铸造 IList
到 ILI 列表 - php - 使用php上传文件后无法将某些扩展文件的详细信息存储到数据库中
- python - 在看到“@”字符后打印字符串
- c# - MOQ 创建一个接受 8 个参数并返回布尔值的模拟对象
- javascript - 是否可以在 nodejs+puppeteer 中包含源文件来添加功能?
- javascript - 将桌面视图中的模态弹出到移动视图中的新页面的任何想法
- c++ - 虚拟化和复制 Qt 应用程序 20 次
- javascript - 使用键盘箭头更改图像
- python - 使用来自 Dymola 的数据在 Python 中进行敏感性分析(无需从 Dymola 转移模型)